Forum Discussion

maarten_74's avatar
maarten_74
Frequent Visitor
7 years ago
Solved

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    maarten_74 

     

    As a calculated column withe DAX you can use

     

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

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    maarten_74 

     

    As a calculated column withe DAX you can use

     

    Calculated Column = Lookupvalue(Table1[Status],Table1[LineNr],[ParentLineNr])
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.