cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Hierarchy output

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
3 REPLIES 3
Super User

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

Super User

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! 🤠

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.