Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Transformar datos

Hola a todos puede alguien ayudar a transformar los datos? Traté de pivotar la tabla, pero seguí contando el error. Aprecio si alguien puede aconsejar. https://gofile.io/d/foD9u5 Gracias.
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Hey @kokoro_1202 ,

pruebe el siguiente código:

let
    Source = Excel.Workbook(File.Contents("D:\_\Sales.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"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}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Column1", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if not Text.StartsWith([Column1], "Shop") then "" else [Column1]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Custom", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Reordered Columns1", "Custom.1", each if Text.StartsWith([#"Column1"], "Shop") then "" else [#"Column1"]),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Conditional Column1",{"Custom", "Custom.1", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns2","",null,Replacer.ReplaceValue,{"Custom.1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Custom.1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"2018", Int64.Type}, {"2018_1", type text}, {"1/1/2018", type any}, {"2/1/2018", type any}, {"3/1/2018", type any}, {"4/1/2018", type any}, {"5/1/2018", type any}, {"6/1/2018", type any}, {"7/1/2018", type any}, {"8/1/2018", type any}, {"9/1/2018", type any}, {"10/1/2018", type any}, {"11/1/2018", type any}, {"12/1/2018", type any}, {"1/1/2019", type any}, {"2/1/2019", type any}, {"3/1/2019", type any}, {"Total", type any}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type2", each [#""] <> null and [#""] <> ""),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"", "Shop"}, {"2018", "Year"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"2018_1", "Total"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Shop", "Year"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}})
in
    #"Renamed Columns1"

El código no está optimizado, pero creo que debería darle el resultado deseado.

Si necesitas ayuda, por favor avísame.
Si respondiera a su pregunta, estaría feliz si pudiera marcar mi publicación como una solución ✔️ y darle un pulgar hacia arriba 👍
Saludos
Denis