Plegado de consultas en consultas nativas

En Power Query, puede definir una consulta nativa y ejecutarla en el origen de datos. En el artículo Importación de datos desde una base de datos mediante una consulta de base de datos nativa se explica cómo realizar este proceso con varios orígenes de datos. Sin embargo, mediante el proceso descrito en ese artículo, la consulta no aprovechará ningún plegado de consultas de los pasos de consulta posteriores.

En este artículo se muestra un método alternativo para crear consultas nativas en el origen de datos mediante la función Value.NativeQuery y mantener el mecanismo de plegado de consultas activo para los pasos posteriores de la consulta.

Nota:

Se recomienda leer la documentación sobre el plegado de consultas y los indicadores de plegado de consultas para comprender mejor los conceptos usados en este artículo.

Conectores de datos admitidos

El método descrito en las secciones siguientes se aplica a los siguientes conectores de datos:

• Amazon Redshift
• Dataverse(cuando se usa un proceso mejorado)
• Google BigQuery
• PostgreSQL
• SAP HANA
• Snowflake
• SQL Server

Conexión al destino desde el origen de datos

Nota:

Para mostrar este proceso, en este artículo se usa el conector de SQL Server y la base de datos de muestra AdventureWorks2019. La experiencia puede variar de conector a conector, pero en este artículo se muestran los aspectos básicos sobre cómo habilitar las funcionalidades de plegado de consultas en consultas nativas para los conectores admitidos.

Al conectarse al origen de datos, es importante que se conecte al nodo o al nivel en el que desea ejecutar la consulta nativa. En el ejemplo de este artículo, ese nodo será el nivel de base de datos dentro del servidor.

Después de definir la configuración de conexión y de indicar las credenciales de la conexión, accederá al cuadro de diálogo de navegación para el origen de datos. En ese cuadro de diálogo, verá todos los objetos disponibles a los que puede conectarse.

En esta lista, debe seleccionar el objeto donde se ejecuta la consulta nativa (también conocida como destino). En este ejemplo, ese objeto es el nivel de base de datos.

En la ventana del navegador de Power Query, haga clic con el botón derecho en el nodo de base de datos en la ventana del navegador y seleccione la opción Transformar datos. Al seleccionar esta opción se crea una nueva consulta de la vista general de la base de datos, que es el destino que necesita para ejecutar la consulta nativa.

Una vez que la consulta llega al editor de Power Query, solo el paso Origen debe mostrarse en el panel Pasos aplicados. Este paso contiene una tabla con todos los objetos disponibles en la base de datos, de forma similar a cómo se mostraron en la ventana Navegador.

Uso de la función Value.NativeQuery

El objetivo de este proceso es ejecutar el código SQL siguiente y aplicar más transformaciones con Power Query que se pueden volver a plegar al origen.

SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'

El primer paso era definir el destino correcto, que en este caso es la base de datos donde se ejecutará el código SQL. Una vez que un paso tiene el destino correcto, puede seleccionar ese paso (en este caso, Origen en Pasos aplicados) y, a continuación, seleccionar el botón fx de la barra de fórmulas para agregar un paso personalizado. En este ejemplo, sustituya la fórmula Source por la fórmula siguiente:

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'  ", null, [EnableFolding = true])

El componente más importante de esta fórmula es el uso del registro opcional para el cuarto parámetro de la función, que tiene el campo de registro EnableFolding establecido en true.

Nota:

Puede obtener más información sobre la función Value.NativeQuery en el artículo de la documentación oficial.

Una vez introducida la fórmula, aparecerá una advertencia que le pedirá que habilite la ejecución de consultas nativas para su paso específico. Puede hacer clic en Continuar para que se evalúe este paso.

Esta instrucción SQL genera una tabla con solo tres filas y dos columnas.

Plegado de consultas de prueba

Para probar el plegado de consultas de la consulta, puede intentar aplicar un filtro a cualquiera de las columnas y ver si el indicador de plegado de consultas en la sección de pasos aplicados muestra el paso como plegado. En este caso, puede filtrar la columna DepartmentID para obtener valores que no son iguales a dos.

Después de agregar este filtro, puede comprobar que los indicadores de plegado de consultas siguen mostrando el plegado de consultas que se genera en este nuevo paso.

Para validar aún más qué consulta se envía al origen de datos, puede hacer clic con el botón derecho en el paso Filas filtradas y seleccionar la opción que indica Ver plan de consulta para comprobar el plan de consulta de ese paso.

En la vista del plan de consulta, puede ver un nodo con el nombre Value.NativeQuery en el lado izquierdo de la pantalla que tiene un texto de hipervínculo que indica Ver detalles. Puede hacer clic en este texto de hipervínculo para ver la consulta exacta que se envía a la base de datos de SQL Server.

La consulta nativa se encapsula alrededor de otra instrucción SELECT para crear una subconsulta de la original. Power Query hará lo mejor para crear la consulta más óptima dadas las transformaciones usadas y la consulta nativa proporcionada.

Sugerencia

En escenarios en los que se producen errores porque no era posible el plegado de consultas, se recomienda intentar validar los pasos como una subconsulta de la consulta nativa original para comprobar si puede haber conflictos de sintaxis o contexto.