Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Abhaykumar
Microsoft Employee
Microsoft Employee

Expanding columns after a Query Merge increases number of rows

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?

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

 

View solution in original post

15 REPLIES 15
yji
Regular Visitor

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
Regular Visitor

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
Helper I
Helper I

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
Advocate I
Advocate I

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

This is the solution 👍

This was by far the easiest

paulob_involves
Advocate I
Advocate I

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
Not applicable

This worked great. Thanks so much

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
Not applicable

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

ankitpatira
Community Champion
Community Champion

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

 

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

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

 

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.