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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BeeleJa
Helper I
Helper I

Finding text using a reference table

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

WOMAINItemID_StandardProductGroupOutput1 - alternative is also present in ItemID_StandardOutput2 - if the product group of the present alternative is not Service than value is 0
W00014951014Parts #N/A#N/A
W00014952384Parts #N/A#N/A
W00017000066AService79000010
W00017000104Parts #N/A#N/A
W00017000104Service#N/A#N/A
W00017006318Parts #N/A#N/A
W00017006653Parts #N/A#N/A
W00017006653Service#N/A#N/A
W00017006771Parts #N/A#N/A
W00017006772Parts #N/A#N/A
W00017006887Service#N/A#N/A
W00017006887Service#N/A#N/A
W00017900001Parts 7000066A1
W00017900007-1Parts #N/A#N/A
W00017900007-2Parts #N/A#N/A
W0001M83485/1-011Parts #N/A#N/A
W0001M83485/1-012Parts M83485-1-0120
W0001M83485/1-013Parts #N/A#N/A
W0001M83485-1-012Parts M83485/1-0120
W0001MS15795-848Parts #N/A#N/A
W0001MS21043-3Parts #N/A#N/A
W0001MS21043-4Parts #N/A#N/A
W0001MS21043-6Parts #N/A#N/A

 

Table 2 - Reference table 

 

ItemIDAltItemIDRecID
7000066A79000015637146272
7000066A79000505637146275
700379370063185637146805
700429170068875637147606
700436570067715637147647
700447770066535637147756
7004477A7001576B5637146637
7004477A70044775637147738
7004477A70066535637147744
700631870037935637146806
70066537001576B5637224078
700665370044775637147757
70066537004477A5637147746
700677170043655637147648
7006773-17001126-15637146537
7006773-17900007-15637147880
7006773-27001126-25637146541
7006773-27900007-25637147887
700688770042915637147607
70068877004291A5637147587
700688770070725637147952
700688779000135637147897
70068877900044B5637147949
79000017000066A5637146274
790000179000505637147872
7900007-17006773-15637147879
7900007-27006773-25637147886
M83485/1-011M83485-1-0115637152111
M83485/1-012M83485-1-0125637152117
M83485/1-013M83485-1-0135637152121
M83485-1-011M83485/1-0115637152112
M83485-1-012M83485/1-0125637152118
M83485-1-013M83485/1-0135637152122
MS21043-3MS21046C35637205386
MS21043-6NAS1291C6M5637152155
MS21044N3MS20365-1032C5637152425
MS21046C3MS21043-35637205385
NAS1291C6MMS21043-65637152154

 

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

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.

 

vhenrykmstf_0-1634539673089.png

 

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 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.