New Features of SQL Server 2014 Bi (iii) power query and power map function Preview

Power query and power map are the new Excel-oriented functions released by Microsoft on the WPC recently. With these two features, self-help BI makes it easier for you to discover and process data and enrich the data visualization functions.

The predecessor of Power query and power map are data explorer and geoflow, both of which are mentioned in SQL Server 2014 ctp1. Combined with power intelligence and power view, these four functions form Microsoft's latest power Bi series.

This article is derived from the official Microsoft Business Intelligence blog. It mainly demonstrates a simple example. You can use power query and power map to obtain and display data and find the appropriate restaurants around you. First, we will use the Yelp API to retrieve restaurant ratings and comments.

Yelp is the largest Review website in the United States. The example in this article gets the required information from its API through Excel's power query.

 

First, open Excel and click from web from power query.

Enter the API address provided by the Yelp API, for example:

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

First, a row and column are returned. here we need to convert it in JSON format.

After the conversion is complete, you can see two columns and expand one of them.

Then a single column of data is displayed. Use the same method to select the required columns.

Then you can see the formatted data.

We can see that the data is obtained from the API provided by Yelp through several simple steps.

In addition, Yelp API queries can be parameterized. In addition, you can obtain a new name for the query, such as getrestaurants.

Reference the query to the document and click Insert Custom column. The parameter can be passed through the data in a workbook.

Create an Excel work sheet that contains the following data.

Click from table in power query to reference data in the workbook to power query. After the reference is complete, add a column. In the insert Custom column dialog box, enter the following formula:

After simple processing, we can get the final data we need.

 

After the basic data is prepared, the data is displayed on the map.

On the query settings panel, click load to data model. Select map in insert to open the power map.

Specify the longitude and latitude columns and map the data to the map.

Specify the measurement column to height.

In this way, through this map chart data, which restaurant is most suitable for the surrounding area is displayed visually.

 

Through the above demonstration, you may say that such a function is not very fresh through the existing mobile app, but this is not the focus of this article. Through the introduction in this article, you can see that data that can only be obtained by programmers in the past can also be obtained in a simple way in Excel, and also provides rich Map Display support, providing more options for self-service business intelligence, thus reducing the threshold for business intelligence. Big Data is not far from you, and it will not be just a patent in the future. With the help of Microsoft's power Bi, it will be closer and closer to us.

 

Appendix:

Power Query

Power Map (still geoflow)