Forum Discussion

Trahan-Almighty's avatar
Trahan-Almighty
New Member
1 year ago
Solved

Need help to transform power query table

Hello,   I wish to transform this current table into grouped table. Is it possible do this in Power Query?    Thanks in advance,    Current table:    2022  2023  2024  2025 66 56 5...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    1 year ago

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.UnpivotOtherColumns(Source,{},"Year","Value"),
    Custom2 = Table.AddColumn(Custom1,"Category",each List.Skip({{75,"75-100"},{50,"50-74"},{25,"25-49"},{0,"0-24"}},(x)=>x{0}>[Value]){0}?{1}?),
    Custom3 = Table.Pivot(Custom2,List.Distinct(Custom2[Year]),"Year","Value",List.Count)
in
    Custom3

     

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
1 year ago

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.UnpivotOtherColumns(Source,{},"Year","Value"),
    Custom2 = Table.AddColumn(Custom1,"Category",each List.Skip({{75,"75-100"},{50,"50-74"},{25,"25-49"},{0,"0-24"}},(x)=>x{0}>[Value]){0}?{1}?),
    Custom3 = Table.Pivot(Custom2,List.Distinct(Custom2[Year]),"Year","Value",List.Count)
in
    Custom3

 

lbendlin's avatar
lbendlin
Super User
1 year ago

wdx223_Daniel two can play this game...

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYzLDQAhCER74exBQRBrIfbfxjqY5TLJfN5EkBk10hS90um0oCnwfsXRjAwNvS2EvUL2n9RZ+ADE+JBceqb5NzemXFMtfqHZ8l7BA3+78wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2022" = _t, #" 2023" = _t, #" 2024" = _t, #" 2025" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Year", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Year", Text.Trim, type text}, {"Value", Text.Trim, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Group", each  {"0-24","25-49","50-74","75-100"}{Number.RoundDown([Value]/25)}),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Year", "Group"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Year]), "Year", "Count")
in
    #"Pivoted Column"