Iterate a web url over YearMonths

[TomTomTom]

Hi, I've successfully queried this web api as a one-off:

https://opendata.nhsbsa.net/api/3/action/datastore_search?resource_id=EPD_202002

However, what I'd like to do is iterate over the bold YearMonths (202002) backwards to the oldest YearMonth available (sometime in 201303 I think) and also search for the most recent one each time the data refreshes - i.e. look for 202003, or next month for 202004, etc - and stop looking when nothing is found.

At the moment, the only way I know to do this is manually - by typing in different YearMonths.

Is there a way to iterate the query over YearMonths automatically?

(The immediate outcome would be to append all the data, if that makes a difference.)

[mahoneypat · Accepted solution]

Here is one way to do it.  Just paste the below M code into a blank query.  It does the following:

- makes a list from 2012 to 2020 and converts to table

- adds a column with list of 1 to 12 (for each month), converts to text and pads with a "0"

- merges those together to get YearMonth in the needed format

- makes a web call with the baseurl and each YearMonth value

- removes rows with errors (i.e., the YearMonths without any data in 2012 and future 2020 months)

- starts to extra the json data.  there is more expansion of Lists and Records needed after this point, since I don't know which fields you are interested in.

Please mark it as the solution, if it works for you.  Please let me know if any questions.

let
Source = {2012..2020},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "MonthNum", each {01..12}),
#"Expanded MonthNum" = Table.ExpandListColumn(#"Added Custom", "MonthNum"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded MonthNum",{{"MonthNum", type text}, {"Column1", type text}}),
#"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"MonthNum", each Text.PadStart(_,2,"0"), type text}}),
#"Merged Columns" = Table.CombineColumns(#"Added Prefix",{"Column1", "MonthNum"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"YearMonth"),
#"Added Custom1" = Table.AddColumn(#"Merged Columns", "Custom", each Json.Document(Web.Contents("https://opendata.nhsbsa.net/api/3/action/datastore_search?resource_id=EPD_"& [YearMonth]))),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"Custom"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Errors", "Custom", {"help", "success", "result"}, {"help", "success", "result"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"YearMonth", "result"}),
#"Expanded result" = Table.ExpandRecordColumn(#"Removed Other Columns", "result", {"help", "success", "result"}, {"help", "success", "result.1"}),
#"Expanded result.1" = Table.ExpandRecordColumn(#"Expanded result", "result.1", {"include_total", "resource_id", "fields", "records_format", "records", "_links", "total"}, {"include_total", "resource_id", "fields", "records_format", "records", "_links", "total"})
in
#"Expanded result.1"

[TomTomTom]

Beautiful. Thank you!

PS, I've changed the 2020 to 2030, so it's good for a decade!

[mahoneypat]

FYI that if you really go out to 2030, it will make many web calls that will result in error and slow your refresh.  You could dynamically generate YYYYMM of the current month and year, and add a filter step before the web call step.  That way, it will keep adapting for future calls, but won't waste refresh time.

Also, please mark the response as the solution (trying to get my community points up).  Thanks.

Regards,

Pat

[TomTomTom]

Hi Pat

Marked as a solution this morning - does it not show up on your screen that way?

Will investigate dynamically generating the current year and inserting it into the query.

(Although we will only query the API once a month, so I figured it wasn't too bad.)