Trasform grouped header in a proper table  [Solved]

luca
Posts: 26
Joined: Sun Mar 03, 2019 6:08 am

Trasform grouped header in a proper table

Postby luca » Fri Jun 28, 2019 1:04 am

Hi all,

i would like to trasform the following table using power query.
the excel grouped header has to be the header of the new table while the year has to become a column
can anyone help me with this transformation.
Thanks
bye
Luca
Attachments
trasform in a table.xlsx
(15.18 KiB) Downloaded 15 times

Rudi
Posts: 16
Joined: Fri Sep 01, 2017 4:33 pm

Re: Trasform grouped header in a proper table  [Solved]

Postby Rudi » Fri Jun 28, 2019 5:51 pm

Hi,

How about this:

trasform in a table.xlsx
(26.36 KiB) Downloaded 17 times

M-Code

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"1", "Nombre 1"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"1", type text}, {"Nombre 1", type text}, {"Attribute", type text}, {"Value", type number}})
in
    #"Changed Type"

luca
Posts: 26
Joined: Sun Mar 03, 2019 6:08 am

Re: Trasform grouped header in a proper table

Postby luca » Wed Jul 10, 2019 2:23 am

Rudi {L_WROTE}{L_COLON}Hi,

How about this:

trasform in a table.xlsx
M-Code

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"1", "Nombre 1"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"1", type text}, {"Nombre 1", type text}, {"Attribute", type text}, {"Value", type number}})
in
    #"Changed Type"


Thank you so much Rudi,

i added a final step to your code:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"1", "Nombre 1"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"1", type text}, {"Nombre 1", type text}, {"Attribute", type text}, {"Value", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"1"]), "1", "Value", List.Sum)
in
#"Pivoted Column"

but it was perfect


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest