Selectively Importing Python Tables and Auto Exporting with Python

[v-lionel-msft]

Python and Power BI are directly passed by DataFrame. Since Python itself does not support dataframe, python calls the pandas library automatically. M transfers its data of table type to python, and python will automatically convert table to dataframe; Python's processing results are output in the form of dataframe, and m will automatically convert dataframe to table format.

Importing Multiple Tables and Filter with Parameter
Instead of using the “Python Script” connector to import data, I need to use Python.Execute() function in power query to retrieve multiple query tables. Please refer to the steps:

1. In power query editor, create a blank query.

2. Create Python.Execute() function to run Python script in the bracket.

3. I create 3 tables with duplicate values just to demonstrate on how to filtered tables.

4. Python.Execute() allows you to create an aggregated query include multiple tables. With this query, we should be able to create a parameter to filter the table we want to display in the Desktop visual interface.

5. It is recommended to create an index column since the table name could be complex in real case, we just need to add an index number before the table name (e.g. 1. Product table, 2.Income table, etc.). Then we can right click index column, add as new query to create a query parameter and use the parameter to filter the index column. Finally, we can just expand the table before Close and Apply.

Exporting Data with Schedules

Here is a very common scenario, if we would like to export data every time the dataset refreshes we could use Python script to achieve this. By executing the following python code, we would create a variable (data) that duplicates the original table (dataset), then use the data.to_csv function to export the data to a specified location.

Once this steps is done, we can create the report and publish it to the service as usual to service like we usually do. However, it is important to know that Python/R scripts are only available for Personal mode Gateway.

After configuring the personal gateway properly, we can then set up schedule refreshes. With the above configuration, the script allows you export and update the csv file every time the dataset refreshes.

[v-lionel-msft]

Hi All,

Sample Files: Pbix Files

Blog Author: @V-pazhen-msft

Best Regards,
Lionel Chen