Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 o...
Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

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


dufoq3's avatar
dufoq3
Community Champion
1 year ago

Hi Anonymous, 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