Formulario de contacto

Nombre

Correo electrónico *

Mensaje *

martes, 22 de enero de 2019

Power Query: una introducción a la herramienta de datos más poderosa de Excel - Parte 2


Bueno ya hemos visto una introducción del porqué Power Query es una herramienta realmente poderosa (Power Query: una introducción a la herramienta de datos más poderosa de Excel - Parte 1), y también hemos visto cómo instalarlo (La guía para instalar Power Query)

Pues veamos hoy cómo empezar a usarlo.

Recordemos que dependiendo de tu versión de Excel, ya sea en 2010, 2013 o 2016, la herramienta de Power Query esta situada en distintos espacios.

En 2010 y 2013 (asumiendo que tu versión es compatible :La guía para instalar Power Query), estaría en la pestaña Power Query.






En 2016, por el contrario, se encuentra en la pestaña Datos.







Importando datos a Power Query



Pues empecemos con una nueva consulta, dependiendo de la fuente de los datos.








  • Si la fuente de los datos es un archivo.








  • O desde una base de datos







  • O desde Azure








  • O desde Servicios en línea






  • O desde otras fuentes







Uno de los primeros pasos en el proceso de análisis de datos en Excel es importarlo. Se puede lograr esto de varias maneras. Los enfoques comunes incluyen la importación manual de datos o el uso de VBA para automatizar el proceso. Estos enfoques pueden no ser los más apropiados en varios casos.

El proceso de importación manual de datos en Excel es generalmente lento, repetitivo, tedioso y propenso a errores. Macros y VBA pueden ayudar a automatizar muchos de nuestros procesos de importación de datos. Sin embargo, este enfoque requiere algunos conocimientos de programación y, dependiendo de nuestra situación, es posible que deba dedicar tiempo a mantener los procedimientos que utiliza.

Power Query (Obtener y transformar) permite importar datos a Excel. Power Query ofrece varias ventajas (en comparación con los enfoques comunes que acabamos de mencionar), que incluyen los siguientes:

Al igual que VBA, Power Query permite automatizar el proceso de importación de datos.
Sin embargo, cuando se compara con VBA, Power Query es generalmente más fácil de trabajar y mantener. Power Query también tiende a dar como resultado un mejor rendimiento (en comparación con VBA).

En general, podemos pensar en Power Query como una herramienta ETL(Extract, Transform and Load, por sus siglas en inglés)  En otras palabras, Power Query permite:


  • Extraer: usamos Power Query para descubrir y conectarse a una variedad de fuentes de datos.
  • Transformar: Transformar los datos extraídos, por ejemplo, combinándolos o refinándolos.
  • Cargar: Compartimos los datos transformados.



Pues en este tutorial de Power Query nos vamos a centrar en trabajar con los 3 siguientes tipos de archivos comunes:


  • Libros de Excel.
  • Archivos CSV (valores separados por comas).
  • Archivos de texto.


Estos ejemplos de archivos de origen contienen varios miles de filas con los siguientes datos de ventas (ficticios):






  • Fecha: entre el 1 de enero de 2018 y el 31 de diciembre de 2019.
  • Elemento: 1 de los siguientes dispositivos de Microsoft (Surface Pro, Surface Book 2, Xbox One X, Surface Studio)
  • Tienda: Supongo que hay 1 tienda por ciudad enumerada a continuación. Divido las ciudades en 4 regiones (Europa, Asia, América Norte + Central y América del Sur). Los archivos de origen contienen 20,000 filas de datos para cada región.
  • Unidades vendidas: entre 1 y 5.
  • Precio unidad: asumo que el precio permanece constante independientemente de la ubicación de la tienda y la fecha de venta:
  • Ventas totales: El producto de unidades vendidas por precio unitario (Unidades Vendidas x Precio unidad).



Las siguientes son las diferencias entre los diferentes ejemplos de archivos fuente.


  • Libro de trabajo: El ejemplo del libro de trabajo tiene 4 hojas de trabajo. Cada hoja de trabajo corresponde a 1 de las regiones que enumero arriba. Adicionalmente:

  1. Los datos de origen de Europa se formatean como una Tabla de Excel (miTablaOrigen).
  2. El rango de celdas que contiene los datos de origen de Asia es un rango de celdas con nombre (miRangoNombreOrigen).


  • CSV: los ejemplos del archivo CSV solo tienen datos correspondientes a la región América Norte + Central. Esta es la misma información que en la hoja de trabajo del mismo nombre del libro de Excel
  • Texto: este es un archivo de texto delimitado por tabulaciones que contiene (solo) datos correspondientes a la América Sur. Este es el mismo dato que en la hoja de trabajo del mismo nombre del archivo de Excel.

Los datos de origen no están formateados. Más abajo, veremos cómo se puede usar Power Query para formatear los datos.

Para descargar los datos usados en este tutorial haz clic Aquí



En las siguientes secciones, veremos el proceso básico que se puede seguir para importar datos desde un libro de Excel, un archivo CSV o archivo de texto con Power Query.

Los pasos esenciales son similares. Power Query tiene varias opciones y características que se pueden usar al importar datos. Sin embargo, estos temas exceden el alcance de este Tutorial de Power Query para principiantes.

# 1: Importación desde un único origen de datos de un libro de Excel con Power Query

Para importar un único origen de datos de un libro de Excel con Power Query, siga estos 6 pasos:

  1. Vamos a Cinta>> Datos>>Mostrar Consulta>> Desde archivo>>Desde un libro 
  2. Power Query nos muestra el cuadro de diálogo Importar datos.
  3. Identificamos el libro de trabajo de origen y se hace doble clic en él.
  4. Power Query nos muestra el cuadro de diálogo del navegador.
  5. Seleccionamos la fuente de datos con la que se desea trabajar.
  6. Se hace clic en Cargar.



# 2: Importación desde múltiples fuentes de datos en un libro de Excel con Power Query

Para importar datos desde múltiples fuentes de datos dentro de un libro de trabajo con Power Query, se han de seguir estos 7 pasos:


  1. Vamos a Cinta>> Datos>>Mostrar Consulta>> Desde archivo>>Desde un libro 
  2. Power Query nos muestra el cuadro de diálogo Importar datos.
  3. Identificamos el libro de trabajo de origen y se hace doble clic en él.
  4. Power Query nos muestra el cuadro de diálogo del navegador.
  5. Seleccionamos "Seleccionar varios elementos".
  6. Seleccionamos las fuentes de datos con las que se desea trabajar.
  7. Se hace clic en Cargar.


# 3: Importación de datos desde un archivo CSV con Power Query


Para importar datos desde un archivo CSV a Excel con Power Query, se ha de seguir estos 5 pasos:


  1. Vamos a Cinta>> Datos>>Mostrar Consulta>> Desde archivo>>Desde un archivo CSV
  2. Power Query nos muestra el cuadro de diálogo Importar datos.
  3. Identificamos el archivo CSV de origen y se hace doble clic en él.
  4. Power Query nos muestra un cuadro de diálogo con el nombre del archivo CSV.
  5. Se hace clic en cargar


# 4: Importación de datos desde un archivo de texto con Power Query


Para importar datos desde un archivo de texto a Excel con Power Query, seguimos estos 5 pasos:


  1. Vamos a Cinta>> Datos>>Mostrar Consulta>> Desde archivo>>Desde un archivo de texto
  2. Power Query nos muestra el cuadro de diálogo Importar datos.
  3. Identificamos el archivo de texto de origen y se hace doble clic en él.
  4. Power Query nos muestra un cuadro de diálogo con el nombre del archivo de texto.
  5. Se hace clic en cargar




Trabajando con el cuadro de diálogo Importar datos


El cuadro de diálogo Importar datos es similar a otros cuadros de diálogo con los que se trabaja cuando realiza procesos comunes de Excel, como abrir o guardar un libro de trabajo. Por lo tanto, puedes hacer lo siguiente:


  • Buscar la carpeta donde se guarda el archivo (libro de trabajo, CSV o texto).
  • Hacer doble clic en el archivo apropiado.

Los criterios de filtrado de archivos aplicados por Excel dependen del tipo de archivo con el que esté trabajando (libro de Excel vs. CSV o texto). Por lo tanto:

  • Cuando trabajamos con un libro de Excel:
  1. El cuadro de diálogo Importar datos normalmente muestra cualquier tipo de archivo de Excel. Esto incluye, por ejemplo, libros de trabajo regulares de Excel, plantillas y libros de trabajo habilitados para macros.
  2. Independientemente del tipo de archivo de Excel preciso con el que se trabaja, Power Query se limita a trabajar con datos en celdas. Por lo tanto, los elementos como tablas dinámicas, gráficos o macros no se importan.
  • Cuando se trabaja con un archivo CSV: el cuadro de diálogo Importar datos muestra solo archivos CSV.
  • Cuando se trabaja con un archivo de texto: el cuadro de diálogo Importar datos muestra solo archivos de texto.


Trabajando con el cuadro de diálogo del navegador


Power Query muestra el cuadro de diálogo del navegador cuando se trabaja con un libro de Excel. El cuadro de diálogo del navegador tiene 3 secciones principales:

Izquierda: fuentes de datos disponibles.
Derecha: Vista previa.
Abajo a la derecha: Cargar, editar y cancelar botones.




1. Fuentes de datos disponibles

En la parte izquierda del cuadro de diálogo del navegador, Power Query enumera las fuentes de datos que se puede seleccionar dentro del libro de trabajo con el que se está trabajando (seleccionado con el cuadro de diálogo Importar datos).

Cuando se trabaja con un libro de Excel, estas fuentes de datos son generalmente 1 de los siguientes 3:


  • Una hoja de trabajo.
  • Una tabla de Excel.
  • Un rango con nombre. Es posible conectar Power Query, por ejemplo, a rangos dinámicos con nombre. El proceso para trabajar con este tipo de rango con nombre, sin embargo, difiere de lo que describo en este Tutorial de Power Query.


Se puede distinguir estos diferentes orígenes de datos en función del icono que muestra Power Query junto al origen.



Si se desea seleccionar múltiples fuentes de datos de un solo libro de trabajo:

  • Marcamos la casilla junto a "Seleccionar varios elementos" en la parte superior izquierda del cuadro de diálogo del navegador antes de seleccionar las fuentes de datos.
  • Una vez que se habilita la opción para seleccionar varios elementos, Power Query muestra las casillas de verificación a la izquierda de todas las fuentes de datos en el libro de trabajo. Utilizamos estas casillas de verificación para seleccionar todos los orígenes de datos que desea importar.

2.Vista Previa

Una vez que seleccionamos un origen de datos de la lista de orígenes de datos disponibles, Power Query muestra una vista previa de los datos.




Utilizamos esta vista previa para confirmar que los datos de origen que se elijieron son correctos.



3. Cargar, editar y cancelar botones

Usamos estos botones para hacer cualquiera de lo siguiente:
  • Cargar: Cargar los datos. Se pueden especificar cómo y dónde se cargan los datos siguiendo el proceso que describimos más adelante.
  • Editar: iniciar el Editor de consultas y editamos la consulta. Aprenderemos los conceptos básicos de trabajar con el Editor de consultas más adelante.
  • Cancelar: cierre el cuadro de diálogo y cancelamos el proceso de importación de datos con Power Query.



Trabajar con el cuadro de diálogo que lleva el nombre del CSV de origen o el archivo de texto


Power Query muestra un cuadro de diálogo con el nombre del archivo de origen cuando se trabaja con archivos CSV o archivos de texto. Este cuadro de diálogo tiene 3 secciones principales:

Arriba: Menús desplegables.
Medio: Vista previa.
Parte inferior: Cargar, editar y cancelar botones.




La principal diferencia entre este cuadro de diálogo y el cuadro de diálogo Navegador (en una sección anterior) es el hecho de que el cuadro de diálogo Navegador permite elegir entre las fuentes de datos disponibles dentro de un libro de trabajo.

Los archivos de texto y CSV solo contienen datos de texto. No tiene rangos con nombre, tablas de Excel, ni múltiples hojas de trabajo para elegir. Por lo tanto, al importar datos de un archivo CSV o de texto, no seleccionamos un origen de datos dentro del archivo.





Resultados de la importación de datos con Power Query.


1, Resultados al importar un único origen de datos desde un libro de trabajo o un archivo de texto o CSV con Power Query


Los resultados de importar datos con Power Query son similares cuando importa datos de cualquiera de los siguientes:


  • Una única fuente de datos de un libro de trabajo.
  • Un archivo CSV.
  • Un archivo de texto
Después de completar el proceso apropiado (descrito en las secciones anteriores), Excel hace lo siguiente:


  1. Carga los datos importados a una tabla de Excel en una nueva hoja de cálculo.
  2. Muestra el panel de tareas Consultas del Libro. Este panel de tareas incluye la consulta que acaba de crear.






2, Resultados al importar datos de múltiples fuentes de datos en un libro de trabajo con Power Query


Después de completar el proceso que describimos anteriormente para importar datos de múltiples fuentes de datos en un libro de trabajo, Excel hace lo siguiente:


  • Carga los datos importados al modelo de datos.
  • Muestra el panel de tareas Consultas del Libro. Este panel de tareas tiene consultas separadas para cada fuente de datos que seleccionamos.






Una vez que hemos especificado de dónde provienen los datos, podemos usar la ventana del editor de Power Query para realizar transformaciones en los datos.











Quieres más trucos?


5. Reconciliación de débitos y créditos utilizando el complemento Solver





Te ha gustado el artículo?




No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 2 siguientes acciones:



1. Puedes dejar una donación pinchando en la siguiente imagen.

PayPal. La forma rápida y segura de pagar en Internet


2. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.



El Sabio de Excel busca colaborades del blog

Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com


Subscríbete y recibe todos las entradas por email


Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales


No hay comentarios:

Publicar un comentario