Hierarchy output

[vinothkumar1990]

Hi All,

I came across some below hierarchical requirment. I have two columns called COL1 and COL2 and want to derive the value (MAP). 

COL1 and COL2 have 4 type of relationships i.e. 1:1, 1:M, M:1, M:M.

Explanation - For example

Explanation - For example

1. XX1 is mapped to A1 (temp result XX1 : A1)
2. A1 is mapped to XX3 (temp result XX1, XX3 : A1)

3. A1 is mapped to XX3 (temp result XX1, XX3 : A1)

4. XX3 is mapped to A8 (temp result XX1, XX3 : A1, A8)

5. A8 is mapped to XX9 (temp result XX1, XX3, XX9 : A1, A8)

Is there anyway to achive?

Input	Input	Output
COL1	COL2	Map	Type
XX1	A1	XX1,XX3,XX9 : A1,A8	M:1
XX2	A2	XX2,XX4 : A2	M:1
XX3	A1	XX1,XX3,XX9 : A1,A8	M:1
XX4	A2	XX2,XX4 : A2	M:1
XX5	A3	XX5:A3	1:1
XX6	A4	XX5:A4,A5	1:M
XX6	A5	XX5:A4,A5	1:M
XX7	A6	XX7,XX8:A6:A7	M:M
XX7	A7	XX7,XX8:A6:A7	M:M
XX8	A6	XX7,XX8:A6:A7	M:M
XX8	A7	XX7,XX8:A6:A7	M:M
XX3	A8	XX1,XX3,XX9 : A1,A8	M:M
XX9	A8	XX1,XX3,XX9 : A1,A8	M:M

[lbendlin]

What you describe sounds more like a network graph than a hierarchy. Hierarchies by definition must be 1:M  (ok, with a corner case of 1:1).

[Daniel29195]

@vinothkumar1990 

im not sure if this would help  : 

go to power query ,

use the split by column feature : 

you will have the following :

Again -->  split by columns -->  by delimiter -->  

you will have the bfollowing : 

last step : 

select Map-2 column, --> split by columns -->  by delimiter -->  

final result : 

if this is not what you are looking for , please share an example of the output you want . 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[vinothkumar1990]

@Daniel29195 - COL1 and COL2 are the data present in input file where as "Map" is the output which i need to derive using COL1 and COL2.

Explanation - For example

1. XX1 is mapped to A1 (temp result XX1 : A1)
2. A1 is mapped to XX3 (temp result XX1, XX3 : A1)

3. A1 is mapped to XX3 (temp result XX1, XX3 : A1)

4. XX3 is mapped to A8 (temp result XX1, XX3 : A1, A8)

5. A8 is mapped to XX9 (temp result XX1, XX3, XX9 : A1, A8)