Compare two columns and bring value of third column

[sid-poly]

There is a table like 

Key Child	Key Parent	Date of Completion
1		22/03/2023
2	1	22/04/2023
3	4	14/11/2022
5		19/11/2022
4	5	18/10/2022

based on the table above there is a parent key and a child key. Need a column

If key child is not null and key parent is null then Date of Completion and if Key child is not null and key parent is not null then give the date based on key parent column. Please find below the required table

Key Child	Key Parent	Date of Completion	Key Parent DOC
1		22/03/2023	22/03/2023
2	1	22/04/2023	22/03/2023
3	5	14/11/2022	19/11/2022
5		19/11/2022	19/11/2022
4	5	18/10/2022	19/11/2022

Please help with the power query logic for the above mentioned scenario

[wdx223_Daniel · Accepted solution]

in DAX, it seems easier

Parent DOC = VAR _p=VALUE(PATHITEM(PATH(SampleData[Key Child],SampleData[Key Parent]),1)) RETURN MAXX(FILTER(SampleData,SampleData[Key Child]=_p),'SampleData'[Date of Completion])

in M, you can create a custom recursion function.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcxRCsAwCAPQu/hd0EQH3Vmk97/GdCuMQr5eQjIFMqRCqrnS6LJGCouwOX72ougqFGjmy9f3gfvQHnaDqbDN6wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key Child" = _t, #"Key Parent" = _t, #"Date of Completion" = _t]),
Custom1 = let fx=(k)=>let a=Source{[Key Child=k]}? ??[] in if a[Key Parent]?=null or a[Key Parent]?="" then a[Date of Completion]? else @Fx(a[Key Parent]) in Table.AddColumn(Source,"Parent DOC",each fx([Key Child]))
in
Custom1

[ronrsnfld]

The code below gives the results you show (almost) with the following caveats:

• Line 3 of your results table has `5` for Key Parent, but your data table shows `4`
  
  • I assumed there should be a `4` in the results table also
• None of your Key Child cells are null
• If both Parent and Child are non-null, then return DOC of the same child column that equals the parent

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczBCcBQCAPQVYrnD5pooZ1F/v5rVItQesjlJSRTIEuOCqnmSqPLXikswnB87EXRVSjQzJfPOcH94152hUthw/sB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key Child" = _t, #"Key Parent " = _t, #"Date of Completion" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Key Child", Int64.Type}, 
        {"Key Parent ", Int64.Type}, 
        {"Date of Completion", type date}},
        "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Key Parent DOC", each 
        if [#"Key Parent "] = null
          then [Date of Completion]
          else  #"Changed Type"[Date of Completion]
            {List.PositionOf(#"Changed Type"[#"Key Child"],[#"Key Parent "])}, type date)
in
    #"Added Custom"

Data

Results

[ND_Pard]

I used four (4) Power Queries.  Below are the names I gave each query and the code used to arrive at the solution.

Hi-Light only the 1st three (3) columns of data: Key Child, Key Parent, Date of Completion

Click on the ribbon: Data, under the group: Get and Transform Data, click the: From Table/Range

Once the Power Query Editor opens, on the right side of the screen enter the query name.
Click on the Advanced Editor and replace with the code provided.

Query Name: Data
Note: in the code below, Power Query named my hi-lighted data: "Table" (Yours may name it something else, change the name accordingly.)
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Key Child", type text}, {"Key Parent", type text}, {"Date of Completion", type date}})
in
#"Changed Type"

Click on "Done"
On the ribbon: Home, click: Close and Load

Hi-Light the newly created table and similar to the above, create the next Power Query:
Query Name: Key_Child
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Remove Key Parent Colmn" = Table.RemoveColumns(Source,{"Key Parent"}),
#"Changed Type" = Table.TransformColumnTypes(#"Remove Key Parent Colmn",{{"Key Child", type text}, {"Date of Completion", type date}})
in
#"Changed Type"

Click on "Done"
On the ribbon: Home, click: Close and Load

Hi-Light the newly created table and similar to the above, create the next Power Query:
Query Name: Key Parent DOC
Code:
let
Source = Table.NestedJoin(Data, {"Key Parent"}, Key_Child, {"Key Child"}, "Key_Child", JoinKind.LeftOuter),
#"Expanded Key_Child" = Table.ExpandTableColumn(Source, "Key_Child", {"Date of Completion"}, {"Key_Child.Date of Completion"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Key_Child",{"Key Child", "Date of Completion"}),
#"Filtered Out Null Key Parent" = Table.SelectRows(#"Removed Columns", each ([Key Parent] <> null)),
#"Rename Key Parent DOC Colmn" = Table.RenameColumns(#"Filtered Out Null Key Parent",{{"Key_Child.Date of Completion", "Key Parent DOC"}}),
#"Removed Duplicate Key Parents" = Table.Distinct(#"Rename Key Parent DOC Colmn", {"Key Parent"})
in
#"Removed Duplicate Key Parents"

Click on "Done"
On the ribbon: Home, click: Close and Load

Hi-Light the newly created table and similar to the above, create the next Power Query:
Query Name: Solution

Code:
let
Source = Table.NestedJoin(Data, {"Key Parent"}, #"Key Parent DOC", {"Key Parent"}, "Key Parent DOC", JoinKind.LeftOuter),
#"Expanded Key Parent DOC" = Table.ExpandTableColumn(Source, "Key Parent DOC", {"Key Parent DOC"}, {"Key Parent DOC.1"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Key Parent DOC", "Key Parent DOC", each if [Key Parent DOC.1] = null then [Date of Completion] else [Key Parent DOC.1]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Key Parent DOC.1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Key Parent DOC", type date}})
in
#"Changed Type"

Click on "Done"
On the ribbon: Home, click: Close and Load

This final query should be what you're looking for ...

Good Luck