cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
maarten_74 Frequent Visitor
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
Super User
Super User

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])
2 REPLIES 2
Super User
Super User

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])
Vikram123 Regular Visitor
Regular Visitor

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 897 guests
Please welcome our newest community members: