PowerQuery

¿Por Qué las Personas más Productivas Invierten en Power Query?

Afirmar que Power Query es una mastodóntica funcionalidad especializada para la limpieza de datos, sería pecar de comedido.

Power Query puede hacer prender el fuego en la mente de los hombres, arrancarles lágrimas a los corazones más duros, minar los problemas más complejo y alfombrar tus habilidades con técnicas profesionales para alcanzar el cénit de cualquier carrera laboral.

Ninguna persona está exenta de los favores de Power Query si trabaja con datos, así sea en escalas diminutas, más aún, si tienes que manipular los mismo para llegar a un formato adecuado, crear tablas dinámicas, realizar análisis posterior, reportes y/o cuadros de mando.

Algunas personas con una peculiar falta de tino creen que al estar en contacto mayoritariamente con gestores de bases de datos como: SQL, PostgreSQL, Oracle, etc. No deben molestarse de pasar ni un nanosegundo en la parte de limpieza de datos, pero la realidad va más allá, al ser Power Query una parte integral del proceso de inteligencia de negocios en algún punto su manipulación facilitará los desarrollos.

¡Este es un libro para todos!

Por otra parte, este libro está diseñado tanto para usuarios de Excel como para usuarios de Power BI que quieran catapultarse a fronteras inimaginables, llegar a otra dimensión.

Conceptos Básicos en Power Query

A lo largo de las últimas semanas, hemos conocido algunos elementos de la suite de herramientas de Microsoft BI en Excel, aplicados en la creación de soluciones en inteligencia de negocios e intuitivamente hemos comprendido que Power Query u Obtener y Transformar como es conocido en la versión de Excel 2016 es inevitablemente la primera etapa en la construcción de este tipo de soluciones y por eso vamos a hablar de los conceptos básicos utilizados al trabajar con esta poderosa herramienta para Extraer, transformar y cargar datos.sPara empezar, entendamos que es una …

Solución en Inteligencia de Negocios ( Solución BI)

Podemos definir una solución BI como todo tipo de herramientas, por ejemplo, reportes o cuadros de mando enfocados en facilitar el proceso de toma de decisiones para alcanzar los objetivos de una compañía, y a su vez hacer más accesible la información para los miembros de la organización que la necesiten, para  así tomar decisiones inteligentes, que no estén basadas solo en la intuición.

Inevitablemente el primer paso para crear cualquier solución BI, desarrollada en Excel o Power BI, debe ser realizado con Power Query, entendamos por que:

Proceso de Creación de una Solución BI en Excel

Para crear una solución BI debemos realizar el siguiente procedimiento:

1. Extraer, transformar y cargar datos, en el libro.
2. Modelar los datos, crear cálculos, Jerarquias o KPIs , o todo tipo de elementos para facilitar el análisis por parte del usuario final, que podemos ser nosotros mismos, dependiendo siempre, del tipo de solución a crear.
3. Crear reportes o cuadros de mando por ejemplo, utilizando tablas dinámicas, gráficos dinámicos , formulas cubo para extraer un dato, Power View, Power Map u otras funcionalidades de Excel.
4. Compartir y publicar la solución para a los miembros de la organización.

En ese orden de ideas, claramente Power Query es nuestro mejor aliado.

• Power Query es un complemento en la versión de Excel 2013

• En la versión de Excel 2016, es una funcionalidad propia llamada Obtener y Transformar.

Que nos permite tomar datos desde múltiples fuentes, limpiarlos y transformarlos en un formato tabular, definiendo una serie de pasos, para luego cargarlos ya sea en el modelo de datos o en una hoja del libro, y en consecuencia, modelarlos y analizarlos con las diferentes herramientas de Excel o Power BI Desktop.

¿Que tipo de profesional tiene el perfil para usar Power Query? te estarás preguntando, pues bien

• Analistas
• Administradores
• Ingenieros
• y en general, cualquier profesional que tenga que trabajar con datos y que posiblemente deba crear reportes o cuadros de mando.

Esta herramienta, está incluida en la nueva solución de Microsoft para crear soluciones en inteligencia de negocios, Power BI Desktop .

Nota: Reporte Creado en Power BI Desktop

Ahora bien, con esta definición un poco más clara de lo que podemos hacer con power query, entendamos, algunos conceptos claves, para trabajar con esta poderosa herramienta.

Qué es una consulta

Una consulta está formada por una serie de Pasos creados en un orden especifico definidos en el propio lenguaje de expresiones de Power Query denominado Lenguaje M  , dichos pasos tienen como objetivo realizar el proceso de Extracción, transformación y carga de datos, para posterior consumo y uso en las soluciones creadas en Excel.

Nota: Debemos tener en cuenta, que cuando agregamos un paso a través del Editor de Consultas, automáticamente se genera el código necesario para lleva a cabo este paso.

Qué es el Editor de Consultas

El editor de consultas es una interfaz gráfica que nos permite agregar, editar o eliminar pasos a la consulta para aplicar el proceso ETL ( Extract – Transform – Load ) a nuestros datos y está compuesto por :

1. Barra de Herramientas del Editor de consultas: Se encuentran las diferentes opciones para agregar pasos a la consulta.
2. Panel de Configuración de la Consulta: Muestra las propiedades de la consulta como por ejemplo el nombre, así como la lista de pasos aplicados que tiene la consulta.
3. Panel de navegación: muestra los orígenes de datos disponibles, se puede ocultar con el botón en forma de flecha.
4. Panel de Resultados: muestra el resultado obtenido de la tabla, de acuerdo al paso actual.

Nota: Si elegimos un paso, el panel de resultados automáticamente nos mostrara el resultado obtenido.

5.Barra de fórmulas: lugar donde puedo modificar las expresiones en el lenguaje M, creadas  para el Paso Actual.

Opciones  de Carga en Power Query  

Conexión – solo consulta:

Establece la conexión con el origen de datos, para llevar a cabo el proceso ETl, sin cargar los datos en ningún destino en Excel hasta que sean solicitados por otra consulta, o hasta que sea cambiado el destino de carga, es una opción muy útil para no duplicar datos en nuestro libro y utilizar la tabla obtenida por esa consulta, en otra.

Para configurar la conexión – solo consulta: Damos clic en el botón Cerrar y  Cargar  en, en el editor de consultas ->  en el cuadro de dialogo Cargar en, que aparece, solo habilitamos la opción Crear  solo Conexión -> Cargar.

Cargar datos en el modelo de Datos

La consulta carga los datos después de transformar y limpiar la tabla, en el Modelo de Datos de Excel.

Cargar  datos en el modelo de datos: Damos clic en el botón Cerrar y Cargar en, en el editor de consultas ->  en el cuadro de dialogo Cargar en, que aparece, habilitamos la opción Crear  solo Conexión y Agregar estos datos al Modelo de Datos -> Cargar.

Cargar datos en una hoja de Excel

La consulta carga los datos directamente en una hoja del libro de Excel, como una tabla estructurada de Excel y es la configuración por defecto cuando presionamos Cargar en.

Pero te estarás preguntando, porque deberías empezar a usar Power Query, pues bien, algunas respuestas:

• Proporciona flexibilidad para trabajar en Excel y más exactamente, para realizar el proceso de Extracción, transformación y carga de datos
• Podemos conectarnos a diversas fuentes, como por ejemplo archivos basese de datos y paaginas web
• Podemos automatizar el proceso de carga para disminuir errores inevitables, producto de la repetición de tareas.
• Ya no es necesario, alojar los datos en una hoja y ocultarla, para crear soluciones en Excel.
• Limpieza y transformación de grandes cantidades de datos, eficientemente

Bueno, estos son algunos conceptos básicos a tener en mente cuando trabajamos con Power Query, por el día de hoy es todo, hásta la proxima ocasión.

Importar y Limpiar Datos desde la Web con Power Query

Es muy probable que para crear modelos y análisis de datos realmente potentes utilizando Power Pivot o incluso para una tarea muy sencilla realizada directamente en Excel, debas recurrir a tablas externas, que se encuentran almacenadas en alguna página Web… de ser así, te invito a que conozcas como Power Query, puede facilitarte la vida en esta labor.

Ejemplo – Análisis PIB

Imagina que nos han solicitado analizar el Producto Interno Bruto de diferentes países, y así entender un poco, como ha sido su economía en los últimos años, para cumplir con nuestra misión, vamos a utilizar los datos que hemos encontrado en la página del Banco Mundial.

Fuente: Banco Mundial

Nota: Recordemos que el Producto Interno Bruto (PIB) es la cantidad total de bienes y servicios producidos por un país, en un periodo de tiempo.

Proceso Importar  – Transformar – Cargar (Extract – Transform – Load ) ETL con Power Query

1. Importar (Extract)

Primero, debemos “cargar” los datos en el Editor de Consultas:

Grupo Obtener y Transformar -> Nueva Consulta -> Desde Otras Fuentes -> Desde una Web.

En la ventana Navegador que surge inmediatamente, aparecen las diferentes tablas que se encuentran en la página Web que hemos ingresado, por eso, debemos elegir la tabla de nuestro interés, en este caso, se denomina Table 0, y damos clic en Editar.

En la ventana Navegador que surge inmediatamente, aparecen las diferentes tablas que se encuentran en la página Web que hemos ingresado, por eso, debemos elegir la tabla de nuestro interés, en este caso, se denomina Table 0, y damos clic en Editar.

Nota: En la siguiente imagen, puedes ver el contenido de otra tabla de la página web denominada Table 1.

Y automáticamente aparece el Editor de Consultas de Power Query, ¡el lugar donde la magia ocurre!, con la tabla de nuestro interés.

2. Transformación (Transform)

En el Editor de Consultas de Power Query, vamos a realizar TODAS las transformaciones necesarias, para que la tabla tenga un formato tabular.

Eliminar Columnas

Primero, eliminamos las ultimas columnas de la Tabla porque no contienen datos y son innecesarias.

Manteniendo CTRL presionado, damos clic izquierdo sobre las columnas a eliminar para así seleccionarlas -> Clic derecho sobre alguna de sus etiquetas -> Quitar Columnas.

En la imagen anterior, podemos ver con claridad que la tabla contiene filas con algunos valores Nulos, por lo que no son útiles y en consecuencia debemos quitar la fila entera:

Primero, nos dirigimos a la columna 2011 y damos clic en el botón de filtro (aparece en la etiqueta de cada columna).

Al dar clic en el botón, se despliegan diferentes opciones para filtrar la tabla, (muy similar a los filtros de Excel), aquí vamos a quitar la selección de valores Nulos:

En las opciones de Filtro quitamos la selección a los valores nulos

Nota: Si nos fijamos en la parte inferior izquierda del editor de consultas, vemos como se han quitado de la consulta, las filas que contenían este ítem.

Antes:

Después:

Quitar Filas aplicando el Filtro en las Columnas restantes

Para poder analizar el PIB a través del tiempo, debemos contar con series continuas y sin valores faltantes, por lo que, si en un solo año hace falta el PIB para un país, se debe eliminar de la consulta.En consecuencia, debemos quitar los valores nulos en las otras columnas repetiendo el proceso de filtro en las columnas restantes, de esta manera, asegurarnos que la tabla es consistente y no tiene campos en blanco.

Anulación de Dinamización de Columnas

Para facilitar análisis posteriores, vamos a agrupar en una sola columna los años:

Manteniendo Shift presionado, damos clic izquierdo en la columna 2011 y luego en la columna 2014 y así seleccionamos todas las columnas que están en el medio, incluyéndolas -> elegimos la opción Anulación de dinamización de columnas.

Y Bang!!!!, finalmente nuestra tabla obtiene el formato tabular apropiado.

Por último, cambiamos las etiquetas de cada columna así como el tipo de dato de la misma. 

Para cambiar el nombre de una columna: Doble clic sobre la etiqueta -> reescribimos el nombre de la columna.

Nota: Recuerda asignar el tipo de dato apropiado a cada columna.

3. Cargar (Load)

Ahora, solo resta cargar nuestra tabla en una hoja del Libro.

Cambiamos el nombre de la consulta por PIB_Pais -> damos clic en el botón Cerrar y Cargar

Y automáticamente se carga una Tabla Estructurada, a partir de la consulta que hemos modificado y creado.

Finalmente, vamos a crear un reporte de tabla de Dinámica con los datos de la página Web, en nuestro libro.

Nota: Debemos tener confianza de la Página Web de la cual vamos a tomar los datos, puesto que como no dependen de nosotros, cualquier modificación realizada puede alterar la integridad del reporte.

Bueno eso es todo por el día de hoy, la próxima semana continuaremos descubriendo más posibilidades que nos proporcionan todas las herramientas de Microsoft BI, mantente conectado a todos los recursos de Excel Free Blog.

Hasta la Próxim