How to build a location aware #PowerApp – ‘Guide book Copenhagen’ – #opendatadk and #powerquery

In one of my latest projects we have used PowerApps to create a location aware selection of stores – and I wanted to share my experience about how to do this.

So, I found an open data set about attractions, restaurants, hotels and much more in Copenhagen.

https://portal.opendata.dk/dataset/guidedanmark-oplevelser-overnantning-aktiviteter-i-hele-danmark

In order to get the data into PowerApps – I created an Excel workbook and used PowerQuery to import the data in a Table.

The Query to create the table is quite simple and contains a little renaming and removal of unwanted columns, and I only imported the rows that has an Latitude and Longitude.

let

Source = Json.Document(Web.Contents(“https://portal.opendata.dk/dataset/44ecd686-5cb5-40f2-8e3f-b5e3607a55ef/resource/23425a7f-cc94-4e7e-8c73-acae88bf1333/download/guidedenmarkcphenjson.json”)),

#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“Id”, “Created”, “CreatedBy”, “Modified”, “ModifiedBy”, “Serialized”, “Online”, “Language”, “Name”, “CanonicalUrl”, “Owner”, “Category”, “MainCategory”, “Address”, “ContactInformation”, “Descriptions”, “Files”, “SocialMediaLinks”, “BookingLinks”, “ExternalLinks”, “MetaTags”, “RelatedProducts”, “Places”, “MediaChannels”, “Distances”, “Priority”, “Periods”, “PeriodsLink”, “PriceGroups”, “PriceGroupsLink”, “Routes”, “Rooms”, “Capacity”}, {“Id”, “Created”, “CreatedBy”, “Modified”, “ModifiedBy”, “Serialized”, “Online”, “Language”, “Name”, “CanonicalUrl”, “Owner”, “Category”, “MainCategory”, “Address”, “ContactInformation”, “Descriptions”, “Files”, “SocialMediaLinks”, “BookingLinks”, “ExternalLinks”, “MetaTags”, “RelatedProducts”, “Places”, “MediaChannels”, “Distances”, “Priority”, “Periods”, “PeriodsLink”, “PriceGroups”, “PriceGroupsLink”, “Routes”, “Rooms”, “Capacity”}),

#”Expanded Address” = Table.ExpandRecordColumn(#”Expanded Column1″, “Address”, {“AddressLine1”, “AddressLine2”, “PostalCode”, “City”, “Municipality”, “Region”, “GeoCoordinate”}, {“AddressLine1”, “AddressLine2”, “PostalCode”, “City”, “Municipality”, “Region”, “GeoCoordinate”}),

#”Expanded GeoCoordinate” = Table.ExpandRecordColumn(#”Expanded Address”, “GeoCoordinate”, {“Latitude”, “Longitude”}, {“Latitude”, “Longitude”}),

#”Filtered Rows” = Table.SelectRows(#”Expanded GeoCoordinate”, each ([Latitude] null and [Latitude] 0)),

#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Municipality”, “Region”, “ContactInformation”, “Descriptions”, “Files”, “SocialMediaLinks”, “BookingLinks”, “ExternalLinks”, “MetaTags”, “RelatedProducts”, “Places”, “MediaChannels”, “Distances”, “Priority”, “Periods”, “PeriodsLink”, “PriceGroups”, “PriceGroupsLink”, “Routes”, “Rooms”, “Capacity”}),

#”Expanded Category” = Table.ExpandRecordColumn(#”Removed Columns”, “Category”, {“Name”}, {“Name.1”}),

#”Removed Columns1″ = Table.RemoveColumns(#”Expanded Category”,{“Owner”}),

#”Expanded MainCategory” = Table.ExpandRecordColumn(#”Removed Columns1″, “MainCategory”, {“Name”}, {“Name.2”}),

#”Renamed Columns” = Table.RenameColumns(#”Expanded MainCategory”,{{“Name.2”, “MainCategory”}}),

#”Removed Columns2″ = Table.RemoveColumns(#”Renamed Columns”,{“AddressLine2”}),

#”Renamed Columns1″ = Table.RenameColumns(#”Removed Columns2″,{{“Name.1”, “Category”}}),

#”Removed Columns3″ = Table.RemoveColumns(#”Renamed Columns1″,{“Created”, “CreatedBy”, “Modified”, “ModifiedBy”, “Serialized”, “Online”, “Language”})

in

#”Removed Columns3″

The Excel file is then saved in Onedrive for business.

 

Lets build the app

 

I use the Web studio.

 

And select the Blank app with a Phone layout

On the canvas I click the Connect to data and create a new connection that connects to Onedrive for Business and pick the Excel file

 

So now we have a connection to data in our App

 

And I insert the following controls

 

The first two labels show the my location as latitude and longitude, and the I inserted a slider with a min and max of 0 to 2000 as the radius in meters around my location. The label above my slider is just to show the selected radius.

Now we can insert a drop down and set the Items to the data connection and the column Name in that data connection and see it works.

 

Now we must filter the items based on our current location. In order to do this, we must filter our items. This can be done using the FILTER function.

The formula the uses the slider to modify the radius around our location

Filter(CopenhagenGuide, Value(Latitude, “en-US”) >= Location.Latitude – Degrees(Slider1/1000/6371) && Value(Latitude, “en-US”) = Value(Location.Longitude, “en-US”) – Degrees(Slider1/1000/6371/Cos(Radians(Location.Latitude))) && Value(Longitude,”en-US”) <= Location.Longitude + Degrees(Slider1/1000/6371/Cos(Radians(Location.Latitude))) )

And if I now limit the radius to 173 meters you can see I have 4 places nearby

If you want to add a map as well highlighting the selected Attraction you can do that as well

 

You can find the information to do that here – https://powerapps.microsoft.com/en-us/blog/image-control-static-maps-api/

 

If you want a copy of the PowerApp file you are welcome to add a comment or ping me on twitter @donsvensen and I will send it to you.

 

Hope you can use this – Power ON!