Plegamiento de consultas de Power Query

Este artículo está dirigido a modeladores de datos que desarrollan modelos en Power Pivot o Power BI Desktop. En él se describe qué es el plegado de consultas de Power Query 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 puedan plegar, ya sea por completo 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 datos de origen. El motor de mashup 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 varias razones:

  • Importar tablas de modelos: 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 del modo de almacenamiento Dual y DirectQuery: cada tabla de modo de almacenamiento dual y DirectQuery (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 de 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 puede lograrse, el objetivo de la actualización incremental queda frustrado. El motor de mashup tendría que recuperar todas las filas de origen y, a continuación, aplicar filtros para determinar los cambios incrementales.

El plegado de consultas puede producirse para una consulta completa de Power Query o para un subconjunto de sus pasos. Cuando no se puede lograr el plegado de consultas (parcial o totalmente), el motor de mashup de Power Query debe compensarse 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.

Le recomendamos que se esfuerce por lograr la eficiencia en sus diseños de modelos, 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 listas de SharePoint), Exchange y Active Directory. Sin embargo, los orígenes de datos como archivos planos, blobs y web normalmente no lo hacen.

Transformaciones que pueden lograr el plegado

Las transformaciones de orígenes de datos relacionales que pueden plegarse en consultas son aquellas que pueden escribirse 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 pueden plegarse en consultas.

  • Eliminar columnas.

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

  • Filtrado de 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 SELECT). La lógica simple implica operaciones poco complicadas, posiblemente incluyendo el uso de funciones M que tienen funciones equivalentes en el origen de datos SQL, como funciones matemáticas o de manipulación de texto. Por ejemplo, las expresiones siguientes devuelven el componente de año del valor de columna OrderDate (para devolver un valor numérico).

    Date.Year([OrderDate])
    
  • Dinamizar y anular la 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 pretende ser exhaustiva.

  • Combinar consultas basadas en orígenes diferentes.

  • Anexar (unir) consultas basadas en orígenes diferentes.

  • Agregar columnas personalizadas con lógica compleja. La lógica compleja implica el uso de funciones M que no tienen funciones equivalentes en el origen de datos. Por ejemplo, las expresiones siguientes formatearán el 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 produzca el plegado de consultas. Para obtener más información, consulte el artículo Niveles de privacidad de Power BI Desktop.

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 de 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.

Example of determining that Power Query can achieve query folding in Power BI Desktop.

Nota:

La opción Ver consulta nativa solo está disponible para determinados conectores de generación de DB/SQL relacionales. 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 SQL (aunque los pasos que plegan no se indican explícitamente), solo se ve la URL resultante que se ha generado.

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.

Example of a native query in Power BI Desktop.

Si la opción Ver consulta nativa no está habilitada (atenuada), indica que no se pueden plegar todos los pasos de consulta. Sin embargo, podría significar que todavía se puede plegar un subconjunto de pasos. Continuando desde el último paso, puede comprobar cada paso para ver si la opción Ver consulta nativa está habilitada. Si es así, sabrá en qué punto de la secuencia de pasos ya no se podía lograr ese plegado de la consulta.

Example of determining that Power Query cannot achieve query folding in Power BI Desktop.

Pasos siguientes

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