Obtener datos externos - Desde una base de datos SQL Server

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

Podemos importar datos desde una base de datos de una instancia de MS SQL Server usando los objetos Tablas y Vistas de la base de datos.

En el ejemplo utilizaremos la instancia de SQL Server Diana-PC y la base de datos Demo Database Nav (8-0).



Para crear la consulta desde de la cinta de opciones Power Query seleccionaremos la opción Obtener datos externos >> Desde una base de datos de SQL Server. 



Cuando escogemos esta opción se muestra la ventana Base de datos de SQL Server donde debemos específicar el nombre del servidor, el nombre de la base de datos (es opcional) y podemos especificar los datos que necesitamos a través de una instrucción SQL (tambien es opcional).





Al hacer clic en Aceptar, si es la primera vez que accedemos a la instancia de MS SQL Server, se abre la ventana Acceder a una Base de datos de SQL Server que nos solicitará las credenciales para conectarse al servidor.




Al presionar el botón conectar Power Query intenta conectarse al servidor, el proceso puede demorar unos minutos.



 
Una vez establecida la conexión al servidor si no hemos específicado una instrucción SQL se abre la ventana del Navegador y podemos escoger uno o varios elementos de la base de datos. Si en la ventana Base de datos de SQL Server no escribimos un nombre base de datos el Navegador mostrará la lista con las bases de datos contenidas en el servidor.


Si en la ventana Base de datos de SQL Server escribimos  una instrucción SQL se cargarán los datos sin necesidad de realizar ningún otro paso.



Cuando seleccionamos una tabla o vista en el panel de la izquierda del Navegador, su contenido se muestra en el panel de la derecha. 

Podemos seleccionar varias tablas y vistas de la base de datos marcando la casilla Seleccionar varios elementos y a continuación seleccionando la casilla delante de cada tabla o vista. 

Una vez que hemos seleccionado todos los elementos debemos decidir donde queremos ver los datos. Desplegamos la lista Cargar que se encuentra en la parte de inferior del panel de la derecha y escogemos la opción Cargar en.. Se abre la ventana Cargar donde podemos configurar como deseamos ver los datos.

Para este ejemplo dejaremos los valores por defecto. Los datos se cargarán en forma de tabla en una hoja de cálculo nueva.



 
Al presionar el botón Cargar se mostrarán los resultados en  hojas de cálculo. Se crearán tantas hojas nuevas como elementos de la base de datos se hayan seleccionado.

 

Si iniciamos el Editor de Consultas de Power Query podemos ver en el navegador las consultas que se han creado (una por cada elemento seleccionado). Al hacer clic en cualquiera de las consultas se cargarán los datos en el panel de resultados lo que nos permitirá realizar las transformaciones sobre los mismos.



 
En el Editor avanzado podremos ver las fórmulas aplicadas para obtener dichos resultados.





Podemos obtener los mismos resultados manualmente desde el Editor avanzado.

Construir la consulta Manualmente

Para ello en la cinta de opciones Power Query seleccionemos la opción Obtener datos externos >> Desde otros orígenes >> Consulta en blanco.

Se inicia el Editor de Consultas de Power Query. 

Abrimos el Editor avanzado.



Sustituimos el valor de Origen por:

Origen = Sql.Database("Diana-PC", "AdventureWorks2012")



 Al hacer clic en Listo se ejecutará la consulta y en el Editor de consultas tendremos una tabla conteniendo las tablas y vistas de la base de datos.



En el Editor avanzado añadiremos una nueva fórmula para indicar que usaremos la tabla Departament del esquema HumanResource.

Departamento = AdventureWorks2012{[Schema="HumanResources",Item="Department"]}[Data]



 Al presionar Listo en el panel de resultados del Editor de consultas tendremos el contenido de la tabla Departament.



Por último iniciemos el Editor avanzado y sustituyamos las fórmulas que aparecen por las siguientes:

 let
 Origen = Sql.Database("Diana-PC", "AdventureWorks2012", [Query="select       [NationalIDNumber], [FirstName], [MiddleName], [LastName], [JobTitle], [BirthDate] from [HumanResources].[Employee] E inner join [Person].[Person] P on P.BusinessEntityID = E.BusinessEntityID"])
in
    Origen 

En lugar de la función Sql.Databases usaremos sql.Database. Esta función admite tres parámetros: el primero es el nombre del servidor, el segundo el nombre de la base de datos y el tercero una consulta SQL.

En el ejemplo hemos seleccionado algunos campos de las tablas Employee y Person.




Al hacer clic en el botón Listo se mostrará la ventana Consulta de base de datos nativa, advirtiendonos que estamos a punto de ejecutar una consulta que puede modificar la base de datos.

Al presionar el botón Ejecutar se muestran los resultados en el Editor de consultas.

 

Obtener datos externos - Desde una base de datos de Access

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

Dentro del grupo Obtener datos externos de la cinta de opciones Power Query, está la opción Desde una base de datos.




Esta opción nos permite importar datos desde diversos tipos de bases de datos como: Microsoft Access, Microsoft SQL Server, Microsoft Analysis Services, Oracle, MySql, PostgreSQL, etc.





Importar datos desde una base de datos Access

La opción Desde una base de datos de Access nos permite utilizar las tablas y consultas de una base de datos Access. Por cada tabla o consulta seleccionada obtendremos una consulta Power Query. 


Para el ejemplo usaremos la base de datos: ContosoSales.accdb que podemos descargar desde el siguiente enlace
 
http://powerpivotsdr.codeplex.com/ 

Una vez que hemos descargado el fichero ContosoV2.zip debemos descompactarlo. 




Seleccionamos la opción Obtener datos externos >> Desde una base de datos >> Desde una base de datos Access de la cinta de opciones Power Query.



 
A continuación escogemos la base de datos con la que vamos a trabajar, en el ejemplo ContosoSales.accdb



Power Query se conecta a la base de datos. Esta operación puede demorar unos minutos dependiendo del tamaño de la base de datos.



Se abre la ventana del navegador y seleccionar los elementos a importar. En el panel de la izquierda podemos seleccionar las tablas y/o consultas que utilizaremos. Podemos seleccionar varios elementos. 

Cuando seleccionamos un elemento del panel de la izquierda su contenido se muestra en el panel de la derecha.



Una vez que hemos escogido los datos debemos específicar donde queremos cargarlos. En la parte inferior del panel de la derecha desplegaremos el menú Cargar y escogeremos la opción Cargar en..



La opción por defecto es Tabla que mostrará los datos en forma de tablas en hojas de datos Excel. Podemos cambiar y escoger Crear solo conexión, de esta manera los datos no se almacenarán en hojas de datos Excel. También podemos seleccionar Agregar estos datos al Modelo de datos lo que nos permitirá usarlos posteriormente en Power Pivot.



En el ejemplo hemos escogido cargar los datos en tabla por lo que se mostrará cada conjunto de resultados en una hoja de cálculo diferente.



Si iniciamos el Editor de consultas de Power Query podemos observar en el navegador que se han creado dos consultas, cada consulta se refiere a cada una de las tablas seleccionadas.

Si hacemos clic sobre DimProduct en el panel de resultados se mostrará el contenido de la tabla. En el panel de Configuración de consulta podemos observar que se han aplicado dos pasos: Origen y Navegación.


Si abrimos el Editor avanzado veremos las fórmulas correspondientes a dichos pasos.



En la primera fórmula a Origen se le ha asignado el resultado de evaluar la función Access.Database que devuelve como resultado una tabla con la lista de tablas y consultas de la base de datos. A la función se le ha pasado como parámetro la función File.Contents. La función File.Contents recibe como parámetro la ubicación física de un fichero, en este caso ContosoSales.accdb y retorna el contenido binario del fichero. Ambas funciones pertenecen a la categoría de funciones Accessing data.

La segunda fórmula devuelve el contenido de  la tabla seleccionada, en este caso DimProducto.

Otra forma de obtener estos resultados es construir la consulta manualmente desde el Editor avanzado.

Para ello en la cinta de opciones Power Query, dentro del grupo Obtener datos externos, seleccionemos la opción Consulta en blanco en Desde otros orígenes.



Se inicia el Editor de Consultas de Power Query.




Abrimos el Editor avanzado.





Sustituimos el valor de origen por:

Origen = Access.Database(File.Contents("C:\soft\AdventureWorks DW 2012.accdb"))



Al presionar el botón Listo podemos ver en el editor de consultas el contenido de la base de datos (27 tablas y 1 vista)



En el Editor avanzado escribiremos una nueva fórmula para escoger la vista Products.

Products = Origen {["Schema = "", Item = "Products"]}[Data]




Al hacer clic en Listo se mostrará en el Editor de consultas el resultado de ejecutar la vista.

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.