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(Table...
Need Help
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