Power BI & Post Codes API

[WG-PBI]

Apologies for what is probably a pretty basic question but I have only really been using Power BI to connect to Excel & SQL Server so far.

I have a CSV file containing the post codes of customers.

I want to connect to a free online API I have found: https://postcodes.io/ that I am hoping will return a table in Power BI that will contain a post code to form a relationship to my customer post code but will provide other useful information such as lat/long/county/ward etc.

I am using Get Data > Web > inputting the https://postcodes.io/ link and am able to return a single post code but am struggling when trying to return more than one using the 'Bulk lookup postcodes' or 'Bulk reverse geocoding' despite these working great on the test requests on the website.

Wondering if anybody would please be able to help

Many thanks!

[hofrats]

Hey dude,

I was looking into this because I really wanted the postcodes.io option to work and I made it work!!! 🙂
For multiple postcodes hehe.

1. I simply input the postcodes request with 1 hard coded postcode. 

let
Source = Json.Document(Web.Contents("api.postcodes.io/postcodes/sr5 2lq")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded result" = Table.ExpandRecordColumn(#"Converted to Table", "result", {"postcode", "quality", "eastings", "northings", "country", "nhs_ha", "longitude", "latitude", "european_electoral_region", "primary_care_trust", "region", "lsoa", "msoa", "incode", "outcode", "parliamentary_constituency", "admin_district", "parish", "admin_county", "date_of_introduction", "admin_ward", "ced", "ccg", "nuts", "pfa", "codes"}, {"result.postcode", "result.quality", "result.eastings", "result.northings", "result.country", "result.nhs_ha", "result.longitude", "result.latitude", "result.european_electoral_region", "result.primary_care_trust", "result.region", "result.lsoa", "result.msoa", "result.incode", "result.outcode", "result.parliamentary_constituency", "result.admin_district", "result.parish", "result.admin_county", "result.date_of_introduction", "result.admin_ward", "result.ced", "result.ccg", "result.nuts", "result.pfa", "result.codes"}),
#"Expanded result.codes" = Table.ExpandRecordColumn(#"Expanded result", "result.codes", {"admin_district", "admin_county", "admin_ward", "parish", "parliamentary_constituency", "ccg", "ccg_id", "ced", "nuts", "lsoa", "msoa", "lau2", "pfa"}, {"result.codes.admin_district", "result.codes.admin_county", "result.codes.admin_ward", "result.codes.parish", "result.codes.parliamentary_constituency", "result.codes.ccg", "result.codes.ccg_id", "result.codes.ced", "result.codes.nuts", "result.codes.lsoa", "result.codes.msoa", "result.codes.lau2", "result.codes.pfa"})
in
#"Expanded result.codes"

2. I looked at the code and extraced the steps I needed for custom function

3. For bulk postcode check - you will first need to create a column that is simply postcodes.io url & "postcode" in whatever column you have it in:
= Table.AddColumn(#"Trimmed Text", "URL", each "api.postcodes.io/postcodes/"&[unit_postcode])

4. Next step is telling power query that is it JSON doc and how to treat it:
= Table.AddColumn(#"Added Custom1", "Custom", each Json.Document(Web.Contents([URL])))

5. expand it:
= Table.ExpandRecordColumn(#"Added Custom", "Custom", {"status", "result"}, {"status", "result"})

6. Final step - get all DIMs that you want:
= Table.ExpandRecordColumn(#"Expanded Custom", "result", {"postcode", "quality", "country", "longitude", "latitude", "region", "admin_county", "admin_ward"}, {"postcode", "quality", "country", "longitude", "latitude", "region", "admin_county", "admin_ward"})

7. Initially I set it up as custom function but then deleted the function and embedded it in the Power Query steps (step by step)

Hope this helps! Also, this is the first time I have done a solution 😄 so please....try it and lets be honest...this is the SOLUTION for this problem! 😄 

Have good one!

[lbendlin]

@hofrats please read about the RelativePath and Query parameters of the Web.Contents() function. Those will be required for service refreshes.

[WG-PBI · Accepted solution]

Thanks for your help

I managed to resolve my query by copying the method used here: https://sqldusty.com/2016/04/26/power-bi-and-the-bing-maps-api/ (in case anyone else has a similar problem).

I created a custom function to call the API for each instance of a postcode found in the data.

Many thanks

[Dan44]

@WG-PBI are you please able to share the code you are using as an example? I am struggling with this 

[WG-PBI]

In PowerQuery > New Source > Blank Query

Then paste:

= (address as text) =>

let
Source = Json.Document(Web.Contents("http://api.postcodes.io/postcodes/"&address&"?0=xml&key=")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded result" = Table.ExpandRecordColumn(#"Converted to Table", "result", {"postcode", "quality", "eastings", "northings", "country", "nhs_ha", "longitude", "latitude", "european_electoral_region", "primary_care_trust", "region", "lsoa", "msoa", "incode", "outcode", "parliamentary_constituency", "admin_district", "parish", "admin_county", "admin_ward", "ced", "ccg", "nuts", "codes"}, {"result.postcode", "result.quality", "result.eastings", "result.northings", "result.country", "result.nhs_ha", "result.longitude", "result.latitude", "result.european_electoral_region", "result.primary_care_trust", "result.region", "result.lsoa", "result.msoa", "result.incode", "result.outcode", "result.parliamentary_constituency", "result.admin_district", "result.parish", "result.admin_county", "result.admin_ward", "result.ced", "result.ccg", "result.nuts", "result.codes"}),
#"Expanded result.codes" = Table.ExpandRecordColumn(#"Expanded result", "result.codes", {"admin_district", "admin_county", "admin_ward", "parish", "parliamentary_constituency", "ccg", "ccg_id", "ced", "nuts", "lsoa", "msoa", "lau2"}, {"result.codes.admin_district", "result.codes.admin_county", "result.codes.admin_ward", "result.codes.parish", "result.codes.parliamentary_constituency", "result.codes.ccg", "result.codes.ccg_id", "result.codes.ced", "result.codes.nuts", "result.codes.lsoa", "result.codes.msoa", "result.codes.lau2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded result.codes",{{"status", Int64.Type}, {"result.postcode", type text}, {"result.quality", Int64.Type}, {"result.eastings", Int64.Type}, {"result.northings", Int64.Type}, {"result.country", type text}, {"result.nhs_ha", type text}, {"result.longitude", type number}, {"result.latitude", type number}, {"result.european_electoral_region", type text}, {"result.primary_care_trust", type text}, {"result.region", type text}, {"result.lsoa", type text}, {"result.msoa", type text}, {"result.incode", type text}, {"result.outcode", type text}, {"result.parliamentary_constituency", type text}, {"result.admin_district", type text}, {"result.parish", type text}, {"result.admin_county", type text}, {"result.admin_ward", type text}, {"result.ced", type text}, {"result.ccg", type text}, {"result.nuts", type text}, {"result.codes.admin_district", type text}, {"result.codes.admin_county", type text}, {"result.codes.admin_ward", type text}, {"result.codes.parish", type text}, {"result.codes.parliamentary_constituency", type text}, {"result.codes.ccg", type text}, {"result.codes.ccg_id", type text}, {"result.codes.ced", type text}, {"result.codes.nuts", type text}, {"result.codes.lsoa", type text}, {"result.codes.msoa", type text}, {"result.codes.lau2", type text}})
in
#"Changed Type"

and then in the table that contains your postcodes you can add a custom column with the formula:
= nameofyourfunction(Text.Replace([postcode],"&"," and "))

You can then expand your custom column to get the results from postcodes.io

Not sure if this is best practice or if there is a better way but worked for me!

[Dan44]

Thanks for that and I have now got it working in Power BI desktop. However, I am having issues configuring this in Fabric whether it be a dataflow, datamart or publishing my PBI semantic model.

Can you please let me know what approach you are taking to schedule refresh this data? As from MS learn it states the following:

In most cases, Power BI semantic models that use dynamic data sources can't be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

[WG-PBI]

Ah sorry I cannot help you, I refresh manually in Power BI desktop and publish to the workspace (infrequently for a quartlery meeting)

[lbendlin]

Provide a sample of your csv file. Make sure to include more than 100 codes (that's the limit for the bulk call)