Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 with more than one movement I want to calculate the value for this good referring to the value before the movement.
As an example:
Date stock per date Article kind of movement(+ for orders quantity
/- for taking material to production)
01.01.2022 10 123456 - 5 02.01.2022 5 123456 - 4
03.01.2022 1 123456 + 15
03.01.2022 16 123456 - 5
Amd so on... Is there a posibility to solve this problem?
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?
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.
Thanks in advance
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. 😞
Hi @Anonymous
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
Hi @CARL12345,
Would this be something similar to this post?
Solved: Re: Power Query/Power Pivot - Dynamically Calculat... - Microsoft Power BI Community
Cheers,
John
Hi @CARL12345 ,
I don't seem to be able to see $value in the provided example.
Let me know if it helps with your case. O
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
35 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
45 | |
27 | |
16 | |
14 | |
14 |