Hierarchy Path Filtering Table in Power Query M

[PANDAmonium]

Hi All,

I'm trying to convert a table containing heirarchy paths into a format I can use for filtering, and I need it in Power Query M so I can put it in a dataflow for reusability and effeciency. Hopefully the example will explain it better:

My table currently looks like this (1 column, the pipes are just the delimiter in each string):

Path

5939|2031|6583|2918

5939|2852

5939

2395|2810

And I want to transform it to a table that looks like this (2 columns):

Parent | Child

5939 | 5939

5939 | 2031

5939 | 6583

5939 | 2918

2031 | 6583

2031 | 2918

6583 | 2918

2918 | 2918

5939 | 5939

5939 | 2852

2852 | 2852

5939 | 5939

2395 | 2395

2395 | 2810

2810 | 2810

The paths have variable depths (maybe up to 10) and the duplicates aren't needed. I plan on removing the duplicates after but left them there so the example wasn't confusing. Also the data we're using isn't perfect which is why I threw in that weird one at the end. But if I can get my data to this point I can merge it with other tables and it'd open up a lot of possibilities.

If there is another way to set up filtering for this heirarchy path, I'm open to any ideas. There honestly might be a much easier solution to accomplish the same thing I'm just not seeing because I've been really exhausted and not thinking straight lately.

Thank you in advance!

Edited: The transformed table was missing some rows.

[v-yangliu-msft]

Hi  @PANDAmonium ,

You can refer to the following link:

https://community.powerbi.com/t5/Power-Query/Flatten-One-column-Hierarchy-into-a-Table/m-p/1259233

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PANDAmonium]

Thanks. I actually figured this out shortly after posting. It's just very messy at the moment. So once I'm caught up on work and have time, I'll clean it up, add some comments, and post it as the solution.