Forum Discussion
Last value power query
- 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 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.
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.
- 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.
Thank you very much!
- edhans3 years agoCommunity Champion
Glad I was able to assist Anonymous