none
Merging Queries - some rows disappear when expanding merged query RRS feed

  • Question

  • Hi there,

    Have got a bit of an issue when I merge 2 of my queries and then expand the columns out. This is described below:

    1) I merge 2 queries and Power Query verifies that every row matches the other (this is meant to be a 1-1 match)

    2) Once they're merged, the 'new' query lives under the NewColumn column, waiting to be expanded. When I click the Table entry for each of the columns, I can verify manually that each entry is indeed the same as the one on the other query

    3) Now I click "Expand Column", selecting the appropriate columns. This is where my issue occurs - I start losing a few rows although I've verified that those rows are non-empty when I click on the Table entry.

    Any thoughts or suggestions on what I may be missing on would be most welcome - am quite new to Power Query and baffled by this, thanks!

    Friday, December 18, 2015 7:20 PM

Answers

All replies

  • I would try to change the merge/join types and see where you find the "lost" rows - this might tell you sth about the problems with your keys (this article describes how to do it: https://www.linkedin.com/pulse/how-change-join-types-power-bi-query-reza-rad)


    Imke Feldmann TheBIccountant.com

    Sunday, December 20, 2015 8:37 AM
    Moderator
  • Thanks for the suggestion! When I click full inner join (i.e. show rows from both tables which may not match), the rows that have disappeared all reappear, but with the corresponding columns in each table being null (i.e. Row 1 in Table A has null entries in the corresponding Table B, and vice versa)

    I suppose that does tell me something about these rows not matching - but I've checked them manually (using INDEX / MATCH) to see if they do match, and also the Table entry does show the desired row, it's only at the Expand Column part when this happens.

    Thanks so much for the help - any other thoughts / suggestions? :)

    Monday, December 21, 2015 2:48 AM
  • Power Query is case sensitive while Excel isn't. You can transform your key column using uppercase or lowercase in order to match in PQ.


    Imke Feldmann TheBIccountant.com

    Monday, December 21, 2015 6:24 AM
    Moderator
  • Have just tried that - no luck unfortunately. I've also gone forward to test it further, used the EXACT function in Excel to see if they were the same (before transformation) - and it looks like they are.

    Also thought that it might have been differing types in Power Query that may have caused the issue - so went ahead and changed all the columns to be matched to type text, and then lowercased all of them, still have the same result (missing rows). Not really sure what to do next now.

    Also, not sure if this is helpful additional information, but I'm matching multiple columns from one table to multiple columns in another (have made sure that the combination of columns does lead to a 1-1 relationship, which is what the merge shows before I expand it). In the past, I've tried concatenating all these columns to create a unique key - that approach didn't work either.

    Thanks so much for the help - really appreciate it!

    Monday, December 21, 2015 10:14 AM
  • This really sounds strange.

    Can only think of further trial & error: Some more text-transformations like clean and trim (although they should have lead to differences in Excel already, but maybe it's actually bugs during transformation to PQ).

    Have you checked the concatenated keys that you've created in PQ in Excel?


    Imke Feldmann TheBIccountant.com

    Monday, December 21, 2015 10:30 AM
    Moderator
  • Just did the following things that were suggested (clean / trim, concatenate + clean / trim and checking in Excel) - no luck, the same rows still disappear even though it works well in Excel.

    I'm starting to wonder if this is a bug - may just report it to the PQ team directly. Thanks so much for the help - if you've any additional ideas I'm all ears!

    Monday, December 21, 2015 4:14 PM
  • Yes, I'd send frown as well - no more ideas from me here :-)

    Imke Feldmann TheBIccountant.com

    Monday, December 21, 2015 4:50 PM
    Moderator
  • Hello,

    I have exactly the same problem. When I merge two columns in a multi columns table, I lose some rows.

    I checked all type of joints. If some rows were missing the anti joints should show them to me, but the tables are empty.

    Anyone found a solution yet ?.

    I noticed something else. If you count the rows (transform/Count Rows in Power Query) AFTER the merge, but BEFORE expanding the merged column, the total matches. Once you have expanded the column, that is where the rows disappear. This is very strange


    Thursday, August 25, 2016 8:28 AM
  • I had a similar issue with a inner merge join returning no rows. What I found to be the issue was the numeric columns that I was identifying as the key columns were not configured as whole numbers. If you explicitly set the data type to whole number for the matched columns in both queries, you may see a better result. This helped me go from an empty resultset to seeing the data I needed. I hope this is helpful.
    Friday, June 23, 2017 2:16 PM
  • Try to add and Index Column in each query
    Thursday, January 25, 2018 4:53 PM
  • Has anyone found a solution for this?

    I am having this same issue while doing a join on a key column specified by a business partner. That column isn't the greatest because sometimes it's numeric, sometimes it's alphanumeric and sometimes it has a dash, but it's all that we have. I make sure to clean it, trim it, and set it as text before using it to do joins and it works 99% of the time. But the other 1% of the time, whether I do a left join or a full outer join, the row from the left table gets removed as soon as I expand the right table. That fundamentally violates the concept of those two join type. Any ideas?

    Friday, April 6, 2018 4:52 PM
  • Has anyone found a solution for this?

    I am having this same issue while doing a join on a key column specified by a business partner. That column isn't the greatest because sometimes it's numeric, sometimes it's alphanumeric and sometimes it has a dash, but it's all that we have. I make sure to clean it, trim it, and set it as text before using it to do joins and it works 99% of the time. But the other 1% of the time, whether I do a left join or a full outer join, the row from the left table gets removed as soon as I expand the right table. That fundamentally violates the concept of those two join type. Any ideas?

    Hey!

    I'd highly suggest that you create a new thread and upload some sample dataset alongside with your expected output so you can get help from the community. Since this thread is marked as solved, it doesn't get the same visibility as new threads.

    Every join depends on the values of the columns that you want to merge, so the sample dataset is extremely important.

    Friday, April 6, 2018 7:52 PM