Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |