cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
andreajohn Frequent Visitor
Frequent Visitor

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 make data show up if one or more of the queries merged doesn't have data?

 

For instance, I am creating a report that combines Financial Aid info, ISIR info, Student Accts info and Student Items info. I merged these separate tables from salesforce into one table. If the student has ISIR info but no FA, SA or SI info on their account, nothing comes up for the student. If I separate all of the tables, the info will populate.

 

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Merge queries

OK, I need to know more about what you are doing. I create four tables like this:

 

Table1

Student      Aid

Greg10
Andrea20
John30
Billy40

 

Table2

Student      ISIR

Greg1
Andrea2
John3

 

Table3

Student       Items

Greg100
Andrea200

 

Table4

Student    Accts

Greg1

 

 

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.

 

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


7 REPLIES 7
Super User
Super User

Re: Merge queries

Sounds like you need to change your "Join Kind" to "Full Outer". The default is Left Outer.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


andreajohn Frequent Visitor
Frequent Visitor

Re: Merge queries

Yes, I did use full outer join and I am still running into this issue.

 

Thanks

Super User
Super User

Re: Merge queries

OK, I need to know more about what you are doing. I create four tables like this:

 

Table1

Student      Aid

Greg10
Andrea20
John30
Billy40

 

Table2

Student      ISIR

Greg1
Andrea2
John3

 

Table3

Student       Items

Greg100
Andrea200

 

Table4

Student    Accts

Greg1

 

 

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.

 

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


andreajohn Frequent Visitor
Frequent Visitor

Re: Merge queries

Here is the issue,

 

I have a main table that is being used to pull  data for each individual student. When I merge the four tables, I am creating a relationship between the main table and the merge tables. This is where the issue is occuring. If that student has data on the FA table but not on the ISIR table, even though these tables are merged, the data is not showing up as if there is no information on either table for the student.

 

Here is my code.

let
    Source = Table.NestedJoin(#"Financial Aid Award",{"Contact Record ID"},#"ISIR Record",{"Contact Record ID"},"ISIR Record",JoinKind.FullOuter),
    #"Expanded ISIR Record" = Table.ExpandTableColumn(Source, "ISIR Record", {"FAFSA Comp", "ISIR Proc'd", "ISIR Recv'd", "ISIR Susp'd", "ISIR Year", "ISIR in SF  Date", "Package Comp"}, {"FAFSA Comp", "ISIR Proc'd", "ISIR Recv'd", "ISIR Susp'd", "ISIR Year", "ISIR in SF  Date", "Package Comp"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded ISIR Record",{"Contact Record ID"},#"Student Financials Item",{"Contact Record ID"},"Student Financials Item",JoinKind.FullOuter),
    #"Expanded Student Financials Item" = Table.ExpandTableColumn(#"Merged Queries", "Student Financials Item", {"Amount", "SA Description", "Effective Date"}, {"Amount", "SA Description", "Effective Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Student Financials Item", each true)
in
    #"Filtered Rows"

 

 

Super User
Super User

Re: Merge queries

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


andreajohn Frequent Visitor
Frequent Visitor

Re: Merge queries

Okay-we will try that. Thank you for your help!

 

Andrea

Super User
Super User

Re: Merge queries

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. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!