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
aTa_Shaikh's avatar
aTa_Shaikh
Frequent Visitor
1 year ago

A Simple and Straightforward way 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