Forum Discussion
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 | 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 |
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
3 Replies
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 Custom3wdx223_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"
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.
