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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.