Forum Discussion

DK_m's avatar
DK_m
Frequent Visitor
3 years ago

How to do If condition inside a Xlookup in power query

Hello,

 

I have two tables, where i need to create a custom column in table1 with Xlookup and if condition as below in power query.

Any suggestion is appreciated!

 

XLOOKUP(Table1[Acct],IF(Table1[Code]="10",Table2[ID],Table2[Reg]),Table2[Result],"",0) - as excel formula

 

above formula i need it in M code - power query

 

@amitchandak @Greg_Deckler any idea ?

 

PFB, example 

 

Need Help

1 Reply

  • JW_van_Holst's avatar
    JW_van_Holst
    Icon for Resolver IV rankResolver IV
    3 years ago
        Table2Raw = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYxVdJRMjUztwBSjkqxOtFKRiamZkCOoZGxCVwMyoEoc4IIAcWAHKhqoFgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Reg = _t, Result = _t]),
    Table2 = Table.TransformColumnTypes(Table2Raw,{{"ID", Int64.Type}, {"Reg", Int64.Type}, {"Result", type text}}),
    Table1Raw = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTI0UIrViVYyNTO3APKMIDxDoCRCzsjE1AwqFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Acct = _t, Code = _t]),
    Table1 = Table.TransformColumnTypes(Table1Raw,{{"Acct", Int64.Type}, {"Code", Int64.Type}}),
    _list10 = List.Zip({Table2[ID], Table2[Result]}),
    _list20 = List.Zip({Table2[Reg], Table2[Result]}),
    Added = Table.AddColumn(Table1, "Result", each
    if [Code]= 10 then
        List.Transform(_list10, each _{1}){List.PositionOf(List.Transform(_list10, each _{0}),[Acct])}
    else
        List.Transform(_list20, each _{1}){List.PositionOf(List.Transform(_list20, each _{0}),[Acct])}
    )
in
    Added