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 Year 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024 2023 - 2024...
  • tharunkumarRTK's avatar
    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