Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DK_m
Frequent Visitor

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 

DK_m_0-1679058400368.png

 

1 REPLY 1
JW_van_Holst
Resolver IV
Resolver IV

    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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors