Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello to everyone,
I am currently working with a big database that has information of 2018, 2019 and 2020. The information of this year is updated monthly. I have three different tables corresponding to the three different years imported from SQL into my Power Bi desktop.
I have had some trouble working with the tables separated and I decided to append the three tables. The problem is that I append the three tables and I still have to keep the original separated tables, thus I have the information loaded twice in my report (I have the 3 tables from 2018 to 2020 and I have the appended table). This is causing my report to be twice heavier, so I can´t share it to Power Bi services and then sharing to my colleagues and it is also becoming very slow and sometimes it freezes.
1) I would like to know which solutions I can use to solve this document weight problem, remember that information from 2020 is updated monthly.
2) Also, I would like to know if there is a way to upload only the new month information because so far, when I update the information it updates the entire appended table and it takes a lot of time to update whereas updating only the last month would we faster.
3) I would like to know ways I can share my report when it is too heavy to be uploaded to Power Bi Services.
Thank you very much for any help and advice.
Best,
JALV
Solved! Go to Solution.
To reduce your file size, you can do the following:
1. In query editor, right click on the queries that are appended and uncheck Enable Load, so you don't load your data twice. They will still be refreshed but not loaded. Also get rid of any columns and rows you don't need, especially columns with high granularity.
2. If available, you can set up incremental refresh to reduce refresh time significantly.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You should have a look at trying implement incremental refresh https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh
This should allow you to only load the data once and only refresh the current month.
Thank you @d_gosbell ,
I just saw that incremental refresh is only for PRO and PREMIUM users. Thank you for the advice, hopefully one day I can have the subscription and use this feature.
Any other advice on how to reduce the size of the document will be helful.
Best,
JALV
Did you uncheck enable load on the three tables you append? It will reduce your file size by a lot.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat
I unchecked the 2018 and 2019 tables. The 2020 table I didn´t uncheck because that table gets updated monthly, should I uncheck 2020 too?
Also, my file is still very heavy.
Regards,
JALV
Yes. You should also uncheck 2020, assuming you appended all three tables into a new query. If you appended into the 2020 query, then you need to keep load enabled. Are there high granularity tables you can remove/not load? How big is your file size after that?
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you @mahoneypat after unchecking unable load in all the tables, my file size was considerably reduced. Thank you for the advice!!
To reduce your file size, you can do the following:
1. In query editor, right click on the queries that are appended and uncheck Enable Load, so you don't load your data twice. They will still be refreshed but not loaded. Also get rid of any columns and rows you don't need, especially columns with high granularity.
2. If available, you can set up incremental refresh to reduce refresh time significantly.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hello @mahoneypat ,
Thank you for replying, regarding 1), it is a good advice as it can help me to reduce the update/refresh time but I am interested also in reducing the size of the document. Do you have any advice on how I can work with three different tables (2018 to 2020) in a way I can reduce document size?
As I mentioned in my post, I appended the three tables but I have to keep the original separated tables because I guess that if I delete the original tables, it will affect the appended table.
2) How can I use incremental refresh? will that help me to update only the last month of 2020?
That's the nice thing about unchecking Enable Load, it still refreshes the data when you hit refresh but it isn't loaded in your model. You don't need to load them. The appended table will be the same.
That should get you a big reduction (half + I'm guessing). After that, get rid of as many columns as you can (and rows of course, if possible). See this video on incremental refresh with pro licenses - https://www.youtube.com/watch?v=-pjmKI66iRQ
If you have premium, it is even easier.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.