what-is-excel-power-pivot
¿Qué es Power Pivot de Excel?
noviembre 13, 2017
power-bi-python-data-science
Power BI + Python – Data Science
octubre 29, 2018
what-is-excel-power-pivot
¿Qué es Power Pivot de Excel?
noviembre 13, 2017
power-bi-python-data-science
Power BI + Python – Data Science
octubre 29, 2018
Mostrar todo

¿Por qué es importante conocer Power Query – Excel?

why-is-it-important-to-know-power-query-excel

Power Query es una herramienta adicional (add-on disponible en la versión 2010) de Excel que permite conectar, integrar y transformar datos, técnicamente se considera una herramienta ETL (Extract, Transform and Load), pero, para el usuario final (autoservicio de Business Intelligence), ¿qué significa?

Conectar/Extraer

Conectar a muchas otras fuentes externas de base de datos, incluso a Facebook.

Integración

En otras palabras, esta herramienta nos permite consolidar información de múltiples archivos Excel sin necesidad de herramientas adicionales o VBA. Por ejemplo, supongamos que tenemos datos de ventas de la ciudad A, la ciudad B y la ciudad C almacenados en archivos Excel separados, y necesitamos generar un informe que agregue las ventas de toda la región. Con Power Query, esta tarea puede completarse fácilmente, permitiéndonos automatizar el proceso de generación de informes. Aunque fusionar tres archivos de Excel puede no parecer importante, piense en las empresas que realizan un seguimiento diario de las ventas a partir de varias fuentes; recopilar manualmente estos datos puede ser desalentador. Tenemos clientes que antes tardaban semanas en generar sus informes, pero con Power Query, ahora pueden hacerlo en sólo unas horas.

Transformación

Supongamos que tenemos información donde los años estén separados de los meses, y días, es posible crear columnas calculadas a partir de otras columnas.

Algunas opciones:

  • Dividir columnas de una fecha separarlo en día, mes y año
  • Reemplazar valores
  • Crear una columna calculada
  • Filtrar Datos
  • Ordenar
  • Cambiar tipos de datos
  • Condicionales

Por ejemplo, supongan que tienen la información de Estados financieros en Excel separados, por año, y Uds. quieren hacer un reporte histórico de la evolución de las cuentas, Ingresos, Gastos operativos, Costos de Proyectos, Estado de resultados, etc., esto no es difícil si son 2 archivos, pero que pasa cuando se tiene varios archivos Excel.

Esto se puede automatizar con Power Query de la siguiente manera:

let
    Source = Folder.Files("E:\Contabilidad"),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "ObjetosExcel", each Excel.Workbook([Content],true)),
    #"Expanded ObjetosExcel" = Table.ExpandTableColumn(#"Added Custom", "ObjetosExcel", {"Name", "Data", "Item", "Kind", "Hidden"}, {"ObjetosExcel.Name", "ObjetosExcel.Data", "ObjetosExcel.Item", "ObjetosExcel.Kind", "ObjetosExcel.Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ObjetosExcel", each ([ObjetosExcel.Name] = "3")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"ObjetosExcel.Item", "ObjetosExcel.Kind", "ObjetosExcel.Hidden", "ObjetosExcel.Name", "Content"}),
    #"Expanded ObjetosExcel.Data" = Table.ExpandTableColumn(#"Removed Columns1", "ObjetosExcel.Data", {"ACME S.R.L.", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36"}, {"ObjetosExcel.Data.ACME S.R.L.", "ObjetosExcel.Data.Column2", "ObjetosExcel.Data.Column3", "ObjetosExcel.Data.Column4", "ObjetosExcel.Data.Column5", "ObjetosExcel.Data.Column6", "ObjetosExcel.Data.Column7", "ObjetosExcel.Data.Column8", "ObjetosExcel.Data.Column9", "ObjetosExcel.Data.Column10", "ObjetosExcel.Data.Column11", "ObjetosExcel.Data.Column12", "ObjetosExcel.Data.Column13", "ObjetosExcel.Data.Column14", "ObjetosExcel.Data.Column15", "ObjetosExcel.Data.Column16", "ObjetosExcel.Data.Column17", "ObjetosExcel.Data.Column18", "ObjetosExcel.Data.Column19", "ObjetosExcel.Data.Column20", "ObjetosExcel.Data.Column21", "ObjetosExcel.Data.Column22", "ObjetosExcel.Data.Column23", "ObjetosExcel.Data.Column24", "ObjetosExcel.Data.Column25", "ObjetosExcel.Data.Column26", "ObjetosExcel.Data.Column27", "ObjetosExcel.Data.Column28", "ObjetosExcel.Data.Column29", "ObjetosExcel.Data.Column30", "ObjetosExcel.Data.Column31", "ObjetosExcel.Data.Column32", "ObjetosExcel.Data.Column33", "ObjetosExcel.Data.Column34", "ObjetosExcel.Data.Column35", "ObjetosExcel.Data.Column36"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded ObjetosExcel.Data", each ([#"ObjetosExcel.Data.ACME S.R.L."] <> "ESTADO DE RESULTADO MENSUAL") and ([ObjetosExcel.Data.Column2] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Removed Columns2" = Table.RemoveColumns(#"Promoted Headers",{"Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns2", "GrupoCuenta", each if [DETALLE] ="INGRESOS OPERATIVOS" then [DETALLE] else if[DETALLE] ="COSTO DE PROYECTOS" then [DETALLE] else if [DETALLE] ="GASTOS ADMINISTRATIVOS" then [DETALLE] else if [DETALLE] ="OTROS  EGRESOS E INGRESOS" then [DETALLE] else if[DETALLE] ="RESULTADO DE LA GESTION" then [DETALLE]  else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"GrupoCuenta"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Informe Económico_ACME_Diciembre_2016.xlsx", "No DE", "GrupoCuenta", "DETALLE", "GESTIÓN", "ENERO", "FEBRERO", "MARZO", "ABRIL", "MAYO", "JUNIO", "JULIO", "AGOSTO", "SEPTIEMBRE", "OCTUBRE", "NOVIEMBRE", "DICIEMBRE", "EST. DE RESULT."}),
    #"Filtered Rows2" = Table.SelectRows(#"Reordered Columns", each ([No DE] <> null)),
    #"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows2",{"GESTIÓN"}),
    #"Filtered Rows4" = Table.SelectRows(#"Removed Columns3", each ([ENERO] <> "ENERO")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows4", {"Informe Económico_ACME_Diciembre_2016.xlsx", "No DE", "GrupoCuenta", "DETALLE"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Informe Económico_ACME_Diciembre_","",Replacer.ReplaceText,{"Informe Económico_ACME_Diciembre_2016.xlsx"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".xlsx","",Replacer.ReplaceText,{"Informe Económico_ACME_Diciembre_2016.xlsx"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"No DE", "NroCuenta"}, {"DETALLE", "Cuenta"}, {"Attribute", "Mes"}, {"Value", "Valor"}}),
    #"Filtered Rows3" = Table.SelectRows(#"Renamed Columns", each ([Mes] <> "EST. DE RESULT.")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows3",{{"Informe Económico_ACME_Diciembre_2016.xlsx", "Anio"}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns1","Informe Económico_ACME_Junio_","",Replacer.ReplaceText,{"Anio"}),
    #"Filtered Rows5" = Table.SelectRows(#"Replaced Value2", each true),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows5",{{"Valor", type number}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Mesnro", each if [Mes] = "ENERO" then "1" else if [Mes] = "FEBRERO" then "2" else if [Mes] = "MARZO" then "3" else if [Mes] = "ABRIL" then "4" else if [Mes] = "MAYO" then "5" else if [Mes] = "JUNIO" then "6" else if [Mes] = "JULIO" then "7" else if [Mes] = "AGOSTO" then "8" else if [Mes] = "SEPTIEMBRE" then "9" else if [Mes] = "OCTUBRE" then "10" else if [Mes] = "NOVIEMBRE" then "11" else if [Mes] = "DICIEMBRE" then "12" else null ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Mesnro", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Mes", Order.Ascending}})
in
    #"Sorted Rows"

Esto se puede automatizar con Power Query de la siguiente manera:

Cargar (Load)

Como paso final, Power Query permite cargar los datos transformados e integrados (pasos anteriores) y ponerlos a disposición para su uso.

Tenemos dos opciones:

  1. Tabla, que son los datos cargados al mismo Excel. A partir de estos datos se puede crear tablas dinámicas o realizar otros reportes. La ventaja de esta opción es que tenemos la restricción de nro. de registros en el Excel 1.4 millón aproximadamente.
  2. Only create connection, esta opción es la que nos permite juntar la herramienta de Power Pivot y Power Query, el cual nos permite añadir la información generada de Power Query y cargarla al Power Pivot, permitiendo manejar miles de millones de registros.

Ejemplo de Reportes Históricos Financieros (Power Query + Power Pivot)

cookies-ippb
Utilizamos cookies para optimizar la experiencia en nuestro sitio web www.ippbsolutions.com. Son esenciales para garantizar el correcto funcionamiento del sitio y nuestros servicios, permitiendo personalizar tu experiencia. Para más información, consulta nuestra Política de Privacidad.
Leer más