cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Abhaykumar
Microsoft
Microsoft

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

8 REPLIES 8
ihorshpyl
New Member

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

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

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.

 

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.