Data references

[pawel_lyszczarz]

Hello, I'm looking for a way to combine data references. I've got data as shown below where A references B, B->C and C->D.
I want to convert that so I know that A has references B,C and D.

Data:

A	B
B	C
C	D

Expected result:

A	B,C,D
B	C,D
C	D

I was able to do that with Excel's  "Filter" and "Textjoin" formulas and create a new query based on the previous one, but that's a workaround far from ideal.

P.S That's my first post in here so not sure if the title is correct.

[ImkeF]

Hi @pawel_lyszczarz ,
indeed, self-references are not supported with my function.
But what you could do is to filter our any self-referencing rows before you apply the function.
Then, add them back in by a simple merge/join.

[TomMartens]

Hey @pawel_lyszczarz ,

@ImkeF has a funtion that creates a path, basically that is what you are looking. The first column contains the parent node, and the second column contains the child node. I assume you have to adapt your table in the way that A also appears in the second column without a value in the parent node column.

You can find the function here: https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi...

After applying the function, you can remove the unwanted column and replace the "|" with a ",".

Hopefully this helps to tackle your challenge.

Regards,

Tom

[pawel_lyszczarz]

Seems like using that function creates an endless loop. That's probably because my data looks more like this:

Data:

A	A
A	B
B	B
B	C
C	C
C	D
D	D

Expected result:

A	A,B,C,D
B	B,C,D
C	C, D

So there's a data where A is both Parent and Child. Any idea how to modify so the loop would work properly?