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