Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
yevhen_87
Frequent Visitor

Create column with values from a previous period

I have a table structure like this:

1.png
Is it possible to transform it to this:
2.png

 




Maybe even in one step?

1 ACCEPTED 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.

 

 

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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.

 

 

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors