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...
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.

     

     

     

     

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.

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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"