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: ruclips.net/video/0iV4FtpSriY/видео.html
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.
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. 🤣
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.
So it sounds like the two technologies are appropriate for different audiences. SQL, and python for it IT professionals, and power query for anybody else.
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: ruclips.net/p/PLTJTBoU5HOCSrExoOVTjDG33lFpDvmz2w
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.
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).
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.
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: ruclips.net/video/0iV4FtpSriY/видео.html
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.
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. 😆
I subbed. But for now and will be chasing after anything sql related. Thanks a bunch bro!
You are welcome! I hope you find my content useful.
@@DaveOnData of course man! Every single one of them. Ty very much!
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.
So it sounds like the two technologies are appropriate for different audiences. SQL, and python for it IT professionals, and power query for anybody else.
Excellent Explanation!
Thank you! Glad you enjoyed the video.
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: ruclips.net/p/PLTJTBoU5HOCSrExoOVTjDG33lFpDvmz2w
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!
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.
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).
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. 🤣
If I may ask, is SQL faster in refresh rate than PowerQuery?
Can you elaborate on what you mean by "refresh rate?"
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.
Gratitude!
Thank you! I am so glad you enjoyed the content.