Preparing Hierarchy/Lineage data for Sankey Chart

[sathar01_1]

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.

[HoangHugo · Accepted solution]

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)

                    )

[HoangHugo · Accepted solution]

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)

                    )

[sathar01_1 · Accepted solution]

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] )

                    )