Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
5 years ago
Solved

Create Parent Hierarchy

Hey guys I believe this is a tuff one. How can i creat a parent column out of 1 column like this?   Thanks to all Pedro
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello pedroccamaraDBI 

     

    try this

     

    let
Source = Excel.Workbook(File.Contents("C:\Users\Pedro\Desktop\Accounts.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"GL SNC AccountID", type text}, {"GroupingCategory", type text}}),
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))
)
in
AddParent

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
5 years ago

Hi again Jimmy801 
The report is showing like this:


Of course i'm not saying it's your fault but is there any way of not showing all the blanks here? If i select all but blanks i also will loose some values....
Thanks again

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello pedroccamaraDBI 

 

simple words... 🙂 ... the logic is to use the material list and check every entry and trying to compare every entry with the material of the current row but using the length of the list entry.. because this is the only logic that work in your example. every match is listed the other get null. Then exclude the nulls end use the last entry.

 

For the second question. I'm not an expert on visualizations. But can you share a small part of your database and what exactly you want to see in the visualisation?

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy