I've been using Python for years and I'm totally new to Power BI, I don't think I can do what I wanted in Power BI, but I watched the whole of the video anyway because it's such a well done video! Also, as Python-fan, watching your enthusiasm made the video even better
Wow...!!! How simple to implement Python you just showed here! Congrats and thank you for sharing! Aside from that, I liked the way you edited your video; audio, zooms, highlighting, story flow... just amazing!
I love the R and Python integration in power query but, for me, the personal gateway is the deal breaker. We have an enterprise gateway but that doesn't work. I could install the personal gateway on the same machine but now I have two gateways to update and configure.. :( Is it worth stating that the gateway isn't required for the visualisations as along as you use the support packages.
Will, absolutely. I was let down alot early on in my PowerBI journey when I'd go to implement python outside of visualizations and just get crushed by the lack of support in enterprise gateway.
Quite funny! Just before the video started, the first thing i did was to scroll down hoping that we dont need a personal gateway and here's the comment. This is a deal breaker
The video in 6:11 set "matplotlib.pyplot as lib" and launch "pit.show". That's an error. The right code is: (after install pandas and anaconda) import seaborn as sns import matplotlib.pyplot as pit sns.swarmplot(x="day", y="total_bill", data=dataset) pit.show()
Hello patrick, I from Nicaragua and don't speak english but this video have subtitle in spanish, so great .. thank you for that could you continue placing the subtitles?
Awesome Patrick ;) I was thinking to test python in PBI since a while. This video made me started... I though it would be a bit complicated, but I am astonished by the simplicity to implement it. Time invested versus the quality and powerfulness of charts. It is definitely worth it. And using data=dataset ... BAM we get the interactivity between charts... For the moment I do not use any service so no issue with the Gateway... I feel I will find out some great usage of python in PBI ;) Patrick you are amazing ! Merci
Amazing presentation as usual. Thank you, Patrick! Python and R integration is good. However, there are too many limitations for using Python and R scripts in Power BI which Patrick did not mention (vide documentation). When we want to get data from a data source that is directly not supported in Power BI (quite rare nowadays!), an R/Python script may help. I remember Adam had a video on creating data connectors with C# as well (long time ago). We can do certain transformations that are beyond the capacity of M using R/Python scripting. What we need is a scripting language for Power BI that is comparable to VBA in Excel, a language that is truly incorporated, not superficially attached. R and Python script support in Power BI is still just sensational than truly useful. Let’s see how it develops in the due course. Definitely Interesting, but I doubt if it would be equally useful. Of course, it’s only my opinion as a user of Power BI. There are experts on the subject who think otherwise.
@@matthiask4602 Neither do I! LOL! C# and R are good enough for MSBI! What I meant was that the scripting language - be it Python, R or whatever -should be well incorporated into and supported by Power BI. Right now, there are ridiculous limitations.
@@leybourne I think python would be better, it would be difficult to use R as we use vba imo. Python fits nicely as a scripting language that will feel like vba for power bi.
I know an intermediate level of python, but have never looked into what's supported in Power BI. Seeing Patrick get something built so easily without evening knowing python was exciting as it shows the integration must be pretty well designed. I'm excited to go through and learn the limitations for myself.
@@ducminh7568 why isnt it good? I found that with complex nested json, powerbi creates all these nested record and list objects and I have to go in and flatten them myself, unless I'm missing something as I just started using it. But with the python script the output comes out already flattened out
@@thefamousdjx so it not that great because you cannot load Python code online via SharePoint but it has to be from your computer so that you cannot automate tasks with PowerBi services but desktop only.
Just a friendly heads up: If you add python to your environment variables at the end of the installation (or the beginning depending on which version of Python you install), you can invoke pip without pointing to the directory. 😃
@@jamesbyrnes8286 Yes, you can manually add the path to your environment variables either through cmd or through searching environment variables in the start menu.
I use a lot R and Python in Power BI but the problem is not being able to take an input from the user and apply it to a model and then give back an output as (chart, table, list etc.) if it would possible to send information to Python and R it would be a big big game change especially to use NLP capabilities from both of languages.
how to read an excel file stored in a sharepoint with python in power bi? Without explicitly giving password and user or from an organizational account?
I have a requirement where I have to get values from 17 different worksheets of a excel file, so I wrote a python script that goes to each sheet and gets this single value and creates a dataframe. Now I am using this script in Power BI to get the dataframe into my model. But when I am trying to schedule a refresh it says the datasource is not supported. Is there any workaround or solution to this? Please help me out.
Do you think this would be effective at defining custom logic for bringing in data? Like having to loop through the dataframe to determine its structure? and returning that dataset? stuff that you cant do just through applying transformations & such...
I have an error: python visuals are currently disable in your organization!!!! so, is python scripts only visible on the Microsoft cloud? and don't visible on the report server?
I am trying to uncompressed a column in a dataset using pandas and lzstring. I have used this syntax but the Python script errors with TypeError: 'function' object is not subscriptable # 'dataset' holds the input data for this script import pandas as pd import lzstring as lz df = pd.DataFrame(dataset) df['DecompreseedAssets'] = lz._decompress['MigratedAssets'] MigratedAssets column has data that is compressed using lzstring Could you help please?
Lol, I took a 3 month course on developing with Python and got stressed because my code, which would work in Jupyter Workbook didn't work in PowerBI. You did all these steps with 0 knowledge of python and were successful. Thanks for the explanation haha.
Python visuals are very slow once the report is published, to slow to be actually used in a report. To my understanding, this is because the script has to be sent back to an azure host that recalculates the visual each time you change it. I try avoiding python visuals in power bi for this reason
Nice video: by any chance you know how to upload my dashboard with python visuals into a website? When I upload the dashboard, everything can be seen except these python visuals.
Hey Patrick, not sure if this is mentioned but in the command line you dont have to put the full directory of python, just "python -m pip install.." is good enough
Will Python script work if the PowerBI is located in Sharepoint. I mean, do other users, that can see my interface not see the visuals because they don't have python downloaded?
It's weird because I followed your exact steps but I kept getting an error for my columns not being defined... I then got am error for the "pit.show()" not being defined. I tweaked it a bit and this code ended up working for me: # The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: #dataset = pandas.DataFrame(day, total_bill) #dataset = dataset.drop_duplicates() # Paste or type your script code here: import seaborn as sns import matplotlib.pyplot as lib sns.swarmplot(data=dataset, x="day", y="total_bill") lib.show()
Hello, thanks for the video. I have a question, I was wondering if this Python scripting would work when I upload my dashboard to the cloud and update it using a gateway to refresh the data sources periodically?
Interaction is a problem with python. Some other visualisation tool has much better integration. Python is great but for visual we can't do interaction from python visual to other part of report
Absolutely it's a great challenge. As a python lover I am dying to use python visuals in Power BI and just because of this interactive limitation I am not able to. SAD. Very 😥
It works from a default viz to the python viz but not the other way round. But that is not the only issue. Currently there are too many limitations. By the way: what other viz tool? Have not seen any good integration so far
@@DanielWeikert If you know Javascript, you can build your own custom Visuals. Although the process is tedious and lengthy if you want a really good working visual. Use Charticulator to build your own visuals with little to no coding.
Hi Patrick! I'm planning to learn power bi right now. can you give me the link from your videos where I can start with? You got tons of videos and I don't know what video to start with. Thanks!
Great video. What is a personal gateway??? Also, I am surprised it’s such a short list of supported python packages… I wish plotly was supported for example!
The integration between Power BI and R/Python is amazing for loading and processing data. I've created an automatic data workflow that creates tidy data files stored in the pickle format which I then import and use for visualizing the data. Some reports require further modifications to the already processed data and for those I've set things up so that Power BI calls the execution of a Python script stored in a custom folder that then does the additional data processing. Pretty neat! The exact same thing works with R, too.
@@rishabhagarwal4702 It's actually fairly straight-forward. Go to the Power Query editor and create a new query by using "Python script" as the source. Then, type in the following command: exec(open("C:/Users/User/Desktop/Script.py", encoding = "utf-8").read()) Then replace the folder and script names with your own and make sure to specify the encoding of your *.PY file. When you press enter, Power BI will start executing the script and then a pop-up window will appear where you will be able to choose one or several pandas data frames and load them into your model.
Hi Patrick, can we perform Text Analytics using Python visual in Power BI? If our data is based out of SSAS so we won't be able to use Text Analytics as we won't have Query Editor in Power BI then due to Analysis Server based data source.
Hi man, I got the examples but I'm a little confused because the Python language in Power Bi is just to plot some libraries or graphs? What are the real advantages related to Power BI? For example, I can download more visuals instead of implement using the R language? Tks
Just out of curiosity, how long did it take you to figure all this stuff out? 🙂 Great to see the "masters" struggle a bit, which is an inevitable part of the learning curve. Thanks for this Patrick!
Appreciate you posting this info! Very informative and something I was looking for. However, I've been trying to figure out the compelling use case to use python. So far, I can do everything that I've need with OOB connectors, dax and mquery. But, I've seen that doing some development using Python (specifically in Jupyter Notebook) is much quicker because you only deal with a small subset of data instead of loading the entire data set. In some cases, where data load time becomes an issue, i just set parameters to only load recent files or a # of records... Then, let data refresh do the heavy lifting after publishing.
It seems like several details were left out on the installation (adding to PATH, including PIP... matplotlib, pandas) I had to reference another video because my installation didn't work just by watching this. I'm just a beginner, so maybe it wasn't intended for people with no prior knowledge.
Great stuff man... Would love to see some videos over the GenerateEmbedTokenV2 changes and what new functionality comes with the newer embed token model. Really the main updates with the move to V2.
I've been using Python for years and I'm totally new to Power BI, I don't think I can do what I wanted in Power BI, but I watched the whole of the video anyway because it's such a well done video!
Also, as Python-fan, watching your enthusiasm made the video even better
How much package do you get??
Wow...!!! How simple to implement Python you just showed here! Congrats and thank you for sharing!
Aside from that, I liked the way you edited your video; audio, zooms, highlighting, story flow... just amazing!
I HAVE NEVER SEEN SOMEONE WITH SO MUCH ENERGY TEACHING !!!!
Thank you, Patrick! I'm toying with the idea of starting with Python, and I love Power BI, hence I need to mix them. You rock!
probably the best tutorial on youtube ive ever seen, awesome job
I love the R and Python integration in power query but, for me, the personal gateway is the deal breaker. We have an enterprise gateway but that doesn't work. I could install the personal gateway on the same machine but now I have two gateways to update and configure.. :(
Is it worth stating that the gateway isn't required for the visualisations as along as you use the support packages.
Will, absolutely. I was let down alot early on in my PowerBI journey when I'd go to implement python outside of visualizations and just get crushed by the lack of support in enterprise gateway.
Quite funny! Just before the video started, the first thing i did was to scroll down hoping that we dont need a personal gateway and here's the comment. This is a deal breaker
Same here. Total deal breaker.
Looks cool but no even going to try because it's not efficient the way to get the data, we use live connection (SSAS).
wait can we just upload the script on OneDrive and then use it ?
That's how you really teach something, rigth in the moment you are learning it yourself. Thanks
This video was excellent and allowed me to get started with Python in my Power BI report.
The video in 6:11 set "matplotlib.pyplot as lib" and launch "pit.show". That's an error. The right code is: (after install pandas and anaconda)
import seaborn as sns
import matplotlib.pyplot as pit
sns.swarmplot(x="day", y="total_bill", data=dataset)
pit.show()
It's should be lib.show()
@@santoshf thank you
Thanks a lot!
Thanks ;)
Nice, thanks for this video ! Hope to have more video about Python integration in Power Bi in future videos
This was phenomenal, bro u just made my life a lot easier
Perfect starter video for a total amateur like me! Thanks Patrick
I need help, when I try to show it with pit.show() appears this: ;NameError: name 'pit' is not defined
lib.show()
Patrick thank you for this. Just yesterday I wanted to use Python in Power BI but had no idea where to start.
Patrick is a legend.
Damn right boy!!!
Hello patrick, I from Nicaragua and don't speak english but this video have subtitle in spanish, so great .. thank you for that
could you continue placing the subtitles?
Thank you for this Patrick, I am actually starting with learning python. Great video you have right there!
Great video. Just starting to explore using Python with Power BI. Interesting to see how the performance of the python queries stack up against DAX.
What do you mean personal gateway? if i share my dashboard with other who doesn't have python install, will they see a error on their side?
Awesome Patrick ;) I was thinking to test python in PBI since a while. This video made me started... I though it would be a bit complicated, but I am astonished by the simplicity to implement it. Time invested versus the quality and powerfulness of charts. It is definitely worth it. And using data=dataset ... BAM we get the interactivity between charts... For the moment I do not use any service so no issue with the Gateway... I feel I will find out some great usage of python in PBI ;) Patrick you are amazing ! Merci
Please more Video like this, always amazing videos.
Amazing presentation as usual. Thank you, Patrick!
Python and R integration is good. However, there are too many limitations for using Python and R scripts in Power BI which Patrick did not mention (vide documentation).
When we want to get data from a data source that is directly not supported in Power BI (quite rare nowadays!), an R/Python script may help. I remember Adam had a video on creating data connectors with C# as well (long time ago).
We can do certain transformations that are beyond the capacity of M using R/Python scripting.
What we need is a scripting language for Power BI that is comparable to VBA in Excel, a language that is truly incorporated, not superficially attached. R and Python script support in Power BI is still just sensational than truly useful. Let’s see how it develops in the due course.
Definitely Interesting, but I doubt if it would be equally useful. Of course, it’s only my opinion as a user of Power BI. There are experts on the subject who think otherwise.
don´t wish more scripting languages, python has to do the job. It should become the new VBA. (please!! I do not want to learn another language!!!)
@@matthiask4602 Neither do I! LOL! C# and R are good enough for MSBI!
What I meant was that the scripting language - be it Python, R or whatever -should be well incorporated into and supported by Power BI. Right now, there are ridiculous limitations.
You're going to be the person blamed when Microsoft shoehorns VBA into PowerBI...!
@@leybourne I think python would be better, it would be difficult to use R as we use vba imo. Python fits nicely as a scripting language that will feel like vba for power bi.
Can you not perform your transformations using DAX (which is similar to VBA) ?
I know an intermediate level of python, but have never looked into what's supported in Power BI. Seeing Patrick get something built so easily without evening knowing python was exciting as it shows the integration must be pretty well designed. I'm excited to go through and learn the limitations for myself.
nah too bad it not that good with powerbi
@@ducminh7568 why isnt it good? I found that with complex nested json, powerbi creates all these nested record and list objects and I have to go in and flatten them myself, unless I'm missing something as I just started using it. But with the python script the output comes out already flattened out
@@thefamousdjx so it not that great because you cannot load Python code online via SharePoint but it has to be from your computer so that you cannot automate tasks with PowerBi services but desktop only.
Awesome, thanks for taking the time to do this!
Yup. Have used Python to extract data from Oracle ERP Cloud to use with Power BI. Never did the visualization though, so that was good to see. 👍
how can I fetch data from sharepoint or web api and connect it here in power bi using python?
I've already use Python integration for some of my freelance work and it's great!
Hello, I got an error when loading the 'tips' dataset. So, In which folder should the tips file be
Just a friendly heads up: If you add python to your environment variables at the end of the installation (or the beginning depending on which version of Python you install), you can invoke pip without pointing to the directory. 😃
@@jamesbyrnes8286 Yes, you can manually add the path to your environment variables either through cmd or through searching environment variables in the start menu.
This guy's are amazing why Microsoft not seen this channel .
thanks a lot , where from the data set of "tips" came from ?
heyy guy, how can i put it on powerbi services? because it don't work there
I use a lot R and Python in Power BI but the problem is not being able to take an input from the user and apply it to a model and then give back an output as (chart, table, list etc.) if it would possible to send information to Python and R it would be a big big game change especially to use NLP capabilities from both of languages.
Hi, I’m wondering if this is still the case as at Jan 2024. I’m looking to do something similar of taking user input and apply it to a model.
how to read an excel file stored in a sharepoint with python in power bi? Without explicitly giving password and user or from an organizational account?
Great video! Can I ask you what is Personal Gateway? Is it something download and install or subscribe to? Thanks.
When we work on R and Python visual, it kinda takes lot of load on pbix file. How to optimize the same?
in python script to visualize, "Duplicate rows will be removed from the data". How can I disable it? :
Liked your video! I am A Python and Power BI Teacher: Very good as a start!
I have a requirement where I have to get values from 17 different worksheets of a excel file, so I wrote a python script that goes to each sheet and gets this single value and creates a dataframe. Now I am using this script in Power BI to get the dataframe into my model. But when I am trying to schedule a refresh it says the datasource is not supported. Is there any workaround or solution to this? Please help me out.
Do you think this would be effective at defining custom logic for bringing in data?
Like having to loop through the dataframe to determine its structure?
and returning that dataset?
stuff that you cant do just through applying transformations & such...
Python for REGEX to validate data quality of a database and then Power Automate to email the users inpuiting bad quality data so they can fix.
Great use case 👍🏾
When I publish it online, I cannot set up the schedule refresh. Any solution?
I have an error: python visuals are currently disable in your organization!!!! so, is python scripts only visible on the Microsoft cloud? and don't visible on the report server?
Good "Intro". Makes one eager to learn more about python in Power BI . But more content on top of this is to be expected now. :-)
Hey, great video but what if I want to take input from the user and use that in the Python code and give an output how can one do that?
I am trying to uncompressed a column in a dataset using pandas and lzstring. I have used this syntax but the Python script errors with TypeError: 'function' object is not subscriptable
# 'dataset' holds the input data for this script
import pandas as pd
import lzstring as lz
df = pd.DataFrame(dataset)
df['DecompreseedAssets'] = lz._decompress['MigratedAssets']
MigratedAssets column has data that is compressed using lzstring
Could you help please?
How can I switch parameters for the Python API call script from Power BI user interface.
Nice one. Can the refresh happen on standard gateway or it has to be a personal gateway
How can we loop through Report object eg: slicer to export the report as PDF file using python script?
Lol, I took a 3 month course on developing with Python and got stressed because my code, which would work in Jupyter Workbook didn't work in PowerBI. You did all these steps with 0 knowledge of python and were successful. Thanks for the explanation haha.
Any idea if we can use python in PBI Q&A feature ? I want to enhance the Q&A feature by adding some NLP api's.
Python visuals are very slow once the report is published, to slow to be actually used in a report. To my understanding, this is because the script has to be sent back to an azure host that recalculates the visual each time you change it. I try avoiding python visuals in power bi for this reason
The only way that your report is not slow is an empty report
Good info
Nice video: by any chance you know how to upload my dashboard with python visuals into a website? When I upload the dashboard, everything can be seen except these python visuals.
Hey Patrick, not sure if this is mentioned but in the command line you dont have to put the full directory of python, just "python -m pip install.." is good enough
Will Python script work if the PowerBI is located in Sharepoint. I mean, do other users, that can see my interface not see the visuals because they don't have python downloaded?
As a python user myself I cringed at your code but was excited to see python work. Great work
Is it possible to refresh a Python source in the Service?
Thanks a lot Patrick! Smooth and very useful orientation! Python and PB! awesome! The best of all the world
Loved this!!
Watching him alias matplotlib as lib was quite funny though 😂
It's weird because I followed your exact steps but I kept getting an error for my columns not being defined... I then got am error for the "pit.show()" not being defined. I tweaked it a bit and this code ended up working for me:
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:
#dataset = pandas.DataFrame(day, total_bill)
#dataset = dataset.drop_duplicates()
# Paste or type your script code here:
import seaborn as sns
import matplotlib.pyplot as lib
sns.swarmplot(data=dataset, x="day", y="total_bill")
lib.show()
Hello, thanks for the video. I have a question, I was wondering if this Python scripting would work when I upload my dashboard to the cloud and update it using a gateway to refresh the data sources periodically?
Amazing, how could you make the blue square move along with your mouse in your video?
we can do manipulations on the table we impprt to data query with python?
Really looking forward to receive some more important lessons on Python in Power BI.
How can I use this with Analysis Services in an emedded app? Thanks!
Apart from personal gateway to refresh the report, any other way or workaround to do it. Personal gateway is a very big limitation
Interaction is a problem with python. Some other visualisation tool has much better integration.
Python is great but for visual we can't do interaction from python visual to other part of report
Absolutely it's a great challenge. As a python lover I am dying to use python visuals in Power BI and just because of this interactive limitation I am not able to. SAD. Very 😥
It works from a default viz to the python viz but not the other way round. But that is not the only issue. Currently there are too many limitations.
By the way: what other viz tool? Have not seen any good integration so far
@@DanielWeikert If you know Javascript, you can build your own custom Visuals. Although the process is tedious and lengthy if you want a really good working visual. Use Charticulator to build your own visuals with little to no coding.
@@Abhishekjain07 I tried R too, and it has the very same issues. Sadly.
You could if power bi supported the right libraries, but power bi only lets you use libraries that do not provide interaction
Brilliantly explained
bro man bro.....you are legit the most solid doo in the cuooo
Brother I need a Dax query for the below table - I need a different column in the matrix table
Another reason to learn python. Thanks.
Thanks Patrick. This was a good start.
Use of personal gateway... does this mean all users have to have the personal gateway installed to be able to access a published report using python?
Hi Patrick! I'm planning to learn power bi right now. can you give me the link from your videos where I can start with? You got tons of videos and I don't know what video to start with. Thanks!
Line number 10. pit.show() gives me an error. I changed lib.show() and it's work.
Great video.
What is a personal gateway???
Also, I am surprised it’s such a short list of supported python packages… I wish plotly was supported for example!
Are there any plans to also make Python available for the standard mode of the power bi gateway (so not the personal mode)?
The integration between Power BI and R/Python is amazing for loading and processing data. I've created an automatic data workflow that creates tidy data files stored in the pickle format which I then import and use for visualizing the data. Some reports require further modifications to the already processed data and for those I've set things up so that Power BI calls the execution of a Python script stored in a custom folder that then does the additional data processing. Pretty neat! The exact same thing works with R, too.
Would love to see a video of this.
Hi, how do you make power BI call a python script stored in a folder? Don't you have to paste the Python code in the power bi itself?
Would love to see a video of this too
@@rishabhagarwal4702 It's actually fairly straight-forward. Go to the Power Query editor and create a new query by using "Python script" as the source. Then, type in the following command:
exec(open("C:/Users/User/Desktop/Script.py", encoding = "utf-8").read())
Then replace the folder and script names with your own and make sure to specify the encoding of your *.PY file. When you press enter, Power BI will start executing the script and then a pop-up window will appear where you will be able to choose one or several pandas data frames and load them into your model.
A really useful guide.
Hi Patrick, can we perform Text Analytics using Python visual in Power BI? If our data is based out of SSAS so we won't be able to use Text Analytics as we won't have Query Editor in Power BI then due to Analysis Server based data source.
Nice video! I have a question. If I send a dashboard with a python script to someone that doesn't have python installed, will it run?
Thanks!
Hoooo, I need to try that! Thanks Patrick
any idea if you could open a pbi dataset in python?
Hi man, I got the examples but I'm a little confused because the Python language in Power Bi is just to plot some libraries or graphs? What are the real advantages related to Power BI? For example, I can download more visuals instead of implement using the R language? Tks
This is great! I will try this out.
Awesome, cool video. Explaining all the details 👍
You could make a video of how to use powerquery and phyton for some data transformation
You rock! thanks for the quick demo. I love it
Just out of curiosity, how long did it take you to figure all this stuff out? 🙂
Great to see the "masters" struggle a bit, which is an inevitable part of the learning curve.
Thanks for this Patrick!
Slick - Thanks Patrick !!
Can we do model simulation in powerbi?
Can you do one for those who have Mac?
Do you know why is Python not supported on the Enterprise gateway and when is MS fixing this?
Appreciate you posting this info! Very informative and something I was looking for.
However, I've been trying to figure out the compelling use case to use python. So far, I can do everything that I've need with OOB connectors, dax and mquery.
But, I've seen that doing some development using Python (specifically in Jupyter Notebook) is much quicker because you only deal with a small subset of data instead of loading the entire data set.
In some cases, where data load time becomes an issue, i just set parameters to only load recent files or a # of records... Then, let data refresh do the heavy lifting after publishing.
Thanks. Good intro to the subject!
It seems like several details were left out on the installation (adding to PATH, including PIP... matplotlib, pandas) I had to reference another video because my installation didn't work just by watching this. I'm just a beginner, so maybe it wasn't intended for people with no prior knowledge.
Great stuff man... Would love to see some videos over the GenerateEmbedTokenV2 changes and what new functionality comes with the newer embed token model. Really the main updates with the move to V2.
This was pretty cool!
Can you please showcase in services on premises
More of the python in power bi please!