cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KadenB Frequent Visitor
Frequent Visitor

Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus dataset

Hello all, 

 

I am getting an interesting issue when merging two tables from the same data source on an ID column that exists in both tables. The left table has 3.2 million rows  When I merge using a left outer join as shown in the picture, upon applying these query changes Powerbi then only loads 1000 rows of the left table. However, in the photo you can see that it estimates there to be 3.2 million matches as there should be. 

 

I have been experiencing this issue when merging on several tables where only a subset, typically only 1000 rows are loaded and I am struggling to understand why since a left outer join should take all from the left table and get matches on the right. 

 Merge.PNG

 

 

Any guidance or information as to other people experiencing this issue would be much appreciated. 

Thank you

 

Sincerely, 

 

Kaden Behan 

1 ACCEPTED SOLUTION

Accepted Solutions
KadenB Frequent Visitor
Frequent Visitor

Re: Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus datas

I found out that there has actually been an issue with merging as noted here 

 

"Customers utilizing specific connectors without using native SQL queries, pulling SQL data that does not include a SQL primary key column in the final result, and doing Append or Merge operations may experience missing or incomplete data when refreshing reports in the Power BI service. The following is a list of connectors impacted: SQL Server, Oracle, PostgreSQL, MySQL, Teradata, Sybase, Informix, DB2 or Access. A fix has been created and is in progress of being deployed. Next update @08/03/2018 13:00 PDT."

 

 

Link

 

7 REPLIES 7
erikajain02 Regular Visitor
Regular Visitor

Re: Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus datas

May be the data type is not same in both or there are some spaces between Ids in Both tables. Try using trim Function .

 

Of You can upload some sample data which ur file is not considering, we can check out the issue.

KadenB Frequent Visitor
Frequent Visitor

Re: Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus datas

I found out that there has actually been an issue with merging as noted here 

 

"Customers utilizing specific connectors without using native SQL queries, pulling SQL data that does not include a SQL primary key column in the final result, and doing Append or Merge operations may experience missing or incomplete data when refreshing reports in the Power BI service. The following is a list of connectors impacted: SQL Server, Oracle, PostgreSQL, MySQL, Teradata, Sybase, Informix, DB2 or Access. A fix has been created and is in progress of being deployed. Next update @08/03/2018 13:00 PDT."

 

 

Link

 

Nickodemus Regular Visitor
Regular Visitor

Re: Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus datas

We are getting this exact same issue at the moment - MS aren't reporting any known issues now, but we're still seeing this.

 

Have made all SQL tables have primary key where possible, but many of the query tables are SQL views, where the end result is does not have a primary key. Is there a workaround or fix??

KadenB Frequent Visitor
Frequent Visitor

Re: Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus datas

A way that we worked around it was to move the merge query up in the query list. 

 

I recommend looking up Query Folding  Essentially at somepoint as you progress through queries in the editor in PBI it will swich from a native SQL query to using M. 

We found that this switch typically happens when replacing values in the query but there is no documentation unfortunately as to when or why it switches, however you can tell where it stops by seeing which query is the first that does not contain the option to "View Native Query" . If your merge query does not have the option to "View Native Query" then it is using M rather than SQL so suggest moving up the merge query step to right after your Navigation query step and then it will be done using SQL. 

mjbernier Frequent Visitor
Frequent Visitor

Re: Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus datas

This problem is also occurring in the Power BI Desktop application. I just encountered it using the July 2018 update.

Nickodemus Regular Visitor
Regular Visitor

Re: Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus datas

That's extremely helpful, thanks KadenB!

 

I have tried what you suggested, and tested with trying to view the native query for the merge, but can't seem to get it to 'fold'. 

 

I've moved the merge right up to immediately follow the 'Navigation' step... the 'Merged Queries' step is viewable as a native query, but the 'Expand' step then prevents folding to the rest of the query. There is nothing fancy about the Expand (it's simply merging on a data column, then expanding for just 2 columns from the merged table), but it seems to have a problem. 

 

Are you aware of anything that will prevent the Expand from folding?

 

Many thanks.

chaz2jerry Regular Visitor
Regular Visitor

Re: Left Outer Merging of Tables Causes Refresh of Dataset to only take 1000 rows of 3mil plus datas

I was able to fix by updating both Desktop and Gateway in my machine.

https://community.powerbi.com/t5/Issues/Only-first-1000-rows-loaded-to-model/idc-p/503940#M31558