My data sample:
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 | Column11 | Column12 | Column13 |
Fiscal Year | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 |
Fiscal Month # | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
Fiscal Month | 01/04/2023 | 01/05/2023 | 01/06/2023 | 01/07/2023 | 01/08/2023 | 01/09/2023 | 01/10/2023 | 01/11/2023 | 01/12/2023 | 01/01/2024 | 01/02/2024 | 01/03/2024 |
Flooring | | | | 1 | | | | 1 | | | | |
Scaffolding | 4 | | | 7 | | | 9 | | | 4 | | |
Development | 1 | | | 6 | 7 | 9 | 9 | 9 | 7 | 7 | | 6 |
Here is my solution:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"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}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fiscal Year", type text}, {"Fiscal Month #", Int64.Type}, {"Fiscal Month", type datetime}, {"Flooring", Int64.Type}, {"Scaffolding", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Fiscal Year", "Fiscal Month #", "Fiscal Month"}, "Supplier", "Value")
in
#"Unpivoted Other Columns"
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
