Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
  • edhans's avatar
    edhans
    3 years ago

    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.