Forum Discussion
Retrieving last non null value grouped by key N_order
Hi everybody,
I'm trying to plug the holes on the dataset that I'm currently working on.
My N_order is the key for grouping. As you can see in below example, I've null values for the column ship_meth, when the status is 'fulfilled'.
I'd love to create a new desired_column that can retrieve the value from any of the other statuses for that N_order (for example, 'new_order') and paste it on the row with the 'fulfilled' status.
I'm not able to provide a solid example of what I've been trying, as is not working anywhere near what I'd like. Just going to point out that it doesnt work with filldown/up function as i've different statuses on the orders, and different ship_meth.
| N_order | Ship_meth | status | desired_column |
| 50010000116187 | ENKM | picked | ENKM |
| 50010000116187 | ENKM | accepted | ENKM |
| 50010000116187 | null | fulfilled | ENKM |
| 50010000116325 | null | fulfilled | ENRC |
| 50010000116325 | ENRC | accepted | ENRC |
| 50010000116325 | ENRC | rejected | ENRC |
Appreciate your time and effort.
Suk
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwMDQAAkNDM0MLcyUdJVc/b18gVZCZnJ2aAuPH6uBWmZicnFpQQkAtEKWV5qRl5uTgVGhsZIpNYZAzdoVgGTTbCagtSs1KTUZWGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [N_order = _t, Ship_meth = _t, status = _t, desired_column = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Ship_meth] = null or [Ship_meth]="" then Table.SelectRows(Source,(k)=>[N_order]=k[N_order] and k[Ship_meth]>"")[Ship_meth]{0} else [Ship_meth]) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
2 Replies
- lbendlin
Super User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwMDQAAkNDM0MLcyUdJVc/b18gVZCZnJ2aAuPH6uBWmZicnFpQQkAtEKWV5qRl5uTgVGhsZIpNYZAzdoVgGTTbCagtSs1KTUZWGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [N_order = _t, Ship_meth = _t, status = _t, desired_column = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Ship_meth] = null or [Ship_meth]="" then Table.SelectRows(Source,(k)=>[N_order]=k[N_order] and k[Ship_meth]>"")[Ship_meth]{0} else [Ship_meth]) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
- Ashish_Mathur
Hi,
This calculated column formula works
=if(Data[status]="fulfilled",CALCULATE(MIN(Data[Ship_meth]),FILTER(Data,Data[N_order]=EARLIER(Data[N_order]))),Data[Ship_meth])Hope this helps.
