lunes, 6 de abril de 2015

Consolidando datos de orígenes diferentes con Power Query en Excel.

¿Cuántas veces hemos tenido que trabajar sobre diferentes orígenes de datos, en diferentes hojas o libros?, y ¿en cuántas ocasiones hemos tenido que recurrir a nuestras macros (VBA para Excel) u otros trucos para poder disponer de nuestra información en un único lugar?...
En el día de hoy aprenderemos una manera sencilla, empleando el complemento Power Query para Excel.


Antes de empezar indicarte que si no lo tienes instalado (para versiones Excel 2010 y superiores) puedes hacerlo gratuítamente desde la página de descargas de Microsoft aquí.
Ojo con la versión y arquitectura (32/64 bits) que tengas...!!!


Una vez instalado aparecerá en tu cinta de opciones la siguiente Ficha:



Podrías preguntarte qué esto de Power Query??.. bueno, hay muchas descripciones pero una buena sería que Power Query para Excel es un complemento de Excel que nos permite conectarnos a cualquier origen de datos; transformar, combinar y acomodar los datos según la necesidad, y cargarlos a una hoja Excel o incluos a un modelo de datos (Power Pivot).
Además, todo el proceso de conexión, transformación, combinado y acomodo de los datos queda grabado para poder ser reutilizado posteriormente (cuando, por ejemplo, los datos se refresquen o haya que incluir los del siguiente periodo)... cosa de la que hoy nos aprovecharemos...
En definitiva es un constructor de consultas muy potente (y fácil de emplear).


Para el caso que nos ocupa hoy partiremos de tres tablas de datos dispuestas en hojas diferentes, con estructuras de campos similares, pero no dispuestas en iguales posiciones:


Observemos que el orden de los campos de las tres tablas es diferente, incluso una de ellas posee un campo extra que no existe en las demás...

Estamos en disposición de generar y unificar en una sola tabla toda la información.
Para ella accederemos a la ficha de Power Query > grupo Desde Excely con una de las tablas seleccionada, por ejemplo la de la Empresa A (tabla con nombre Tbl_A), presionaremos el botón Desde tabla:



Tras aceptar se acaba de generar una hoja nueva con una tabla resultante (de momento igual a la 'Tbl_A'); podríamos repetir esta acción para el resto de tablas... pero he optado por un método personalizado avanzado que ahorrará tiempo y espacio...
A continuación presionaremos, dentro de la ficha de Power Query el botón Mostrar Panel, lo que sacará un panel de control para las consultas generadas...

Consolidando datos de orígenes diferentes con Power Query en Excel.



A continuación haremos doble clic sobre la consulta generada dentro del Panel de Consultas del Libro (o bien botón derecho del ratón y Editar), se abrirá de nuevo nuestra ventana de Editor de Consultas... (la misma que habíamos cerrado anteriormente). Igualmente podríamos haberlo configurado en el mismo momento de la creación...
En esta ventana buscaremos en el menú Inicio > grupo Consultas > botón Editor Avanzado:

Consolidando datos de orígenes diferentes con Power Query en Excel.



Y escribiremos lo siguiente:

let
 Origen = Excel.CurrentWorkbook(){[Name="Tbl_A"]}[Content],
 Anexar = Table.Combine({Origen,Excel.CurrentWorkbook(){[Name="Tbl_B"]}[Content]}),
 Anexar1 = Table.Combine({Anexar,Excel.CurrentWorkbook(){[Name="Tbl_C"]}[Content]})
in
  Anexar1



Como se intuye lo que se ha conseguido es ir anexando datos de una tabla a otra directamente de las Tablas existentes en el Libro de trabajo actual...
Y listo, tenemos lo que buscábamos, toda la información de las diferentes tablas unificada en un único lugar:

Consolidando datos de orígenes diferentes con Power Query en Excel.



Sobre esta Tabla resultado de nuestra 'Super Consulta' (Power Query) podemos construir una Tabla dinámica, o incluirla en el modelo de datos de Power Pivot, o cualquier otra acción que se nos ocurra.

Sobre mi ejemplo, de tres orígenes diferentes, he incorporado algo más:
1- dado formato a las fechas
2- añadido un campo calculado resultante del cociente entre Importe y Uds
3- aplico un redondeo al calculo realizado
4- reordenado las columnas/campos.

El código introducido en el editor avanzado hubiera sido:

let
Origen = Excel.CurrentWorkbook(){[Name="Tbl_A"]}[Content],
 Anexar = Table.Combine({Origen,Excel.CurrentWorkbook(){[Name="Tbl_B"]}[Content]}),
 Anexar1 = Table.Combine({Anexar,Excel.CurrentWorkbook(){[Name="Tbl_C"]}[Content]}),
 #"Tipo cambiado" = Table.TransformColumnTypes(Anexar1,{{"Fechas", type date}}),
 #"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "PzMedio", each [Importe]/[Uds]),
 Redondeo = Table.TransformColumns(#"Personalizada agregada",{{"PzMedio", each Number.Round(_, 2)}}),
 #"Columnas reordenadas" = Table.ReorderColumns(Redondeo,{"Empresa", "Fechas", "Zona", "Uds", "Importe", "PzMedio"})
in
 #"Columnas reordenadas"


Consolidando datos de orígenes diferentes con Power Query en Excel.



Recuerda que esto mismo es posible realizarlo paso a paso, que queda constancia en la Consulta de las distintas etapas, yq ue todas ellas son modificables...
Las posibilidades de trabajo de esta herramienta son bastante potentes, incluyendo la opción de vincular o relacionar información de diferentes Tablas o Consultas.

En un ejemplo posterior mostraré cómo relacionar esta consulta con una nueva.

8 comentarios:

  1. EXCELENTE APORTE. PERO ESTE NO ME SIRVIO PARA CONVERTIR EN EXCEL DOS ARCHIVOS CON EXTENSION .CDX Y .PDB QUE ME PARECE SE REALIZO CON FOXPRO. NECESITO AYUDA. GRACIAS!!!!!!!!!!!!!!!!!!!!!

    ResponderEliminar
    Respuestas
    1. Hola Osvaldo,
      no es el fin reconvertir ficheros de otras extensiones...
      Quizá te sirva mejor para lo que pretendes la herramienta de Importar Datos (Obtener Datos Externos)

      Un saludo

      Eliminar
  2. Una duda técnica. Si realizamos una consulta con Power Query y le pasamos ese archivo a otra persona, para que esta pueda actualizar los datos es necesario que tenga instalado Power Query ¿no?

    ResponderEliminar
    Respuestas
    1. Hola Adolfo,
      sería lo lógico, aunque sí sería posible trabajar con el resultado final, para actualizar la tabla final necesitaríamos el motor del modelo de datos, que se encuentra en Power Query.
      Un saludo

      Eliminar
  3. Muchas gracias por la respuesta.
    A raiz de tus post estaba enredando con el Power Query que es una pasada y he tenido que modificar un archivo desde la versión gratuita de excel 365 y no podía actualizar los datos, y lo que es peor intente instalar Power Query y me dijo que ni de coña, que me hacia falta otra versión del 365 (supongo que la de pago).
    En cualquier caso es un complemento muy bueno. Esperare con ansia otros post que hagas sobre el mismo tema.
    Un saludo.

    ResponderEliminar
  4. Si es potente, la verdad es que MS está esforzándose en habilitar herramientas de BI.. y esta a mi modo de ver es muy sencilla de utilizar y con buenos resultados (sin llegar a ser PowerPivot o un Access).
    Un saludo

    ResponderEliminar
  5. una duda, estoy haciendo conexión de Power query a una base de datos con el fin de limpiar datos.
    Existirá alguna manera que Al hacer cambios o actualizaciones en Power query, se reflejen en la base de datos?

    ResponderEliminar
    Respuestas
    1. Hola Ana,
      el sentido de PowerQuery es unidireccional.. cualquier ajuste, cambio, modificación realizada en la herramienta nunca debería afectar el origen de los datos.
      Saludos

      Eliminar