Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Within my Table1 I have a MainWo, ItemID-Standard and Product Group.
I want to add 1 comlumn that displays the alternatie Item based on a reference table2
The relation between the Table1 and Table2 is a many to many
Note: The RecID of Table2 is unique for each combination ItemID - AltItemID
Example:
ItemID_Standard 7000066A has 2 alternatives 7900001 and 7900050
In this example 7900001 is also present in the ItemID_Standard
Output1 for ItemID_Standard 7000066A is 7900001 and
Output1 for ItemID_Standard 7900001 is 7000066A
The product group for ItemID_Standard 7000066A is Service and 7900001 is Part
Output2 for the combination 7000066A - 7900001 - (79000010) Part = 0
Outpur2 for the combination 7900001 - 7000066A - (7000066A) Service = 1
Table1 & desired output
WOMAIN | ItemID_Standard | ProductGroup | Output1 - alternative is also present in ItemID_Standard | Output2 - if the product group of the present alternative is not Service than value is 0 |
W0001 | 4951014 | Parts | #N/A | #N/A |
W0001 | 4952384 | Parts | #N/A | #N/A |
W0001 | 7000066A | Service | 7900001 | 0 |
W0001 | 7000104 | Parts | #N/A | #N/A |
W0001 | 7000104 | Service | #N/A | #N/A |
W0001 | 7006318 | Parts | #N/A | #N/A |
W0001 | 7006653 | Parts | #N/A | #N/A |
W0001 | 7006653 | Service | #N/A | #N/A |
W0001 | 7006771 | Parts | #N/A | #N/A |
W0001 | 7006772 | Parts | #N/A | #N/A |
W0001 | 7006887 | Service | #N/A | #N/A |
W0001 | 7006887 | Service | #N/A | #N/A |
W0001 | 7900001 | Parts | 7000066A | 1 |
W0001 | 7900007-1 | Parts | #N/A | #N/A |
W0001 | 7900007-2 | Parts | #N/A | #N/A |
W0001 | M83485/1-011 | Parts | #N/A | #N/A |
W0001 | M83485/1-012 | Parts | M83485-1-012 | 0 |
W0001 | M83485/1-013 | Parts | #N/A | #N/A |
W0001 | M83485-1-012 | Parts | M83485/1-012 | 0 |
W0001 | MS15795-848 | Parts | #N/A | #N/A |
W0001 | MS21043-3 | Parts | #N/A | #N/A |
W0001 | MS21043-4 | Parts | #N/A | #N/A |
W0001 | MS21043-6 | Parts | #N/A | #N/A |
Table 2 - Reference table
ItemID | AltItemID | RecID |
7000066A | 7900001 | 5637146272 |
7000066A | 7900050 | 5637146275 |
7003793 | 7006318 | 5637146805 |
7004291 | 7006887 | 5637147606 |
7004365 | 7006771 | 5637147647 |
7004477 | 7006653 | 5637147756 |
7004477A | 7001576B | 5637146637 |
7004477A | 7004477 | 5637147738 |
7004477A | 7006653 | 5637147744 |
7006318 | 7003793 | 5637146806 |
7006653 | 7001576B | 5637224078 |
7006653 | 7004477 | 5637147757 |
7006653 | 7004477A | 5637147746 |
7006771 | 7004365 | 5637147648 |
7006773-1 | 7001126-1 | 5637146537 |
7006773-1 | 7900007-1 | 5637147880 |
7006773-2 | 7001126-2 | 5637146541 |
7006773-2 | 7900007-2 | 5637147887 |
7006887 | 7004291 | 5637147607 |
7006887 | 7004291A | 5637147587 |
7006887 | 7007072 | 5637147952 |
7006887 | 7900013 | 5637147897 |
7006887 | 7900044B | 5637147949 |
7900001 | 7000066A | 5637146274 |
7900001 | 7900050 | 5637147872 |
7900007-1 | 7006773-1 | 5637147879 |
7900007-2 | 7006773-2 | 5637147886 |
M83485/1-011 | M83485-1-011 | 5637152111 |
M83485/1-012 | M83485-1-012 | 5637152117 |
M83485/1-013 | M83485-1-013 | 5637152121 |
M83485-1-011 | M83485/1-011 | 5637152112 |
M83485-1-012 | M83485/1-012 | 5637152118 |
M83485-1-013 | M83485/1-013 | 5637152122 |
MS21043-3 | MS21046C3 | 5637205386 |
MS21043-6 | NAS1291C6M | 5637152155 |
MS21044N3 | MS20365-1032C | 5637152425 |
MS21046C3 | MS21043-3 | 5637205385 |
NAS1291C6M | MS21043-6 | 5637152154 |
Hi @BeeleJa ,
It is not yet clear what you are looking for. Are you able to provide test data (remove sensitive information) and desired results, I will answer you as soon as possible.
Similar to Find, SEARCH, CONTAINS, TREATAS and other query functions are helpful to you. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear @Henry,
I have revised the Table1 information and added the table 2 information.
In short wording:
Check #1
From Table1 [ItemID_Standard] find the related Table2 [AltItemID] - can be multiple
Check if the found Table2 [AltItemID] is present in the Table 1 [ItemID_Standard]
If present than the output is Table2 [AltItemID] else leave blank
Check #2
From the output in Check#1 get the Table1 [ProductGroup] that is connected to the Table2 [AltItemID] in the Table1 [ItemID_Standard]
Output Table1 [ProductGroup] or when Table1 [ProductGroup] is 'Service' then 1 else 0
Hope this is more clear
Regards
Beeleja
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
34 | |
27 | |
26 |
User | Count |
---|---|
97 | |
96 | |
60 | |
44 | |
41 |