Cómo filtrar por una lista en Power Query (4 métodos)

El filtrado es una tarea común en Power Query. Por lo general, filtramos por un único valor, o tal vez por una pequeña cantidad de valores conocidos. Estos valores de filtro están codificados en el código M subyacente. Por tanto, para cambiar el filtro, tenemos que editar la consulta. Pero, ¿qué pasa si no sabemos por qué elementos queremos filtrar o cuántos elementos hay? Bueno, eso es lo que descubriremos en esta publicación, al ver cómo filtrar por una lista en Power Query.

Tabla de contenido

• Ejemplo
• Comprender el filtrado en Power Query
• Filtrar por una lista
• Lista. Contiene argumentos de sintaxis.
• Convertir la tabla FilterList en una lista
• Modificar la función Table.SelectRows
• Probando la solución
• ¿Tenemos que convertir FilterList en una lista?
• Filtrar para excluir artículos
• Fusionar: enfoque alternativo más rápido
• Merge vs List.Contains: ¿cuál es más rápido?
• Filtrar columnas por una lista
• Filtrado dinámico
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0137 Power Query – Filtrar por list.zip

Tener acceso

Ejemplo

Los datos de ejemplo con los que estamos trabajando en esta publicación son los siguientes:

Todos los ejemplos utilizan datos en tablas de Excel, pero pueden provenir de cualquier fuente. Los nombres de las tablas son:

• Datos: Los datos que queremos filtrar
• FilterList: Los valores por los que queremos filtrar la columna Elemento de la tabla de Datos
• ColumnFilter: se utiliza en el siguiente ejemplo para demostrar cómo filtrar las columnas en la salida.

En el archivo de ejemplo, las tres tablas están cargadas en Power Query.

Comprender el filtrado en Power Query

Comencemos filtrando con la interfaz de usuario para comprender la sintaxis del código M.

Haga clic en el botón de filtro en la parte superior del artículo y seleccione Alpha y Charlie .

El código M para este paso es:

= Table.SelectRows(#"Changed Type", each ([Item] = "Alpha" or [Item] = "Charlie"))

Analicemos esta fórmula

• Table.SelectRow : la fórmula para seleccionar filas en una tabla
• #”Tipo cambiado” : ​​El nombre de la tabla a filtrar (normalmente el nombre del paso anterior)
• cada uno : una palabra clave para indicar que la siguiente sección de código se aplica fila por fila.
• ([Artículo] = “Alfa” o [Artículo] = “Charlie”) : una prueba lógica para verificar si el valor en la columna Artículo es igual a Alfa o Charlie

La prueba lógica es el elemento que debemos cambiar para un filtrado más avanzado.

Si modificamos la fórmula de la siguiente manera, se devuelven todas las filas.

= Table.SelectRows(#"Changed Type", each true)

O, si la fórmula fuera la siguiente, no se devuelve ninguna de las filas:

= Table.SelectRows(#"Changed Type", each false)

Por lo tanto, para filtrar según una lista, necesitamos una fórmula que devuelva Verdadero o Falso para cada fila de la tabla. Se conservan los elementos verdaderos y se excluyen los elementos falsos.

Filtrar por una lista

Para filtrar por una lista, usamos la función List.Contains .

Lista. Contiene argumentos de sintaxis.

List.Contains comprueba si existe un valor dentro de una lista. La sintaxis y los argumentos de List.Contains son los siguientes:

Sintaxis:

List.Contains(list as list, value as any, optional equationCriteria as any) as logical

Argumentos :

• lista: la lista de valores
• valor: el valor para verificar si existe en la lista
• criterios de ecuación: determina cómo se comparan los valores (por ejemplo, si se ignoran mayúsculas o minúsculas)

El argumento de criterios de ecuación puede volverse bastante complejo. Esto está fuera del alcance de esta publicación. Para obtener más información, consulte esto: https://blog.crossjoin.co.uk/2017/01/22/the-list-m-functions-and-the-equationcriteria-argument/

Resultado : la salida de List.Contains es un valor lógico (es decir, Verdadero o Falso)

Más información: Encuentre más información sobre List.Contains aquí: https://learn.microsoft.com/en-us/powerquery-m/list-contains

Convertir la tabla FilterList en una lista

La consulta FilterList es actualmente una tabla; Necesitamos convertir esto en una lista.

Seleccione la consulta FilterList y luego haga clic en Transformar Convertir a lista

Modificar la función Table.SelectRows

Seleccione la consulta de datos y busque el paso Filas filtradas que creamos anteriormente. Modifique la fórmula de la siguiente manera:

= Table.SelectRows(#"Changed Type", each List.Contains(FilterList, [Item]))

• FilterList: el nombre de la consulta de lista
• [Elemento]: El nombre de la columna a filtrar. Cada palabra clave garantiza que esta comparación se realice fila por fila .

La ventana de vista previa muestra el resultado:

Actualmente, el caso se considera en el filtro; “alfa” y “Alfa” se consideran valores diferentes. Para ignorar el caso, agregaríamos un argumento de criterios de ecuación a la fórmula de la siguiente manera:

= Table.SelectRows(#"Changed Type", each List.Contains(FilterList, [Item], Comparer.OrdinalIgnoreCase))

Probando la solución

Cierre y cargue las consultas y regrese a Excel.

Cambie los valores en la tabla FilterList y luego haga clic en Actualizar datos todos .

¡Ta Dah! La consulta se actualiza para mostrar solo los elementos de la lista.

¿Tenemos que convertir FilterList en una lista?

Es posible que tengamos otros usos para la consulta FilterList ; por lo tanto, es posible que no queramos convertirlo en una lista.

En ese escenario, podríamos crear la lista dentro de la función List.Contains; esto evita cambiar la consulta de la tabla FilterList.

= Table.SelectRows(#"Changed Type", each List.Contains(FilterList[Filter List], [Item]))

• FilterList: el nombre de la consulta
• [Lista de filtro]: el nombre de la columna para convertir en una lista

Filtrar para excluir artículos

Otro escenario común es filtrar los elementos de la tabla. Para lograr esto agregamos la palabra not a la función; esto invierte los resultados Verdadero y Falso .

 Table.SelectRows(#"Changed Type", each not List.Contains(FilterList, [Item]))

Fusionar: enfoque alternativo más rápido

Si tenemos un escenario de filtro razonablemente simple, pero muchos datos, podemos usar la transformación de fusión como alternativa.

En la transformación de fusión, suponiendo que tenemos la tabla de datos primero y la tabla FilterList en segundo lugar, usamos una combinación externa derecha para devolver solo los elementos de la tabla de filtro. Vea el ejemplo a continuación:

Una vez que se devuelve la combinación, podemos eliminar la columna combinada ya que no es necesaria. Esto crea el mismo efecto que filtrar por una lista.

Merge vs List.Contains: ¿cuál es más rápido?

Probé los métodos Merge y List.Contains para mayor velocidad; el resultado es sorprendente.

Prueba n.º 1: 100.000 filas de 8 valores únicos, que se filtran por 2 valores:

• Lista.Contiene: 18,2 segundos
• Fusionar con unión exterior derecha: 1,6 segundos

Prueba n.º 2: 100.000 filas de 8 valores únicos, que se filtran por 4 valores:

• Lista.Contiene: 26,3 segundos
• Fusionar con unión exterior derecha: 1,7 segundos

Esto muestra que la fusión es significativamente más rápida.

Filtrar columnas por una lista

Bien, ahora entendemos cómo funciona todo esto. Es hora de llevarlo al siguiente nivel. Filtremos los nombres de las columnas para devolver solo las columnas que queremos.

Convierta la consulta ColumnList en una lista seleccionándola y luego haciendo clic en Transformar Convertir a lista .

En la consulta de datos , seleccione algunas columnas y luego haga clic en Inicio Quitar columnas (desplegable) Quitar otras columnas .

El código M para el paso se ve así:

= Table.SelectColumns(#"Filtered Rows",{"Date", "Item", "Model"})

El código M enumera las columnas que conservamos. Para hacer esto dinámico, solo necesitamos reemplazar la lista de valores codificados con nuestra consulta ColumnList .

= Table.SelectColumns(#"Filtered Rows",ColumnList)

Esto también coloca las columnas en el mismo orden que la lista.

Nota: Recomiendo realizar este paso al final, porque si otros pasos hacen referencia a columnas que se han eliminado, se producirá un error.

Filtrado dinámico

Bien, ahora nuestro ejemplo final. Sugerimos que queremos devolver solo las filas de los 3 modelos principales. Sin embargo, con el tiempo, a medida que nuestros datos cambien, también lo harán los 3 modelos principales. Nuestra lista para filtrar no es fija. Por lo tanto, debemos hacer que esto sea completamente dinámico.

Seleccione la columna Modelo y luego haga clic en Transformar grupo por .

En el cuadro de diálogo Agrupar por , ingrese lo siguiente:

• Nuevo nombre de columna: Total
• Operación: Suma
• Columna: Valor

Haga clic en Aceptar para cerrar el cuadro de diálogo Agrupar por y mostrar los valores resumidos.

Para ordenar los valores, seleccione la columna Total y luego haga clic en Inicio ZA .

A continuación, debemos conservar solo las 3 filas superiores. Haga clic en Inicio Mantener filas (desplegable) Mantener filas superiores

En el cuadro de diálogo Mantener filas superiores , ingrese 3 en el campo de número de filas y luego haga clic en Aceptar .

Seleccione la columna Modelo y luego haga clic en Transformar Convertir en lista .

Haga clic en el icono fx en la barra de fórmulas. Ingrese la siguiente fórmula:

= Table.SelectRows(#"Changed Type", each List.Contains(Model, [Model]))

• #”Tipo modificado” : ​​el nombre del paso donde los datos completos estuvieron disponibles por última vez.
• Modelo : el nombre del paso anterior, que creó la lista.
• [Modelo] : el nombre de la columna por la que filtrar

La ventana de vista previa muestra lo siguiente:

Ahora tenemos una lista de filtros dinámicos. Cuando los datos de origen cambian, se vuelve a calcular la lista de los 3 modelos principales. Esa lista dinámica se utiliza para filtrar la tabla.

Conclusión

En esta publicación, hemos visto cómo filtrar por una lista en Power Query. Esto se logró utilizando la función List.Contains que devuelve un resultado Verdadero o Falso para cada fila de una tabla. También utilizamos la unión exterior derecha de la transformación de fusión para crear un efecto de filtrado; esto fue significativamente más rápido que List.Contains.

Finalmente, exploramos cómo filtrar los nombres de las columnas y cómo crear un filtro dinámico para mostrar los resultados de los n elementos principales.

Artículos Relacionados

• Power Query: valor de búsqueda en otra tabla con combinación
• Cómo introducir datos en Power Query: 5 fuentes de datos comunes
• Errores comunes de Power Query cómo solucionarlos

---