Best file type for Power BI | 5X FASTER REFRESH!
Вставка
- Опубліковано 22 лип 2024
- Same data, different file type - how much of a difference does it make? In this video I show you how to measure the refresh time using query diagnostics. So at the end you can see what the
the best file type for Power BI is... with even 5x faster refresh! Curious what file type refreshes the quickest? Or what the actual file refresh time in Power BI is?
Run query diagnostics in Power BI with me!
Enjoy and.... subscribe, like and comment :)
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
1:03 Connecting to a csv file
1:58 Query Diagnostics Overview
7:46 Testing different file types
12:03 Double checking our results
15:05 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on UA-cam: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX - Наука та технологія
Bro, your videos are so well-made. Appreciate the way you explain things very eloquently and simply. Tēnā koe (thank you) from Aotearoa New Zealand!
nau mai haere mai 😀 thanks for watching!
Great video, I've tested this concept with parquet files and the results are much better than with csv files
FYI, the XLSX "file" is a container file. You can change the extension of your XLSX file to .ZIP and then open it to see the XML documents it contains (including the workbook sheets). That's also one reason why it's smaller: it's basically a compressed file.
god, you are a gold star for that show! amazing information and vital too!
Thank you.. 👍
Out of topic question, can aggregation be used in scenario like : hit cache for recent data and hit SQL dwh for historical data? I read about filtered Aggs article from Phil Seamark, but it doesn't actually do aggregation rather it's two separate fact tables which are summarised in measures.
Awesome video!! thanks for sharing!
Thanks for watching!😀
Thanks for the video. Is there such a thing for Excel power query as well? I reached the same conclusion through hit and try. I needed to refresh over 2GBs worth of data daily into the model and using CSV massively improved performance. Over 20-25 min down to a couple of minutes.
thanks, i have xlsx files and have up 6 sheets which help me organize the data well i have xlsx upto 4 files. having a csv file means i will have upto 12 files becouse csv files dont allow more than sheet. but my xlsx file take for ever what do you suggest i should use
It reasons well with the fact that Power BI Service Dataflows store the queries as .csv files on a CDM structure behind the scenes.
Thank you for sharing the experiment. Really helpful for our project. You have my like.
awesome, thx Nabil!
thanks for the deep dive.definetly file size doesnt matter nowadays. refresh time is more important.
yea, can sometimes be an easy first step to speed up the refresh time :)
hello
I've been trying to import and xlsb file to power bi it's not importing. it says the form is wrong. the offline file is good and when I convert the file to xlsx it work perfectly, can you help
Thank you - that was remarkably interesting. I'm guessing that the other file formats have data compression. Data Compression would require more time, as Power BI would need to decompression before it could load it within it's engine
yes that makes sense, great point - thanks! 😃
I just need to compare a sharepoint file, excelente video gracias saludos desde Chile.
muchas gracias!!! 😀
you are the best.. Big thanks..
thanks for the nice words!😀
Awesome✨
Another interesting question would be: which one is better? Using many average size CSV files of few very large CSV files?!?
This is so helpfull
🤓💻
Thanks Hero
Well done
thx!
I would be interesting to see if it makes difference if the files are stored in online storage, like SharePoint. I guess the times would be afected as CSVs are bigger and maybe the loading times would be more even?
Definitely a good question since sharepoint can get bogged down and run slow at times.
Thank you for this unique video, curious to know if you included MS Access in your exercises
I didn't try. Just wanted to compare flat files for this video. Maybe time for a part II for this video 😉
@@HowtoPowerBI can't wait to see it
Cheers!!
My Excel files are stored in SharePoint as XLSB file type. When I am refresh the data set in Power BI services it not getting refresh. How to solve this issue?
update the source connection step -> here is how to do it ua-cam.com/users/shortsGGHbbg6yi-A
@@HowtoPowerBI as you mentioned I have used the SharePoint path to get my list of excel files. In Power BI Desktop it's getting refresh properly but the issue comes only is when I click refresh button in Power BI services. Reason is when I pull Sharepoint Excel file as XLSB format . Not getting refresh. Please suggest alternative way to get refresh or is there anything I can able to do just editing the Query?
How did you connect with xslxb the binary excel i have a lot of problems please tell us ?
get data, excel connector, choose the xlsb file
@@HowtoPowerBI it doesn't open it i have a message error whenever i try can you make a short reel or video how to open it please ?
Hi Bas, Thanks for your video. I tested in my work environment and *.csv is at least 4 times faster than *.xls. Unfortunately, when converting the xls in csv, we lose the special characters (e.g. Russian or Chinese for instance, they are replaced by "???? ?? ?? ???????????? ??). Would you know a workaround to avoid this issue, by any chance ? Thanks for your great content, anyway !
In the import wizard choose the correct language character identifier under file origin .. for russian I would try "1251: cyrillic"
@@HowtoPowerBI Thanks a lot, but this will probably not do, we have roman, cyrilic, Chineese, and Japanese characters in the same file (multi-national environment), it is a nightmare :-)
How do you connect xlsb in power BI?
same as xlsx ... why would you still want to though after watching this video 😅
@@HowtoPowerBI power bi not showing xlsb file in file explorer, and I googled for a solution, there is no result come up with a solution. According to many power bi doesn't support xlsb files.
Did not give me new insights. Confirmed old insights.
Great