Skip to main content

Microsoft Power BI-blog

Getting started with Power Query and Power Map

Headshot of article author Faisal Mohamood

Group Product Manager @ Power BI
7 juli, 2013

Update: Visit our Getting Started with Excel and Power BI Series post to access updated training

Update 2: Try what’s next for Power BI

Power Query and Power Map are two of the new Excel features for Power BI announced this morning at the Worldwide Partner Conference. Together, these two tools enhance the self-service business intelligence experience in Excel by allowing you to discover, combine, refine and visualize your data. But as you will see in this post, self-service BI can become truly personal depending on the nature of the questions that you are trying to answer.

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:

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.

Import data from Yelp API

The first thing to do is click the From Web button in the POWER QUERY ribbon tab. Power Query supports importing data from a wide range of Web sources, including scrapping tables from HTML pages, as well as importing data from Web APIs. Power Query supports XML, JSON and OData formats.

 

Taking a look at the Yelp API documentation, you will find that they offer a Search API which accepts multiple GET parameters to customize your query, including a search term (i.e. restaurants, sushi, seafood, etc.), a location (i.e. “Seattle”, “98052”, lat/long information) and a few others. Finally we also need to provide the API Key that we will receive after registering for this API (wsid parameter).

With that in mind, let’s paste the following URL in the From Web dialog box in Power Query which will return Seafood restaurants in Redmond (note that you will have to replace the wsid value with your own key).

https://api.yelp.com/business_review_search?term=seafood&location=Redmond&ywsid={your Yelp API key}

We will get a result that looks like a single column table with one row, containing text data which, in fact, is structured as JSON. We can interpret this text as JSON by right-clicking in the column header and selecting Transform > JSON.

 

Now as a result we get a record which we can expand and select which fields to include in the output. The “businesses” field is the one that contains the list of restaurants that we are looking for, so we can just expand that one by unselecting “message” in the Expand menu and clicking OK.

This will return a single cell containing a list, which we can expand using the same mechanism again and turn it into a bunch of records, one per restaurant. Now we can decide which columns to expand, containing different information about each restaurant including Name, Lat/Long information, and Rating. We will expand the following columns: city, review_count, latitude, address1, name, avg_rating and longitude.

Now we have the list of Seafood restaurants into a tabular shape, and after applying some column renames and reordering the columns, we can get to the following shape.

 

That was actually easy. At the end, all we’ve done is define a set of data acquisition and transformation steps, which get recorded in the Steps pane on the right side of the Query Editor dialog. This Query Editor dialog is an easy-to-use interface for defining these steps, which get translated into the corresponding set of Power Query formulas (you can access them by clicking the script icon to the right end of the formula bar). Rename the query to Seafood restaurants in Redmond.

 

Parameterizing our Yelp API query

As we saw in the previous section, we can customize our Yelp Search by modifying the GET parameters in the URL. We can also make our Power Query query parameterized by turning it into a function, which can take input parameters and use them within the function body. In order to do this, we just need to add the following first line to our query script and use the parameters in the first step (Source). Finally, we can also rename our query to GetRestaurants.

This was very straightforward, but if you want to learn more about Power Query functions you can take a look at the Language and Library Specifications. There’s more cool stuff waiting for you there!

Invoking the GetRestaurants function

Now that we have a function, it would be a good thing to learn how to invoke it. There’re basically three ways in which you can invoke a function:

  1. Invoking the function “inline”: Simply open the Query Editor for your function query and click Invoke. You will get asked for parameters as needed. This is a very quick way of invoking the function; however, it defeats the purpose of reusability since at this point the function will no longer be available from outside this query (given that the result is a table).

  1. Referencing the function: You can use the Reference option in the Query ribbon tab to create a new query in the workbook that references the current query. In this case, we could do this and be able to invoke your function from a new query.
  2. Invoking the function over a table: We can also use the Insert Custom Column option from a different query in order to invoke a function across all rows in the table. We can also make the function parameters be based on other columns from the table.

The third method is the one that we will use in this example. For instance we could create a table with different cities in the King County area and invoke the GetRestaurants() function for each row, so that we can get restaurants for each of the cities in the list.

First, we will create a table in the Excel sheet containing a list of cities, such as Redmond, Bellevue, Seattle, Kirkland, Issaquah and Sammamish.

Now we can go to the POWER QUERY tab and click the From Table option. This will create a new query that references the sheet table. We can insert a custom column into this table by clicking on the table icon on the top-left corner of the table and selecting Insert > Custom.

In the Insert Custom Column dialog box, we can type in any custom formula using the Power Query formula language. In this case, we will simply invoke the GetRestaurants(“seafood“, [Cities]) function using the following formula:

At this point, we will be asked for information about the privacy level of my workbook data. This is done so that users don’t accidentally leak data from a private or organizational source and inadvertently send it to a public source (like the Yelp API in this case). We will select Public in this case. You can learn more about Privacy Levels in Power Query in our Online Help pages.

After providing this information, the new column will be added to the table. Note that we have a new column with nested tables on each row (containing the restaurants for each of the cities) and we can expand these columns similarly to how we did earlier with the JSON records. We can also remove the “Cities” column since we don’t need it anymore.

Once we have expanded all columns, you may find that there are some duplicated rows. This is due to the fact that the Yelp API may return restaurants within a radius up to 25 miles (which can also be customized in the Yelp API parameters) so a given restaurant in Seattle might be returned when searching within Seattle and Bellevue, for instance. We can get rid of duplicated rows by selecting Remove Duplicates in the table menu (top-left corner of the table). This will leave us with approximately 60 candidate seafood restaurants, with their ratings and total number of reviews, as well as location information (Address, City, Lat and Long). We can select the Ratings, Review_Count, Latitude and Longitude columns and make sure they are formatted as Number by right-clicking the column headers and selecting Change Type > Number. Finally, let’s also rename the query to “Seafood restaurants in King County”.

Visualize restaurant locations and reviews in Power Map

To finish this post, we will put the resulting query into a nice Power Map map. First, we need to load the final query into the Excel Data Model by clicking the Load to data model option in the Query Settings pane.

Now you can go to the INSERT tab in the Excel ribbon and select Map. This will launch Power Map where we can create a new tour with multiple layers to visualize our data.

Let’s create the visualization layer for the Yelp ratings, which we can represent as a Column chart and use the Review_Count as column height and the restaurant Name as categories.

In order to add a layer, we will need to pick the Seafood restaurants in King County table and select the Latitude and Longitude columns first and tell Power Map to “Map It” based on these fields.

Then we can pick the Review_Count field and select Column type chart, using the Review_Count as the Height and the Name as Category.

Hope you have enjoyed this post. Let us know what you think after you’ve had a chance to play with Power Query and Power Map for yourself.