Power Query: Seeing JSON

Welcome to our Power Query blog. This week, I look at how to view a complex JSON record.

Last week I looked at viewing links in Power BI Power Query – I am going to stay in Power BI, as it enables me to create a complex JSON record from a webpage. The site I am going to use has a page http://www.tvmaze.com/api, which is designed to output JSON data for use in applications.

I am going to use the ‘Show single search’ option.

In particular, I am going to search for ‘Doctor Who” (yup, we’re all nerds here):

This record is quite detailed, so I can try expanding the information. It’s easy enough to view it:

However, if I want to expand the data so I may use it, I encounter problems:

If I ‘Drill Down’ or double click on ‘Record’ then I am taken out of my main data:

I could try the option available in the ‘Record Tools’ tab, to convert to a record.

However, this doesn’t help much:

I am still taken away from the rest of my data in order to expand the record, and I’d need to do more work to keep the data in one table.

There is another approach, using the M code:

This creates a table from the list of JSON data:  

This is much more promising, as I can expand the schedule column and keep the rest of the data.

This is one time when ‘Use original column name as prefix’ is useful!

Now I can extract the list of schedule.days.

I choose to extract the values.

I choose to separate the schedule days with a comma.

I can view it on Sundays. I can expand all the data held in records and lists so that I have all the information in one table.

I now have all the information I need in one place.

Come back next time for more ways to use Power Query!