Data Refresh taking hours

[Anonymous]

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 , After that i updated Souce file with more data (i.e) earlier it is one month data now i updated Source file with 6 months data ..now CSV file size (8260) KB , After i done refresh all the tables loaded expect that Table where i add coloums using Merge operation , it taken like 10 hours to load and nearly 20 GB Loaded  why ??

Could any one help me to understood why size increased to 20 GB , how query editor loads table if we use merge operation ??

How can i optimize size even when i am using Merge operations?

I am using power BI Free Trail , So power BI free version Data Limit is 1 GB, So Query Editor loading Shows 20 GB means it is means PBIX File size ? so It crossed the limit of Free version limit of 1 GB

Colud you please suggest me ways to improve my report , Thanks

[MAwwad]

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.