how to get & process data from & using python scripts

[vincent_lu]

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

[siricher · Accepted solution]

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

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

a	b
1	2

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)

[vyacheslavg]

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.

[DeepEureka]

Can you confirm if loading using Feather format is faster since it's already a Tabular format which is memory optimized?

Parquet is better for reduction of disk space, not memory like Feather.

[Anonymous]

@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.