Forum Discussion
cottrera
2 years agoPost Prodigy
Unpivot / Pivot help needed
Hi My power bi report is connecting to an excel file that looks like this. Fiscal Year 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024...
- 2 years ago
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
cottrera
2 years agoPost Prodigy
Thank you for your quick reponse this solution worked.
Richard