Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have two tables: "Data" and "Report."
The "Data" table consists of columns such as type, id, code, and country.
Meanwhile, the "Report" table includes columns named Item, type, code, and country.
The connection between these tables is established through the type, id, and code columns.
My objective is to extract the id from the "Data" table and incorporate it into the "Report" table, utilizing the criteria of matching type, code, and country.
Report Table:
Item | Type | Code | Country | Desired Result |
123 | AZ1 | 0070 | 077 | |
124 | AZ1 | 1000 | 077 | |
125 | AZ1 | 0070 | 077 | |
126 | AZ1 | 1000 | 077 | |
127 | A07 | 0600 | CHE | |
128 | A07 | 0930 | CHE | |
129 | A07 | 0600 | CHE | |
130 | A07 | 0930 | CHE | |
131 | A08 | 0840 | UK | GT |
132 | A08 | 0840 | US | LUK |
133 | A08 | 0840 | EUR | RR |
134 | A08 | 0840 | ||
135 | AA1 | 0260 | UK | RR |
136 | AA1 | US | CHE | |
137 | AA1 | EUR | GT | |
138 | AA1 | BAL | LUK | |
139 | AA1 | PAK | SL1 | |
140 | AA1 | IND | SL2 | |
141 | AA1 | IT | SL3 | |
142 | AA1 | CAN | SL4 | |
143 | AA1 | AUS | SL5 |
Data:
Type | Id | Code | Country |
A02 | LPG | 0140 | |
AZ1 | 077 | 0070 | |
AZ1 | 077 | 1000 | |
A01 | RBI | 0160 | |
A06 | A01 | 0490 | |
A06 | A02 | 0990 | |
A07 | CHE | 0600 | |
A07 | CHE | 0930 | |
A08 | SRH | 0840 | |
A08 | DEL | 1050 | |
A08 | MI | 0840 | |
A08 | GT | 0840 | UK |
A08 | LUK | 0840 | US |
A08 | RR | 0840 | EUR |
A08 | RCB | 0610 | |
A08 | KKR | 0200 | |
A08 | XPHJ | ||
AV1 | X01 | 1160 | |
AA1 | RR | 0260 | UK |
AA1 | CHE | US | |
AA1 | GT | EUR | |
AA1 | LUK | BAL | |
AA1 | SL1 | PAK | |
AA1 | SL2 | IND | |
AA1 | SL3 | IT | |
AA1 | SL4 | CAN | |
AA1 | SL5 | AUS |
In Excel I am applying the following formula to get the result:
=IFERROR(INDEX(DATA!$B$3:$B$29,MATCH($B3&$C3&$D3,DATA!$A$3:$A$29&DATA!$C$3:$C$29&DATA!$D$3:$D$29,0)),"")
I am using the below mentioned DAX but it's not working
Solved! Go to Solution.
Table Report:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDLDoMgEAXQXzGsXQwPQZfUmrYpMY2PTY3//xsyA8WgqQtHkuPNHVkWxoVkJbNf7ieAAf8q8GgMW0tklZgDnLm6TuvrtEEGnKATt88ucr1zI8/cXKYp8D8tOTFWQK2Q57cfjymyOPHoh/MfBZdH7+bBz2GIro5e0BOQbs3SrQmdulNWJy5+xfveJsPQmrauM7xZl+3cZPqxWDo6HpSW3PXV30lFVJ7rRCgjigxb25OqqDJTS/8zuoqt6wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Code = _t, Country = _t, #"Desired Result" = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"Type", "Code", "Country"}, Data, {"Type", "Code", "Country"}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"Id"}, {"Id"})
in
#"Expanded Data"
In DAX you would similarly use a compaosite key
The problem is that it is not unique - A080840 appears twice. That introduces ambiguity.
Hi,
Write this calculated column formula in the Report table
Column = CALCULATE(MAX(Data[Id]),FILTER(Data,Data[Type]=EARLIER(Report[Type])&&Data[Code]=EARLIER(Report[Code])&&Data[Country]=EARLIER(Report[Country])))
Hope this helps.
Does it need to be DAX or can this be in Power Query?
@lbendlin, Thanks for your reply. If possible to provide the solution both ways(DAX new calculate column and Power query) it will help to understand the Power BI.
Table Report:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdDLDoMgEAXQXzGsXQwPQZfUmrYpMY2PTY3//xsyA8WgqQtHkuPNHVkWxoVkJbNf7ieAAf8q8GgMW0tklZgDnLm6TuvrtEEGnKATt88ucr1zI8/cXKYp8D8tOTFWQK2Q57cfjymyOPHoh/MfBZdH7+bBz2GIro5e0BOQbs3SrQmdulNWJy5+xfveJsPQmrauM7xZl+3cZPqxWDo6HpSW3PXV30lFVJ7rRCgjigxb25OqqDJTS/8zuoqt6wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Code = _t, Country = _t, #"Desired Result" = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"Type", "Code", "Country"}, Data, {"Type", "Code", "Country"}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"Id"}, {"Id"})
in
#"Expanded Data"
In DAX you would similarly use a compaosite key
The problem is that it is not unique - A080840 appears twice. That introduces ambiguity.