Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Merge Queries crash out

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

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

6 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    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
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
    Anonymous
    Not applicable
    5 years ago

    Yes taking off the unrelated columns and rows helped.

    Thanks,

    -Shirish