Porque es importante Power Query?-Inteligencia de Negocios

Que es Power Pivot de Excel?

noviembre 13, 2017

Power BI + Python – Data Science

octubre 29, 2018

Porque es importante conocer Power Query – Excel ?

Power Query es un herramienta adicional (complemento disponible versión 2010) de Excel el cual permite conectar, integrar y transformar los datos, técnicamente es considerada una herramienta de ETL (Extracción, transformación y Carga) pero para usuario final (Inteligencia de negocios de auto servicio) , que significa esto?

Conectar/Extraer:

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

Integración: Es decir esta herramienta nos permite por ejemplo unir información de varios Excels sin la necesidad de utilizar herramientas adicionales o VBA. Tomando este ejemplo, supongamos que tenemos las ventas de La Paz, Cochabamba y Santa cruz en diferentes archivos Excels, y se requiere un reporte uniendo las ventas de toda la región, con power query esto es posible de manera muy fácil, logrando automatizar el reporte. Para este caso parece no ser tan importante unir 3 Excels, pero cuando hay empresas que por ejemplo tienen las ventas diarias por día en diferentes fuentes, imagínense hacer este trabajo de manera manual. Tengo clientes que les tomaba hacer semanas sus reportes, con Power Query es posible hacerlo en 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 Excels 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 Excels.

Esto es posible automatizarlo con power query.

Esto genera un código  llamado Lenguaje M(Monkey).
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"

Resultado de Power Query.

Cargar (Load):

Como paso final de Power query permite cargar los datos transformados e integrados (pasos previos) y ponerlos disponibles para su uso.

Tenemos dos opciones:

a) 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.

b) Only créate connection, esta opción es la que nos permite juntar la herramienta de Power pivot (https://www.linkedin.com/pulse/que-es-power-pivot-de-excel-fernando-terrazas-ocp/ ) 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).

Fernando Terrazas, OCP