SQL vs. Power Query - What You Should Use for Python in Excel in 2024!
Vložit
- čas přidán 8. 07. 2024
- Using SQL or Power Query is the best way to get high-quality data for your Python in Excel analyses. However, choosing which is best isn't straightforward. In this video, I discuss choosing between SQL or Power Query based on your situation.
☕ If you found this content useful and would like to support the channel, you can buy me a coffee: www.buymeacoffee.com/DaveOnData
--------------------------------------------------------------------------------------------
VIDEO CHAPTERS
--------------------------------------------------------------------------------------------
00:00 Intro
01:03 Power Query Introduction
06:09 SQL Introduction
11:00 Power Query Pros & Cons
14:22 SQL Pros & Cons
18:15 Which You Should Use
--------------------------------------------------------------------------------------------
MY FAVORITE POWER QUERY BOOK
--------------------------------------------------------------------------------------------
www.amazon.com/Collect-Combin...
--------------------------------------------------------------------------------------------
MY SQL FOR EXCEL USERS TUTORIAL SERIES
--------------------------------------------------------------------------------------------
Part 1 - Overview: • Introduction to SQL Pr...
Part 2 - Basic Tables: • Introduction to SQL Pr...
Part 3 - Basic Filters: • Introduction to SQL Pr...
#pythoninexcel #pythonexcel #pythonforexcel - Věda a technologie
Stuck waiting for Microsoft to grant you access to Python in Excel? Don't wait! Check out my video on how you can start building skills NOW: czcams.com/video/0iV4FtpSriY/video.html
It is funny how many people even know about Power Query. Really powerful when they find out about it. Another advantage of Power Query is that it is what PBI uses to clean data.
And another great video. So glad I found your channel.
Agreed! I've always thought it's a crying shame that Microsoft hasn't done a better job marketing Power Query to Excel users.
Glad to hear that you are still enjoying my content.
Amazingly well explained. love your videos. You should have a lot more views!!!
Thank you! Oddly enough, I think this about my videos as well. 🤣
In my opinion, the problem with M and power query is that if you know python/sql and have access to these tools then power query becomes obsolete. You can do whatever you want to do in sql/python
This is 100% true for professionals that have the option of accessing databases and/or Python. However, I work with many clients where IT policy precludes this. In these situations, Power Query is what they have to work with.
I tried to explain the difference of SQL and Excel (Power Query) to my boss today. He can’t understand that why the two technologies have different methods of extracting data. He has good motivation but He wants SQL to behave like Excel. 😂
It's actually a great question, IMHO! For example, why couldn't the Power Query GUI just as easily generate SQL?
For whatever reason, M was Microsoft's choice and it is here to stay. 🤣
@@DaveOnData true. But, I’m not Microsoft owner 😅 so there are certain things I cant change even if I want to. 😆
David, thanks! Great comparison of the tools, but IMHO the topic wouldn't be fully covered without mentioning about Query folding - a mechanism of translating M code into SQL. It passes data processing from a client machine to a server side
Thanks for adding this to the conversation! I will admit, unfortunately, what I see is that Power Query is very rarely used. In those rare instances where it is used, it is mostly used for cleaning up vagaries in Excel data formatting. It's too bad, really. Power Query is so powerful.
Excellent Explanation!
Thank you! Glad you enjoyed the video.
In Power Query I really miss the SQL CASE statement but Power Query is so much better than SQL if you have to pivot the data especially dynamic pivot
I hear you! In my work building features for advanced analytics, the combination of GROUP BY and CASE WHEN in SQL are my bread and butter!
I have access to the ERP database, but it is not an SQL database. Rather, it is a ProvideX database (Sage 100). Thus far, I've gotten by with exporting tables via ODBC connection to power query or MS Access. I am interested in learning SQL, is it possible to use MySQL or similar with a setup like this? I have been learning some Python as well ( with your series if course,).
If you're interested in learning SQL, can I suggest my tutorial series? It uses Microsoft SQL Server which has a free Developer Edition. Here's my SQL series playlist: czcams.com/play/PLTJTBoU5HOCSrExoOVTjDG33lFpDvmz2w.html
Gratitude!
Thank you! I am so glad you enjoyed the content.
If I may ask, is SQL faster in refresh rate than PowerQuery?
Can you elaborate on what you mean by "refresh rate?"
Do you thing Python will be available inside Power Query for Excel anytime soon as seen in Power BI.
I'm not sure, but my Spidey Sense tell me not likely. Power BI had access to Python and R relatively early on. Based on my experience, and client discussions, I think this is how the Excel stack is going to look for advanced analytics:
1 - Data acquisition and wrangling is going to be SQL and/or Power Query. While data wrangling can certainly be done using Python (e.g., pandas), given the current architecture, it's best to send cleaned/wrangled/filtered data up to Azure for Python to process.
2 - Excel will be used to the max (e.g., PivotCharts) for exploratory data analysis (EDA). While EDA can certainly be done in Python, there are advantages for using out-of-the-box Excel functionality when you can. The exception will be things like data visualizations that are difficult, or not possible, to do with Excel features.
3 - Python in Excel will be used for modeling and complex processing (e.g., text analytics).
I dont understand the title: SQL works with databases, PowerQuery is unique to MS's attempt to corner the market on data flow.
And both can be used with Python in Excel.
I have been asked by Excel users unfamiliar with these technologies which one should they use with Python in Excel.