Power Query Self-Merge/Lookup Question

[cr731]

I have a table with values such as below,

ID	ID Description	Parent ID
A	Description A	A
A	Description A	A
B	Description B	A
C	Description C	B

<tbody>
</tbody>

I am trying to create a new column to show the Parent ID description. The outcome would be as shown below:

ID	ID Description	Parent ID	Parent ID Description
A	Description A	A	Description A
A	Description A	A	Description A
B	Description B	A	Description A
C	Description C	B	Description B

<tbody>
</tbody>

I'm struggling with how to accomplish this in Power Query. When I try to self-merge the table, I get many duplications because I have duplicate rows as shown above (where ID A is listed twice... and where ID A is the parent of ID A, meaning it is the highest level of the hierarchy).

If I self-merge this table onto itself, it results in a multiplication effect where I get four rows for A.

Is there a way to perform just a simple lookup to get the title from the ID Description title for the Parent ID? Would a Custom Column be more well suited for this?

Thanks

 

[billszysz]

Try this code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"ID Description", type text}, {"Parent ID", type text}}),
    RemoveDuplicates = Table.Distinct(ChType),
    SecondTable = Table.RemoveColumns(RemoveDuplicates,{"Parent ID"}),
    Merge = Table.NestedJoin(ChType,{"Parent ID"},SecondTable,{"ID"},"New Column",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "New Column", {"ID Description"}, {"Parent ID Description"})
in
    Expand

Regards