Pass column values into API using POST method

[Rueben]

My goal is to use query my SQL server to return a list of postcodes and then pass that into the postcode.io API to return long / lat / ward data.

I've put some dummy data into the 'Query' step for now, but it usually returns 2000~ postcodes. Similarly, in the 'Body' step I've hardcoded two postcodes just to make sure I was using the syntax correctly, and it returns the long/lat/ward for those postcodes just fine.

How can I reference 'Query' in my step 'Body' so that it will take the postcodes from my column [postcode] and pass it into the API?

The API documentation also says it only accepts 100 postcodes, is there any way to pass multiple batches of 100 postcodes at a time?

Will this work on a scheduled refresh once it's published? I had something similar working using the GET method but it is prolonged and doesn't work with scheduled refresh (dynamic Datasource error).

let
    // SQL Query to return each unique poscode in core Caseload dataset
    Query = Sql.Database("server", "database", [Query="select postcode from addresses"]),
    //Core JSON goes here. Here we will reference the above query row-by-row.
    body = "{""postcodes"" : [""SW1A 1AA"", ""SW1A 0AA""]}",
    //API URL goes here
    Data= Web.Contents("api.postcodes.io/postcodes",
    [Content=Text.ToBinary(body),
    Headers=[#"Content-Type"="application/json"]]),
    DataRecord = Json.Document(Data),
    Source=DataRecord,
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{1}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"result"}, {"result"}),
    #"Expanded result" = Table.ExpandRecordColumn(#"Expanded Column1", "result", {"longitude", "latitude", "admin_ward"}, {"longitude", "latitude", "admin_ward"})
in
    #"Expanded result"

[Anonymous]

Hi @Rueben ,

To reference the query in the Body step, you can use the Table.ToList function to convert the Query table to a list of postcodes. You can then use the Text.Combine function to combine the list of postcodes into a comma-separated string that you can use in the body of your API request. 

To handle the API limit of 100 postcodes per request, you can use the List.Buffer function to split the list of postcodes into smaller lists of 100 postcodes each. You can then use a List.Transform function to apply a custom function to each of the smaller lists. This custom function would combine the smaller list of postcodes into a comma-separated string, make the API request, and return the response from the API.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rueben]

Thanks Stephen. I'm a bit of a newbie when it comes to Power Query M but I'll give it my best shot.

Thank you! 👍

Update:

I've got up to the buffer stage, how do I get them into groups of 100? Not sure how to pass a value to the buffer function, and I get the error:

Expression.Error: We cannot convert the value ""2BN 3LD", "B4 7AU",..." to type List.

The data that goes into the 'body' step is syntactically correct, and correctly returns the data I need as long as I restrict the results in my SQL query down to 100 records.

let
    // SQL Query to return each unique poscode in core Caseload dataset
    Query = Sql.Database("MySqlServer", "MyDatabase", [Query="SELECT DISTINCT [postcodes] FROM addresses"]),
    //Convert query into a list
    QueryList = Table.ToList(Query),
    //Concatenate list items together with a delimiter
    ListDelimiter = Text.Combine(QueryList, """, """),
    //Wrap entire list in double-quotes to ensure syntax is correct.
    ListWrap = """"&Text.From(ListDelimiter)&"""",
    //Buffer into groups of 100, because the API only accepts 100 items at a time
    GroupsOf100 = List.Buffer(ListWrap),
    //Core JSON goes here. Here we will reference the above query row-by-row.
    body = "{  ""postcodes"" : ["&ListWrap&"]}",
    //API URL goes here
    Data= Web.Contents("api.postcodes.io/postcodes",
    [Content=Text.ToBinary(body),
    Headers=[#"Content-Type"="application/json"]]),
    DataRecord = Json.Document(Data),
    Source=DataRecord,
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{1}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"result"}, {"result"}),
    #"Expanded result" = Table.ExpandRecordColumn(#"Expanded Column1", "result", {"longitude", "latitude", "admin_ward"}, {"longitude", "latitude", "admin_ward"})
in
    #"Expanded result"