Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a mapping table and automatically update Dashboards

Hi there, 

 

I'm fairly new to PowerBI and have the following problem: I get reports from different distribution partners where the end client is always called differently such as the below:

 

Report 1 - Client name is Client1234 - Revenue $5

Report 2 - Client name is 12Client23 - Revenue $3

Client1234 and 12Client23 are the same Client on my side.

I want to have: Client - Revenue $8

 

I would like to build a match table so that when I upload a report from any distributor the data aligns with one client.

 

Is there any way to do this?

 

Thanks a lot!

Guillaume 

Dataflow
  • selimovd's avatar
    selimovd
    5 years ago

    Hey Anonymous ,

     

    that's usually a data preparation task.

    Create a mapping table in your data source or if you want also in Excel or directly in Power BI. It should have two columns, one for the correct client name or the name you want to see in the report and one column for the synonyms. Then for each synonym add a new line to map it to the correct name.

     

    In Power Query you then merge this table (is like a join in SQL) to the real table and replace all the duplicate entries with the correct one.

    Then you can analyze all the data together.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

1 Reply

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    that's usually a data preparation task.

    Create a mapping table in your data source or if you want also in Excel or directly in Power BI. It should have two columns, one for the correct client name or the name you want to see in the report and one column for the synonyms. Then for each synonym add a new line to map it to the correct name.

     

    In Power Query you then merge this table (is like a join in SQL) to the real table and replace all the duplicate entries with the correct one.

    Then you can analyze all the data together.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis