Power Pivot - Duplicate Column Values

%3CLINGO-SUB%20id%3D%22lingo-sub-2439466%22%20slang%3D%22en-US%22%3EPower%20Pivot%20-%20Duplicate%20Column%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439466%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20attempting%20to%20create%20relationships%20between%20Category%20Fields%20and%20the%20corresponding%20Value%20Fields.%20The%20issue%20I'm%20running%20into%20is%20that%20Excel%20triggers%20the%20%22Relationship%20cannot%20be%20created%20because%20each%20column%20creates%20duplicate%20values.%20Select%20at%20least%20one%20column%20that%20contains%20only%20unique%20values.%22.%20The%20duplicate%20values%20are%20intentional%20(ex.%20price%20is%20same%20on%20two%20different%20products).%20Another%20example%20is%20attached.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20any%20way%20to%20bypass%20this%20error%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2439466%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2440127%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20-%20Duplicate%20Column%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076765%22%20target%3D%22_blank%22%3E%40CDItim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EData%20model%20in%20Excel%20doesn't%20support%20many-to-many%20relationship.%20Common%20practice%20is%20to%20build%20bridge%20table%2C%20but%20from%20your%20screenshot%20it's%20not%20clear%20which%20relationships%20and%20which%20tables%20you%20try%20to%20build.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2440323%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20-%20Duplicate%20Column%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20reply.%20An%20example%20is%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20one%20table%20that%20lists%20all%20of%20my%20different%20fields%20in%20one%20column%20and%20the%20corresponding%20category%20(that%20I%20would%20like%20to%20toggle%20with%20a%20slicer)%20with%20in%20the%20other%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20table%20has%20those%20fields%20labeled%20as%20column%20headers%20in%20the%20top%20row%20with%20the%20values%20below.%20Each%20row%20corresponds%20to%20a%20different%20product%20(size).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIncluding%20screenshots%20of%20the%20tables%20and%20my%20relationships.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, 

 

I'm attempting to create relationships between Category Fields and the corresponding Value Fields. The issue I'm running into is that Excel triggers the "Relationship cannot be created because each column creates duplicate values. Select at least one column that contains only unique values.". The duplicate values are intentional (ex. price is same on two different products). Another example is attached. 

Is there any way to bypass this error?

3 Replies

@CDItim 

Data model in Excel doesn't support many-to-many relationship. Common practice is to build bridge table, but from your screenshot it's not clear which relationships and which tables you try to build.

@Sergei Baklan Thank you for your reply. An example is attached.

 

There's one table that lists all of my different fields in one column and the corresponding category (that I would like to toggle with a slicer) with in the other column. 

 

Another table has those fields labeled as column headers in the top row with the values below. Each row corresponds to a different product (size). 

 

Including screenshots of the tables and my relationships. 

@CDItim 

If I understood correctly you'd like to build relationship between columns in Networking table and list of these columns in another table. If so it doesn't work. You need to unpivot Networking table and work with it, Power Query will the best tool for that. 

However, it's not clear what is the final result you'd like to achieve, relationships is only of of the means.