Merge Queries crash out

[Anonymous]

Hi,

I have dataset with 15 million records and trying to merge with other dataset with approximately 100 records.

But, the merge process crashes out.

Just wanted to check if we have any limitations with merge.

Thanks,

-Shirish

[Anonymous]

Yes taking off the unrelated columns and rows helped.

Thanks,

-Shirish

[Anonymous]

Hi Yingjie,

Thanks for your response.

I am bit confussed, until now my visulization was using Direct Query mode and dataset was 15 millions records. And with each click it was taking approximately 50-60 seconds for page to refresh.

Reason for confussion is you were saying direct query has limitation 1 million, but I was able to work with 15 million but with delayed response time.

So now I changed to import mode and deleted all unrelated columns. But, now I see that .pbix file is too big approximately 8 MB.

Please guide me can I reduce the .pbix file size (with direct query mode file size was 150 KB)

Thanks,

-Shirish

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Here are some suggestions that you can refer:

1. Remove uncessary columns
2. Remove uncessary rows
3. Group by and summarize
4. Optimize column data types
5. Preference for custom columns
6. Disable Power Query query load
7. Disable auto date/time
8. Switch to mixed mode

Please refer this document about reducing report size under import mode in power bi desktop, it introduces it in details, hopes it could help.

Data reduction techniques for Import modeling 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Denis.

I have not applied any steps to my dataset, merge queries fails at initial source step.

And yes its a relational DB (MS SQL Server).

Thanks,

-Shirish

[v-yingjl]

Hi @Anonymous ,

If you are using the direct query mode to connect to sql server, here is the limitation:

Limit of 1 million rows returned on any query: 

There's a fixed limit of 1 million rows placed on the number of rows that can be returned in any single query to the underlying source. However, the limit can occur in cases where Power BI isn't fully optimizing the queries sent, and there's some intermediate result being requested that exceeds the limit. 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hi @Anonymous ,

what is your data source? Is it a relational database like SQL Server or CSV files?

Did you check if query folding happens until the end of the table? Then the merge could also happen on the database.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi