I have a number in a table that could be a primary key in one of two tables: ParentTable or ChildTable. If the number is in ChildTable, that table will also have a number that joins via the same primary key to the ParentTable.
| ChildTable | ParentTable |
|---|---|
| ChildTablePK | ParentTablePK |
| ParentTableFK | {additional columns} |
| {additional columns} |
Right now I'm joining the number to each of the tables via two NestedJoin steps. If the number is the ChildTablePK, then the resulting table from one of the NestedJoins looks like the ChildTable above and the other NestedJoin is an empty table. It's the opposite if the number is a ParentTablePK.
What I want to do is get data from the ParentTable in both cases. So if the number is a ChildTablePK, use the ParentTableFK to join to the ParentTablePK and get data from the ParentTable. If the number is a ParentTablePK, just get the data from ParentTable (there won't be anything in ChildTable in this case).
How do I go about this?
Example
I have two records, one with number 1234 and the other is 5678. I do the NestedJoin to ParentTable and ChildTable for each of them.
1234 matches a record in ChildTable, thus the result of the NestedJoin to ChildTable has a non-empty table while the NestedJoin to ParentTable has an empty table.
5678 matches a record in ParentTable, so the result of the NestedJoin to ParentTable is a non-empty table while the result to ChildTable is empty.
| Number | NestedJoinChildTable | NestedJoinParentTable |
|---|---|---|
| 1234 | Table {ChildTableNum = 1234, ParentTableNum = 0111} | {empty table} |
| 5678 | {empty table} | Table {ParentTableNum = 5678} |
At this point, I want to get data from the ParentTable for number 0111, then use ExpandTableColumn.
2 Answers
Let's make a function to get the ParentTablePK value for a given record, called "fParentTablePK". There are many ways to do it, this is just one...
let ParentTablePKs = ParentTable[ParentTablePK], C = Table.Buffer( // Table.Buffer might make it faster or slower. Experiment. Table.SelectColumns(ChildTable, {"ChildTablePK","ParentTableFK"}) ) in // return a function that takes a record and returns a parent key each let K = [#"Unknown FK Column Name in DataTable"] in if List.Contains(ParentTableFKs, K) then K else ChildTable{[ChildTablePK = K]}[ParentTableFK] Then we can add the ParentTableFK column to your data table...
Table.AddColumn( DataTable, "ParentTableFK", fParentTablePK, Int64.Type ) This gives you a table you can join against ParentTable!
What I ended up doing was a NestedJoin from the Number to both the Parent and Child tables (which I already said in my original question). I then used ExpandTableColumn on each of them, except I only expanded the Number from the Parent table join. And I named the new columns as "NumberFromParent" and "NumberFromChild". Then added a new column, and checked if the NumberFromChild was null, then use the NumberFromParent, otherwise use NumberFromChild. Then I did another join on that new column to the Parent Table.
ChildTableJoin = Table.NestedJoin("", {"Number"}, #"Child Table", {"Number}, "Child Column"), ParentTableJoin = Table.NestedJoin("", {"Number"}, #"Parent Table", {"Number}, "Parent Column"), ChildTableExpand = Table.ExpandTableColumn("", "Child Column", {"Number", "Other data..."}, {"NumberFromChild", "Other data..."}), ParentTableExpand = Table.ExpandTableColumn("", "Parent Column", {"Number"}, {"NumberFromParent"}), NumberToUse = Table.AddColumn("", "NumberToUse", each if [NumberFromSite] = null then [NumberFromParent] else [NumberFromSite]), NumberParentJoin = Table.NestedJoin("", {"NumberToUse"}, #"Parent Table", {"Number}, "Number Parent Column") 
