Need help to transform power query table

[Trahan-Almighty]

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	55	0
43	58	86	1
65	67	80	1
28	55	54	0
16	23	30	18
58	49	52	0
58	55	79	93
2	2	3	0

Wished result (grouped)

	2022	2023	2024	2025
0-24	2	1	1	7
25-49	3	2	1	0
50-74	4	3	3	0
75-100	0	0	3	1

[wdx223_Daniel · Accepted solution]

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]

@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"

[lbendlin]

Please check your math.

NOTE: While this can be done in Power Query it should NOT be done in Power Query. Instead, bring your data into a usable format and then let Power BI do the pivoting.

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 if [Value]<25 then "0-24" else 
if [Value]<50 then "25-49" else 
if [Value]<75 then "50-74" else "75-100"),
    #"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"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.