Create a Reference/Mapping Table from multiple data sets

[Anonymous]

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!

[Sergiy · Accepted solution]

Fuzzy merging could help.

A sample file that illustrates the idea:

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

[MFelix]

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

[Anonymous]

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.

[MFelix]

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

[Anonymous]

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.

[MFelix]

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

Regards
MFelix

[Anonymous]

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?

[MFelix]

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