Learn how Table.Buffer Power Query "M" function improved the dataset refresh time - Power BI
Вставка
- Опубліковано 2 лис 2024
- #POWERBI #MICROSOFT #MICROSOFTPOWERBI #DAX #MICROSOFTPOWERBIDATAANALYSIS, #DATAANALYSIS, #BUSINESSINTELLIGENCE #BI #PERYTUS #DECIPHERYOURDATA #POWERQUERY #TABLE FUNCTION
Obtaining clean, well-structured data for your analytics and reporting needs is often a challenging task. Data doesn't always come in the perfect format for building a scalable and efficient data model. This is where Power Query comes into play, allowing us to transform and shape the data to align with our reporting requirements. However, these transformations can sometimes come at a high cost - they may lead to extended refresh times for your datasets.
In this enlightening video, we embark on a journey through the optimization of a dataset refresh process. Initially, the refresh was a laborious task, taking more than 2 hours due to specific data transformations. It was clear that something needed to change to make this process more efficient. Fortunately, the Table.Buffer function emerged as the hero of our story, reducing the refresh time to a mere fraction, clocking in at less than a minute.
Throughout this video, we will cover:
👉 The specific data transformations that were essential for our reporting needs
👉 A deep dive into the reasons behind these transformations - understanding the 'why'
👉 A step-by-step demonstration of how the TABLE.BUFFER function was leveraged to revolutionize the refresh process, making it blazing fast
Join us on this captivating journey, where we uncover the hidden gems of data transformation, optimization, and efficiency. Learn how to supercharge your data model refresh and save valuable time in this information-packed video.
Download the pbix file here tinyurl.com/bu...
Enjoy!!
🌏: perytus.com/
✍: perytus.com/blog/
📞: perytus.com/co...
Beautiful content it is. Thank you very much.
Glad you enjoyed it! Appreciate the feedback. 🙏
Great use of this function. I think it is made for such kind of scenarios , where one has to iterate the table to check conditions. Great stuff and use case based video.
Thanks for the valuable feedback. Glad to hear you find it useful. Best regards!
I was looking for this kind of solution thanks
Hi there, Glad you find it helpful. Best regards!
Very interesting!
I am trying to reduce Power Query time to load multiple times the same CSV. With no luck
Is it possible? Load once and then copy results-only to another tables?
Great question! You should be able to use Table.Buffer as described in this video. Load CSV file once and reuse it. Did you try the technique/solution shown in this video? Did it improve the performance?
I know it is late to reply, but you can read the CSV file in one query, then reference the previous query results in another query. This will prevent the multiple query steps to load the same CSV file. If you find that you require some common transformation steps in both queries, move those transformation steps in the first query.
We all love your videos on Power BI and Power Query. I an a regular follower of oyur videos and content.
The way you explain the issue and usage of each function is amazing.
One small request that Could you please share .PBIX File to practice or else
can you give access to these .PBIX Files if they are available on your website?
Please reply back with your response to this request
So nice of you, Thank you! I will share the link to the pbix file in the description of the video. Best regards!
@@PowerBIHowTo , Thank you very much for your quick and prompt response. Is there a service like paid subscription on your website. I would like to pay you for the solution you provide for some specific scenarios or issues? Let me know so that I can share my details to work it out. Thank you once again Sir.👍👍👍
Hi Venkatesh, I realized the link to download files already exists in the description of the video. Let me know if you are unable to get these files.
Regarding subscription, my core work is to provide Power BI consultancy. Feel free to reach out via email to discuss on how I can assist you with your projects/questions. Thanks!
This looks great and I believe will resolve many issues I have where I use Power Query to load data from SharePoint data sources that hold CSVs, workbooks, etc. I'm not very familiar with using functions though, how would one use this without using functions?
Hi Tom, Thanks for the comment and the feedback. The function was created to meet the data transformation requirement, depending on what you are trying to do, maybe there is no need for a function. It is a bit hard to tell without knowing the full details. Feel free to reach out if you have further questions or need any other clarification.
Best!
Great advice. Does this make having a computer with larger RAM a big advantage?
i.e. Microsoft state it may make you queries run slower due to storing the buffered query in memory but more RAM on PC would fix this (I am really struggling with slow PQ connecting to Dataverse since my company moved to D365 CRM)
Hi there, I don't have much experience using _Dataverse_ so cannot comment what needs to be done there to improve the performance. Maybe some point I will try to look into it and get back to you. Thanks!
Have you tested doing a crossjoin and then filtering based on the criteria?
I wonder how fast it would be
No I haven't as far as I remember. Why not you try it and share your findings? Thanks!
I am adding a buffer table into my function, similar to you. But the time is extremely slower than without the Table.Buffer. Any thoughts on why this is?
Hi Spencer, if you look at the MSFT document, it says "using this function might or might not make your queries run faster", check it here. docs.microsoft.com/en-us/powerquery-m/table-buffer
So it depends on your use case. Let me know if I can provide more details. Thanks!
Can u please share your file so I will be beneficial for all of us.
Hi Danial, Sorry for the late reply. I have copied the link to the pbix file in the description of the video. Please download and let me know if you run into any issue.
Best,
Parv
Could you PLEASE just explain your videos with simple examples??? Also, it would be helpful if you walked us thru by building the querry instead of showing the final results 😢
Hi there, I appreciate the feedback and sorry that video was not able to explain everything. Just to let you know I try my best to do detailed explanation in my video but I guess I still need to improve on this.
Could you please let me know what part of the video was not clear and if I can explain it better? Maybe if you give the time of the video, it will help me to look into it.
Once again, appreciate the feedback, apologies for the inconvenience. Will do better in the future videos.