Turning a JSON API into data with Get Data and Power Query

[ukozlows]

Hello,

I generated a API from a website hosting data and used it to create my power query source code which is a follows:

= Json.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/statistics/1.0/data/prc_hicp_midx?format=JSON&freq=M..."))

At that point, the following is displayed.  I have been searching to no avail to learn how to turn this into the data.  How do I get at the data?

[ukozlows]

I saw that the source code in Power Query was truncated so let me give the complete code incase someone would please use it to create the solution.  

=Json.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/statistics/1.0/data/prc_hicp_midx?format=JSON&freq=M..."))

Incase it truncates it again when i repost this, here is the code in sections.

= Json.Document(Web.Contents(

"https:

//ec.europa.eu/eurostat/api/dissemination/statistics/1.0/data/prc_hicp_midx?format=JSON&freq=M&unit=I15&coicop=CP00"))

[BrianConnelly]

What should the data look like from the response?  That is to say, how is it structured in the source?  Can you provide a sample or screenshot?

[ukozlows]

[BrianConnelly]

Are you able to change the API?    The API is creating objects, but it is not well structured.

[ukozlows]

here is the link to the data I show above

Statistics | Eurostat (europa.eu)

https://ec.europa.eu/eurostat/databrowser/view/prc_hicp_midx/default/table?lang=en

[BrianConnelly · Accepted solution]

So, I have spent more time then I would have liked to understand the JSON response that you have and your issues.  Which is kinda of a good thing to learn something new.  So here is the breakdown...

1. The JSON response is return is in a JSON-stat format which is a main statistical standard.
2. You can spend a lot of time breaking down the response and building the table in Power BI
3. I found a go to source which was very helpful at https://json-stat.org/
4. Here are the instructions for how you can get the JSON in a format to be used...https://eriksvensen.wordpress.com/2019/01/09/guide-how-to-import-data-from-eurostat-directly-into-po...

YOur code to use is prc_hicp_midx

The tool URL is Tool - Query Builder - Eurostat (europa.eu)