Plegamiento de consultas de Power Query

Este artículo se dirige a los modeladores de datos que desarrollan modelos en Power Pivot o Power BI Desktop. Describe qué es Power Query plegado de consultas y por qué es importante en los diseños del modelo de datos. En este artículo también se describen los orígenes de datos y las transformaciones que pueden lograr el plegado de consultas y cómo determinar que las consultas de Power Query se pueden plegar, ya sea totalmente o parcialmente.

El plegado de consultas es la capacidad de una consulta de Power Query para generar una única instrucción de consulta para recuperar y transformar los datos de origen. El motor de mashup de Power Query se esfuerza por lograr el plegado de consultas siempre que sea posible por motivos de eficiencia.

El plegado de consultas es un tema importante para el modelado de datos por varios motivos:

  • Importar tablas de modelo: La actualización de datos tendrá lugar de forma eficaz para importar tablas de modelo (Power Pivot o Power BI Desktop), en términos de uso de recursos y duración de actualización.
  • Tablas de modo de almacenamiento DirectQuery y Dual: Cada tabla de modo de almacenamiento DirectQuery y Dual (solo Power BI) debe basarse en una consulta de Power Query que se pueda plegar.
  • Actualización incremental: La actualización incremental de datos (solo Power BI) será eficaz, en términos de uso de recursos y duración de actualización. De hecho, la ventana de configuración actualización incremental de Power BI le notificará una advertencia si determina que no se puede lograr el plegado de consultas para la tabla. Si no se puede lograr, se anula el objetivo de la actualización incremental. A continuación, el motor de mashup tendría que recuperar todas las filas de origen y, después, aplicar filtros para determinar los cambios incrementales.

El plegado de consultas se puede realizar para una consulta de Power Query completa o para un subconjunto de sus pasos. Cuando no se puede lograr el plegado de consultas (ya sea parcialmente o totalmente), el motor de mashup de Power Query debe compensar mediante el procesamiento de transformaciones de datos. Este proceso puede implicar la recuperación de los resultados de la consulta de origen, que para grandes conjuntos de datos es muy intensivo y lento.

Se recomienda que se esfuerza por lograr la eficiencia en los diseños del modelo asegurándose de que el plegado de consultas se produzca siempre que sea posible.

Orígenes que admiten el plegado

La mayoría de los orígenes de datos que tienen el concepto de lenguaje de consulta admiten el plegado de consultas. Estos orígenes de datos pueden incluir bases de datos relacionales, fuentes de OData (incluidas las listas de SharePoint), Exchange y Active Directory. Sin embargo, los orígenes de datos como los archivos planos, blobs y web normalmente no lo hacen.

Transformaciones que pueden lograr el plegado

Las transformaciones de orígenes de datos relacionales que se pueden plegar son aquellas que se pueden escribir como una única instrucción SELECT. Una instrucción SELECT se puede construir con las cláusulas WHERE, GROUP BY y JOIN adecuadas. También puede contener expresiones de columna (cálculos) que usan funciones integradas comunes admitidas por las bases de datos SQL.

Por lo general, en la lista siguiente se describen las transformaciones que se pueden plegar.

  • Quitar columnas.

  • Cambiar el nombre de las columnas (alias de columna SELECT).

  • Filtrar filas, con valores estáticos o parámetros de Power Query (predicados de cláusula WHERE).

  • Agrupación y resumen (cláusula GROUP BY).

  • Expandir columnas de registro (columnas de clave externa de origen) para lograr una combinación de dos tablas de origen (cláusula JOIN).

  • Combinación no aproximada de consultas plegables basadas en el mismo origen (cláusula JOIN).

  • Anexar consultas plegables basadas en el mismo origen (operador UNION ALL).

  • Agregar columnas personalizadas con lógica simple (expresiones de columna de la instrucción SELECT). La lógica simple implica operaciones no replicadas, posiblemente incluyendo el uso de funciones M que tienen funciones equivalentes en el origen de datos SQL, como las funciones matemáticas o de manipulación de texto. Por ejemplo, las expresiones siguientes devuelven el componente year del valor de la columna OrderDate (para devolver un valor numérico).

    Date.Year([OrderDate])
    
  • Dinamización y anulación de dinamización (operadores PIVOT y UNPIVOT).

Transformaciones que impiden el plegado

Por lo general, en la lista siguiente se describen las transformaciones que impiden el plegado de consultas. Esta lista no está pensada para ser una lista exhaustiva.

  • Combinar consultas basadas en orígenes diferentes.

  • Anexar consultas (union-ing) basadas en orígenes diferentes.

  • Agregar columnas personalizadas con lógica compleja. La lógica compleja implica el uso de las funciones M que no tienen funciones equivalentes en el origen de datos. Por ejemplo, las siguientes expresiones da formato al valor de la columna OrderDate (para devolver un valor de texto).

    Date.ToText([OrderDate], "yyyy")
    
  • Agregar columnas de índice.

Tenga en cuenta que cuando una consulta de Power Query abarca varios orígenes de datos, la incompatibilidad de los niveles de privacidad del origen de datos puede impedir que se realice el plegado de consultas. Para obtener más información, consulte el artículo Power BI Desktop niveles de privacidad.

Determinar cuándo se puede plegar una consulta

En la ventana Editor de Power Query, es posible determinar cuándo se puede plegar una consulta Power Query. En el panel Configuración de consulta, al hacer clic con el botón derecho en el último paso aplicado, si la opción Ver consulta nativa está habilitada (no atenuada), se puede plegar toda la consulta.

Ejemplo de determinación de que Power Query puede lograr el plegado de consultas en Power BI Desktop.

Nota

La opción View Native Query (Ver consulta nativa ) solo está disponible para determinados conectores de generación de base de datos relacionales o SQL. No funciona para conectores basados en OData, por ejemplo, aunque se produzca un plegado en el back-end. La característica Diagnóstico de consultas es la mejor manera de ver qué plegado se ha producido para los conectores que no son de SQL (aunque los pasos que se doblan no se llaman explícitamente), solo verá la dirección URL resultante que se generó.

Para ver la consulta plegada, seleccione la opción Ver consulta nativa . A continuación, se le presentará la consulta nativa que Power Query usará para los datos de origen.

Ejemplo de una consulta nativa en Power BI Desktop.

Si la opción Ver consulta nativa no está habilitada (atenuada), esto es evidencia de que no se pueden plegar todos los pasos de consulta. Sin embargo, podría significar que se puede plegar un subconjunto de pasos. Trabajando hacia atrás desde el último paso, puede comprobar cada paso para ver si la opción Ver consulta nativa está habilitada. Si es así, ha aprendido dónde, en la secuencia de pasos, ya no se pudo lograr el plegado de consultas.

Ejemplo de determinación de que Power Query no puede lograr el plegado de consultas en Power BI Desktop.

Pasos siguientes

Para más información sobre el plegado de consultas y artículos relacionados, consulte los recursos siguientes: