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 5...
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
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.