Hello everyone, today I am going to show you how you can use Python to create a dataset to use in Power BI. This will be a simple walkthrough of all the steps you need to do so. There are some prerequisites though. Let’s go ahead and get right into it.
The First Prerequisite: You will need to have Pandas already installed on your computer. If it is not already installed it is super simple to do so. Open your command prompt. Once you have it open type “pip install pandas”. It should look like this.
The Second Prerequisite: You will need to allow Power BI to use Python Scripting. This is slightly more complicated than installing pandas but still easy. From inside Power BI you are going to want to click on File > Options and settings > Options > Python scripting. Now that you are inside this part of the settings, you are going to want to set up where the home directory of python is, and the IDE that Power BI will use for scripting. If you have multiple different IDEs on your local machine, then you are going to want to manually set it. My settings look like this.
Now that we are all set up, we can get into creating our dataset. My sample dataset that I have created is based on a sample script from AdventureWorks2019 database. From the Get data menu, you are going to want to click Other and then click Python Script. Then you are going to want to click Connect.
Now you are going to have the Python script text box appear where you can enter your script to be used as a source. This is what I used:
import pandas as pd
PythonDataset = pd.DataFrame({
‘LastName’:[‘Mitchell’,’Blythe’,’Carson’,’Reiter’,’Vargas’,’Ansman-Wolfe’,’Pak’,’Varkey Chudukatil’,’Saraiva’,’Ito’,’Valdez’,’Mensa-Annan’,’Campbell’,’Tsoflias’],
‘SalesYTD’:[‘4251368.5497′,’3763178.1787′,’3189418.3662′,’2315185.611′,’1453719.4653′,’1352577.1325′,’4116871.2277′,’3121616.3202′,’2604540.7172′,’2458535.6169′,’1827066.7118′,’1576562.1966′,’1573012.9383′,’1421810.9242’],
‘PostalCode’:[‘98027′,’98027′,’98027′,’98027′,’98027′,’98027′,’98055′,’98055′,’98055′,’98055′,’98055′,’98055′,’98055′,’98055’],
})
print (PythonDataset)
Once you are done the Navigator window should look like this if you used the same script that I used.
Once you check the box and click Load your dataset will be loaded into your Power BI model and then you’re done.
In another blog I will go over how you can use Python to create visuals in Power BI, but you’ll just have to be on the lookout for that one. Anyways, I hope that this blog was helpful and that you may have learned something. Thanks for reading and have a great rest of your day.
Bailey McDonald
Data Engineer, Patriot Consulting
Email: bkmcdonald@patriotconsultingcorp.com | Blogs: Patriot Consulting Blogs
LinkedIn: Personal: BaileyMcDonald | Company: Patriot Consulting
