excelpowerbipowerquerydata-modelingm

Unpivot columns with multiple relevant rows with headers. PowerQuery M language | Multiple Unpivot simultaneously


I have a table which have multiple headers in the rows that I want to keep when I unpivot my table, but I couldn't find a way to unpivot it as needed. Please find below the example of the original table

Actuals Actuals Plan Plan
FY20 FY20 FY21 FY21
Country Owner 1/1/2020 1/1/2020 1/1/2020 1/1/2020
France Richard 100 150 80 160
France Martin 120 140 130 140
France Pierre 50 100 50 80

Below is what I want to achieve (unpivot the 4 columns, but keep the headers in the rows as new columns)

Country Owner Version Year Date Value
France Richard Actuals FY20 1/1/2020 100
France Richard Actuals FY21 1/1/2020 150
France Richard Plan FY20 1/1/2020 80
France Richard Plan FY21 1/1/2020 160
France Martin Actuals FY20 1/1/2020 120
France Martin Actuals FY21 1/1/2020 140
France Martin Plan FY20 1/1/2020 130
France Martin Plan FY21 1/1/2020 140
France Pierre Actuals FY20 1/1/2020 50
France Pierre Actuals FY21 1/1/2020 100
France Pierre Plan FY20 1/1/2020 50
France Pierre Plan FY21 1/1/2020 80

I believe this can only be achieved with the custom code, but I'm not familiar with it apart of the standard functionalities.

Thank you in advance and please let me know if any other info is relevant.

[Edit: The date in the table for Actuals should be FY20 and FY21, and the year should change as well, same for the Plan, should be FY20 and FY21 and the year should change as well. I won't change now as that would change the answers that solved my issue of converting the multiplied headers in the rows to columns]

Cheers


Solution

  • let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    NewNames=List.Transform(List.Zip({
            Record.FieldValues(Source{0}),
            Record.FieldValues(Source{1}),
            List.Transform(Record.FieldValues(Source{2}), each Text.From(_)
            )}), each Text.Combine(_,":")),
    Rename=Table.RenameColumns(Table.Skip(Source,3),List.Zip({Table.ColumnNames(Source),NewNames})),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Rename, {"Country", "Owner"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Version", "Year", "Date"}),
    Touchup=Table.TransformColumns(#"Split Column by Delimiter",{{"Version", each Text.Combine(List.RemoveItems(Text.ToList(_),{"0".."9"}))}}),
    Touchup2=Table.TransformColumns(Touchup,{{"Date", each Date.From(Number.From(_))}}),
    #"Changed Type" = Table.TransformColumnTypes(Touchup2,{{"Date", type date}})
    in #"Changed Type"
    

    enter image description here