PL-300: cómo optimizar consultas Power Query para la preparación de datos
Voy a enseñar una competencia del dominio "Preparar los datos" del PL-300: cómo optimizar consultas en Power Query (M). Esta técnica es crucial en el examen y en la práctica porque consultas ineficientes hacen que los informes sean lentos, aumentan costes y dificultan el mantenimiento de los modelos.
Qué necesitas saber
Power Query (editor de consultas) transforma datos antes de que entren en el modelo. Optimizar significa reducir trabajo innecesario, ejecutar operaciones en la fuente cuando sea posible y escribir pasos que el motor pueda “fold” (query folding). Query folding es el proceso por el cual Power Query convierte pasos M en instrucciones nativas de la fuente (por ejemplo, SQL) para que el servidor de origen ejecute el trabajo, no tu equipo.
Ejemplo: si cargas una tabla SQL con millones de filas y a continuación aplicas un filtro por fecha y eliminas columnas antes de cargar, lo ideal es que esos pasos se traduzcan a la consulta SQL y se ejecuten en el servidor. Si algún paso impide el query folding, toda la transformación puede ejecutarse localmente, causando lentitud.
Cómo funciona / Puntos clave prácticos
Sigue un paso a paso para analizar y optimizar una consulta en Power Query.
- Identifica fuentes con soporte de query folding: bases SQL, Azure SQL, Synapse, OData y algunas otras soportan folding. Fuentes como archivos CSV generalmente no soportan.
- Aplica filtros y elimina columnas pronto: coloca pasos de filtrado y eliminación de columnas justo después de la referencia a la fuente. Esto reduce el volumen de datos y aumenta la probabilidad de folding.
- Usa transformaciones soportadas: operaciones como Filter Rows, Remove Columns, Rename Columns, Group By (dependiendo), y Select Columns típicamente hacen folding; operaciones complejas en M (p. ej.: funciones personalizadas, List.Transform sobre cada fila) rompen el folding.
- Verifica el estado del query folding: en Power Query, haz clic con el botón derecho en un paso y busca "View Native Query" / "Ver Consulta Nativa". Si está disponible, el paso hasta ahí está haciendo folding.
- Evita pasos que forcen la materialización prematura: usar Table.Buffer, insertar columnas con funciones invocadas fila por fila, o unir con fuentes que no soportan folding puede impedir el folding.
- Combina operaciones en el servidor cuando sea posible: para origen SQL, prefieres que el WHERE, SELECT y JOIN se procesen en el servidor. Si necesitas una transformación compleja, considera crear una vista/CTE en la base de datos y leer esa vista en lugar de aplicar la lógica en Power Query.
Ejemplo práctico simple (M) — aplica filtro y selecciona columnas pronto:
// Passo de ligação à fonte
Fonte = Sql.Database("servidor", "BD"),
Tabela = Fonte{[Schema="dbo",Item="Vendas"]}[Data],
// Aplicar filtro por ano imediatamente
FiltrarAno = Table.SelectRows(Tabela, each Date.Year([DataVenda]) = 2024),
// Remover colunas desnecessárias
Colunas = Table.SelectColumns(FiltrarAno, {"DataVenda","ProdutoID","Valor"})
Si haces clic con el botón derecho en "Colunas" y aparece "View Native Query", las operaciones anteriores se están convirtiendo a SQL en el servidor. Mantén ese patrón: filtrar y reducir columnas antes de operaciones más pesadas.
Errores comunes
Aquí tienes 3 trampas típicas.
- Realizar transformaciones complejas pronto: usar funciones personalizadas que iteran filas justo tras la fuente impide el folding. En lugar de eso, intenta simplificar o mover la lógica a la fuente.
- Table.Buffer sin necesidad: materializar la tabla localmente puede parecer resolver problemas, pero consume memoria e impide optimizaciones; úsalo solo cuando sea estrictamente necesario y con tablas pequeñas.
- Ignorar el View Native Query: muchos candidatos no verifican si los pasos foldan. Si un paso crítico no tiene Native Query, el resto de la cadena también puede dejar de hacer folding — reordena o reescribe pasos.
Cómo practicar
Practica con fuentes que soporten query folding (por ejemplo Azure SQL o SQL Server local). Crea escenarios: una tabla grande, varios pasos M y examina cuándo se pierde el folding. Usa el botón derecho para ver la consulta nativa y cambia la secuencia de pasos hasta que el folding se mantenga.
Para la preparación del examen, utiliza el Practice Assessment OFICIAL y gratuito de Microsoft y la study guide oficial (también gratuita). Estos recursos muestran las áreas evaluadas y ayudan a centrarte en las competencias sin recurrir a material no autorizado.
En resumen
- Query folding envía trabajo a la fuente; es esencial para el rendimiento con grandes volúmenes.
- Aplica filtros y elimina columnas de inmediato para favorecer el folding.
- Verifica "View Native Query" para confirmar que los pasos hacen folding; reordena/reescribe pasos si es necesario.
- Evita Table.Buffer y transformaciones fila a fila innecesarias; cuando sea preciso, considera mover la lógica a la fuente o crear vistas.