Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

FIRSTNONBLANK Equivalent in Power Query

 

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:

EMP_0-1725486055783.png

 

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:

EMP_1-1725486077354.png

 

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:

EMP_2-1725486095423.png

 

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:

EMP_3-1725486100513.png

 

As a reminder, I want to see this result:

EMP_4-1725486113375.png

 

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

1 ACCEPTED SOLUTION
Omid_Motamedise
Super User
Super User

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})


If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

View solution in original post

5 REPLIES 5
dufoq3
Super User
Super User

Hi @Anonymous, try it with buffer:

 

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

 

Output

dufoq3_0-1725553925127.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Omid_Motamedise
Super User
Super User

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})


If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
Anonymous
Not applicable

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:

EMP_0-1725548152588.png

 

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
Super User
Super User

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


Omid_Motamedise_1-1725487489056.png

 

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}



 

Omid_Motamedise_2-1725487576257.png

 



use the next fromula to reach the result

 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
Anonymous
Not applicable

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

EMP_0-1725494970854.png

 

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? 😞 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors