Forum Discussion
Create column with values from a previous period
I have a table structure like this:
Is it possible to transform it to this:
Maybe even in one step?
"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.
3 Replies
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.
