How to hold values in variables while populating a column?

[kalleeljas]

Hello.

Im trying to create a table that will replace cell values based on previous value for each itemID.

Row 1 should be 0, because there is no value yet, row 2 gets a value and row 3 should get the value from row 2 etc.

But to do this i need to use a variable that can hold values for me while populating the column, and this is where i need help.

i tried to use parameters and Lists, but i cant seem to get it to work, (if it can be done at all?)

The table below is the table im using, and I manually inserted a column that shows the correct values and its sorted the way i want (date and itemID).

Date	ItemID	Value	Correct values
2017-01-01	1	null	0
2017-01-02	1	2000	2000
2017-01-03	1	null	2000
2017-01-04	1	null	2000
2017-01-05	1	1800	1800
2017-01-01	2	null	0
2017-01-02	2	null	0
2017-01-03	2	500	500
2017-01-04	2	400	400
2017-01-05	2	null	400

The image below is my current result, and as you can see it doesnt hold the last valus for each itemID.

The Column "Calc val clm" is the one i want to look like "Correct values"

Table code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\kalle.eljas\SharePoint\Dalarna Power BI - Dokument\Extern\Byxshoppen\problem\Tableprobs.xlsx"), null, true),
    ValueTable_Table = Source{[Item="ValueTable",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(ValueTable_Table,{{"Date", type date}, {"ItemID", Int64.Type}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ItemID", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Correct values", Int64.Type}}),
    #"Added Value clm" = Table.AddColumn(#"Changed Type1", "Calc val clm", each 
        if [ItemID] = List.Last(ValueList)
        then List.First(CurrStockValueFn([Value],[ItemID]))
        else List.First(CurrStockValueFnFirst([Value],[ItemID])) )

in
    #"Added Value clm"

Functions:

CurrStockValueFn():
let
    CurrStock = (stock, item) => 
            List.ReplaceRange(ValueList,  0 , 2,  {stock, item}) 
in
    CurrStock


CurrStockValueFnFirst():
let
    CurrStock = (stock, item) => 
            if stock = null
            then List.ReplaceRange(ValueList,  0 , 2,  {0, item}) 
            else List.ReplaceRange(ValueList,  0 , 2,  {stock, item}) 
in
    CurrStock

And  the ValueList is simply a list i made from ={0,0} where the first parameter is value and second is ItemID

So the questions are really:

• can I use lists the way im trying to here?
• Can I use parameters? I couldnt find a way to set the values at all with parameters.
• Or am I missing something? Is there any other way of holding values while populationg a column?

// Kalle Eljas

[MarcelBeug · Accepted solution]

It's much easier: just use Fill Down and Replace Values on the Transform tab as demonstrated in this 30 sec video.

[MarcelBeug · Accepted solution]

It's much easier: just use Fill Down and Replace Values on the Transform tab as demonstrated in this 30 sec video.

[kalleeljas]

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.

[MarcelBeug · Accepted solution]

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"

[kalleeljas]

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.

[MarcelBeug]

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

[kalleeljas]

That was easy. Thanks!