Editor de Consultas IV

Para ver una versión actualizada de esta entrada visita el blog de mi nuevo sitio www.dataXbi.com

La pestaña Vista

Contiene el grupo de opciones Mostrar que permiten ver y modificar las fórmulas de la consulta.



 Opción Configuración de la consulta: Permite ocultar o mostrar el panel Configuración de la consulta.



En el panel de Configuración de la consulta podemos ver y modificar el nombre de la consulta y los pasos aplicados.



Cada paso de la consulta se corresponde con una fórmula de Power Query. Esas fórmulas las podemos crear o modificar manualmente o a través de las herramientas de los distintos menús del Editor de consultas de Power Query.

Para poder construir manualmente las fórmulas necesitamos el Editor avanzado o la Barra de fórmulas y el lenguaje de fórmulas de Power Query.

Opción Editor avanzado: Muestra el Editor avanzado




Al abrir el Editor avanzado podemos ver y modificar las fórmulas utilizadas en la consulta.



Opción Barra de fórmulas: Permite mostrar u ocultar la barra de fórmulas.




La barra de fórmulas se encuentra situada sobre el panel de resultados y muestra por defecto la última fórmula que hemos utilizado. Cuando abrimos el Editor de consulta nos muestra la que aparece al final de los PASOS APLICADOS el panel de Configuración de la consulta.

En la barra de fórmulas podemos modificar el paso seleccionado, para ello debemos conocer el Lenguaje de formulación de Power Query.



 

Lenguaje de Formulación de Power Query

El lenguaje de fórmulas de Power Query (informalmente conocido como "M") es un poderoso lenguaje optimizado para la construcción de consultas que integran y reutilizan  datos de multiples orígenes.
Se trata de un lenguaje funcional (basado en funciones), en su mayoría puro (solo se pueden utilizar funciones), de orden superior, tipado dinámicamente (la comprobación de tipificación se realiza durante su ejecución en vez de durante la compilación), parcialmente perezoso (retrasa el cálculo de una expresión hasta que su valor sea necesario y evita repetir la evaluación en caso de que sea necesaria posteriormente) y case sensitive,  similar a F#, que puede ser utilizado con Power Query en Excel y Power BI Designer.

Podemos encontrar  una introducción al lenguaje en  msdn en el siguiente enlace: Introducción al lenguaje de fórmulas de Power Query

El lenguaje posee un conjunto amplio de funciones agrupadas por categorías que podemos consultar en msdn en el enlace: Categorías de funciones

Veamos un ejemplo del uso del lenguaje en el Editor avanzado.

Abriremos el editor de consultas y seleccionemos una de las consultas que aparecen en el panel de navegación. En el panel de resultados podemos ver una tabla con dos columnas.



En el panel de configuración se muestran los pasos para obtener ese resultado: Origen y Navegación. Si abrimos el Editor avanzado podemos ver las expresiones correspondientes.



Analicemos las expresiones que aparecen en el editor.

En la primera línea aparece la expresión let que nos permite calcular un conjunto de valores, asignarles nombres y luego usarlos para formar nuevas y más complejas expresiones.

La segunda linea contiene el primer valor calculado  Origen que  se obtiene como  resultado de evaluar la función  Sql.Database. Esta función pertenece a la categoría  Accessing data y tiene dos parámetros: el primero para escribir el nombre de la instancia del servidor de base de datos SQL Server y el segundo para el nombre de la base de datos que queremos utilizar. La función devuelve como resultado una tabla que contiene todas las tablas de la base de datos.

En el ejemplo a la función Sql.Database(".", "Futbol") le hemos pasado como parámetros la instancia por defecto de SQL Server y la base de datos Futbol.

Si cerramos el editor avanzado y seleccionamos Origen en el panel de Configuración de la consulta podremos ver en el panel de resultados todas las tablas que contiene la base de datos Futbol, así como sus características.



La tercera línea del Editor avanzado contiene el segundo valor calculado que denominamos #"dbo_'Jornadas 2014 - 2015$'" y al que le asignamos la tabla Jornadas 2014 - 2015$.

La cuarta línea corresponde a la palabra reservada in, que indica que termina la construción de expresiones.

En la quinta línea devolvemos la expresión resultante. En este caso, devolvemos el valor calculado en la linea 3:  #"dbo_'Jornadas 2014 - 2015$'"


Crear una consulta de forma manual en la Barra de fórmulas.

Lo primero será crear una consulta en blanco. Para ello en el menú Power Query de la cinta de opciones seleccionaremos la opción Desde otros origenes | Consulta en blanco



Se muestra el Editor de consultas, en el panel de resultados no se observa ningún valor y la barra de fórmulas está vacía, sin embargo en el panel de Configuración de la consulta contiene un paso llamado Origen.



Si abrimos el Editor avanzado podemos ver se ha asignado la cadena vacia al valor Origen y la expresión que se devuelve es Origen.



 Cerremos el Editor avanzado y a continuación escribamos en la Barra de fórmulas la expresión:
= Text.Combine({"Power", "Query"}, " ")



y oprimimos la tecla Enter.



Veremos que en el panel de resultados se muestra el texto Power Query.

Si abrimos el Editor avanzado podemos ver que se ha modificado, a Origen se le asignado la expresión que escribimos en la Barra de fórmulas.

La función Combine pertenece a la categoría de funciones Text functions  y permite enlazar la lista de textos pasados en el primer parámetro usando como separador el segundo parámetro.



 Crear una consulta de forma manual en el Editor avanzado

Vamos a modificar la consulta. Sustituiremos ahora la expresión que aparece después de let con las expresiones:
x = 1,
y = 2,
z = x+ y

Sustituiremos en la parte del in la expresión que aparece por la expresión

x + y + z

como se puede apreciar en la imagen.



Chequearemos que esté sintacticamente bien escrita y oprimiremos el botón Listo. En el panel de resultados se mostrará el valor 6 que es el resultado de evaluar la expresión.



Por último veamos un ejemplo de consulta que conecta con una base de datos.
Para ello crearemos otra consulta en blanco en el menú Power Query de la cinta de opciones.

A continuación en la pestaña Inicio del Editor de consultas dentro en el grupo Nueva consulta seleccionaremos la opción Nuevo Origen | Base de datos | SQL Server.



Se mostrará una ventana solicitando el nombre del servidor de bases de datos, el nombre de la base de datos (opcional) y  una consulta T-SQL (opcional).



Una vez introducidos los datos y presionado el botón Aceptar se muestra una nueva ventana solicitando las credenciales para acceder al servidor de base de datos.



Después de especificar las credenciales y presionar el botón conectar y en el caso de no haber escrito ninguna instrucción sql se mostrará una ventana con la lista de tablas disponibles. Debemos seleccionar la tabla que utilizaremos.



En este caso hemos escogido la tabla 'Jornadas 2014 - 2015$',  que se abrirá el editor de consulta mostrando su contenido en el panel de resultados.



 Si abrimos el Editor avanzado podemos ver las fórmulas empleadas.



Por último podemos cerrar el editor y cargar los datos en una hoja de cálculo  y/o en el modelo de datos.



En este ejemplo hemos seleccionado la hoja de calculo.



Al oprimir el botón Cargar se abrirá la hoja con los resultados de la consulta.