Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I have a table structure like this:
Is it possible to transform it to this:
Maybe even in one step?
Solved! Go to Solution.
"Everything is doable in Power Query" - but - does it make sense? How will you guarantee that your raw data always has exactly two periods? Your IDs don't overlap completely - what if the older period has more IDs than the newer period?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XczBCQAgDEPRVSRnD21U0Fmk+68hAUHpIZcHP3uDxl5socI1IuqPFI6ETTgfut2cTKicnlA5R8Kui4WIAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [period = _t, id = _t, value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"period", each List.Max([period]), type nullable text}, {"rows", each _, type table [period=nullable text, id=nullable text, value=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "value", (k)=> List.Sum(Table.SelectRows(k[rows],each [period]=k[period])[value]),Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "prev. value", (k)=> List.Sum(Table.SelectRows(k[rows],each [period]<>k[period])[value]),Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"rows"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"period", "id", "value", "prev. value"})
in
#"Reordered Columns"
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.
Use a matrix visual with the periods in the column area, ids in the rows area, and the value in the values area.
Use Visual Calculations if you need to compare the values.
Need to do this particularly in PQ if it's even doable.
"Everything is doable in Power Query" - but - does it make sense? How will you guarantee that your raw data always has exactly two periods? Your IDs don't overlap completely - what if the older period has more IDs than the newer period?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XczBCQAgDEPRVSRnD21U0Fmk+68hAUHpIZcHP3uDxl5socI1IuqPFI6ETTgfut2cTKicnlA5R8Kui4WIAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [period = _t, id = _t, value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"period", each List.Max([period]), type nullable text}, {"rows", each _, type table [period=nullable text, id=nullable text, value=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "value", (k)=> List.Sum(Table.SelectRows(k[rows],each [period]=k[period])[value]),Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "prev. value", (k)=> List.Sum(Table.SelectRows(k[rows],each [period]<>k[period])[value]),Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"rows"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"period", "id", "value", "prev. value"})
in
#"Reordered Columns"
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.