Power BI

Data Manipulation using Python Scripts

Due to its increasing user base, we have decided to include Python scripting in our Power BI online and onsite trainings across Australia. This will help our attendees write their visualization codes in Python and analyze their data in a better way.  This blog assumes that Python has been connected to Power BI successfully.

For getting started in Python: Click here.

In this blog post, a step-by-step guide to importing and manipulating python scripts in Power BI has been discussed.

Import Data

For this section you need to download IRIS dataset which is publicly available. You can download the dataset from https://gist.github.com/netj/8836201.

 Once the data is downloaded save it in the csv format.

1. Import the sample data by clicking on Get data> Text/CSV ‘.

Power BI online training - Python Manipulation 15

2. Select the CSV file and click on ‘Load’ data.

Power BI online training - Python Manipulation 14

Data Manipulation using Python Scripts

The process of changing the raw data is known as Data Transformation. Data can easily be manipulated using the powerful Python Libraries.  We can manipulate our dataset to display information in a more organized and meaningful manner.

For more data transformation guides: click here

Let’s Calculate “Area” by multiplying “Petal.length” into Petal.width”.

1. The first step is to load the data set into Power BI. Click Get data in the Home Tab.

2. Select the type of dataset you want to load. After that browse the file explorer, choose your dataset and click on Load.

Power BI online training - Python Manipulation 13

You can apply data manipulation using python scripts, but before running the python scripts you have to change/adjust the privacy levels.

3. Click on File > Options and Settings.

4. Click on Data Source Settings.

Power BI online training - Python Manipulation 12

5. The following window will appear. Now click on Edit Permissions.

Power BI online training - Python Manipulation 11

6. Now change the privacy levels and click OK.

Power BI online training - Python Manipulation 10

In this example we have changed the privacy level to Public but you can choose according to your requirement.

7. Click on Edit Queries in the Home tab to open Power BI Query Editor.

The following window will appear.

 

Power BI online training - Python Manipulation 9

8. Click on Transform Tab.

9. Click on Run Python Scripts in the Scripts Section.

Power BI online training - Python Manipulation 8

The following window will appear.

10. You will write all the Python scripts in this window. In this manual we will explain how you can perform different arithmetic operations on the loaded data set.

Power BI online training - Python Manipulation 7

11. Now write the code as shown below to perform multiplication of two columns of our dataset.

12. Click OK to execute the script.

Power BI Online Training - Python Data Manipulation

The result of the multiplication operation has to be stored in a separate column. For this, we are creating a new column “Area” using python script.

Power BI online training - Python Manipulation 5

13. The following window will appear, mark the Ignore Privacy Levels checkbox, and click on

After clicking save, Power BI will start running your Python Script.

Power BI online training - Python Manipulation 4

14. The result of the script will appear as below.

Power BI online training - Python Manipulation 3

15. Click on the Table to see the results of this multiplication of columns.

Power BI online training - Python Manipulation 2

A result table will appear in the bottom showing the results of the multiplication.

Power BI online training - Python Manipulation 1

Here you can see that the result of the multiplication is stored in a new column named ‘Area’ which we created in the Python script. You can scroll through all the rows to see the result by using these navigation buttons.

Conclusion:

Python is becoming popular due to its healthy and active support community, capacity to handle big data and its amazing libraries. Power BI now provides its users with the option to integrate python with Power BI. In this blog post, importing and manipulating in Python scripts has been discussed.  

More Checkouts for Power BI Training in Australia.

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *