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!

 

 

 

 

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

  1. Nice Work! I just used in one of my apps. I think you have a type on your Items formula. I think it should be

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

  2. Excellent work! This really helped me too – thanks for publishing it. Note that I only got it going with the following variation on the Filter:
    Filter(CopenhagenGuide,
    Value(Latitude) >= Location.Latitude – Degrees(Slider1/1000/6371) &&
    Value(Latitude) = Value(Location.Longitude) –
    Degrees(Slider1/1000/6371/Cos(Radians(Location.Latitude))) &&
    Value(Longitude) <= Location.Longitude + Degrees(Slider1/1000/6371/Cos(Radians(Location.Latitude))) )

  3. What be interesting take on this solution is to check if user is within a location radius of a site a specific site – then output what that site is – that way it could be used throughout a app – i.e if used to test which office site a user is at, so a table with office1, office 2 and office 3 – the app then tests if user is in radius of each site and if at say office 3 – user is presented with office 3 information/screens/data etc.

    1. Hi James,

      That can be done using this solution/method.

      You just need to check the number of locations returned by the filter function and if it’s 1 then you can set the default to that location otherwise prompt the user to select between the filtered locations

      BR
      Erik

  4. Such a great project! can you please email me a copy of it? my email is fbsx503@hotmail.com and I have a question, what if I want to search for users within that specified radius? I mean their locations are not static they keep moving. how can I do that. If they are within the radius I need to count their steps, otherwise, I should stop counting. any suggestions?

  5. Hi. Really enjoying learning this. I have a question. In my drop down I am trying to use a database table via a SQL Connector. My code looks like this : Filter(‘[Assets].[work_in_area]’,
    Value(latitude) >= Location.Latitude – Degrees(Slider1/1000/6371) &&
    Value(Latitude) = Value(Location.Longitude) –
    Degrees(Slider1/1000/6371/Cos(Radians(Location.Latitude))) &&
    Value(Longitude) <= Location.Longitude + Degrees(Slider1/1000/6371/Cos(Radians(Location.Latitude))) )
    I am not sure where I specify the column that needs to show in the drop down box as my table has multiple columns and I am not sure how to select the one to display. Is it part of the filter, or somewhere else. Any pointers appreciated. Hope that makes sense. Thanks Andrew

  6. This looks great. I’m new enough to this I need to ask, I can apply this filter to say a gallery couldn’t I?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s