Forum Discussion
Table Transformation for sankey diagram
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?
3 Replies
v-lionel-msftCommunity Support
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
Community Champion
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?
