cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
William29
Helper I
Helper I

How to refer to a dataset under 'fields' using python script in powerBi

William29_0-1660467633753.png

 

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:

 

William29_1-1660467834215.png

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

 

1 ACCEPTED SOLUTION

Hey @William29 ,

 

basically there are two option to use Python within Power BI:

 

  1. Python insided Power Query https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-in-query-editor?WT.mc_id=DP-MV...
  2. Python for data visualization https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-visuals?WT.mc_id=DP-MVP-500306...

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
TomMartens
Super User
Super User

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.

  1. Layer 1 (Power Query) - Extract and transform source data
  2. Layer 2 (Power BI dataset) - data will be loaded from the PQ layer into the data model (stored inside the vertipaq engine) durig data refresh
  3. Layer 3 (Interaction layer) - measure layer, this layer is also part of the Power BI dataset, but for better understanding I imagine measures as a separate layer.

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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:

 

  1. Python insided Power Query https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-in-query-editor?WT.mc_id=DP-MV...
  2. Python for data visualization https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-visuals?WT.mc_id=DP-MVP-500306...

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.  

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors