mikes6058
asked on
Power Pivot - Can't create relationship - No dublicates???
Attached is an excel document with two tables loaded into a power pivot data model. I am trying to create a relationship between the stock code field in each table. I have used conditional formatting to check for duplicates in the supplier table stock code column and can't find any. Yet when I try and create a relationship I am getting the many-to-many error.
Can anyone double check for me, perhaps I'm missing something.
Rob
jan-marEE.xlsx
Can anyone double check for me, perhaps I'm missing something.
Rob
jan-marEE.xlsx
ASKER
I've done this several times but am still getting the same error msg.
Please could you have a look at the supplier sheet (attached) to check for duplicate stock codes and delete these rows
Thanks
jan-mar.xlsx
Please could you have a look at the supplier sheet (attached) to check for duplicate stock codes and delete these rows
Thanks
jan-mar.xlsx
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Brilliant, now I see what you mean!
Glad it helped.
ASKER
No problem,
I don't no if this question would interest you? It relates to the same project.
https://www.experts-exchange.com/questions/28939506/Power-Pivot-SKU-by-Customer-by-Rank-DAX-Measure.html
I don't no if this question would interest you? It relates to the same project.
https://www.experts-exchange.com/questions/28939506/Power-Pivot-SKU-by-Customer-by-Rank-DAX-Measure.html
To check this, select the column A --> Conditional Formatting --> Highlight Cells Rules (first option) --> Duplicate Values --> OK.
Do this on both the sheets and you will find the the duplicate stock codes get highlighted in both the sheets.
To deal with this scenario, copy the Stock Codes from the Supplier Sheet (assuming this sheet has all the possible Stock Codes) and paste them to a New Sheet in cell A2, name that sheet as Stock_Codes and place a header in A1 as Stock_Code.
Now on Stock_Code sheets select all the Stocks --> Data Tab --> Remove Duplicates, this will leave only unique stock codes on this sheet, now apply the Table style for the data on this sheet by pressing Ctrl+T and name the table from Table1 to something meaningful like tblStock_Codes.
Add this table into the Data Model and in the diagram view you can create a Relationship between the three tables where tblStock_Codes will have the unique stock codes.
Hope this helps.