Parent-child hierarchy: Adding parentless parents to child column

[nhoff]

From different data sources I have information about parent-child relationships in a dataset. Since the information only includes these entities (and not the complete dataset) I get issues, which can be seen in the spreadsheet.

In order to build the parent-child hierarchy it is apparently a prerequisite, that all parents exist in the child column, even though they don't have parents.

I have tried to:

a) Append queries from source1, source2. This gives an error, that all parents must be in the child column

b) Append queries from source1, source2 and CompleteList, giving all values from CompleteList the value null in parents. This removes the error from a), but results in another error since there is now ambiguity about the parents of the parents, that actually do have parents.

The actual dataset is XML based and contains completely different information, but I thought I'd provide a simplified example below and attached. 

[BhaveshPatel]

Is that the List from Source 3 only has the Names?? 

You have mentioned that Append from Source 1 and 2 throwing a error. That doesn't seem accurate. It is just appending the data fro two different sources.

I would like you to post more detailed explaination of the problem. 

[nhoff]

Hi Bhavesh

a) Yes, the list from Source 3 has in the example only names. The two other columns I have only added in Excel to demonstrate the issue at hand.

b) It is correct, that the append of Source 1 and Source 2 works fine. It is once the PATH function in DAX is used, that it complains about missing children. If you open the PowerPivot of the workbook you will get the error.

Kind regards

Nikolaj

[ImkeF · Accepted solution]

Hi Nikolaj,

you can use this query:

let
    ListOfAllChildren = List.Distinct(List.Combine({Source1[Child], Source2[Child]})),
    ListOfAll = List.Distinct(CompleteList[Name]),
    OnlyParents = List.Difference(ListOfAll, ListOfAllChildren),
    TblAppend = Table.FromList(OnlyParents, null, {"Child"}),
    Append = Table.Combine({Source1, Source2, TblAppend})
in
    Append

[nhoff]

Hi Imke

Thanks for the reply. Apologies for not getting back, but I did not receive the notification about your post and I thought no-one had answered.

Your solution worked very well. It seems so simple once you have seen it. Thanks a lot!

Nikolaj