Power Query code to find parent

[Anonymous]

Hi all! 

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

Level	Name
1	Person 1
2	Person 2
3	Person 3
3	Person 4
3	Person 5
4	Person 6
4	Person 7
3	Person 8
3	Person 9
2	Person 10
3	Person 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 

[AlB · Accepted solution]

@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]

@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 

[AlB · Accepted solution]

@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]

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 · Accepted solution]

@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]

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. 

[Anonymous]

Thanks!!

[AlB]

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]

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.