Hierarchy output

Question

Hi All,I came across some below hierarchical requirment. I have two columns called COL1 and COL2 and want to derive the value (MAP).&nbsp;COL1 and COL2 have 4 type of relationships i.e. 1:1, 1:M, M:1, M:M.&nbsp;Explanation - For exampleExplanation - For example1. XX1 is mapped to A1 (temp result XX1 : A1)2. A1 is mapped to XX3&nbsp;(temp result XX1, XX3 : A1)3. A1 is mapped to XX3&nbsp;(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)&nbsp;&nbsp;Is there anyway to achive?InputInputOutputCOL1COL2MapTypeXX1A1XX1,XX3,XX9 : A1,A8M:1XX2A2XX2,XX4 : A2M:1XX3A1XX1,XX3,XX9 : A1,A8M:1XX4A2XX2,XX4 : A2M:1XX5A3XX5:A31:1XX6A4XX5:A4,A51:MXX6A5XX5:A4,A51:MXX7A6XX7,XX8:A6:A7M:MXX7A7XX7,XX8:A6:A7M:MXX8A6XX7,XX8:A6:A7M:MXX8A7XX7,XX8:A6:A7M:MXX3A8XX1,XX3,XX9 : A1,A8M:MXX9A8XX1,XX3,XX9 : A1,A8M:M

daniel29195 · Answer

vinothkumar1990&nbsp;&nbsp;im not sure if this would help&nbsp; :&nbsp;go to power query ,use the split by column feature :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;you will have the following :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Again --&gt;&nbsp; split by columns --&gt;&nbsp; by delimiter --&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;you will have the bfollowing :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;last step :&nbsp;select Map-2 column, --&gt; split by columns --&gt;&nbsp; by delimiter --&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;final result :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if this is not what you are looking for , please share an example of the output you want .&nbsp;&nbsp;&nbsp;&nbsp;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! 🤠&nbsp;

lbendlin · Answer

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

vinothkumar1990 · Answer

Daniel29195&nbsp;- COL1 and COL2 are the data present in input file where as "Map" is the output which i need to derive using&nbsp;COL1 and COL2.&nbsp;Explanation - For example1. XX1 is mapped to A1 (temp result XX1 : A1)2. A1 is mapped to XX3&nbsp;(temp result XX1, XX3 : A1)3. A1 is mapped to XX3&nbsp;(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)&nbsp;

Forum Discussion

Hierarchy output

3 Replies

Related content

Date hierarchy

Date Hierarchy

Ragged Hierarchy?

VAR and SWITCH Output issues

Field Parameter Hierarchy

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