Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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,

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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.

     

     

     

     

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

       

      Thanks

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        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.