Forum Discussion
How to hold values in variables while populating a column?
It's much easier: just use Fill Down and Replace Values on the Transform tab as demonstrated in this 30 sec video.
Well, it seems I was a bit fast with my answer...
In this case, the trick is to merge the table with itself so you will have Items and previous items on the same row.
The rest is pretty straightforward as you can see in this video.
During video recording, the following code was generated (in Power BI Desktop, with data from Excel):
let Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\How to hold variables etcetera.xlsx"), null, true), Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"Date", type date}, {"ItemID", Int64.Type}, {"Value", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Prev",JoinKind.LeftOuter), #"Expanded Prev" = Table.ExpandTableColumn(#"Merged Queries", "Prev", {"ItemID"}, {"Prev.ItemID"}), #"Sorted Rows" = Table.Sort(#"Expanded Prev",{{"Index", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if ([Prev.ItemID] = null or [Prev.ItemID] <> [ItemID]) then (if [Value] = null then 0 else [Value]) else [Value]), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Value", "Index", "Index.1", "Prev.ItemID"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Value"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}}) in #"Changed Type1"
It's much easier: just use Fill Down and Replace Values on the Transform tab as demonstrated in this 30 sec video.
I was a bit fast there in accepting the answer as a solution. It was not the solution i needed. I need the value to "reset" on each new item.
if we look at row 6, thats a new itemID and the value there sould be 0, not 1800.
Well, it seems I was a bit fast with my answer...
In this case, the trick is to merge the table with itself so you will have Items and previous items on the same row.
The rest is pretty straightforward as you can see in this video.
During video recording, the following code was generated (in Power BI Desktop, with data from Excel):
let Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\How to hold variables etcetera.xlsx"), null, true), Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"Date", type date}, {"ItemID", Int64.Type}, {"Value", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Prev",JoinKind.LeftOuter), #"Expanded Prev" = Table.ExpandTableColumn(#"Merged Queries", "Prev", {"ItemID"}, {"Prev.ItemID"}), #"Sorted Rows" = Table.Sort(#"Expanded Prev",{{"Index", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if ([Prev.ItemID] = null or [Prev.ItemID] <> [ItemID]) then (if [Value] = null then 0 else [Value]) else [Value]), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Value", "Index", "Index.1", "Prev.ItemID"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Value"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}}) in #"Changed Type1"Yes, thank you that solved my problem perfectly.
And you made a video! Awesome.
I have struggled learning the Power Query Formula Language, you know any good tutorials that covers the basics that can help me (and others ofc) to better understand the "flow" of the code.
Some suggestions can be found in the answer to this topic: https://community.powerbi.com/t5/Desktop/Training-in-the-UK/m-p/116687/highlight/true#M49239
Otherwise I can recommend the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
Personally I have been studying Power Query the hard way going through the information from Microsoft, reading blogs, trying things myself, answering questions on forums, for almost half a year now and continuing...