get data query parameter from cell value and output in a table

[Pete Wright]

I would like to process data from a JSON API website.

It is an API of German national holidays returned as JSON-formatted data
The parameters are year and state
The website: https://feiertage-api.de
An example query: https://feiertage-api.de/api/?jahr=2020&nur_land=RP
where 2020 and RP are the "dynamic parameters" that I want to change in the Excel sheet

The first value in the example JSON string is:
"Neujahrstag":{"datum":"2020-01-01","hinweis":""}
Neujahrstag is the name of the holiday
"datum":"2020-01-01" means: date: 2020/01/01
"hinweis":"" is additional information on the holiday

I know how to add a static data query, but I have no idea how to pass a parameter from a cell value.

An example Excel Sheet (simplified) would look like this:

cell A1: year
cell B1: state
columns C to E: date, name, info

I have already searched the web and there are so many forum topics, blog posts and tutorials dealing with Power Query and Data Query, but it is all a little bit difficult to understand.
For example the Advanced editor: What does "let" source = "" in ... mean?

I would like to have an example based on my requirements to understand how all this data stuff works. Then I can try other examples and queries.

Any help, hints, links, etc. highly appreciated.
Many thanks in advance
Pete

 

[GraH - Guido]

Hi Peter,

From the data tab start from "From Web" then paste https://feiertage-api.de/api/?jahr=2020&nur_land=RP.


Then convert to table, just hit the button on the ribbon


Finally expand the Records


The basic code will be like this

let
    Source = Json.Document(Web.Contents("https://feiertage-api.de/api/?jahr=2020&nur_land=RP")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"datum"}, {"datum"})
in
    #"Expanded Value"

Turning https://feiertage-api.de/api/?jahr=2020&nur_land=RP into parameters can be done.

Load the range A1:B1 in PQ from Table/Range. Drill down on Year. Duplicate this query. Delete the drill down step in this copy and redo a drill down on state.

Name each query something like vYear and vState. Save and load both as connection only.

Then update your source "https://feiertage-api.de/api/?jahr="& vYear &"&nur_land="& vState

Change your parameters and click refresh on the table (mouse right click, or on the data ribbon).

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

 

[Pete Wright]

...

Many, many thanks !!!

I'll take a look at it...

 

[GraH - Guido]

You're welcome Pete (without "r"). Thanks for the feedback.

 

[Pete Wright]

...

There seems to be a problem with the query.
It does not update, when I change the values.

See Screenshots below

 

[GraH - Guido]

If I'm not mistaken these parameter setting are the cause. (Try option 2 or 3)

 

[Pete Wright]

If I'm not mistaken these parameter setting are the cause. (Try option 2 or 3)

Ok, but where do I find this dialog?

 

[GraH - Guido]

Here, on the data tab.

 

[Pete Wright]

Here, on the data tab.

Great, it finally works

But, it only gets updated when i right-click on the query table and then "refresh".
And there seems to bo no option to refresh on data change. See Screenshot below.

 

[GraH - Guido]

Nope, there is indeed no such setting. Would need a macro to do that.
I'm leaving now, so try to google how to call an event an sheet update. That will give you the basic code. Record a macro in which you click refresh. Then copy this line in the sample code.

 

[Pete Wright]

Nope, there is indeed no such setting. Would need a macro to do that.

Thank you so much for your help and patience.

Actually, I don't need an automation, just been curious if it's possible though.

 

[GraH - Guido]

Something like attached is an example. Though in this case it will refresh twice if you update first the year then the state.
I imagine better coders can offer a solution in which both cells are updated before the refresh is called.

Thanks for the feedback and for improving my reaction score. ;-)