Select Value from row before

Question

Hello to everybody,&nbsp;I have a question for calculating in PowerQuery with the value of the row before.&nbsp;I have a list with movements of goods in a warehouse.Because there a of couse some goods with more than one movement I want to calculate the value for this good referring to the value before the movement.&nbsp;&nbsp;As an example:&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;stock per date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Article&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;kind of movement(+ for orders&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;quantity&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; /- for taking material to production)&nbsp; &nbsp;&nbsp;01.01.2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02.01.2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;403.01.2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1503.01.2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 16&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp;&nbsp;Amd so on... Is there a posibility to solve this problem?&nbsp;I alredy added a colum with an index and an second colum with an index +1. So I can merge the table with itsself an I can check if the value of the colum for the arcitle in the row before is the same value in the actual row. So far, so nice. But now I am not flexbile enough with my code and not far enough with my knowledge of writing formulas in M. How can I calculate the current value with the value before?&nbsp;&nbsp;In Excel so far it is possible for me, but I would like to do it in Query, because doing it in Excel after refreshing the data not all colums are filled with the formula I use in Excel.&nbsp;&nbsp;Thanks in advance

kt_bsmart2gethe · Answer

Hi&nbsp;CARL12345&nbsp;,
&nbsp;
I don't seem to be able to see $value in the provided example.&nbsp;
&nbsp;
Cell Reference&nbsp;
&nbsp;
Let me know if it helps with your case. O

jbwtp · Answer

Hi&nbsp;CARL12345,&nbsp;Would this be something similar to this post?Solved: Re: Power Query/Power Pivot - Dynamically Calculat... - Microsoft Power BI Community&nbsp;Cheers,John&nbsp;

anonymous · Answer

Good morning,&nbsp;&nbsp;thx for your fast replies. Maybe it will be easier to understand if I upload some screenshots from the data I use.&nbsp;Because I decrease the column width, I will first explain what is in the colums.Colum1: project number of cumstomerColum2: date of warehouse movementColum3: article codeColum9: stock of arcticle before movementColum10: quantity of momement (- for taking material to production)Colum11: stock after movementRest of colum is for my promblem not needed, but is needed for the presentation of the result.&nbsp;&nbsp;&nbsp;I tried this code as a solution for my problem:= Table.AddColumn(#"addcolum", "Benutzerdefiniert", each if [Häufigkeit]="Doppelt" then ([colum11]{-1}+[colum10]) else [colum10]).&nbsp;If there is just one movement for an article this code is useful, but with two or more movements not. (s. Screenshot) I get an error, for article with more movements. Value can't be converted in a list.I also had a look at this solution:&nbsp;Solved: Re: Power Query/Power Pivot - Dynamically Calculat... - Microsoft Power BI Community&nbsp;But to be honest: I didn't understand the code. 😞&nbsp;

jbwtp · Answer

Hi&nbsp;AnonymousCARL12345&nbsp;&nbsp;The straightforward solution to your code (Table.AddColumn(#"addcolum", "Benutzerdefiniert", each if [Häufigkeit]="Doppelt" then ([colum11]{-1}+[colum10]) else [colum10]).) is below. I've added some comments in the code, which hopefully make sense.let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDC2VNJRcs1MS0zOADIMDcz1jCzNgCxdQz0DI7CImZ6RuZlSrA6Sepf8goLUnBIiNJhYGJkiW2AExSao8ggDQZK6BnrGIFFDPTNTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atricle = _t, Häufigkeit = _t, colum9 = _t, colum10 = _t, colum11 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"colum9", type number}, {"colum10", type number}, {"colum11", type number}}),
    
    // Code in the next line is iterating through all rows in the table applying the test and it test is satisfied we take a previous line [stored as last item in a] via List.Last(a)
    // and then add it to colum10, otherwise [if the test fails] we take colum10 only
    Calculate = List.Accumulate(Table.ToRecords(#"Changed Type"), {}, (a,n)=&gt; a &amp; {Record.AddField(n, "Benutzerdefiniert", if n[Häufigkeit]="Doppelt" then (List.Last(a)[colum11]+n[colum10]) else n[colum10])} ),
    
    //converting the list of records which we made on the previous step back to a table using value.type to format the table
    BackToTable = Table.FromRecords(Calculate, Value.Type(Table.AddColumn(#"Changed Type", "Benutzerdefiniert", each null, type number)))
in
    BackToTable&nbsp;But I do not understand the meaning of the column [Benutzerdefiniert] that you create. May be there is someting wrong with the formula? Did you mean to use&nbsp;else coum11&nbsp;instead of&nbsp;colum10? Anyway, it should be easy to sort out. AAs you can see, I pretty much just using a straight copy paste of your formula, just refereing it to List.Last rather than {-1}.&nbsp;Cheers,John&nbsp;

Forum Discussion

Select Value from row before

4 Replies

Related content

Count Lastest Records Before Selected Date

Latest value before a selected date

Display Selected and not selected values

show last value on or before selected date

Parameter: Selected Value doesn’t updated