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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a Reference/Mapping Table from multiple data sets

Hi All,

I have trouble creating a reference / mapping table using the Power Query Editor. I'm trying to create a reference / mapping table from two different data tables that have no relationship to one another.

The goal is to use this reference table for allowing the Slicer to slice the data for the two data tables at the same time.

For example:

Table1 Data:
Apple
Oranges
Peaches
Watermelon

Table2 Data:
Aappple Cider
Oorrange Peel
Ppeeach Puree
Wwaatermelon Juice

I want to create a separate reference table:
Column 1 | Column 2
Apple | Aappple Cider
Orange | Oorrange Peel
Peach | Ppeeach Puree
Watermelon | Wwaatermelon Juice


Thank you!
1 ACCEPTED SOLUTION
Sergiy
Resolver II
Resolver II

Fuzzy merging could help.

 

A sample file that illustrates the idea:

https://www.dropbox.com/s/yp6x6i0wzwjc5u7/FuzzyMerging.pbix?dl=0

 

1.png

View solution in original post

8 REPLIES 8
Sergiy
Resolver II
Resolver II

Fuzzy merging could help.

 

A sample file that illustrates the idea:

https://www.dropbox.com/s/yp6x6i0wzwjc5u7/FuzzyMerging.pbix?dl=0

 

1.png

MFelix
Super User
Super User

Hi @Anonymous ,

 

Why don't you add a column on the second table with onyl the first word of the 1st column and then you could use that column to link the tables?

 

You can use the Create column from examples button.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Sorry, I wasn’t clear in my original post. The problem is a bit more complex where there you cannot extract the first word.

I have updated my original post to reflect the problem I’m facing.

Hi @Anonymous,

How do you know the match between the rows? Are they in the same order?

If yes add and index column to both tables and due merge of both table by index.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

There is no logic to automatically match the two data tables, so I was hoping to reach out to community and see if a reference table from the two preexisting tables can be created within the Power Query Editor.

My alternative is to manually maintain a mapping table outside of Power BI but I don’t want to maintain another external table from an extra data table.

The reference table can be created but there must be a logic between the connection otherwise your data will be filter incorrectly.

Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

How do I create this reference table in Power BI? Is there a way I can manually map each member of the tables to one another within the application?

You can add a manual table in the query editor or trough dax depends on what's your needs but this will make you always keep the table manually updated.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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