Problem relationship ManytoMany

[Nouma]

[PowerPivot] Hello guys,
I would really appreciate some help regarding an issue I have when using PowerPivot.
Here is my problem :
I have several tables for which I created a "conversation" table, mapping the different names used in the different tables.
(e.g. a supplier has a name "Ascoli" in one table and "PGS Ascoli" in another table, so I created the conversion table to match those)
I then linked this conversion table to my respective tables containing data.
See screenshots : screenpp2 - HostingPics.net - H?bergement d'images gratuit & screenpp3 - HostingPics.net - H?bergement d'images gratuit
My issue is : there is a table in which a supplier can have several names that I would like to regroup under ONE line. (==> I want to link two names under one unique key, here being the SAP code, so I repeated this code and information from other tables in a new line, on which I added the second name in the correct column) However, PowerPivot doesn't allow me to upload the conversion table if two lines contain the same data except for the change in one column...
Could somebody please help me understand why ? I have tried to look up information about the many to many relationships but I didn't really get the solutions they proposed.
Thanks for your help !
Emmanuel.

 

[Matt Allington]

Power Pivot enforces one to many relationships in order to leverage performance benefits in the engine. In your case you don't need the many to many pattern, you need to cleanse your data before loading it. It is easy to do this in Power Query. Just create a substitute table, join it to the original table and replace the data that needs to be changed with the cleansed data. So you know how to do that?

 

[Nouma]

Hello Matt,
Thanks for your comment ! I had not access to my computer this weekend, therefore my late reply.

To answer : no I don't know how to do that in PowerQuery.. is there maybe a way to do a "pre-cleansing" in excel regarding that my conversion table is an excel file table ? In fact I don't visualize well how we would clean it in order not to run into this problem.

Thanks again for your help ! (And happy new year by the way )

Emmanuel.

 

[Matt Allington]

I need a blog article for this week, so let me blog about this topic. Please watch my blog over the next days.
Blog - Excelerator BI

also note this article has a cleansing process included (about half way down)
Data Modelling with OneDrive Source Files - Excelerator BI

 

[Matt Allington]

Here is the article I wrote as mentioned above. Cleansing Data with Power Query - Excelerator BI

 

[Nouma]

Great,
Thank you Matt ! So I get it : the key is that I need to put all names in one
column instead of one column per source, so that I have unique keys and I can
create a one to many relationship. However, I do not well see the added value
of this PowerQuery manipulation : is it not easier to just reorganize the excel
table so that we can just simply import it in the datamodel ? Because anyway it
will have to be updated manually, right ?

 

[Matt Allington]

If your data is manually created in a Excel, then yes you should just fix it there. If your data comes from a source system that contains dirty data, you can use the approach I linked above.