Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power Query code to find parent

Hi all! 

 

I have a table containing a hierarchal breakdown of employees as the one shown below:

 

LevelName
1Person 1
2Person 2
3Person 3
3Person 4
3Person 5
4Person 6
4Person 7
3Person 8
3Person 9
2Person 10
3Person 11

 

Each person reports to the person above them with the lower-number in the level column. This means that Person 2 reports to Person 1, Person 5 to Person 2, Person 10 to Person 1, etc. I want to make a new column after I have loaded my data in PowerBI that contains each person's "Parent". It should be done in the Power Query Editor as soon as the data has been loaded. 

 

I hope that you can help me.

 

Br. Trond 

 

 

 

Need Help
  • AlB's avatar
    AlB
    5 years ago

    Anonymous 

    Ok. You can just keep the first item in the list then:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILSrOz1MwVIrViVYyQggYgQWMEQLG6AIm6AKmYAEThIAZuoA5uhYLdAFLdHcYGqArMQS6NRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Name", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Parent", each try Table.SelectRows(#"Changed Type", (inner)=>inner[Level]=[Level]-1)[Name]{0} otherwise null, type text)
in
    #"Added Custom"

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

  • AlB's avatar
    AlB
    5 years ago

    Anonymous 

    Then we need to add an index to establish that order:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILSrOz1MwVIrViVYyQggYgQWMEQLG6AIm6AKmYAEThIAZuoA5uhYLdAFLdHcYGqArMQS6NRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Name", type text}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Parent", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=[Level]-1 and inner[Index]<[Index])[Name]) otherwise null, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

    It would have helped if you had shown the expected result from the beginning

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

     

8 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Anonymous 

    Ok. You can just keep the first item in the list then:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILSrOz1MwVIrViVYyQggYgQWMEQLG6AIm6AKmYAEThIAZuoA5uhYLdAFLdHcYGqArMQS6NRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Name", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Parent", each try Table.SelectRows(#"Changed Type", (inner)=>inner[Level]=[Level]-1)[Name]{0} otherwise null, type text)
in
    #"Added Custom"

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Sorry, that actually still doesn't work. Person 7 parent is Person 5 not Person 3 as it gets me now. So the rule is:

       

      It's the parent, if the level is 1 number lower, and of all the persons with one level lower, it is the first person that is above the person in the list. 

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Anonymous 

    Then we need to add an index to establish that order:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILSrOz1MwVIrViVYyQggYgQWMEQLG6AIm6AKmYAEThIAZuoA5uhYLdAFLdHcYGqArMQS6NRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Name", type text}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Parent", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=[Level]-1 and inner[Index]<[Index])[Name]) otherwise null, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

    It would have helped if you had shown the expected result from the beginning

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

       

      Thanks! Now if I want to add a column for each parent level, I can just replace the "[Level]-1" with the level I want. However, when I add multiple columns in the advanced editor, I only get the last column that I write out.

       

       

       

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILSrOz1MwVIrViVYyQggYgQWMEQLG6AIm6AKmYAEThIAZuoA5uhYLdAFLdHcYGqArMQS6NRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Name", type text}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom 0" = Table.AddColumn(#"Added Index", "Parent0", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=0 and inner[Index]<[Index])[Name]) otherwise null, type text),
    #"Added Custom 1" = Table.AddColumn(#"Added Index", "Parent1", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=1 and inner[Index]<[Index])[Name]) otherwise null, type text),
    #"Added Custom 2" = Table.AddColumn(#"Added Index", "Parent2", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=2 and inner[Index]<[Index])[Name]) otherwise null, type text),
    #"Added Custom 3" = Table.AddColumn(#"Added Index", "Parent3", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=3 and inner[Index]<[Index])[Name]) otherwise null, type text),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

       

       

       

       

      Why is that?

       

      Br. Trond 

       

       

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    Place the following M code in a blank query to see the steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILSrOz1MwVIrViVYyQggYgQWMEQLG6AIm6AKmYAEThIAZuoA5uhYLdAFLdHcYGqArMQS6NRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Name", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Parent", each Text.Combine(Table.SelectRows(#"Changed Type", (inner)=>inner[Level]=[Level]-1)[Name], ", "))
in
    #"Added Custom"

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi AlB ,

       

      Thanks for the help! However, it doesn't work. In the Parent column, I only need the parent that is above the person. The code that you has provided me with, makes Person 6's parent all level 3, however, it should only be the first level 3 above the person in the table.

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Anonymous 

    You have to refer to the immediately previous step in each new step:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILSrOz1MwVIrViVYyQggYgQWMEQLG6AIm6AKmYAEThIAZuoA5uhYLdAFLdHcYGqArMQS6NRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Name", type text}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Parent0", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=0 and inner[Index]<[Index])[Name]) otherwise null, type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Parent1", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=1 and inner[Index]<[Index])[Name]) otherwise null, type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Parent2", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=2 and inner[Index]<[Index])[Name]) otherwise null, type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Parent3", each try List.Last(Table.SelectRows(#"Added Index", (inner)=>inner[Level]=3 and inner[Index]<[Index])[Name]) otherwise null, type text),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Index"})
in
    #"Removed Columns"

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers