Pass Postcodes to ofcom API

[Anonymous]

Hi All,

Fairly new to PowerBI so bear with me.

Ihave a list of Postcodes i want to pass to the ofcom api to check thier broadband speed. I created a function as below

let
get_speed = (#"site Postcodes" as text) =>

let
Source = Json.Document(Web.Contents("https://api-proxy.ofcom.org.uk/broadband/coverage/"&#"site Postcodes"&"", [Headers=[#"Ocp-Apim-Subscription-Key"="5xxxxxxxxx", Name="api-proxy.ofcom.org.uk"]])),
Availability = Source[Availability]
in
Availability
in

get_speed

I then try to create a custom column to iterate the function on each postcode 

=FN_NetworkSpeed[Postcode]

Im being thrown the error

: We cannot apply field access to the type Function.
Details:
Value=[Function]
Key=Post Code

Im not sure how to fix it, can anyone help 

[PhilipTreacy · Accepted solution]

HI @Anonymous 

Download this PBIX file with code - just enter your API key.

I signed up for an OfCom API key to test this.  This code is tested and working.  The Post Code has to be in upper case and without spaces e.g. E146NJ

(PostCode as text) =>

    let
        Source = Json.Document(
            
                    Web.Contents(
            
                        "https://api-proxy.ofcom.org.uk/broadband/coverage/"&Text.Replace(Text.Upper(PostCode), " ", ""), 
                    
                        [Headers=[#"Ocp-Apim-Subscription-Key"="xxxxxx", Name="api-proxy.ofcom.org.uk"]])),

        Availability = Source[Availability]
    in
        Availability

If you start with a column of post codes, the API will return a list of records for each house in that post code

You can expand these lists to get the records and then expand those records

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[mahoneypat]

Please try this intead for your function.  Your previous expression is a query that includes a function, not just a function that can be invoked in another query.

(#"site Postcodes" as text) =>
let
Source = Json.Document(
Web.Contents(
"https://api-proxy.ofcom.org.uk/broadband/coverage/" & #"site Postcodes" & "",
[Headers = [#"Ocp-Apim-Subscription-Key" = "5xxxxxxxxx", Name = "api-proxy.ofcom.org.uk"]]
)
),
Availability = Source[Availability]
in
Availability

[amitchandak]

@Anonymous , refer if this can help a bit

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/