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

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

Accepted Solutions
ankitpatira Super Contributor
Super Contributor

Re: Expanding columns after a Query Merge increases number of 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.

 

6 REPLIES 6
ankitpatira Super Contributor
Super Contributor

Re: Expanding columns after a Query Merge increases number of rows

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

 

Abhaykumar Regular Visitor
Regular Visitor

Re: Expanding columns after a Query Merge increases number of rows

@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 Super Contributor
Super Contributor

Re: Expanding columns after a Query Merge increases number of 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.

 

Abhaykumar Regular Visitor
Regular Visitor

Re: Expanding columns after a Query Merge increases number of 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. 

paulob_involves Regular Visitor
Regular Visitor

Re: Expanding columns after a Query Merge increases number of rows

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

eyaskelani Frequent Visitor
Frequent Visitor

Re: Expanding columns after a Query Merge increases number of rows

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? 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 285 members 2,867 guests
Please welcome our newest community members: