FIRSTNONBLANK Equivalent in Power Query

[EMP]

Greetings.  I'm trying to do the equivelant of essentially a simple FIRSTNONBLANK without merging the query in Power Query (M) and haven't been able to figure it out/find a solution. In need of help please.

Data Sample:

Sample Explanation:

There are 3 orders in the table (1234, 2345 & 3456) and 1 credit order (4567). The orders contain Lot Number detail, but when credits hit the table they hit at an agreggated level by Item Type only (no Lot Number detail). In the case above 2345 had 6 Roosters on it with 2 different Lot Numbers & 1 Dog with 1 Lot Number. When the credit against the order was done it hit the table as a single row for (6) Roosters & the (1) Dog (vs 2 rows at 3 Roosters each, one for each Lot Number & the 1 Dog). 

The goal is to create a new column and if there is a value in Original Order Number lookup that value in Order Number and bring back the first non blank Lot Number found if the Item Types match. Example of expected result:

Because the credit lines aggregate the total by Item Type I don't want to merge the query to itself, because I don't want to have to blow those rows back out to 2 for Rooster and the associated quantities ( 3 & 3 vs. 6, and all the other attributes that go along with the row not shown here, there are a TON). It's fine if it just brings back the first value found, preferably if the Item Types match. 

I was able to partially accomplish this in DAX, without the Item Number logic, using this formula: MAXX(FILTER('Table', 'Table'[Order Number] = EARLIER('Table'[Original Order Number])), 'Table'[Lot Number]). Produces this result:

I was able to accomplish it by using a fuzzy match merge in Power Query, but it's a very slow load (this is a big table). Merged the query onto itself, Left Outer Join, Original Order Number = Order Number, Similarity Threshold = 1, Maximum Number of Matches = 1. Also produces this result:

As a reminder, I want to see this result:

I tried using the list and Position.Of functions but received an error because of the blanks. I've tried so many other things I've lost track at this point. 

Can someone please help me with this?

Thanks,
EMP

[dufoq3]

Hi @EMP, try it with buffer:

Regarding (x) or x - if you want to understand, check this video.

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXLOLweRBgYGhkAaiEyVYnXgsh75RcWpIBohb4gs75KfDiKRZA3A0kBZUyAvKD+/uCS1CMRCKDHGrcIIiwqEFabIDgBKmqE50AQibwKWB0qbo5gPRFATdc2QVUDMR5IFmh8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, #"Item Type" = _t, #"Lot Number" = _t, #"Original Order Number" = _t, Quantity = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"Original Order Number", Int64.Type}, {"Quantity", Int64.Type}}),
    BufferedColumns = Table.Buffer(Table.SelectColumns(ChangedType,{"Order Number", "Item Type", "Lot Number"})),
    Ad_LotReference = Table.AddColumn(ChangedType, "Lot Reference", each if [Original Order Number] = null then null else Table.SelectRows(BufferedColumns, (x)=> x[Order Number] = [Original Order Number] and x[Item Type] = [Item Type]){0}?[Lot Number]? , type text)
in
    Ad_LotReference

[Omid_Motamedise · Accepted solution]

se my formula is 

if [Original Order Number] = null then "" else Table.SelectRows(#"Changed Type",(x)=> x[Order Number]=_[Original Order Number] and x[Item Type]=[Item Type] and x[Lot Number]<>null)[Lot Number]{0}

you remove the part trlated to  Table.SelectRows, pick add custom column and then copy and past all the formula I provided into the custom colu,mn formula, so your final formula for this step become like 

= Table.AddColumn(#"Changed Type", "* Test2", each if [Original Order Number] = null then "" else Table.SelectRows(#"Changed Type",(x)=> x[Order Number]=_[Original Order Number] and x[Item Type]=[Item Type] and x[Lot Number]<>null)[Lot Number]{0})

[EMP]

Ah, thank you! So, when I run it against the small data sample I provided it works perfectly. But when I try to run it against the actual data table I get this error message:

When I Google that error I get all kinds of possible causes... Any ideas specific to this formula? 

Edit: I only get that error message when I create it in Power Query. If I actually apply the changes it works just fine (?) 

Now that it's working, would you mind explaining what this formula is doing? It's the 'x' I'm lost on and I'd like to understand 🙂 

Thanks again for all your help!

[Omid_Motamedise]

consider this table in power query with last step equal to change type 

if [Original Order Number] = null then "" else Table.SelectRows(#"Changed Type",(x)=> x[Order Number]=_[Original Order Number] and x[Item Type]=[Item Type] and x[Lot Number]<>null)[Lot Number]{0}

use the next fromula to reach the result

[EMP]

Thanks for response. The result I'm getting is Function in every row, and if I click on it I get this:

This is my formula (from add custom column dialog box):

if [Original Order Number] = null then "" else
(x)=> x[Order Number]=_[Original Order Number] and x[Item Type]=[Item Type] and x[Lot Number]<>null [Lot Number]{0}

Looks like this in the formula bar:

= Table.AddColumn(#"Changed Type", "* Test2", each if [Original Order Number] = null then "" else
(x)=> x[Order Number]=_[Original Order Number] and x[Item Type]=[Item Type] and x[Lot Number]<>null [Lot Number]{0})

What did I do wrong? 😞