Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2023 - 2024 | 2024 - 2025 | 2024 - 2025 | 2024 - 2025 | 2024 - 2025 |
Fiscal Month # | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 1 | 2 | 3 | 4 |
Fiscal Months | 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 | 01/04/2024 | 01/05/2024 | 01/06/2024 | 01/07/2024 |
Flooring | 10 | |||||||||||||||
Scaffolding | 1 | 1 | 1 | |||||||||||||
Development | 1 | 1 | 1 | |||||||||||||
Roofs | 1 | 1 | 1 | 3 | ||||||||||||
Fence | 1 | 1 | 1 | 1 | 3 | |||||||||||
Electrics | 1 | 3 |
I need help in using the unpivot power qury function to make the file look like this
Supplier | Fiscal Year | Fiscal Month # | Fiscal Months | Value |
Flooring | 2023 - 2024 | 1 | 01/04/2023 | |
Flooring | 2023 - 2024 | 2 | 01/05/2023 | |
Flooring | 2023 - 2024 | 3 | 01/06/2023 | |
Flooring | 2023 - 2024 | 4 | 01/07/2023 | |
Flooring | 2023 - 2024 | 5 | 01/08/2023 | |
Flooring | 2023 - 2024 | 6 | 01/09/2023 | |
Flooring | 2023 - 2024 | 7 | 01/10/2023 | |
Flooring | 2023 - 2024 | 8 | 01/11/2023 | |
Flooring | 2023 - 2024 | 9 | 01/12/2023 | |
Flooring | 2023 - 2024 | 10 | 01/01/2024 | |
Flooring | 2023 - 2024 | 11 | 01/02/2024 | |
Flooring | 2023 - 2024 | 12 | 01/03/2024 | |
Flooring | 2024 - 2025 | 1 | 01/04/2024 | 10 |
Flooring | 2024 - 2025 | 2 | 01/05/2024 | |
Flooring | 2024 - 2025 | 3 | 01/06/2024 | |
Flooring | 2024 - 2025 | 4 | 01/07/2024 | |
Flooring | 2024 - 2025 | 5 | 01/08/2024 | |
Flooring | 2024 - 2025 | 6 | 01/09/2024 | |
Flooring | 2024 - 2025 | 7 | 01/10/2024 | |
Flooring | 2024 - 2025 | 8 | 01/11/2024 | |
Flooring | 2024 - 2025 | 9 | 01/12/2024 | |
Flooring | 2024 - 2025 | 10 | 01/01/2025 | |
Flooring | 2024 - 2025 | 11 | 01/02/2025 | |
Flooring | 2024 - 2025 | 12 | 01/03/2025 | |
Scaffolding | 2023 - 2024 | 1 | 01/04/2023 | |
Scaffolding | 2023 - 2024 | 2 | 01/05/2023 | |
Scaffolding | 2023 - 2024 | 3 | 01/06/2023 | 1 |
Scaffolding | 2023 - 2024 | 4 | 01/07/2023 | |
Scaffolding | 2023 - 2024 | 5 | 01/08/2023 | |
Scaffolding | 2023 - 2024 | 6 | 01/09/2023 | |
Scaffolding | 2023 - 2024 | 7 | 01/10/2023 | 1 |
Scaffolding | 2023 - 2024 | 8 | 01/11/2023 | |
Scaffolding | 2023 - 2024 | 9 | 01/12/2023 | |
Scaffolding | 2023 - 2024 | 10 | 01/01/2024 | |
Scaffolding | 2023 - 2024 | 11 | 01/02/2024 | 1 |
Scaffolding | 2023 - 2024 | 12 | 01/03/2024 | |
Scaffolding | 2024 - 2025 | 1 | 01/04/2024 | |
Scaffolding | 2024 - 2025 | 2 | 01/05/2024 | |
Scaffolding | 2024 - 2025 | 3 | 01/06/2024 | |
Scaffolding | 2024 - 2025 | 4 | 01/07/2024 | |
Scaffolding | 2024 - 2025 | 5 | 01/08/2024 | |
Scaffolding | 2024 - 2025 | 6 | 01/09/2024 | 2 |
Scaffolding | 2024 - 2025 | 7 | 01/10/2024 | |
Scaffolding | 2024 - 2025 | 8 | 01/11/2024 | |
Scaffolding | 2024 - 2025 | 9 | 01/12/2024 | |
Scaffolding | 2024 - 2025 | 10 | 01/01/2025 | |
Scaffolding | 2024 - 2025 | 11 | 01/02/2025 | |
Scaffolding | 2024 - 2025 | 12 | 01/03/2025 | |
Development | 2023 - 2024 | 1 | 01/04/2023 | |
Development | 2023 - 2024 | 2 | 01/05/2023 | |
Development | 2023 - 2024 | 3 | 01/06/2023 | |
Development | 2023 - 2024 | 4 | 01/07/2023 | |
Development | 2023 - 2024 | 5 | 01/08/2023 | |
Development | 2023 - 2024 | 6 | 01/09/2023 | 1 |
Development | 2023 - 2024 | 7 | 01/10/2023 | |
Development | 2023 - 2024 | 8 | 01/11/2023 | 1 |
Development | 2023 - 2024 | 9 | 01/12/2023 | |
Development | 2023 - 2024 | 10 | 01/01/2024 | |
Development | 2023 - 2024 | 11 | 01/02/2024 | |
Development | 2023 - 2024 | 12 | 01/03/2024 | |
Development | 2024 - 2025 | 1 | 01/04/2024 | |
Development | 2024 - 2025 | 2 | 01/05/2024 | 1 |
Development | 2024 - 2025 | 3 | 01/06/2024 | |
Development | 2024 - 2025 | 4 | 01/07/2024 | 2 |
Development | 2024 - 2025 | 5 | 01/08/2024 | |
Development | 2024 - 2025 | 6 | 01/09/2024 | |
Development | 2024 - 2025 | 7 | 01/10/2024 | |
Development | 2024 - 2025 | 8 | 01/11/2024 | |
Development | 2024 - 2025 | 9 | 01/12/2024 | |
Development | 2024 - 2025 | 10 | 01/01/2025 | |
Development | 2024 - 2025 | 11 | 01/02/2025 | |
Development | 2024 - 2025 | 12 | 01/03/2025 | |
Roofs | 2023 - 2024 | 1 | 01/04/2023 | 1 |
Roofs | 2023 - 2024 | 2 | 01/05/2023 | |
Roofs | 2023 - 2024 | 3 | 01/06/2023 | |
Roofs | 2023 - 2024 | 4 | 01/07/2023 | |
Roofs | 2023 - 2024 | 5 | 01/08/2023 | 1 |
Roofs | 2023 - 2024 | 6 | 01/09/2023 | |
Roofs | 2023 - 2024 | 7 | 01/10/2023 | |
Roofs | 2023 - 2024 | 8 | 01/11/2023 | |
Roofs | 2023 - 2024 | 9 | 01/12/2023 | 1 |
Roofs | 2023 - 2024 | 10 | 01/01/2024 | |
Roofs | 2023 - 2024 | 11 | 01/02/2024 | 3 |
Roofs | 2023 - 2024 | 12 | 01/03/2024 | |
Roofs | 2024 - 2025 | 1 | 01/04/2024 | |
Roofs | 2024 - 2025 | 2 | 01/05/2024 | |
Roofs | 2024 - 2025 | 3 | 01/06/2024 | |
Roofs | 2024 - 2025 | 4 | 01/07/2024 | |
Roofs | 2024 - 2025 | 5 | 01/08/2024 | 1 |
Roofs | 2024 - 2025 | 6 | 01/09/2024 |
Thank you
Richard
Solved! Go to Solution.
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
Thank you for your quick reponse this solution worked.
Richard
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