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
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"