Forum Discussion
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 | 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
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
2 Replies
- tharunkumarRTKSuper User
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
- cottreraPost Prodigy
Thank you for your quick reponse this solution worked.
Richard