Retrieving last non null value grouped by key N_order

[jpolanco]

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

[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.

[lbendlin · Accepted solution]

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".