"Model" Dimension from two sources without aligned values

[B_J_S]

Hi,

I have two tables coming in from different data sources, both contain a Model Name column but they do not necessarily have the same values, the two different systems have model as an open field so I am seeing different models in each table, as well as variations of the same model name.

I'd like to create a reference table which basically pulls the full list from each source, and allows me to align the values from each source to a master list.

I've never really done any modelling before and I'm not really sure how to do this.  Every example/instructional video i find sees to have completely clean data which perfectly syncs up.

I'm sure it's a simply solution but would very much appreciate some guidance.

Thanks

[Anonymous]

Hi @B_J_S ,

May I ask if your question is solved? If johnt75's answer helps you, please don't forget to mark his reply as a solution.
If the question is not solved, feel free to continue adding details below as well.

Best Regards,
Gao
Community Support Team

[johnt75 · Accepted solution]

It will be pretty manual, especially at first, but I would recommend maintaining a spreadsheet which you could use as a lookup to map the model names from the 2 data sources to your desired name.

First, generate a list of all the model names coming from both your sources. If you go into the DAX Query View you can run

EVALUATE
DISTINCT (
    UNION ( DISTINCT ( 'Table1'[Model name] ), DISTINCT ( 'Table2'[Model name] ) )
)

which will give you a list of all the unique model names from both sources. Copy and paste it into Excel and add a new column which holds the "correct" name.

In Power Query load this spreadsheet in, but untick the "Enable load" option so that it is not loaded into the model.

For each of your sources do a merge with this new query on the original model name. Expand the result, choosing only the new model column.

Make a copy of the spreadsheet, delete the original model name column so that you only have the new name, and then Remove Duplicate. Load the data into the model and create a one-to-many relationship from the table with the proper model names to the other data sources, and use the column from this table in any filters or visuals.

You could set up exception reporting to alert you if a new variant of the model name is picked up from the data sources. Create a couple of card visuals which show the count of rows in each data source and filter the visual to show only when the new model name column from that data source table is blank. In the Power BI service when you have published the report, add the card visuals to a dashboard so that you can set up an alert when the number goes above 0, and you will then be notified if any model names have not been properly mapped.