Pete Wright
Member
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
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