Forum Discussion

rinson's avatar
rinson
New Member
1 year ago
Solved

Unpivot

Hi, In Power Query Editor, I have different columns like Item, Type, Country, along with monthly columns (Jan–Dec) for 2024 and 2025, plus columns like Current Month Sales, YTD, etc. When I try to un...
  • vojtechsima's avatar
    vojtechsima
    1 year ago

    Hey, rinson

    by nature of what you wanna do, you will always produce duplicates, however, it's possible to unpivot as you want, follow this code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY+7CsJAEEX/ZesU2XkIlgG10UJ8VCFFhICFmBT5f7J7hF2buzuXMwem70MX2xiacH3P3ym9z3uXMkqbU0kjPae0ZCRhlMYTMzTIJI2XcVnnJdvOBRRkgky8rioyhTGafZVpGh/j6zOt6XM4FlCRKTL1umrIDMZpolSb/d15uhXQkBky87rqyBxm95Nx57AB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Country = _t, #"Jan-2024" = _t, #"Feb-2024" = _t, #"Mar-2024" = _t, #"Apr-2024" = _t, #"Jan-2025" = _t, #"Feb-2025" = _t, #"Mar-2025" = _t, #"Current Month Sales" = _t, YTD = _t]),
    columnsToKeep= List.Select( Table.ColumnNames( Source ), each not Text.Contains(_, "2024" ) ),
    unpivot = Table.UnpivotOtherColumns(Source, columnsToKeep, "YearMonth", "Sales" )
in
    unpivot

     

    Copy paste it into Blank Query to see example.

     

    If you want a better approach, I recommend keeping only the stuff you want to pivot, like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY+7CsJAEEX/ZesU2XkIlgG10UJ8VCFFhICFmBT5f7J7hF2buzuXMwem70MX2xiacH3P3ym9z3uXMkqbU0kjPae0ZCRhlMYTMzTIJI2XcVnnJdvOBRRkgky8rioyhTGafZVpGh/j6zOt6XM4FlCRKTL1umrIDMZpolSb/d15uhXQkBky87rqyBxm95Nx57AB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Country = _t, #"Jan-2024" = _t, #"Feb-2024" = _t, #"Mar-2024" = _t, #"Apr-2024" = _t, #"Jan-2025" = _t, #"Feb-2025" = _t, #"Mar-2025" = _t, #"Current Month Sales" = _t, YTD = _t]),
    columnsToKeepStatic= {"YTD", "Current Month Sales", "Country", "Type", "Item"},
    columnsToKeep2024 = List.Select( Table.ColumnNames( Source ), each  Text.Contains(_, "2024" ) ),
    filterRightColumns = Table.SelectColumns( Source, columnsToKeepStatic & columnsToKeep2024 ),
    unpivot = Table.UnpivotOtherColumns(filterRightColumns, columnsToKeepStatic, "YearMonth", "Sales" )
in
    unpivot
v-aatheeque's avatar
v-aatheeque
Community Support
11 months ago

Hi rinson 

We haven’t heard from you on the last response and was just checking back to see if your query was answered, Otherwise, will respond back with the more details and we will try to help .

v-aatheeque's avatar
v-aatheeque
Community Support
11 months ago

Hi rinson 

We’d like to check if you were able to go through the  responses to your issue.
Please let us know if you need further clarification we’ll do our best to support you.


If we don’t hear back, we may close this thread in line with our community guidelines, but you’re always welcome to post a new query anytime.

Thank you for being part of the Microsoft Fabric Community!