Last value power query

[Anonymous]

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

[Anonymous]

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)

[edhans · Accepted solution]

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
    LastCodeZero

This all happens in the LastCodeZero step.

1. It gets the "current" date from the Date column and stores it in varCurrentDate
2. It filters the table from the previous step only where the code is 0 and the date is before varCurrentDate
3. It then keeps the last record only (most recent)
4. Then it gets the [stallment] field value.
5. 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.

[Anonymous]

Thank you very much!

[edhans]

Glad I was able to assist @Anonymous 

[edhans]

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.