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: kzbin.info/www/bejne/Zpq5ZXmqpbiln7s
@ankursharma61579 ай бұрын
Gratitude!
@DaveOnData9 ай бұрын
Thank you! I am so glad you enjoyed the content.
@ninjakaplumbag9 ай бұрын
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
@DaveOnData9 ай бұрын
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.
@WillyKillya5 ай бұрын
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.
@kevinsotorecabarren56749 күн бұрын
Great video and explanation! thanks 😁👍🏻
@michaelt3129 ай бұрын
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.
@DaveOnData9 ай бұрын
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.
@mannylabrinos19639 ай бұрын
Excellent Explanation!
@DaveOnData9 ай бұрын
Thank you! Glad you enjoyed the video.
@shout-style6 ай бұрын
Amazingly well explained. love your videos. You should have a lot more views!!!
@DaveOnData6 ай бұрын
Thank you! Oddly enough, I think this about my videos as well. 🤣
@crispindeleon19304 ай бұрын
I subbed. But for now and will be chasing after anything sql related. Thanks a bunch bro!
@DaveOnData4 ай бұрын
You are welcome! I hope you find my content useful.
@crispindeleon19303 ай бұрын
@@DaveOnData of course man! Every single one of them. Ty very much!
@aleksandrm.49229 ай бұрын
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
@DaveOnData9 ай бұрын
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.
@Ugk8719 ай бұрын
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. 😂
@DaveOnData9 ай бұрын
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. 🤣
@Ugk8719 ай бұрын
@@DaveOnData true. But, I’m not Microsoft owner 😅 so there are certain things I cant change even if I want to. 😆
@jasongins9 ай бұрын
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,).
@DaveOnData9 ай бұрын
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: kzbin.info/aero/PLTJTBoU5HOCSrExoOVTjDG33lFpDvmz2w
@jimmychangtiunnkunui78773 ай бұрын
Pure SQL skill might be limited to the various data sources. However, mostly SQL users also know how to do ETL.
@christophernazareno91146 ай бұрын
If I may ask, is SQL faster in refresh rate than PowerQuery?
@DaveOnData6 ай бұрын
Can you elaborate on what you mean by "refresh rate?"
@goodyearspokane9 ай бұрын
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
@DaveOnData9 ай бұрын
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!
@JoseCastillo-yp1fn9 ай бұрын
Do you thing Python will be available inside Power Query for Excel anytime soon as seen in Power BI.
@DaveOnData9 ай бұрын
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).
@markk3649 ай бұрын
I dont understand the title: SQL works with databases, PowerQuery is unique to MS's attempt to corner the market on data flow.
@DaveOnData9 ай бұрын
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.