API datalink (JSON data) DIRECT to Single Column

[MT_MANC]

Could anyone help with JSON data parsing via API web link?
I'm having some issues setting-up an (auto-update) API link from DB NOMICS DBnomics to a single Excel 2019 sheet column.
eg I want Col C11 in my .XLSX to show latest/updated quarterly data from 1973Q1-Latest Q from the DB NOMICS quarterly series
https://api.db.nomics.world/v22/series/OECD/QNA/JPN.P31S14_S15.CARSA.Q?observations=1

My unsuccessful attempts:
Menu Choice1 - “Data”-“From Web”-[Above URL] I then get to the Power Query editor to parse the JSON data but I get stuck trying to show the raw series DATA feed in a single column - see below:

It would appear that there is maybe a “Json.Document…” line missing at the top of this dialogue cf other YouTube videos on JSON parsing in Excel. ? Maybe this might explain some of the problems occurring ?

Menu Choice2 - “Data”-“Get Data”- “From Other Source”-“From Web” - [Above URL]-Into Table(Convert), I managed only to insert a a 2-column Table into two new columns! - see below:

Key Stumbling block = the procedure for JSON parsing to feed a single column with (live-feed) data
Any pointers gratefully received

 

[Rijnsent]

Hi MT_MANC,
welcome to the wonderful world of the Power Query editor. The steps I took to get that data (excel 365):
-in excel go to the menu data->get data->other source->"from the web"
-paste your url there and click (basic) OK ( https://api.db.nomics.world/v22/series/OECD/QNA/JPN.P31S14_S15.CARSA.Q?observations=1 )
-now the PowerQuery editor should open, the question now is: where in this JSON file is the data you are looking for. That's hard to do in the PowerQuery editor, but easy in a browser. I'm using the JSONview add-in for Brave to get an idea what the JSON looks like. With that, I see that the numbers are in series->docs->value .
-So in the PQ screen, click next to series ( on "record"), after that next to docs (on "list") and on "record" and next to value (on "list"). Finally, click in the bar on "to table" and "OK". Then press "save and load" and you should get the table in your sheet.

For the "advanced editor":

let
    Src = Json.Document(Web.Contents("https://api.db.nomics.world/v22/series/OECD/QNA/JPN.P31S14_S15.CARSA.Q?observations=1")),
    series = Src[series],
    docs = series[docs],
    docs1 = docs{0},
    value = docs1[value],
    #"Convert table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Convert table"

This tutorial has a bit more info: How To Access A JSON API With Power Query | How To Excel (skip the part about authorization).
Hope that helps,
Koen