Unpivot

[rinson]

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 unpivot only the 2024 columns, it automatically applies to all columns, including 2025, and I also notice duplicates. I tried using ‘Unpivot Selected Columns,’ ‘Unpivot Other Columns,’ and ‘Unpivot Columns,’ but it still doesn’t work as expected. I only want to unpivot the selected months. How can I fix this?

[v-aatheeque]

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]

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]

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!

[aTa_Shaikh]

A Simple and StraightForward to solve this type of problem would be something like this :
You can explicity convert all those month name and year values to date and unpivot them .. Also you can select which columns using Text.Contains inside list.select function as well !
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]),
Cols = List.Select(
Table.ColumnNames( Source ) ,
each not ( try Date.From(_) )[HasError] ),
unpivot = Table.Unpivot(Source , Cols , "Month" , "Value")
in
unpivot

[vojtechsima]

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]

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!

[Jai-Rathinavel]

Hi @rinson , You would have to unpivot all the month columns from both the years (2024 & 2025) to avoid the duplications. It would be great if you can provide us with an expected output to resolve your issue.

Thanks,

Jai