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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

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