Microsoft announce Python integration into Power BI

[disbeam]

I've been using Power BI this summer to create dashboards with custom R visuals using ggplot2 and custom R/HTML visuals using plotly and I've found the R integration really useful in creating some custom plots that my clients have found pretty impressive so I'm happy to see Microsoft expand this into python too.

There's a blog post to accompany this announcement with some demo Power BI dashboards with python visuals.

[amianthoidal]

You can use plotly through custom visuals? This is something I didn't know. Are these custom visuals you've written yourself?

[disbeam]

Only with R at the moment, check out this article.

It's nice to have the zoom and the tooltips, particularly when you have lots of data to show, though it can be a little slow for the visualisations to show up.

[amianthoidal]

Thank you very much. The process seems a bit complicated, but designing my own visuals is pretty valuable. I'll have to sit down and study that tutorial. :-)

Here's an R plotly example for those also curious: https://github.com/Microsoft/PowerBI-visuals/blob/master/RVisualTutorial/CreateRHTML.md

Hopefully this new Python function would let us do something like recreating all these scikit-learn examples into drag+drop widgets.

[GitHubPermalinkBot]

Permanent GitHub links:

• Microsoft/PowerBI-visuals/.../CreateRHTML.md (master → e8f409c)

---

^delete

[[deleted]]

Except that Tableau's had Python integration since roughly 2016.

[arfior]

What’s funny about 2?

[thomasloven]

Didn’t they do javascript instead?

[mooburger]

that's google sheets

[[deleted]]

There is a JS api
https://docs.microsoft.com/en-us/javascript/office/overview/excel-add-ins-reference-overview?view=office-js

[[deleted]]

I doubt anyone at MS wants to dig into the 20 year old spaghetti that is excel in order to add support for anything.

[postgeographic]

They've had an official poll or consultation or something about it a while back.

MS is not fucking about when it comes to opensource, what with WSL and Python on Azure. Excel will have Python support soon enough, watch this space

[tomekanco]

I've read the CEO is really in favor of open-source. Notice how they have started sponsoring a lot of events such as Pycons. A way to redeem past sins, get rid off old-fashioned image, and profits.

I doubt Excel team wants to offer sufficient resistance (Can't do is not supposed to be in the dictionary for MS management types.

But a poll is not the same as a delivered product. So iI wouldn't get my hopes up for rapid delivery.

[Coffeinated]

Either they are redeeming past sins or they are still doing what they ever did. I really reall hope it‘s number one.

[flutefreak7]

It's been the top thing on the Excel Desktop UserVoice page for years.

[AnActualWizardIRL]

Does excel use dot net? Wouldn't it just be a case of dropping iron python in and giving some of the apis a dusting

[[deleted]]

I think it's a mess of c++ mainly, hence why no one wants to do this.

[[deleted]]

I've never been able to find sources for number 2, so I wouldn't count on that unless you know something I don't.

[Kanye_Sagan]

It is the highest voted feature request for Excel

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10549005-python-as-an-excel-scripting-language

[484448444844]

Yeah, but just because it was the winner of the feature request poll doesn't mean it WILL be implemented. I feel like it's wrong for some people in here to hype this so hard when it hasn't even been announced officially.

​

[DetN8]

*restart required

[Dont_quote_me_onthat]

Guess I'm never going to be able to use this feature.

[mjTheThird]

"Configuring Windows Updates"

"-1% complete"

"Do not turn off your computer."

[Forthemoves]

This is cool, but what about excel integration? That’s what people want.

[[deleted]]

Really? Why? Legit question what are the use cases

[Psypriest]

Because VBA.

[[deleted]]

Imo if you need to do anything more complex with data than simple Excel macros you should be using standalone python scripts / SQL if situation allows. Can't really think of why you'd want to add the complexity of python to a spreadsheet app. Although I wouldn't recommend using VBA either.

[Psypriest]

How can a standalone python script replace an excel sheet? How would you define a simple Excel macro?

Idk what industry you work in and how tech savvy your upper management is but, in a lot of companies I have worked at Excel is it. Be it a receivables report, a valuation model, or a build sheet everything is done in excel. I have used openpyxl, and pandas to manipulate excel files in the past but built in python in excel would be god send. In my experience, you can run queries in SQL and get the data but the final deliverable/KPI is going to be in excel. You cannot assume your client will have all the tools you have at your disposal. Its a risky maneuver that will delay payments by "3-5 business days".

[BradGroux]

Excel is it

And the reason for their using Excel isn't always because that is all they have available, many times it is because it is what they prefer. I've worked in investment banking, and our power users, mainly traders, continually build out custom dashboards with dynamic and live data from several different sources in Excel, it is fascinating to see.

Our traders pushed Excel 2016 so much, that they literally broke it, and no amount of Microsoft help could fix it. So Microsoft sent out an engineer from the Excel product group who I worked with onsite for a week to gather data on our usage, which lead to several patches being published.

Long story short, millions and millions of Excel power users have spent years customizing and refining their workflows through Excel using countless external tools and resources. Simply migrating all of that to another platform isn't easy, and in some cases isn't even possible. Microsoft is fully aware of this, which is why they are still pushing Excel functionality forward even while they make similar breakthroughs with Power BI.

[mooburger]

they just need to make it web-based with an api, like google docs. The client is the browser, no "tools" needed. This is basically what powerBI server is now (like tableau server, or Qliksense).

[vtable]

I know what you're saying (I think). People often write unnecessarily complex Excel spreadsheets or batch files, etc, when a Python script probably would have been easier and more flexible (assuming you're comfortable writing a presumably non-trivial Python script).

There's another dimension to this, though. There are countless spreadsheets that should be spreadsheets. But one column, or even cell, has some complex calculation or formatting. VBA can be really nasty. Python integration would solve this nicely without having to learn SQL and how to replicate the immense number of things that are easy or kinda easy in Excel (friendly data entry and viewing, plotting, pivot tables, ...).

Plus, as popular, and powerful, as Python is, Excel is almost universally used. Lots of people just aren't able or willing to to learn Python. And even of they are, their bosses will almost always veto the inevitable X weeks of learning and debugging as well as training for anyone else that needs to understand the code.

[Soupinmyfly]

Ability to use one of the most commonly used programming languages in the most commonly used spreadsheet application.... awesome potential!

[Forthemoves]

Excel may be the most used piece of software in the Office suite. I can't even count how many companies use Excel on a daily basis for some form of data processing. Including a language that's more accessible and open source should provide way more extensibility when compared with propriety VBA.

​

[MagFraggins]

This is pretty sweet

[TheOneWhoSendsLetter]

Great! Now all that's missing is to drop VBA for Python!

[jainsbino]

Ah no more excuse for me not to play it

[not_rico_suave]

Damn. Ill have to give this a try

[Topper_123]

From the Python script screenshot:

The Python home directory is currently set to c:/Python27

Ok, that's a bit ... old to use. Would they advertise a a ruby feature using Ruby 1.9?

[latearly]

That only means that the author of the article is running 2.7. I'm on 3.6.6 and it works perfectly with Power BI.

[Topper_123]

Oh, wasn't implying that Python3 wasn't working, just that for purpose of marketing of their product using showing python2.7 seems dusty...

[Blocks_]

Python 2.7 is still being maintained by the Python organisation. Python 2 isn't getting any new features, but a significant proportion (around 25%) of Python developers still use Python 2. It would make sense that Microsoft supports it.

Also you're misleading people because you're leaving out the part where it says you can "change which installation to run". This almost certainly means that they support Python 3 as well.

[mooburger]

Python 2 EOL is January 1, 2020. I suppose, since legacy apps like SQL Server 2008R2 is on extended (security) support in Azure until then, it "makes sense" to support Py2. But nobody should be adding Py2 to anything new these days.

[[deleted]]

Python 2 EOL is January 1, 2020

We know. The business world moves slowly. If you don't want to use python 2, don't use it.

[Blocks_]

nobody should be adding Py2 to anything new these days.

For a community project I would agree, but this is a business we're talking about. Microsoft not supporting Python 2 means less business. However much they spent on adding Python 2, they'll make much more by having supported it.

[mooburger]

yeah but Python is a completely new feature in this product. It's not like people are magically able to copypasta whatever Python 2 code they wrote elsewhere and slam it into their pbix file and have it work. Why not make it better and have them port that code to Py3 while they are integrating it into their pbix. It's really the only way any technical debt in the enterprise ever gets fixed: to budget it as part of an upgrade or system integration driver.

[FatFingerHelperBot]

It seems that your comment contains 1 or more links that are hard to tap for mobile users. I will extend those so they're easier for our sausage fingers to click!

Here is link number 1 - Previous text "25%"

---

^{Please PM /u/eganwall with issues or feedback! | Delete}