Table Transformation for sankey diagram

[krishna_sipl]

Hello,

I want to create a Sankey chart and as I understand there is a need for Source and destination column but my data is like in the above format :

ID	Name	Time
1	A	20/04/2020 12:25:11
1	B	20/04/2020 12:20:05
1	C	20/04/2020 12:30:07
2	B	20/04/2020 11:10:20
2	C	20/04/2020 11:15:11
3	A	21/04/2020 10:05:18
4	A	21/04/2020 13:07:00
4	D	21/04/2020 13:07:10
4	B	21/04/2020 13:08:00
4	C	21/04/2020 13:06:20

And form above I want to Transform table by using timewise:

Source	Destination	Count
B	A	1
A	C	1
B	C	2
A	A	1
A	D	1
D	B	1

is it possible to do in power bi? 

[v-lionel-msft]

Hi @krishna_sipl ,

Can you explain how each column in this table is obtained?
You may need to use DAX if you want to get this table, but I don’t know its calculation logic.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[krishna_sipl]

Hello @v-lionel-msft  

So From Table 1(1st Image): 

Using Date and ascending Time on that Date, ID wise create a source and Destination

Example: If one ID has 3 names A, B, C then it will be like by date and ascending time : 

Source   Destination

A              B

B              C

 also, the Same ID can repeat on a different date as well. and count repeated source and destination in Count column.

[az38]

Hi @krishna_sipl 

it's completely unclear logic.

could you explain whats your final result based on your table1? how does should look like your chart?