Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

Thanks

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.

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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"

 

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.