Forum Discussion
Last value power query
Please, a need help for the following problem.
I have 3 columns, Date, Code and Stallment. I need add two columns woth the following values:
1) The last value of Stallments. That is ok. I got it!
2) The last value of Stallment that the column Code is equal zero (Code=0). That is the problem.
The answer would be like that.
| Date | Code | stallment | Last stallment | Last stalment with code = 0 |
| 29/03/2022 | 50 | 03/15 | ||
| 30/04/2022 | 0 | 05/15 | 03/15 | 05/15 |
| 01/05/2022 | 21 | 05/15 | 05/15 | |
| 25/05/2022 | 0 | 06/15 | ||
| 30/05/2022 | 50 | 05/15 | 06/15 | 06/15 |
| 28/06/2022 | 50 | 06/15 | 05/15 | 06/15 |
| 26/07/2022 | 0 | 08/15 | 06/15 | 08/15 |
| 29/07/2022 | 50 | 07/15 | 08/15 | 08/15 |
| 12/08/2022 | 33 | 07/15 | 08/15 | |
| 25/08/2022 | 62 | 09/15 | 08/15 |
Ok. Still not 100% sure, 😁 but is this what you want?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDBCsMgDAbgd/HckRgb1x53Ley0o/Sww9hljDHooW+/qjXVDESCfn8ihmBoBHRASGQ6w7htlk/X+3cr4pq7YBwC9oWIWGu6n0RtgYslm7u0PjNiQFZNp+WtxvL/y9ba7kVqOAB6zUU1ucQ94FnNvyxPFYsnSY+VPpq/VK5wSzAU7Zx8QhMonyDQU768PT6SSHD+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t, stallment = _t, #"Last stallment" = _t, #"Last stalment with code = 0" = _t]), #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BM"), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"stallment", type date}, {"Last stallment", type date}, {"Last stalment with code = 0", type date}, {"Date", type date}}), LastCodeZero = Table.AddColumn( #"Changed Type", "Last Code 0", each let varCurrentDate = [Date] in try Table.Last( Table.SelectRows(#"Changed Type", each [Code] = "0" and [Date] < varCurrentDate) )[stallment] otherwise null ) in LastCodeZeroThis all happens in the LastCodeZero step.
- It gets the "current" date from the Date column and stores it in varCurrentDate
- It filters the table from the previous step only where the code is 0 and the date is before varCurrentDate
- It then keeps the last record only (most recent)
- Then it gets the [stallment] field value.
- If there are no records in step #2 above, step #4 will return an error, so the entire thing is wrapped in a try/otherwise construct and returns null in that case.
Note: this will work fine for a few hundred rows in Power Query, maybe a couple of thousand. After that, this really should be done in DAX where it could do it quickly over millions of rows.How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
5 Replies
I don't understand your answer. Why is the last "stallment" for 5/15 code 50 6/15, but for 5/15 code 0 it is 3/15? Explain the logic a bit.
Row 3, "date"=12/08/2022, "code"=33, "Stallment"=blank, "Last stallment" = 05/15 (Value of prevew row of column "Stallment"...
row 9, "date"=01/05/2022, "code"=21, "Stallment"=blank, "Last stallment" = 07/15 (Value of prevew row of column "Stallment", "Last stallment with code = 0"=08/15 (the last value of column "Stallment" that the column "Code" =0 is the row 7)
Ok. Still not 100% sure, 😁 but is this what you want?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDBCsMgDAbgd/HckRgb1x53Ley0o/Sww9hljDHooW+/qjXVDESCfn8ihmBoBHRASGQ6w7htlk/X+3cr4pq7YBwC9oWIWGu6n0RtgYslm7u0PjNiQFZNp+WtxvL/y9ba7kVqOAB6zUU1ucQ94FnNvyxPFYsnSY+VPpq/VK5wSzAU7Zx8QhMonyDQU768PT6SSHD+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t, stallment = _t, #"Last stallment" = _t, #"Last stalment with code = 0" = _t]), #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BM"), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"stallment", type date}, {"Last stallment", type date}, {"Last stalment with code = 0", type date}, {"Date", type date}}), LastCodeZero = Table.AddColumn( #"Changed Type", "Last Code 0", each let varCurrentDate = [Date] in try Table.Last( Table.SelectRows(#"Changed Type", each [Code] = "0" and [Date] < varCurrentDate) )[stallment] otherwise null ) in LastCodeZeroThis all happens in the LastCodeZero step.
- It gets the "current" date from the Date column and stores it in varCurrentDate
- It filters the table from the previous step only where the code is 0 and the date is before varCurrentDate
- It then keeps the last record only (most recent)
- Then it gets the [stallment] field value.
- If there are no records in step #2 above, step #4 will return an error, so the entire thing is wrapped in a try/otherwise construct and returns null in that case.
Note: this will work fine for a few hundred rows in Power Query, maybe a couple of thousand. After that, this really should be done in DAX where it could do it quickly over millions of rows.How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.