JSON to Power BI table

1.

Open new Power BI document and select Query Editor

2.

On Query Editor, click on New Source, then Web and paste the link obtained from Fingertips

3.

The data will look something like this:

Your only option is to select ‘Transform into Table’ on the top left corner of the home ribbon

4.

Rename the table to Original_Data

5.

Click on the double arrow square sign on the top right corner of the Value column in the table

6.

A drop down will appear with all the columns for the data, select: Data, Comparator Value, Periods, IID, Sex and Age

7.

Now, the order of the next few steps is crucial

8.

Click on the double arrow sign on data and expand it. This will create as many columns as areas. Plus one named ‘Value’

9.

While they are still selected, right click on any of them and select ‘Unpivot Only selected columns’

10.

Right click on the ‘Value’ column and select ‘Add as new query’.

11.

Right click on the ‘ComparatorValue’ column and select ‘Add as new query’

12

Expand ‘periods’ to new rows

13.

Expand sex and age

14.

Click on the tiny table symbol in the top left corner of the table and select ‘Add Index Column’ starting from 1

15.

Go to the Comparator Value query and expand it – will come up with two columns 1 and 4. 1 = London, 4= England. It’s a good idea to rename them

16.

Click on the tiny table symbol in the top left corner of the table and select ‘Add Index Column’ starting from 1

17.

Go to the Value query and expand it. Rename the columns something meaningful

18.

Click on the tiny table symbol in the top left corner of the table and select ‘Add Index Column’ starting from 1

19.

Go back to the original_data query and in the Home ribbon, top right corner select the drop down from merge queries.

20.

In the window that will appear merge the original data query with either of the two through a left join on the Index column (one at a time)

21.

Now expand the columns from the merged queries (no need to expand the index column from them)