About dynamics 365 Connections table and account tables (self-referencing)

[rickylee]

Hello,

This question possibly applies to experts who knows about the database structure in Dynamics365 online.

I have two duplicate tables called Account1 and Account2. One intermediate table called Connections. They are the tables in Dynamics 365 online.

Table structure and relationships:
Account1:
accountid

Account2:
accountid

Connections:
_record1id_value
_record2id_value

Relationship:
Account1.accountid = Connections. _record1id_value
Connections. _record2id_value= Account2.accountid
Due to the above relationship:
Account1.accountid always connects to many many Account2.accountid


My question:
1. Do we have a way to show Ac1.accountid once and many Ac2.accountid? In the above table, Ac1.accountid occurs many times.

2. I need to have a duplicate table (Account2) because 
Account1.accountid links to Connections. _record1id_value, then
Connections. _record2id_value links to Account2.accountid
So Account1.accountid can link to Account2.accountid.

Since Account1 table is self-referencing (I believe this is the right way to say it), is there a simpler way to achieve the above? I don’t want to duplicate a table but it seems this is the only way.


Many thanks,
Ricky

[v-cherch-msft · Accepted solution]

Hi @rickylee

Question1: You may try to use Matrix visual and drill down it like below.

Question2: It seems there's no better way. Maybe i don't fully understand it. But you may try to use 'Merge queries' in Query Editor to get the table. Below is the article about this function.

http://radacad.com/append-vs-merge-in-power-bi-and-power-query

Regards,

Cherie