Información general sobre el plegado y la evaluación de consultas en Power Query

En este artículo se proporciona información general básica sobre cómo se procesan y convierten las consultas M en solicitudes de origen de datos.

Script M de Power Query

Cualquier consulta, sin importar si la ha creado Power Query, si la ha escrito usted manualmente en el editor avanzado o si se ha introducido mediante un documento en blanco, consta de funciones y sintaxis del lenguaje de fórmulas M de Power Query. El motor de Power Query interpreta y evalúa esta consulta para generar sus resultados. El script M actúa como el conjunto de instrucciones necesarias para evaluar la consulta.

Sugerencia

Puede considerar el script M como una receta que describe cómo preparar los datos.

La forma más habitual de crear un script M es mediante el editor de Power Query. Por ejemplo, cuando se conecta a un origen de datos, como una base de datos de SQL Server, observe en el lado derecho de la pantalla que hay una sección denominada pasos aplicados. En esta sección se muestran todos los pasos o transformaciones usados en la consulta. En este sentido, el editor de Power Query actúa como una interfaz para ayudarle a crear el script M adecuado para las transformaciones que usted busca y además garantiza que el código que usted utilice sea válido.

Nota:

El script M se usa en el editor de Power Query para:

• Mostrar la consulta como una serie de pasos y permitir la creación o modificación de nuevos pasos.
• Mostrar una vista de diagrama.

La imagen anterior resalta la sección pasos aplicados, que contiene los pasos siguientes:

• Origen: establece la conexión con el origen de datos. En este caso, es una conexión a una base de datos de SQL Server.
• Navegación: navega a una tabla específica de la base de datos.
• Otras columnas quitadas: selecciona las columnas de la tabla que se van a conservar.
• Filas ordenadas: ordena la tabla con una o varias columnas.
• Filas superiores mantenidas: filtra la tabla para mantener solo algunas filas de la parte superior de la tabla.

Este conjunto de nombres de paso es una manera fácil de ver el script M que Power Query creó automáticamente. Hay varias formas de ver el script M completo. En Power Query, puede seleccionar Editor avanzado en la pestaña Vista. También puede seleccionar Editor avanzado en el grupo Consulta de la pestaña Inicio. En algunas versiones de Power Query, también puede cambiar la vista de la barra de fórmulas para mostrar el script de consulta; para ello, diríjase a la pestaña Vista y, en el grupo Diseño, seleccione Vista de script>Script de la consulta.

La mayoría de los nombres que se encuentran en el panel de Pasos aplicados también se usan tal como están en el script M. Los pasos de una consulta se denominan mediante lo que se conoce como identificadores en el lenguaje M. En ocasiones, los caracteres adicionales se encapsulan alrededor de los nombres de los pasos en M, pero estos caracteres no se muestran en los pasos aplicados. Un ejemplo es #"Kept top rows", que se clasifica como un identificador entre comillas debido a estos caracteres adicionales. Se puede utilizar un identificador entre comillas para permitir que cualquier secuencia de cero o más caracteres Unicode se use como identificador, incluidas las palabras clave, los espacios en blanco, los comentarios, los operadores y los signos de puntuación. Para obtener más información sobre los identificadores en el lenguaje M, diríjase a estructura léxica.

Los cambios que realice en la consulta a través del editor de Power Query actualizan automáticamente el script M de la consulta. Por ejemplo, con la imagen anterior como punto de partida, si cambia el nombre del paso Mantener las filas superiores para que sea Las 20 primeras filas, este cambio se actualiza automáticamente en la vista de script.

Aunque se recomienda usar el editor de Power Query para crear todo o la mayoría del script M de forma automática, puede agregar o modificar manualmente partes del script M. Para obtener más información sobre el lenguaje M, diríjase al sitio oficial de documentación sobre el idioma M.

Nota:

El script M, también conocido como código M, es un término que se usa para cualquier código que use el lenguaje M de Power Query. En el contexto de este artículo, el script M también hace referencia al código que se encuentra dentro de una consulta de Power Query y al que se puede acceder a través de la ventana del editor avanzado o a través de la vista de script en la barra de fórmulas.

Evaluación de consultas en Power Query

En el siguiente diagrama se explora el proceso que se produce cuando se evalúa una consulta en Power Query.

1. El script M, que se encuentra en el editor avanzado, se envía al motor de Power Query. También se incluye otra información importante, como las credenciales y los niveles de privacidad del origen de datos.
2. Power Query determina qué datos se deben extraer del origen de datos y envía una solicitud a dicho origen de datos.
3. El origen de datos responde a la solicitud de Power Query mediante la transferencia de los datos solicitados a Power Query.
4. Power Query recibe los datos entrantes del origen de datos y, si fuera necesario, realiza cualquier transformación mediante el motor de Power Query.
5. Los resultados derivados del punto anterior se cargan en un destino.

Nota:

Aunque en este ejemplo se muestra una consulta con una instancia de SQL Database como origen de datos, el concepto se aplica tanto a las consultas con origen de datos como sin él.

Cuando Power Query lee el script M, lo ejecuta a través de un proceso de optimización para evaluar de forma más eficaz la consulta. En este proceso, se determina qué pasos (transformaciones) de la consulta se pueden descargar en el origen de datos. También se determina qué otros pasos deben evaluarse mediante el motor de Power Query. Este proceso de optimización se denomina plegado de consultas, donde Power Query intenta insertar la mayor parte de la posible ejecución en el origen de datos para optimizar la ejecución de la consulta.

Importante

Se siguen todas las reglas del lenguaje de fórmulas M de Power Query (también conocido como el lenguaje M). En particular, la evaluación diferida desempeña un papel importante durante el proceso de optimización. En este proceso, Power Query entiende qué transformaciones específicas de la consulta deben evaluarse. Power Query también entiende qué otras transformaciones no necesitan evaluarse porque no son necesarias en la salida de la consulta.

Además, cuando intervienen varios orígenes, se tiene en cuenta el nivel de privacidad de los datos de cada origen de datos al evaluar la consulta. Más información: Firewall de privacidad de datos en segundo plano

En el siguiente diagrama se muestran los pasos que tienen lugar en este proceso de optimización.

1. El script M, que se encuentra en el editor avanzado, se envía al motor de Power Query. También se proporciona otra información importante, como las credenciales y los niveles de privacidad del origen de datos.
2. El mecanismo de plegado de consultas envía solicitudes de metadatos al origen de datos para determinar las funcionalidades de dicho origen de datos, de los esquemas de tabla, de las relaciones entre tablas diferentes en el origen de datos, etc.
3. En función de los metadatos recibidos, el mecanismo de plegado de consultas determina qué información extraer del origen de datos y qué conjunto de transformaciones deben producirse dentro del motor de Power Query. Envía las instrucciones a otros dos componentes que se encargan de recuperar los datos del origen de datos así como de transformar, si fuera necesario, los datos entrantes en el motor de Power Query.
4. Una vez que los componentes internos de Power Query reciben las instrucciones, Power Query envía una solicitud al origen de datos mediante una consulta de origen de datos.
5. El origen de datos recibe la solicitud de Power Query y transfiere los datos al motor de Power Query.
6. Una vez que los datos están dentro de Power Query, el motor de transformación de Power Query (también conocido como motor de mashup) realizará las transformaciones que no se pudieron plegar ni descargar en el origen de datos.
7. Los resultados derivados del punto anterior se cargan en un destino.

Nota:

Según las transformaciones y el origen de datos utilizados en el script M, Power Query determina si transmite o almacena en búfer los datos entrantes.

Introducción al plegado de consultas

El objetivo del plegado de consultas es descargar o insertar la mayor parte de la evaluación de una consulta en un origen de datos que pueda procesar las transformaciones de la consulta.

El mecanismo de plegado de consultas logra este objetivo al traducir el script M a un lenguaje que el origen de datos pueda interpretar y ejecutar. A continuación, inserta la evaluación en el origen de datos y envía el resultado de esa evaluación a Power Query.

Esta operación suele proporcionar una ejecución de consulta más rápida que extraer todos los datos necesarios del origen de datos y ejecutar todas las transformaciones necesarias en el motor de Power Query.

Cuando utilice la experiencia "Obtener datos", Power Query le guiará por el proceso que, una vez completado, le permitirá conectarse al origen de datos. Al hacerlo, Power Query utiliza una serie de funciones en el lenguaje M clasificadas como funciones de acceso a datos. Estas funciones específicas utilizan mecanismos y protocolos para conectarse al origen de datos mediante un lenguaje que el origen de datos pueda reconocer.

No obstante, los pasos siguientes en la consulta son los pasos o transformaciones que el mecanismo de plegado de consultas intenta optimizar. A continuación, se comprueba si se pueden descargar en el origen de datos en lugar de procesarse mediante el motor de Power Query.

Importante

Todas las funciones de origen de datos, que normalmente se muestran como el paso Origen de una consulta, consulta los datos en el origen de datos en su lenguaje nativo. El mecanismo de plegado de consultas se utiliza en todas las transformaciones aplicadas a la consulta después de la función del origen de datos para que se puedan traducir y combinar en una sola consulta de origen de datos o en tantas transformaciones que se puedan descargar en el origen de datos.

Según cómo se estructure la consulta, podría haber tres resultados posibles en el mecanismo de plegado de consultas:

• Plegado de consultas completo: cuando todas las transformaciones de consulta se devuelven al origen de datos y se produce un procesamiento mínimo en el motor de Power Query.
• Plegado parcial de consultas: cuando solo unas pocas transformaciones de la consulta, y no todas, se pueden devolver al origen de datos. En este caso, solo se realiza un subconjunto de las transformaciones en el origen de datos y el resto de las transformaciones de consulta se producen en el motor de Power Query.
• Sin plegado de consultas: cuando la consulta contiene transformaciones que no se pueden traducir al lenguaje de consulta nativo del origen de datos, ya sea porque las transformaciones no son compatibles o porque el conector no admite el plegado de consultas. En este caso, Power Query obtiene los datos sin procesar del origen de datos y utiliza el motor de Power Query para lograr la salida que usted desea mediante el procesamiento de las transformaciones necesarias en el nivel del motor de Power Query.

Nota:

El mecanismo de plegado de consultas está disponible principalmente en conectores para orígenes de datos estructurados, entre los que se incluyen, entre otros, Microsoft SQL Server y la fuente de OData. Durante la fase de optimización, el motor puede reordenar a veces los pasos de la consulta.

Aprovechar un origen de datos que tenga más recursos de procesamiento y que cuente con funcionalidades de plegado de consultas puede acelerar los tiempos de carga de consultas, ya que el procesamiento se produciría en el origen de datos y no en el motor de Power Query.

Contenido relacionado

Para obtener ejemplos detallados de los tres resultados posibles del mecanismo de plegado de consultas, diríjase a Ejemplos de plegado de consultas.

Para obtener información sobre los indicadores de plegado de consultas que se encuentran en el panel de Pasos aplicados, diríjase a Indicadores de plegado de consultas.