Loading a json file into Power Query

All we need is an easy explanation of the problem, so here it is.

I am trying to load a JSON file I pulled from data.gov into Power Query for manipulation, but I can’t figure out how to get Power Query to convert the JSON file to a tabular format.

Is there a way to convert JSON to a table in Power Query without writing a custom query?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

JSON data often appears as Records within Lists within Records (and other variations). You can usually use the PQ UI to expand those objects without writing code.

Here’s a sample JSON endpoint: http://api.nobelprize.org/v1/laureate.json

After running the PQ “From Web”, the next step is to click the Record Tools / Convert / Into Table button in the PQ ribbon.

This returns a single row, with the second column (Value) containing a List. Click the expand button next to “Value” to expand the List.

This returns multiple rows, with each second column (Value) now containing a Record. Expand the record to get the columns of data in a tabular format.

Note there is more depth to this particular JSON layout – see the Value.prizes column at the far right. Your exact steps may vary, depending on your JSON input and your output requirements.

Here’s the full code to get to a table – each step was generated by clicking on the UI.

    Source = Json.Document(Web.Contents("http://api.nobelprize.org/v1/laureate.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", 
        {"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode", 
         "bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"},
        {"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
         "bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"})
    #"Expanded Value1"

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply