How to setup data for Force Directed graph

[MrMP]

Hello Everyone,

Ive got interesting problem to solve and I would need help from anyone. I am aiming to use Force directed graph but the data structure is a problem.

I have dataset as following:

Order Number	Product Name	Sales Amount
1234	Product1	100
1234	Product2	200
1234	Product3	300
3333	Product4	400
4444	Product1	500

And this is data structure I need: (table below is all possible combinations of Product names and their sales if those two products belong to the same order)

Product Name	Product Name	Sales
Product1	Product2	300 (200+100)
Product1	Product3	400 (300+100)
Product1	Product4	0 (they dont have same Order number)
Product1	Product5	0 (they dont have same Order number)

Thank you in advance!

[v-eachen-msft · Accepted solution]

Hi @MrMP ,

You could use merge queries feature to do it in query editor.

Here is my test file for your reference.