Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
2 years ago
Solved

Unpivot / Pivot help needed

Hi 

My power bi report is connecting to an excel file that looks like this.

 

Fiscal Year2023 - 20242023 - 20242023 - 20242023 - 20242023 - 20242023 - 20242023 - 20242023 - 20242023 - 20242023 - 20242023 - 20242023 - 20242024 - 20252024 - 20252024 - 20252024 - 2025
Fiscal Month #1234567891011121234
Fiscal Months01/04/202301/05/202301/06/202301/07/202301/08/202301/09/202301/10/202301/11/202301/12/202301/01/202401/02/202401/03/202401/04/202401/05/202401/06/202401/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

SupplierFiscal YearFiscal Month #Fiscal MonthsValue
Flooring 2023 - 2024101/04/2023 
Flooring 2023 - 2024201/05/2023 
Flooring 2023 - 2024301/06/2023 
Flooring 2023 - 2024401/07/2023 
Flooring 2023 - 2024501/08/2023 
Flooring 2023 - 2024601/09/2023 
Flooring 2023 - 2024701/10/2023 
Flooring 2023 - 2024801/11/2023 
Flooring 2023 - 2024901/12/2023 
Flooring 2023 - 20241001/01/2024 
Flooring 2023 - 20241101/02/2024 
Flooring 2023 - 20241201/03/2024 
Flooring 2024 - 2025101/04/202410
Flooring 2024 - 2025201/05/2024 
Flooring 2024 - 2025301/06/2024 
Flooring 2024 - 2025401/07/2024 
Flooring 2024 - 2025501/08/2024 
Flooring 2024 - 2025601/09/2024 
Flooring 2024 - 2025701/10/2024 
Flooring 2024 - 2025801/11/2024 
Flooring 2024 - 2025901/12/2024 
Flooring 2024 - 20251001/01/2025 
Flooring 2024 - 20251101/02/2025 
Flooring 2024 - 20251201/03/2025 
Scaffolding2023 - 2024101/04/2023 
Scaffolding2023 - 2024201/05/2023 
Scaffolding2023 - 2024301/06/20231
Scaffolding2023 - 2024401/07/2023 
Scaffolding2023 - 2024501/08/2023 
Scaffolding2023 - 2024601/09/2023 
Scaffolding2023 - 2024701/10/20231
Scaffolding2023 - 2024801/11/2023 
Scaffolding2023 - 2024901/12/2023 
Scaffolding2023 - 20241001/01/2024 
Scaffolding2023 - 20241101/02/20241
Scaffolding2023 - 20241201/03/2024 
Scaffolding2024 - 2025101/04/2024 
Scaffolding2024 - 2025201/05/2024 
Scaffolding2024 - 2025301/06/2024 
Scaffolding2024 - 2025401/07/2024 
Scaffolding2024 - 2025501/08/2024 
Scaffolding2024 - 2025601/09/20242
Scaffolding2024 - 2025701/10/2024 
Scaffolding2024 - 2025801/11/2024 
Scaffolding2024 - 2025901/12/2024 
Scaffolding2024 - 20251001/01/2025 
Scaffolding2024 - 20251101/02/2025 
Scaffolding2024 - 20251201/03/2025 
Development2023 - 2024101/04/2023 
Development2023 - 2024201/05/2023 
Development2023 - 2024301/06/2023 
Development2023 - 2024401/07/2023 
Development2023 - 2024501/08/2023 
Development2023 - 2024601/09/20231
Development2023 - 2024701/10/2023 
Development2023 - 2024801/11/20231
Development2023 - 2024901/12/2023 
Development2023 - 20241001/01/2024 
Development2023 - 20241101/02/2024 
Development2023 - 20241201/03/2024 
Development2024 - 2025101/04/2024 
Development2024 - 2025201/05/20241
Development2024 - 2025301/06/2024 
Development2024 - 2025401/07/20242
Development2024 - 2025501/08/2024 
Development2024 - 2025601/09/2024 
Development2024 - 2025701/10/2024 
Development2024 - 2025801/11/2024 
Development2024 - 2025901/12/2024 
Development2024 - 20251001/01/2025 
Development2024 - 20251101/02/2025 
Development2024 - 20251201/03/2025 
Roofs2023 - 2024101/04/20231
Roofs2023 - 2024201/05/2023 
Roofs2023 - 2024301/06/2023 
Roofs2023 - 2024401/07/2023 
Roofs2023 - 2024501/08/20231
Roofs2023 - 2024601/09/2023 
Roofs2023 - 2024701/10/2023 
Roofs2023 - 2024801/11/2023 
Roofs2023 - 2024901/12/20231
Roofs2023 - 20241001/01/2024 
Roofs2023 - 20241101/02/20243
Roofs2023 - 20241201/03/2024 
Roofs2024 - 2025101/04/2024 
Roofs2024 - 2025201/05/2024 
Roofs2024 - 2025301/06/2024 
Roofs2024 - 2025401/07/2024 
Roofs2024 - 2025501/08/20241
Roofs2024 - 2025601/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

  • 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