Forum Discussion

CARL12345's avatar
CARL12345
Regular Visitor
3 years ago

Select Value from row before

Hello to everybody,  I have a question for calculating in PowerQuery with the value of the row before.  I have a list with movements of goods in a warehouse. Because there a of couse some goods wi...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Good morning, 

 

thx for your fast replies. Maybe it will be easier to understand if I upload some screenshots from the data I use. 

Because I decrease the column width, I will first explain what is in the colums.

Colum1: project number of cumstomer

Colum2: date of warehouse movement

Colum3: article code

Colum9: stock of arcticle before movement

Colum10: quantity of momement (- for taking material to production)

Colum11: stock after movement

Rest of colum is for my promblem not needed, but is needed for the presentation of the result. 

 

 

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

 

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: 

Solved: Re: Power Query/Power Pivot - Dynamically Calculat... - Microsoft Power BI Community

 

But to be honest: I didn't understand the code. 😞 

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi Anonymous

    CARL12345 

     

    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)=> a & {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

     

    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 else coum11 instead of 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}.

     

    Cheers,

    John