cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vincent_lu
Regular Visitor

how to get & process data from & using python scripts

I want to get data from local files which will be processed by Python scripts in PBI desktop, using the following steps:

Open PBI Desktop -> Get Data -> Other -> Python scripts -> entering scripts in the new window.

Question: What kinds of scripts I should write which can return some variants that can be used in PBI visuals

when I wrote the following scripts, I can get nothing from the scripts:

a=1

b=2

 

what I expected is that I can use var a and b for some new visuals, just like I getting the data from the following csv file:

a b

1 2

 

1 ACCEPTED SOLUTION

Accepted Solutions
siricher
Regular Visitor

Re: how to get & process data from & using python scripts

 

The Python connector will import the DataFrame variables defined in your script.

For instance, the following code generates the table you've described:

 

ab
12

 

dataset = pandas.DataFrame([[1,2]], columns = [ 'a', 'b' ])


If you want to import csv files, you can use panda's read_csv function: 

dataset = pandas.read_csv("C:\\path\\to\\my\\data.csv")

(However, if you all you want is import csv files, you can also use the "Text/Csv" data source from Get Data -> File)

View solution in original post

3 REPLIES 3
siricher
Regular Visitor

Re: how to get & process data from & using python scripts

 

The Python connector will import the DataFrame variables defined in your script.

For instance, the following code generates the table you've described:

 

ab
12

 

dataset = pandas.DataFrame([[1,2]], columns = [ 'a', 'b' ])


If you want to import csv files, you can use panda's read_csv function: 

dataset = pandas.read_csv("C:\\path\\to\\my\\data.csv")

(However, if you all you want is import csv files, you can also use the "Text/Csv" data source from Get Data -> File)

View solution in original post

vyacheslavg Helper II
Helper II

Re: how to get & process data from & using python scripts

thanks a lot, this works.

 

The whole code in Python would be like this

import pandas as pd
dataset = pd.DataFrame([[1,2]], columns = [ 'a', 'b' ])

 

And Power BI creates a Power Query wrapper around it, automagically

 

let
Source = Python.Execute("import pandas as pd#(lf)dataset = pd.DataFrame([[1,2]], columns = [ 'a', 'b' ])"),
dataset1 = Source{[Name="dataset"]}[Value],
#"Changed Type" = Table.TransformColumnTypes(dataset1,{{"a", Int64.Type}, {"b", Int64.Type}})
in
#"Changed Type"

 

What I could not figured out, how to edit Python code in IDE, for example in VS Code or Sublime.

I've tried to list IDE in Options, but Power BI Desktop keeps using a simple internal so called "Advanced Editor"

 

Regarding your comments about csv... Well, I have not tested it yet, but the integration with Python would resolve one of the weakest point of PBD for me - quick reload of datasets, where cleansing thru Pandas or Power Query is needed before I can work with it in DAX and visual DAX.  I use a lot of Parquet in my Pandas workflow. So, if the load speed of Parquet will be the same in Python script inside of Power BI that could be a game changer, especially for datasets > 100M. Using Evo 960 I can get amazing load speeds in Pandas. Will test and post back.

 

Update - tested, now I can use Parquet and Feather formats to upload data into Power BI. This opens some new ways to use PBD in my workflows.

VivianDabre
Frequent Visitor

Re: how to get & process data from & using python scripts

@vyacheslavg 

To edit the python script in power bi, i.e once the script is run and data is loaded and you want to edit the script then Go to edit queries > select the table and on the right-hand side in applied steps click on the source setting it will open the python script. You can edit your python script here.

 

Cheers.

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors