Many to many relationship in power pivot

[sharshra]

I want to create a relationship in power pivot between 2 tables having many to many relationship. In the tables shown below, I wanted to link account fields between 2 tables. I get an error shown below when I try to do that. Is there a way to do this?

I understand why this error is coming, but this is a possible scenario. In such cases, how can we use power pivot?

Sales table -

Date	Order#	Account
01-Nov-21	12345	98765432
01-Nov-21	12346	87654321
01-Nov-21	12347	87654321
01-Nov-21	12348	76543210
01-Nov-21	12349	98765432
01-Nov-21	12350	65432109
01-Nov-21	12351	76543210
01-Nov-21	12352	65432109
01-Nov-21	12353	76543210
01-Nov-21	12354	87654321
02-Nov-21	12355	65432109
02-Nov-21	12356	54321098
02-Nov-21	12357	54321098
02-Nov-21	12358	76543210
02-Nov-21	12359	98765432
03-Nov-21	12360	65432109
03-Nov-21	12361	87654321
03-Nov-21	12362	54321098
03-Nov-21	12363	54321098
03-Nov-21	12364	65432109
03-Nov-21	12365	65432109
03-Nov-21	12366	65432109
04-Nov-21	12367	76543210
04-Nov-21	12368	43210987
04-Nov-21	12369	87654321
04-Nov-21	12370	43210987
04-Nov-21	12371	43210987
04-Nov-21	12372	76543210
04-Nov-21	12373	43210987
05-Nov-21	12374	98765432
05-Nov-21	12375	43210987
05-Nov-21	12376	43210987
05-Nov-21	12377	76543210
05-Nov-21	12378	87654321
05-Nov-21	12379	87654321
05-Nov-21	12380	65432109
05-Nov-21	12381	76543210
05-Nov-21	12382	98765432

User & accounts table -

user	account	company name	actioned
user-1	65432109	abc	p-1
user-1	54321098	abc	p-1
user-1	43210987	abc	p-1
user-2	98765432	def
user-2	87654321	def
user-3	76543210	xyz	p-3
user-3	54321098	xyz	p-3
user-3	43210987	xyz	p-3
user-4	87654321	mno	p-4

 

[RoryA]

Create a bridging table that is a unique list of account numbers.

 

[sharshra]

I'm already using the combination of pivot table & table to get the required result. Refer the screenshot below. It may not have unique account numbers & that is the requirement. I thought it would be more efficient if similar result can be obtained with pivot table / power pivot, without having to use combination of pivot table & table.

 

[RoryA]

You can create the table in the data model - eg using Power Query.