Forum Discussion

rinson's avatar
rinson
New Member
11 months 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
    11 months 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 

Just following up on the solution already shared earlier regarding unpivoting only the 2024 columns in Power Query. Were you able to try that approach and check if it works for your scenario?

If you’re still facing the same issue with duplicate values or unwanted 2025 columns being unpivoted, please let us know we’d be happy to help troubleshoot further.

  • 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!