SQL vs. Power Query - What You Should Use for Python in Excel in 2024!

Подписаться 74 тыс.

Просмотров 4,7 тыс.

50% 1

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

Наука

Опубликовано:

19 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 22

@DaveOnData 3 месяца назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-0iV4FtpSriY.html

@michaelt312 3 месяца назад

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.

@DaveOnData 3 месяца назад

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.

@ninjakaplumbag 3 месяца назад

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

@DaveOnData 3 месяца назад

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.

@Ugk871 3 месяца назад

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. 😂

@DaveOnData 3 месяца назад

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. 🤣

@Ugk871 3 месяца назад

@@DaveOnData true. But, I’m not Microsoft owner 😅 so there are certain things I cant change even if I want to. 😆

@mannylabrinos1963 3 месяца назад

Excellent Explanation!

@DaveOnData 3 месяца назад

Thank you! Glad you enjoyed the video.

@aleksandrm.4922 2 месяца назад

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

@DaveOnData 2 месяца назад

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.

@jasongins 3 месяца назад

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,).

@DaveOnData 3 месяца назад

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: ru-vid.com/group/PLTJTBoU5HOCSrExoOVTjDG33lFpDvmz2w

@johno1104 3 месяца назад

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

@DaveOnData 3 месяца назад

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!

@ankursharma6157 3 месяца назад

Gratitude!

@DaveOnData 3 месяца назад

Thank you! I am so glad you enjoyed the content.

@JoseCastillo-yp1fn 2 месяца назад

Do you thing Python will be available inside Power Query for Excel anytime soon as seen in Power BI.

@DaveOnData 2 месяца назад

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).

@markk364 3 месяца назад

I dont understand the title: SQL works with databases, PowerQuery is unique to MS's attempt to corner the market on data flow.

@DaveOnData 3 месяца назад

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.