Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Pass Postcodes to ofcom API

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 

 

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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.

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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