Add custom column from parent value

[maarten_74]

Hi there,

I've got a table that contains projects and has a parent child hiëarchy and i would like to show the parent's status on every child row. My table looks like this:

LineNr	ParentLineNr	Status
1		Completed
2	1
3	1
4		In progress
5	4
6	4
7	4
8		To do
9	8

LineNr	ParentLineNr	Status	Parentstatus
1		Completed
2	1		Completed
3	1		Completed
4		In progress
5	4		In progress
6	4		In progress
7	4		In progress
8		To do
9	8		To do

Thanks

[Vikram123]

hi 

1. Go to edit Query

2. Create table From Home Tab with "Enter Data" click ok 

3. go to advanced Query replace all with below code :

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8snMS/UrUtJRCkgsSs0rgXODSxJLSouVYnWilQyBXAUgds7PLchJLUlNAYsaAUUgMiCeMQrPBKrDM0+hoCg/vSi1GGKSKVDMBK7KDIVnjsKzgJoQkq+Qkg8WsQTyIKKxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"LineNr", Int64.Type}, {"ParentLineNr", Int64.Type}, {"Status", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [ParentLineNr] = null then [LineNr] else [ParentLineNr]),
#"Merged Queries" = Table.NestedJoin(#"Added Conditional Column", {"Custom"}, #"Added Conditional Column", {"LineNr"}, "Added Conditional Column", JoinKind.LeftOuter),
#"Expanded Added Conditional Column" = Table.ExpandTableColumn(#"Merged Queries", "Added Conditional Column", {"Status"}, {"Added Conditional Column.Status"}),
#"Added Conditional Column1" = Table.AddColumn(#"Expanded Added Conditional Column", "Parentstatus", each if [ParentLineNr] = null then null else [Added Conditional Column.Status]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Custom", "Added Conditional Column.Status"})
in
#"Removed Columns"

-------------------------------

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

[Zubair_Muhammad · Accepted solution]

@maarten_74 

As a calculated column withe DAX you can use

Calculated Column = Lookupvalue(Table1[Status],Table1[LineNr],[ParentLineNr])