Expanding columns after a Query Merge increases number of rows

[Abhaykumar]

I am joining two tables using Mege query with a Left Outer join. The left table has 137 rows initially. After merge it is still showing 137 rows. However when i expand the columns, the number of rows increases to 161 rows. How to avoid adding these extra rows due to merge and expand?

[yji]

The way to solve it is make table A including all the columns of Table B, ( if there is a need to do a merge/join, then based on all the columns you need from Table B to join, holding Ctrl key to choose all the columns from table A and B). After that, there is no need to expand table B columns, table A has them all already (Table B can be fully removed from the merged Table A).

[Huogas]

It looks interesting but I don't fully understand your process. Is it possible to rephrase it with more details for the newbie I am ?

Gaston😁

[lishapruthi]

I am also having the same issue of getting more number of rows after expanding the table. I tried inner join as well but not getting the correct output. And if I eliminate the duplicate rows then rows is decresed by 3 only which is also not the correct thing. How can I fix this?

[ihorshpyl]

The solution is to set Maximum number of matches to 1 under the Fuzzy matching options.

[Jesterr]

This is the solution 👍

[aviator_batman]

This was by far the easiest

[paulob_involves]

You can:

1. Add a custom column = Table.First( [your_column_with_tables] )

2. Delete your column "your_column_with_tables"

3. Expand the new column.

More info on Table.First: https://msdn.microsoft.com/pt-br/library/mt260821

[Anonymous]

This worked great. Thanks so much

[eyaskelani]

Hello

Im getting a similar issue. i have 20 records and three columns (first name, last name, country). each column contains a nested table. if i expand one i get 20 rows. if i expand two columns i get 400 rows... and so on. 

This has frustrated me. how can i fix this? 

[Anonymous]

I am having this same issue. How can I fix?

[ankitpatira]

@Abhaykumar when you expanding you can select and filter out columns that will increase the number of rows. you can uncheck them.

[Abhaykumar]

@ankitpatira, i removed the columns while expanding but it didnt help. I have to atleast keep one column for the join. Keeping any column gives the same result of extra rows.

[ankitpatira · Accepted solution]

@Abhaykumar in the second table (one that you're merging with) you will have more than one rows for matching row in the first table that is why you're seeing increased number of rows. Instead of Left Outer use Inner Join if you don't want to have extra rows.

[Abhaykumar]

@ankitpatira, You are right. The problem was duplicated rows in second table. However, i think, even the Inner join would give the extra rows in this case. The solution for this seems to be eliminating the duplicated rows. 

[Shubhamavon]

Hi abhay , with using inner option im getting less rows than required, can u guide?