Forum Discussion
Preparing Hierarchy/Lineage data for Sankey Chart
Hi All,
I want to prepare the following data to suit the Sankey needs. Can you please help with the wasy way of doing it.
Original Data
| Layer 1 | Layer 2 | Layer 3 | Layer 4 | Layer 5 |
| A | B | C | D | E |
| AA | BB | CC | DD | EE |
| AAA | BBB | CCC | DDD | EEE |
Expected data:
| Source | Target | Weight |
| A | B | 1 |
| B | C | 1 |
| C | D | 1 |
| D | E | 1 |
| AA | BB | 1 |
| BB | CC | 1 |
| CC | DD | 1 |
| DD | EE | 1 |
| AAA | BBB | 1 |
| BBB | CCC | 1 |
| CCC | DDD | 1 |
| DDD | EEE | 1 |
What is the easiest way to prep the data like the one above? Thanks in advance.
Hi, try this
Table = UNION(SELECTCOLUMNS(table,"Source",Layer 1 column,"Target",Layer 2 column),
SELECTCOLUMNS(table,"Source",Layer 2 column,"Target",Layer 3 column),SELECTCOLUMNS(table,"Source",Layer 3 column,"Target",Layer 4 column),
SELECTCOLUMNS(table,"Source",Layer 4 column,"Target",Layer 5 column)
)
It works great. Thanks for your response... The Exact code i used is given below.
Sankey = UNION(SELECTCOLUMNS(Test,"Source",[Layer 1] ,"Target",[Layer 2] ),SELECTCOLUMNS(Test,"Source",[Layer 2] ,"Target",[Layer 3]),SELECTCOLUMNS(Test,"Source",[Layer 3] ,"Target",[Layer 4] ),SELECTCOLUMNS(Test,"Source",[Layer 4] ,"Target",[Layer 5] ))
2 Replies
Hi, try this
Table = UNION(SELECTCOLUMNS(table,"Source",Layer 1 column,"Target",Layer 2 column),
SELECTCOLUMNS(table,"Source",Layer 2 column,"Target",Layer 3 column),SELECTCOLUMNS(table,"Source",Layer 3 column,"Target",Layer 4 column),
SELECTCOLUMNS(table,"Source",Layer 4 column,"Target",Layer 5 column)
)
It works great. Thanks for your response... The Exact code i used is given below.
Sankey = UNION(SELECTCOLUMNS(Test,"Source",[Layer 1] ,"Target",[Layer 2] ),SELECTCOLUMNS(Test,"Source",[Layer 2] ,"Target",[Layer 3]),SELECTCOLUMNS(Test,"Source",[Layer 3] ,"Target",[Layer 4] ),SELECTCOLUMNS(Test,"Source",[Layer 4] ,"Target",[Layer 5] ))