Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Data Refresh taking hours

Hi,   I am new to power BI, i created a report in power BI desktop from CSV file (2316) KB, and in transformations i used merge queries in the tables to get required Coloums and i done report , Aft...
Need Help
MAwwad's avatar
MAwwad
Solution Sage
3 years ago

When you use the Merge operation in Power Query, it can result in a Cartesian product, where the number of rows in the output table is the product of the number of rows in each input table. This can cause the size of the table to increase significantly. In addition, merging large tables can be a resource-intensive process that can take a long time to complete.

To optimize the size of your table and reduce the time it takes to refresh, you can consider the following:

  1. Use filters: If possible, filter your tables before merging them to reduce the number of rows in each table. This can help to reduce the size of the output table.

  2. Reduce the number of columns: If you don't need all the columns in your tables, remove the unnecessary columns before merging them. This can help to reduce the size of the output table.

  3. Use Append instead of Merge: If you are merging two tables with the same structure, consider using the Append operation instead. This will stack the rows of the two tables on top of each other, resulting in a smaller output table.

  4. Consider using DirectQuery: If your data source supports DirectQuery, you can consider using it to avoid loading large tables into memory. With DirectQuery, the data remains in the data source and is queried on demand.

  5. Upgrade to Power BI Premium: If your data exceeds the limits of the Power BI free version, you can consider upgrading to Power BI Premium, which offers higher data capacity and more processing power.

  6. Use incremental refresh: If your data is growing over time, you can consider using incremental refresh to only load new or updated data, rather than loading the entire dataset every time.

By following these tips, you should be able to optimize the size of your tables and reduce the time it takes to refresh your report.