Forum Discussion
FIRSTNONBLANK Equivalent in Power Query
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})
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})
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!