cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Add custom column from parent value

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:

 

LineNrParentLineNrStatus
1 Completed
21 
31 
4 In progress
54 
64 
74 
8 To do
98 

 

 

LineNrParentLineNrStatusParentstatus
1 Completed 
21 Completed
31 Completed
4 In progress 
54 In progress
64 In progress
74 In progress
8 To do 
98 To do

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Add custom column from parent value

@maarten_74 

 

As a calculated column withe DAX you can use

 

Calculated Column = Lookupvalue(Table1[Status],Table1[LineNr],[ParentLineNr])
Try my new Power BI game Cross the River

View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: Add custom column from parent value

@maarten_74 

 

As a calculated column withe DAX you can use

 

Calculated Column = Lookupvalue(Table1[Status],Table1[LineNr],[ParentLineNr])
Try my new Power BI game Cross the River

View solution in original post

Highlighted
Helper I
Helper I

Re: Add custom column from parent value

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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors