none
Left join causing left table row loss RRS feed

  • Question

  • Hello, I am absolutely stumped by this issue and I'm hoping someone can give me a direction to troubleshoot. The issue is that when I do a left outer join in Power Query and then expand the right table, I am losing rows from the left table. I am only losing 5 out of 28,409, so it's not a massive issue, just an infuriating one. The issue unfortunately only occurs when working with all of the data, which I can't share. The best I can do is reduce the tables to two examples and provide the below print screens of the sequence. What I want from the right table is the Assignment Exception if it exists. The merge works without issue and I see the New Column. The expand however, causes both rows of the left table to disappear. Yes, the join key is a weird format but I have no control over it. In an early stage, it is cleaned and trimmed on both of the tables. A full outer join doesn't seem to produce an expected result either. Thoughts?

    Left Table

    Right Table

    Code

        #"Merged to Right Table" = Table.NestedJoin(LeftTable,{"UCM ID"},RightTable,{"UCM ID"},"New Column",JoinKind.LeftOuter),
        #"Expanded Right Table" = Table.ExpandTableColumn(#"Merged to Right Table", "New Column", {"Assignment Exception"}, {"Assignment Exception"}),

    Merged

    Expanded

    If I tweak the code to this

        #"Merged to Right Table" = Table.NestedJoin(LeftTable,{"UCM ID"},RightTable,{"UCM ID"},"New Column",JoinKind.FullOuter),
        #"Expanded Right Table" = Table.ExpandTableColumn(#"Merged to Right Table", "New Column", {"UCM ID", "Assignment Exception"}, {"new.UCM ID", "new.Assignment Exception"}),

    I get this bit of wackiness

    Monday, April 16, 2018 3:39 PM

Answers

  • Hi,

    Are you still seeing this issue?

    Let me summarize to have a better understanding of all the options covered so far:

    1) You also mentioned that you are only able to repro this with one specific set of data. Which leads me to believe that this is an issue specific to that one particular set of data that unfortunately you cannot share.

    2) You have also mentioned that this is not spaces related or case (upper / lower) related either, and you have already addressed these.

    3) You mentioned filtering down to only the problem rows from the beginning works, but anything else does not.

    You mentioned that the left table data is from a CSV file from a business partner and the right data is a SQL Table.

    Can you please try the following and let us know if that helped:

    • Import the data from the CSV file to another table in the same SQL database that hosts the right table and try the repro scenario again using that new table as the left table?
    • Check if the CSV file has any access permission issues?
    • If the CSV file is on a share, please try copying it to a local drive and try again.
    • Try the same scenario with Power BI Desktop and explicitly set the 'Privacy Level' for both the sources as 'Public'. You can find more information about it at https://docs.microsoft.com/en-us/power-bi/desktop-privacy-levels . 
    • Can you create another sample where both the left and right tables contain just the key column and no other columns? Please note, I don't mean filtered columns in Power Query; But the source itself only contains the key columns and no other columns. what happens if you attempt to repro the same scenario with such sources? Do you still see the same repro behavior?

    Please update the post with more information and the results for the above mentioned questions.

    Thanks.

    Wednesday, August 15, 2018 3:57 AM

All replies

  • Sounds like you're describing the problem mentioned in this link:

    https://social.technet.microsoft.com/Forums/en-US/7ce7cfb7-1379-486e-91d0-2dfabb53d7f7/merging-queries-some-rows-disappear-when-expanding-merged-query?forum=powerquery


    Is it possible that there are one or more spaces (or non-breaking spaces) after the 34885 value in one of the tables?
    Monday, April 16, 2018 5:14 PM
  • I responded on that thread but it was suggested that I start a new thread, so I did.

    As far as I can tell, there are no spaces. Earlier in the operation on both of those tables, I am running the below bit of code to help clean that key as much as possible. I'm also not sure why a lack of a match would cause rows to be removed from the left table.

        #"Cleaned UCM" = Table.TransformColumns(Source,{{"UCM ID", Text.Clean, type text}}),
        #"Trimmed UCM" = Table.TransformColumns(#"Cleaned UCM",{{"UCM ID", Text.Trim, type text}}),

    Monday, April 16, 2018 6:49 PM
  • Are both tables from the same data source? If so, what is the data source type (SQL Server etc.)?

    To help troubleshoot this, we're trying to understand whether the query is folded back to the source or run locally (in memory).

    Thanks,
    Sid

    Wednesday, April 18, 2018 1:59 AM
  • are there any errors in the expanded table, or just null values whenever there is no match? if you filter only the 5 disappearing rows before you join - how does the table behave?
    Wednesday, April 18, 2018 11:16 AM
  • The left table is a CSV file from our business partner that goes through several prior steps to remove any errors. The right table comes from our SQL server.

    It doesn't matter if I use the whole dataset or filter to just the problem rows, it behaves the same once it hits this step. However, if I only use the problem rows from the start of the whole operation, then it works. There are no errors in either table.

    Monday, April 23, 2018 9:44 PM
  • Could be that there is an error in the folding algorithm. Therefore, please try buffering the SQL-table before the join (ideally) or in the join like this:

        #"Merged to Right Table" = Table.NestedJoin(LeftTable,{"UCM ID"},Table.Buffer(RightTable),{"UCM ID"},"New Column",JoinKind.LeftOuter),


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, April 29, 2018 6:09 AM
    Moderator
  • Hi,

    Are you still seeing this issue?

    Let me summarize to have a better understanding of all the options covered so far:

    1) You also mentioned that you are only able to repro this with one specific set of data. Which leads me to believe that this is an issue specific to that one particular set of data that unfortunately you cannot share.

    2) You have also mentioned that this is not spaces related or case (upper / lower) related either, and you have already addressed these.

    3) You mentioned filtering down to only the problem rows from the beginning works, but anything else does not.

    You mentioned that the left table data is from a CSV file from a business partner and the right data is a SQL Table.

    Can you please try the following and let us know if that helped:

    • Import the data from the CSV file to another table in the same SQL database that hosts the right table and try the repro scenario again using that new table as the left table?
    • Check if the CSV file has any access permission issues?
    • If the CSV file is on a share, please try copying it to a local drive and try again.
    • Try the same scenario with Power BI Desktop and explicitly set the 'Privacy Level' for both the sources as 'Public'. You can find more information about it at https://docs.microsoft.com/en-us/power-bi/desktop-privacy-levels . 
    • Can you create another sample where both the left and right tables contain just the key column and no other columns? Please note, I don't mean filtered columns in Power Query; But the source itself only contains the key columns and no other columns. what happens if you attempt to repro the same scenario with such sources? Do you still see the same repro behavior?

    Please update the post with more information and the results for the above mentioned questions.

    Thanks.

    Wednesday, August 15, 2018 3:57 AM
  • Broken_Gears, please let us know if you're still able to repro this. It sounds like it could be a significant bug and we'd like to get to the bottom of it.

    Ehren

    Wednesday, August 22, 2018 8:28 PM
    Owner