Create Parent Level

Power Query
1

Hey guys,
I believe this is a tuff one…

How to create a parent column in power query from this column:

Capture

Thank you all
Pedro

2

@pedroccamara,

You may find these two videos helpful in addressing your hierarchy creation issues:

  • Brian
1 Like
3

Hey @BrianJ
Thank you very much for those videos. I saw them a while ago, and i was looking for them.
About this, it didn’t work the way i wanted because it showed me rows without any value, because not all levels have the same length, you know?

Capture1
Capture1223×548 19.2 KB

I wish i had an option “don’t show with no data” on these columns…
Do you know a way to fix it?
Let me send you my pbi file[Hierarchies.pbix|attachment]
(upload://xaisy0UBBJ4NtrGLY5uCiIBXmjK.pbix) (437.9 KB)
Thanks a lot @BrianJ

4

@pedroccamara,

For some reason, the PBIX file in your prior message didn’t come through - can you please resend?

  • Brian
5

Hierarchies.pbix (437.9 KB)
Sorry

7

@pedroccamara,

Great to see that you were able to solve this issue. For the benefit of others on the forum who may have the same or similar questions, can you please post your solution?

Thanks.

  • Brian
8

Hey @BrianJ
I did post the solution but then i’ve deleted because it was another solution, but in dax, not M code like the videos from Ruth. At the end, the problem still exists, i mean, exactly the same as my picture above. Can’t get rid of those spaces without values…
For sure you didn’t come up with a solution for it, right?

9

@pedroccamara,

I haven’t had a chance to tackle this one yet, since I’ve got another complex one that I had in the queue ahead of this one that I’m still working on generating a solution for, So, anyone on the forum should please feel free to take a run at this one, and if not solved by the time I finish the current one, I will turn my attention to this.

  • Brian
1 Like
10

Hey @BrianJ
Found the solution to remove the spaces in blank (no values). Please watch this video from Ruth:

Also, if you want to build the parent column in an account column just add this code to your query :

BufferMaterial = List.Buffer(#“Changed Type”[GL SNC AccountID]),
AddParent = Table.AddColumn
(
#“Changed Type”,
“Parent”,
(row)=> List.Last(List.Select(List.Transform(BufferMaterial, (trans)=> if trans = Text.Start(row[GL SNC AccountID], Text.Length(trans)) and trans<> row[GL SNC AccountID] then trans else null),each _<> null))
),
#“Changed Type2” = Table.TransformColumnTypes(AddParent,{{“Parent”, type text}})
in
#“Changed Type2”

And that’s it!.
Thank you all guys
(it felt very good to help you guys after you’ve helped me like 50 times or more… :slight_smile:)

1 Like
11

@pedro,

Nice work! Thanks for posting the detailed solution.

  • Brian