Note that Power Query was previously known as Codename “Data Explorer”. Similarly, Power Map was previously known as Project codename “GeoFlow”.
Let’s say your 10 year anniversary is approaching and you might be thinking about a good place to have a nice dinner with your partner. We would like to show you how Power Query and Power Map can help you gather some insights to make a good decision.
In order to achieve this, we will look at publicly available data using the Yelp API to look at ratings and reviews for restaurants. For the purpose of this post, we will focus on the King County area (Redmond, WA).
What you will need:
Microsoft Power Query for Excel
Microsoft Power Map for Excel (GeoFlow)
Access to the following data:
Yelp API (more details):
“Together, these two tools enhance the self-service business intelligence experience in Excel by allowing you to discover, combine, refine and visualize your data.”
What you will learn in this post:
Import data from the Yelp Web API (JSON) using Power Query.
Reshape the data in your queries.
Parameterize the Yelp query by turning it into a GetRestaurants() function, using the Power Query formula language, so you can reuse it to retrieve information about different types of restaurants as well as different geographical locations.
Invoke a function given a set of user-defined inputs in an Excel table.
Load the final query into the Data Model.
Visualize the results in Power Map.
That sounds like too much for a single blog post, but let’s get started and you’ll see how it is easier than you might think.