cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
donaldm Regular Visitor
Regular Visitor

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

Accepted Solutions
Sergiy Member
Member

Re: Create a Reference/Mapping Table from multiple data sets

Fuzzy merging could help.

 

A sample file that illustrates the idea:

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

 

1.png

8 REPLIES 8
Super User
Super User

Re: Create a Reference/Mapping Table from multiple data sets

Hi @donaldm ,

 

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



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

Proud to be a Datanaut!




donaldm Regular Visitor
Regular Visitor

Re: Create a Reference/Mapping Table from multiple data sets

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.
Super User
Super User

Re: Create a Reference/Mapping Table from multiple data sets

Hi @donaldm,

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


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

Proud to be a Datanaut!




donaldm Regular Visitor
Regular Visitor

Re: Create a Reference/Mapping Table from multiple data sets

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.
Super User
Super User

Re: Create a Reference/Mapping Table from multiple data sets

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

Regards
MFelix


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

Proud to be a Datanaut!




donaldm Regular Visitor
Regular Visitor

Re: Create a Reference/Mapping Table from multiple data sets

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?
Super User
Super User

Re: Create a Reference/Mapping Table from multiple data sets

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


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

Proud to be a Datanaut!




Sergiy Member
Member

Re: Create a Reference/Mapping Table from multiple data sets

Fuzzy merging could help.

 

A sample file that illustrates the idea:

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

 

1.png

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 294 members 3,196 guests
Please welcome our newest community members: