Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
In the image above you can see that I have the dataset 'Prop12' under fields. It has been imported into PowerBi.
Now how do I get access to that dataset within the python interface that PowerBI. In other words if I go to
"get data'->'More'->'Python Script' and click connect the python interface comes up as per the screen shot below:
If I want to load PowerBi's version of the dataset Prop12 and call it x, what do I type within the python script box. Note I am not talking about reading the original csv file that contains Prop 12 that was used to import it into PowerBI. I specifically want to load the version of Prop 12 mentioned in PowerBi, so that if I use powerquery to make some changes to it, it is the changed version that is loaded in the python script interface.
If it helps this is the link to the report: https://drive.google.com/file/d/1qXVDJVwccvrIBggvHy7RTlt5rLYHAjP0/view?usp=sharing
And this is the link to the dataset, containing Prop 12: https://drive.google.com/file/d/1E0aWMCECOWdsMTZFUPUMYaAS6dhUsrTU/view?usp=sharing
Solved! Go to Solution.
Hey @William29 ,
basically there are two option to use Python within Power BI:
The queries Power Query are representing Layer 1 (as i call it). Queries representing tables will be loaded to the Power BI dataset on data refresh when the Data load option is enabled.
It's not possible to create any DAX statement inside the Power BI dataset (Layer 2) that is referencing a query from Layer 1.
Custom python scripts can be used to create data visualizations, data from the Power BI dataset has to be used.
If you are using Python inside Power Query and plan to configure an automated refresh inside the Power BI Service a gateway has to be used.
If you are using Python to create a custom Python script visual a gateway is not necessary. but you have to check if the python library that you are using for the data visualization is supported by the Power BI Service: Learn which Python packages are supported - Power BI | Microsoft Docs
Hopefully, this helps to clarify my initial post.
Regards,
Tom
Hey @William29 ,
it's important to understand that there are data layers, each data layer is separate from each other, it's not possible to create an interaction between these layers.
As it's not possible to access data from subsequent layers you have to use the table that is created by importing the csv.
You have to consider that transforming data using python during an automated refresh requires an installation of the Power BI gateway using the personal mode.
Hopefully, this provides some ideas on to tackle your challenge.
Regards,
Tom
Hello TomMartens,
Could you explain more about what you mean by the 'PowerBi gateway using the personal mode'
With regard to the original question of the post are you using that when using python within PowerBi, that the only datasets I can access are the original csv files that were used to import the data into PowerBi, I cannot access (within python) the data in its imported form?
You mention that in layer 2, that the data is loaded from the PQ layer. However I thought that layer 2 cannot access the results of layer 1 and so wouldn not be able to access the data from the PQ layer. I thought the only thing it could access is the table created when importing from csv?
Hey @William29 ,
basically there are two option to use Python within Power BI:
The queries Power Query are representing Layer 1 (as i call it). Queries representing tables will be loaded to the Power BI dataset on data refresh when the Data load option is enabled.
It's not possible to create any DAX statement inside the Power BI dataset (Layer 2) that is referencing a query from Layer 1.
Custom python scripts can be used to create data visualizations, data from the Power BI dataset has to be used.
If you are using Python inside Power Query and plan to configure an automated refresh inside the Power BI Service a gateway has to be used.
If you are using Python to create a custom Python script visual a gateway is not necessary. but you have to check if the python library that you are using for the data visualization is supported by the Power BI Service: Learn which Python packages are supported - Power BI | Microsoft Docs
Hopefully, this helps to clarify my initial post.
Regards,
Tom
I am using PowerBi desktop. Will a gateway allow me to access the data that has been edited in powerquery within the python interface?
Hey @William29 ,
if you are using Power BI Desktop, and are not planning to configure a scheduled refresh inside the Power BI Service, no Power BI gateway is needed.
Please be specific about the Python interface you want to use, above I provided two links that point you to the documentation on how to use Python, did you read the articles?
Regards,
Tom
Where you mentioned about using the python interface within powerquery helps. There it gives you the name of the dataset to use in python. However, I notice that the python scripts are used only used on a one off basis. Suppose I have one column X and I want the column to the right to be just the original column X multiplied by 3. I can do that in power query. However, I notice if I then edit one of original values in some row in column X, Column Y does not automatically update to give 3 times that value on that same row. Column Y still shows three times the old value. Is there any way to get the python scripts to automatically so that I recomputes the python function after you edit the dataset, without having to manually create a new python script each time.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |