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
SundarRaj's avatar
SundarRaj
Super User
11 months ago

Hi rinson

As per what I understood from your query, I have created a dummy dataset. I'll share the Excel file with the required transformations in the Power Query window.

1. If you intend to only unpivot the SelectYear columns, you might have to get rid of the other year columns.

2. For unpivoting, Your FixedColumns (Item, Type, Country) and Attribute (Month-Year) and Value (Sales) should be present.

3. In the solution I've attached, what it does is, create a sort of slicer that enables you to select what year you would want to select and select only those columns pertaining to that year. (Along with the FixedColumns).

4. Post the unpivoting, you can create a separate YTD / Current Month logic since you'd have to remove them while unpivoting. Let me know if you'd the logic for YTD in PQ as well.

5. It's all dynamic, even if more years / FixedColumns come into the picture. Your query shouldn't break.

6. Also, here I've taken years, but you can surely create a custom logic in order to have just the selected months you want to.

Let me know if this is what you were looking for. Thanks!