Stuck waiting for Microsoft to grant you access to Python in Excel? Don't wait! Here's how you can start building skills NOW: kzread.info/dash/bejne/Yp2KlqitoLbYmbw.html

Thank you for the demo : really cool !

@DaveOnData

4 ай бұрын

You are most welcome! Glad you enjoyed the video.

I absolutely LOVE Power Query and that is 95%+ of my income.

@DaveOnData

4 ай бұрын

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.

@michaelt312

4 ай бұрын

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

@slezakmichal

4 ай бұрын

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.

@michaelt312

4 ай бұрын

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

@josephdaquila2479

3 ай бұрын

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.

@DaveOnData

3 ай бұрын

Glad you are liking the content and thanks for the tip!

Hi David. You've got a new subscriber and fan. Cheers .

@DaveOnData

3 ай бұрын

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.

@DaveOnData

3 ай бұрын

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.

@DaveOnData

3 ай бұрын

As a Power BI user, you've had access to Python and R for quite a while.

Thanks for Sharing

@DaveOnData

3 ай бұрын

My pleasure! I hope you found the content useful.

PRIEST!! 🤟🏻 I wish the opposite was possible, to transform with Power Query then load to PostgreSQL via excel.

@DaveOnData

3 ай бұрын

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.

@RedCloudServices

3 ай бұрын

@@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

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.

@DaveOnData

4 ай бұрын

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.

Excellence!

@DaveOnData

3 ай бұрын

Thank you! I am glad you enjoyed the video.

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!

@DaveOnData

3 ай бұрын

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.

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.

@DaveOnData

3 ай бұрын

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: kzread.info/dash/bejne/l5-IlqaxYKeWobw.html

@rpopecpa

3 ай бұрын

Thanks@@DaveOnData! Python looks extremely powerful. I will continue to watch and learn.

@DaveOnData

3 ай бұрын

Copy that. If I may be so bold, you may enjoy this video that combines clustering with a machine learning predictive model: kzread.info/dash/bejne/gWhrm9eQfcjFdpM.html

This topic isn't even remotely related to my profession. But I love it anyway!

@DaveOnData

3 ай бұрын

Glad to hear the KZread recommendation engine worked out in your case!

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

@DaveOnData

4 ай бұрын

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.

How does the connection only query refresh? Doesn't a query need to be loaded somewhere to be refreshed if the data is updated?

@DaveOnData

3 ай бұрын

Executing the Python cell again will pull data from the database.

How to install the Python libraries which are missing in the Python for Excel?

@DaveOnData

3 ай бұрын

At this time, you can only use the vetted libraries provided by Anaconda.

@memolingvo5563

3 ай бұрын

@@DaveOnData Thank you for your response.

Would Python in Excel be useful for trading the furures market (Dow Nasdaq ect.)?

@DaveOnData

3 ай бұрын

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.

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

@DaveOnData

Ай бұрын

@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: kzread.info/dash/bejne/nI2Gm6Rvd92bpsY.html Don't Use Python in Excel for Data Wrangling: kzread.info/dash/bejne/pIGe1sWsnavMaco.html

@DIGITAL_COOKING

Ай бұрын

@@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

does python in excel only run with the windows insider program?

@DaveOnData

3 ай бұрын

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

@MarcelLindner

3 ай бұрын

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

@DaveOnData

3 ай бұрын

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

@MarcelLindner

3 ай бұрын

@@DaveOnDataokay, that's enough answer for me. :D Are there any plans for when Python will be fully integrated into Excel?

@DaveOnData

3 ай бұрын

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.

❤

@DaveOnData

3 ай бұрын

Glad you enjoyed the video!

Why doesn’t =py( work for me?

@DaveOnData

3 ай бұрын

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: kzread.info/dash/bejne/Yp2KlqitoLbYmbw.html

The problem is EXEL and acsses are limited .

@DaveOnData

3 ай бұрын

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: kzread.info/dash/bejne/Yp2KlqitoLbYmbw.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.😆

@DaveOnData

3 ай бұрын

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.

@DaveOnData

3 ай бұрын

Also, as I demonstrate in the video, using PQ as a SQL pass-through is a great option.

@TomaszBI

3 ай бұрын

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

@DaveOnData

3 ай бұрын

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

@DaveOnData

3 ай бұрын

You would be surprised at how many of my clients are dealing with much less than 1 million rows.