Solved: complex data transformation

jcamilo1985 · ‎03-04-2021

Good Night Experts

This time I need help with data transformation I have a structure as in "original data source" and the idea is to get a structure as in "final file".

Can someone help me with this issue please, I need guidance and help.
Thank you

ah forgot the following, this is just a sample of the data, the records are more numerous

download the sample files

Jimmy801 · ‎03-04-2021

Hello @jcamilo1985

yes this was quite complex. But I found a way to do it. But be also aware that if the data is not extactly at the same place as in your Excel, the code will not work... meaning the starting point. Here the code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\ltnhoe\Documents\Kopie von original data source.xlsx"), null, true),
    #"200 Empresas (Mensual) COM_Sheet" = Source{[Item="200 Empresas (Mensual) COM",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"200 Empresas (Mensual) COM_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}, {"Column71", type any}, {"Column72", type any}, {"Column73", type any}, {"Column74", type any}, {"Column75", type any}}),
    GetFirstColumnNames = Table.FillDown(Record.ToTable(#"Changed Type"{1}), {"Value"}),
    ReplaceFirstColumnNames = Table.ReplaceRows
    (
        #"Changed Type",
        1,
        1,
        {Record.FromList(GetFirstColumnNames[Value],Table.ColumnNames(#"Changed Type") )}
    ),
    #"Removed Top Rows" = Table.Skip(ReplaceFirstColumnNames,1),
    CombineFirstTwoRows = List.Zip({Record.FieldValues(#"Removed Top Rows"{0}),Record.FieldValues(#"Removed Top Rows"{1})}),
    GenerateNewColumnNames = List.Zip({Table.ColumnNames(#"Removed Top Rows"), List.Transform(CombineFirstTwoRows, each if _{0}=null then "name" & Text.From(_{1}) else _{0} & "&" & Text.From(_{1}))}),
    RenameColumns = Table.RenameColumns(#"Removed Top Rows",GenerateNewColumnNames),
    #"Removed Top Rows1" = Table.Skip(RenameColumns,2),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Removed Top Rows1", {"nameNit con digito ", "nameNit Sin digito ", "namePRODUCTO", "nameRazon Social ", "nameEmpleados (Dato entregado por la caja)", "VENTAS 2020&TOTAL VENTAS Cotizantes Bog + Cund PBS 2020", "VENTAS 2020&TOTAL VENTAS Beneficiarios Bog + Cund PBS 2020", "VENTAS 2020&TOTAL VENTAS Bog + Cund PBS 2020", "VENTAS 2020&Total Cotizantes Bog+Cund Dic20"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns1", "Attribute", Splitter.SplitTextByDelimiter("&", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.2", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Fecha"}, {"nameNit con digito ", "Nit con digito"}, {"nameNit Sin digito ", "Nit Sin digito"}, {"namePRODUCTO", "PRODUCTO"}, {"nameRazon Social ", "Razon Social"}, {"nameEmpleados (Dato entregado por la caja)", "Empleados (Dato entregado por la caja)"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"VENTAS 2020&TOTAL VENTAS Cotizantes Bog + Cund PBS 2020", "VENTAS 2020&TOTAL VENTAS Beneficiarios Bog + Cund PBS 2020", "VENTAS 2020&Total Cotizantes Bog+Cund Dic20", "VENTAS 2020&TOTAL VENTAS Bog + Cund PBS 2020"})
in
    #"Removed Columns"

it transforms this (reading from your Excel)

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

jcamilo1985 · ‎03-05-2021

@Jimmy801 This is the solution I was needing, you are simply brilliant, thank you very much for the correct and agile response
good to find people like you who can help. Brilliant, cool. really thank you very much