Forum Discussion
Merge queries
Hello, When I merge queries, I am finding that if one of the queries doesn't have information, the tile that I create is blank even though the other queries have information. What can I do to...
OK, I need to know more about what you are doing. I create four tables like this:
Table1
Student Aid
Greg 10 Andrea 20 John 30 Billy 40 Table2
Student ISIR
Greg 1 Andrea 2 John 3 Table3
Student Items
Greg 100 Andrea 200 Table4
Student Accts
Greg 1 Then I used this code:
let Source = Table.NestedJoin(Table1,{"Student"},Table2,{"Student"},"Table2",JoinKind.FullOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ISIR"}, {"Table2.ISIR"}), #"Merged Queries" = Table.NestedJoin(#"Expanded Table2",{"Student"},Table3,{"Student"},"Table3",JoinKind.FullOuter), #"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries", "Table3", {"Items"}, {"Table3.Items"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Table3",{"Student"},Table4,{"Student"},"Table4",JoinKind.FullOuter), #"Expanded Table4" = Table.ExpandTableColumn(#"Merged Queries1", "Table4", {"Accts"}, {"Table4.Accts"}) in #"Expanded Table4"This returned all rows to my dataset.
What I would suggest would be to create a table with all students in it. Use this table and merge in a second. Use the output of that to merge in the next table and so on and so on, all with Full Outer Joins. Then you are guaranteed to have all students.
Okay-we will try that. Thank you for your help!
Andrea
No problem. Note that if you don't have such a table, you could create one by Appending all four tables together, remove all columns but Student and then do a Remove Duplicates on that column.