Python in Excel Makes Power Query a MUST-HAVE in 2024!
Vložit
- čas přidán 5. 07. 2024
- Power Query and Python in Excel are like chocolate and peanut butter - better together! Skills with Power Query are a must in 2024 if you want to supercharge your analytics.
The combination of Power Query and Python makes Excel a complete technology stack for doing data science.
For example, sourcing 300,000 rows from a database so that you can craft new and powerful insights using Python in Excel.
☕ 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:22 Sizing the Data
05:59 Loading All the Data
08:54 Visualize the Data with a Count Plot
10:20 Visualize the Data with Histograms
--------------------------------------------------------------------------------------------
FREE MACHINE LEARNING CRASH COURSES
--------------------------------------------------------------------------------------------
If you're ready to see what Python in Excel skills make possible, be sure to check out my FREE on-demand machine learning crash courses!
Here's what you get with any of these courses:
1️⃣ On-Demand: Watch the course when it fits your schedule
2️⃣ PDF of all slides
3️⃣ All Python code as a Jupyter Notebook
4️⃣ All data so you can experiment
5️⃣ Resources to continue your learning.
Machine learning with Python in Excel. You've got this if you want it:
💻 Decision Trees with Python:
bit.ly/DecisionTreesWithPython
💻 Tuning Decision Trees with Python:
bit.ly/TuningDecisionTreesWit...
💻 Logistic Regression with Python:
bit.ly/LogisticRegressionWith...
💻 Cluster Analysis with Python:
bit.ly/ClusterAnalysisWithPython
#pythoninexcel #pythonexcel #pythonforexcel - Věda a technologie
Stuck waiting for Microsoft to grant you access to Python in Excel? Don't wait! Here's how you can start building skills NOW: czcams.com/video/0iV4FtpSriY/video.html
I absolutely LOVE Power Query and that is 95%+ of my income.
It's a crying shame that Excel users like you are such a small minority. The silver lining is that it provides folks like you with a business opportunity.
@@DaveOnData, it is hilarious when I talk to someone at a gig that uses PBI and they ask what PQ is when I bring it up.
A lot of old school leadership in Healthcare still want Excel reports and not PBI. But I'll keep grabbing those paychecks.
I’ve been using PQ for 3y. Not for analytics but for building some tools for my people. It’s sad when you hear that new tender is open for a piece of software…and solution is quick and Excel is able do it.
@@slezakmichal, I love it even better when discussing what you can do during a meeting and someone from the PBI team says that their IT group doesn't allow Macros. And yes, no macro is a typical policy in hospitals. If allowed, it is in folders segregated from patient data.
How does PQ do with large data sets?
Hey David, love the step-by-step explanation, definitely something I will be investigating more.
One tip: I see you growing and shrinking the formula area in the formula bar. Therre is a shortcut for that: Ctrl+Shift+U.
Once you have set the height you need using the mouse, using the shortcut will shrink it to the original single line size; using it again will re-grow it to what you set it previously.
Glad you are liking the content and thanks for the tip!
Thank you for the demo : really cool !
You are most welcome! Glad you enjoyed the video.
Hi David. You've got a new subscriber and fan. Cheers .
Woohoo! I hope you find my other videos useful as well.
Dave, Excellent video and very helpful. I am using Powerquery quite a bit now and find it to be a useful tool, along with PowerBi. Python does some things that Powerbi or Excel can't do or can't do well. Keep em' coming.
Thank you for the feedback! I am glad you liked the video.
Thanks for sharing this. But I think I might stick with Power BI for now. But it’s nice to finally have an idea how to use python in excel.
As a Power BI user, you've had access to Python and R for quite a while.
Thanks for Sharing
My pleasure! I hope you found the content useful.
Thanks for your video. But you know, i am really wondering what is the advantage of creating charts using phyton over creating using pivot chart. I think using pivot chart along side with many slicers and defining measures is by far more flexible.
While Python in Excel allows you to easily create visualizations that are not possible using Excel charts, it's real power is providing access to analytics not available in Excel.
PRIEST!! 🤟🏻 I wish the opposite was possible, to transform with Power Query then load to PostgreSQL via excel.
Most of the DBAs I've worked with over the years wouldn't allow that! 🤣
However, I agree with you. That would be a great feature.
@@DaveOnData yeah we get a csv file monthly and manually load it into postgresql as backend for our Hasura api. pgadmin works fine just not automated. why Hasura lacks a basic csv import seems odd. MS Power automate seems plausible
Hi David, have you hit the limits on Python in Excel when it comes to loading data to the cloud? If so, how big the dataset was? Amazing video mate!
Great question! Most real-world client datasets I work with are "Excel size" and cause no issues with Python in Excel for uploading. I am, however, encountering problems with processing timeouts (e.g., permutation importance of ML models). I will be providing feedback to Microsoft on these processing timeout scenarios.
Excellence!
Thank you! I am glad you enjoyed the video.
Does power query have the same limitations of 1 million row of data? In case i want only a connection and don’t want to load the data into an excel sheet
Power Query connections are not limited to 1 million rows when not used to load a worksheet. However, they are limited by the computer's resources.
Would Python in Excel be useful for trading the furures market (Dow Nasdaq ect.)?
I am not familiar with this space, so I can only answer at a high level.
Python in Excel expands the types of data analyses you can conduct by a tremendous amount. If there are specific libraries in the Anaconda distribution useful for your problem domain (e.g., scikit-learn and statsmodels), then Python in Excel might be helpful for you.
How does the connection only query refresh? Doesn't a query need to be loaded somewhere to be refreshed if the data is updated?
Executing the Python cell again will pull data from the database.
David, thanks for this video! I was waiting to see the connection between SQL and Python. Next, I am waiting to see the benefits of using Python. So far, I don't see the advantage.
Python in Excel is designed for professionals who want to have more impact using analytics, especially more advanced analytics techniques. This rules out most Excel users since they don't really analyze data-they mostly report on the "what."
You may find this video of mine comparing logistic regression analysis using the Solver vs Python in Excel interesting: czcams.com/video/ekT4Dx0D0qY/video.html
Thanks@@DaveOnData! Python looks extremely powerful. I will continue to watch and learn.
Copy that. If I may be so bold, you may enjoy this video that combines clustering with a machine learning predictive model: czcams.com/video/O479uWMe_F0/video.html
How to install the Python libraries which are missing in the Python for Excel?
At this time, you can only use the vetted libraries provided by Anaconda.
@@DaveOnData Thank you for your response.
This topic isn't even remotely related to my profession. But I love it anyway!
Glad to hear the CZcams recommendation engine worked out in your case!
question: do we need to study power query m code or pandas or both of them ?
because I mean if we can use pandas in power query why do we just study pandas
@DIGITAL_COOKING-I'm not sure what you mean by "use pandas in power query." Python in Excel is currently designed to have limited functionality (e.g., for Security reasons). At this time, Power Query becomes the primary conduit for feeding data to Python in Excel (e.g., from a SQL Server database). I have some video comparing the various ETL options with Python in Excel:
SQL vs. Power Query: czcams.com/video/jYR9B6Gz5vc/video.html
Don't Use Python in Excel for Data Wrangling: czcams.com/video/rMjtcsmHf9g/video.html
@@DaveOnData ok! , my mistake was thinking that the option of Power BI is present in Excel because in Power BI we can add phyton script in Power query
maybe you can answer me the same question but for power BI can we rely only on pandas because we can write phyton script or should we study them both the M language and pandas when it comes to data cleaning
I don't know if my question is clear and sorry for my poor English
❤
Glad you enjoyed the video!
does python in excel only run with the windows insider program?
Per this Microsoft article, joining the Microsoft 365 Insider Program is required to request and receive access to Python in Excel:
support.microsoft.com/en-us/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d
@@DaveOnData ah i see. okay thank you for the answer.
is the python connection stable? reading through this, it all looks like an early test phase that should not be included in an active production.
The former Enterprise Architect in me would caution against the use of any Microsoft Public Preview software for production implementations. Oh, that's also coming from someone that worked at Microsoft for 8 years. 😁
@@DaveOnDataokay, that's enough answer for me. :D
Are there any plans for when Python will be fully integrated into Excel?
I'm not sure if Microsoft ever plans to do this. There are other Python technologies (e.g., xlwings) that integrate more fully with Excel.
Why doesn’t =py( work for me?
Python in Excel is currently in Public Preview. While it is free right now, you do have to request access from Microsoft to get it. Here's a link to Microsoft's website: support.microsoft.com/en-us/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d
BTW - You don't have to wait for Microsoft to grant you access to start building your Python skills! Check out my video to learn more: czcams.com/video/0iV4FtpSriY/video.html
The problem is EXEL and acsses are limited .
I agree that Microsoft could have done a better job granting access to the Python in Excel Public Preview. The good news is you don't have to wait for Microsoft to grant you access to build skills. Check out my video to learn how you can start NOW: czcams.com/video/0iV4FtpSriY/video.html
I don't know. PQ is so slow, that even in dataflows and PowerBi it kinda suck. In at time that I have to spend waiting for query to spit out results, I can learn python.😆
I'm assuming here you mean running Python locally. If so, it would be faster typically than PQ.
However, for many of my clients, running Python locally isn't possible - which is why they are excited by Python in Excel.
Also, as I demonstrate in the video, using PQ as a SQL pass-through is a great option.
@@DaveOnData I agree with SQL part. By Python I meant Pyspark on databricks. I know it's more complicated than PQ, but as I said time wasted on PQ is so immense that you better use it to learn more difficult things that works faster.
I remember writing VBA that worked few seconds and was doing same thing as PQ that worked 8 minutes.
I see! I would imagine Pyspark on Databricks is quite powerful (I never used it myself). The vast majority of my clients are on traditional RDBMS, with SQL Server being by far the most common.
You imported 300k rows and you are happy?
I merged tables with 30M rows, it was nothing to talk about with my mediocre Python skill at that time. (And yes I didnt use Excel at all 😂)
You would be surprised at how many of my clients are dealing with much less than 1 million rows.