By: Nat Sundar | Comments | Related: > Power BI
Problem
I want to analyze UK parliamentary petition data using Power BI. How this can be achieved and what are all the things I need to consider?
Solution
UK Petition Data
The UK Parliament allows the general public to raise a petition online and anyone can vote (via signature) in the UK to support the petition. This includes the petition details, number of signatures on the petition and other details are available to download using CSV or in JSON format. In this tip, I will walkthrough a method to consume JSON data and generate a report using Power BI.
This link will allow you to see all the UK parliamentary petitions. This link will supply the petition details in JSON format.
Loading Petition data into Power BI
UK petition data (JSON URL) can be loaded into Power BI using the below steps.
Open the Get Data menu and click on the “More” sub menu to open the next dialog box.
![Open Get Data Menu - Description: Open Get Data Menu](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.001.png)
In the detail dialog box, select “JSON” format.
![Select JSON menu - Description: Select JSON menu](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.002.png)
This will open another window for you to select a physical JSON file or to type the URL in the address bar. As we are getting the JSON data from the URL, I have copied the URL in the address bar as shown in the image below.
![Type URL for JSON feed - Description: Type URL for JSON feed](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.003.png)
Once the data gets loaded, you will see the output in the query editor. As you can see, we have to navigate thru the JSON format to get the desired dataset.
![Click on the List link - Description: Click on the List link](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.004.png)
You have to click on the data item (“List”) to navigate further. Once you navigated, you will see all the details of the list item as shown in the below image.
![Convert to Table - Description: Convert to Table](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.005.png)
Now you have to convert this list item to a table by clicking on the button “To Table”. Click on the Split into Columns button to open a list of all possible columns. If you prefer, you can use the option "Use original column name as prefix" to add column name as prefix. I have decided to use the default option in this example. Also, I have selected all the columns (type, id, links and attributes) in the list. Once the columns have been expanded, you will see the output as per the below picture.
![Expand columns - Description: Expand columns](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.006.png)
![Expand columns for attributes - Description: Expand columns for attributes](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.007.png)
Now click on the Split into columns button on the "links" column to see the URL for the petition, as per the image below.
![Expand links columns - Description: Expand links columns](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.008.png)
Once expanded, the URL details are visible in the table.
![Expanded link column - Description: Expanded link column](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.009.png)
Let’s click on the Split into columns button on the attributes column as shown in the image below. This will open the list of all possible columns (action, background, additional_details, state, signature_count, etc.) as per the image below.
![Expanded attribute column - Description: Expanded attribute column](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.010.png)
After the expansion, you will see all the additional columns in the table as per the image below.
![Expand attribute action column - Description: Expand attribute action column](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.011.png)
As there are so many unwanted columns in the tables, I have decided to remove the unwanted columns. After I have removed the unwanted columns, the table looks very clean and the below mentioned image represents the actual table. After clicking "Close and Apply", the final query table has been successfully created.
![Click close and apply - Description: Click close and apply](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.012.png)
Creating Top 20 Petitions
This simple table can be queried to generate useful reports. The weight of a petition depends on the number of signatures on the petition. So, let’s create a top 20 petitions based on the signature count.
For this purpose, I am creating a "New table" with the below expression. This expression will filter the top 20 petitions based on the signature count.
Expression -- Top20Petitions= TOPN(20,All_Petitions,All_Petitions[SignatureCount])
![Creating Top 20 Petitions - Description: Creating Top 20 Petitions](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.013.png)
A report can be developed by adding a table into the report pane by selecting the columns PetitionDescription, PetitionID and SignatureCount as per the image below.
![Create a report for Top 20 Petitions - Description: Create a report for Top 20 Petitions](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.014.png)
Petition Count by Month
As we have the “Petition created date” column in the original table, we can easily develop a report to see how many petitions have been created for each month. A simple report has been developed by adding the “petition created date” and the count of Petition ID to give the number of petitions. As the petition ID is a unique identifier, a count of petition ids can be used to count the number of petitions. After generating the report, it has been realized there were only 50 petitions overall.
![Overall report - Description: Overall report](/tipimages2/5274_analyze-uk-petition-data-using-power-bi.015.png)
Due to a technical limitation, the parliamentary petitions system only allows you to download JSON data with 50 petitions at a given time. This means, to download the next set of 50 petitions you need to navigate to different URL. I will explain the details to combine multiple sets of petitions in the next tip.
Summary
In this tip we have learned how to extract UK petition data from JSON feed. Also, we have learned to develop a simple report to show the stats at very high level.
Next Steps
- Read other Power BI tips here.
Learn more about Power BI in this 3 hour training course.
About the author
![MSSQLTips author Nat Sundar](/images/Nat-Sundar.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips