Tutorial: Create a Power View report with Azure Marketplace data

In this Power View tutorial, you download free data from Microsoft Azure Marketplace, add more data, and create relationships between tables. You create a series of visualizations in Power View that illustrate which visualization is right for what kind of data: When to use a map, bar chart, or line chart; how to sort and filter data in visualizations; how drill-down works. You also create some simple calculated fields in Power Pivot so Power View can better consume the data in the workbook. First you need the data.

Make a Power View sheet with a map of US airports, a bar chart of airline average delays, and a line chart showing delays by month.

Create a map

Wouldn’t this data be more interesting if we saw it on a map? Now that we have the city and state names in the Airports table, we can map the data. First, let’s move the airline chart so we have more room.

Drag the moving handles for the airlines chart to put it along the left side of the chart, and drag the sizing handles to make it tall and narrow.

Select the Airports chart and on the Design tab > Map.

The first time you create a map in Power View, you see a privacy warning that your data needs to be geocoded by sending it to Bing through a secure web connection.

Click OK.

Power View might put AirportName in the Color box. If so, drag it to the Locations box.

Drag the sizing handles to make the map fit the whole remaining width of the sheet.

That’s too many dots! Let’s focus on airports where delays are longest. And you notice the dot north of Australia? That’s Guam.

Filter the map

With the map selected, in the Filters Area click Map.

Click Average of DepDelayMinutes.

You see the scroll bar from 0 to 25.37 minutes.

Drag the left side of the scroll bar so you’re only showing delays greater than 10 minutes.

Tip: For a more precise number, you can also do this in Advanced filter mode. Click the icon to the right of the filter name (Average of DepDelayMinutes), and Show items for which the value > is greater than or equal to > type 10 in the box.

Now let’s filter out Guam. In the Filters Area, click AirportName. Select the All box, in the Search box, type Guam, and then click the Search magnifying glass icon.

Guam International Airport is the only result.

Clear the check box next to Guam.

Click the plus sign in the upper-right corner of the map to zoom in and use the cursor to reposition so you’re only showing the continental US and Alaska.

Hover over the large dot in the Bering Sea: Adak Airport, almost 24 minutes’ delay.

Cross-filter visualizations

Now comes the fun stuff – playing with the interaction between visualizations.

Click the Adak Airport bubble.

Look what happened to the Airlines bar chart! Apparently Alaska is the only airline that flies to Adak Airport. Notice how you can still see the Alaska total average and all the other airlines? If the average of the individual value is more than the total, Power View makes the individual average bar narrower than the total bar, so you can still see the total.

Zoom in and reposition further to just the continental US.

In the bar chart, click ExpressJet.

Mostly eastern and central US.

Click the bar for Frontier.

Just three: San Francisco, Palm Springs, and Greater Rockford airports. San Francisco might be the biggest airport on this map.

On the map, click the bubble for San Francisco.

Oh, my! Almost every airline that flies out of San Francisco has a worse average there than elsewhere.

Add a time visualization

Let’s see how the airlines did in different months. We can guess which months were worst; let’s see if we’re right. The On_Time_Performance table contains several date fields, including one, FlightDate, formatted M/D/YYYY HH:MM:SS AM – for example, 1/1/2012 12:00:00 AM.

There’s also a Month field. It’s just numeric – no month names, but let’s start with that.

Start a new Power View sheet: in Excel > Insert > Power View Report.

In the Field List, expand the On_Time_Performance table and check Month.

Because it’s a numeric field (marked with a Sigma Σ), Power View add the values.

In the Fields box, click the arrow next to Month > Do Not Summarize.

Power View creates a table with the months in order. 1, 2, 10, 11, and 12. There’s only one problem: They’re not actually in order.

With the Month table selected on the sheet, in the On_Time_Performance table, drag Year and put it above Month in the Fields box.

In the Fields box, click the arrow next to Year > Do Not Summarize.

Now you can see months 10, 11, and 12 are actually before months 1 and 2, because they’re in 2011.

Create a Month Name table

In Excel on a new sheet, start a table in cell A1:

MonthNumber

MonthName

1

January

2

February

Select cells A2 to B3 and then drag the fill handle in the lower-right corner of cell B3 [ZA010278334] down to cell B13.

This fills rows 4 to 13 with the rest of the month numbers and names.

Select rows 1 to 13, columns A and B, and create a table (Ctrl + T).

Make sure My table has headers is selected.

On the Excel Design tab > Table Name, type MonthNames.

Rename the worksheet Month Names.

Add the table to the model

With the table selected, in Excel on the Power Pivot tab > Add to Data Model.

This opens the Power Pivot window on the MonthNames sheet.

In the Power Pivot window on the Home tab > View > Diagram View.

Create a relationship between the tables by dragging from the Month field in the On_Time_Performance table to the MonthNumber field in the MonthNames table.

Create a MonthName Year calculated field

On the Power PivotHome tab > View > Data View.

Go to the On_Time_Performance table and click in the Add Column column.

You’re going to create a basic calculated column.

Type

=RELATED

Click the MonthNames sheet, and click the MonthName column.

This brings the MonthName field from the MonthNames table and adds (MonthNames[MonthName] to your formula in the On_Time_Performance table.

Type

) & " " &

Make sure they’re straight quotation marks, not curly.

Click the Year column in the On_Time_Performance table.

This adds [Year] to your formula. The whole formula looks like this:

=RELATED(MonthNames[MonthName]) & " " & [Year]

Press Enter.

Power Pivot populates a field with the month name from your MonthNames table, a space, and the year from the On_Time_Performance table – for example, October 2011.

Select the column, right-click, and rename it MonthName Year.

Use the new MonthName Year calculated field in Power View

Go back to Excel and the Power View sheet.

Drag your new MonthName Year field to the sheet.

Hmmm, something’s wrong: The months are in alphabetical, not chronological, order!

Sort one field by another field

Go back to the Power Pivot window.

Go to the On_Time_Performance table and click in the Add Column column to create another basic calculated column.

Type

=YEAR([

This brings up the list of columns in the table.

Click the FlightDate column and then press the Tab key.

Type

) & FORMAT(MONTH([

Click the FlightDate column again in the list of columns in the table and then press the Tab key.

Type

), “00”

The whole formula looks like this:

=YEAR([FlightDate]) & FORMAT(MONTH([FlightDate]),”00″)

Press Enter.

Power Pivot creates a column with just the year from the FlightDate field and just the month number from the same field. Because of the “00”, the month number is displayed with two digits. For example, 201201 is January 2012.

Select the column, right-click, and rename it MonthNumber Year.

Select the MonthName Year column and on the Power PivotHome tab > Sort by Column.

MonthName Year is in the Sort Column field.

In the By Column field, click MonthNumber Year > OK.

Create a line chart by months

Go back to Excel and on the Power View sheet, click OK for the message that the Data Model has changed.

Indeed it has changed: the months are now ordered chronologically, October 2011 through February 2012. Much better!

With the MonthName Year table selected, in the Field List > On_Time_Performance table, check the DepDelayMinutes field.

The field is added as a sum.

In the Fields box, click DepDelayMinutes > Average.

On the Design tab > Other Chart > Line.

Aha! As expected, December was the worst month, closely followed by January.

Now add a second line: With the MonthName Year table selected, in the Field List > On_Time_Performance table, drag the ArrDelayMinutes field to the Values box.

That field is added as a sum, too.

In the Fields box, click ArrDelayMinutes > Average.

Notice that DepDelayMinutes – departures – were worst in December, but ArrDelayMinutes – arrivals – were worst in January.

Let’s see which days of the month were worst.

Add drill-down to a line chart

With the line chart selected, from the Field List > On_Time_Performance table, drag Day of Month to the Axis box, below MonthName Year.

The line chart looks unchanged.

Double-click the point for December 2011.

Now we’re seeing the day-to-day delays for the month of December 2011. Notice the two spikes for DepDelayMinutes?

Hover over the spikes for DepDelayMinutes: December 22nd and 27th – before and after Christmas.

What about November?

Click the Drill Up arrow in the upper-right corner to go back to the chart by months.

Double-click November 2011.

Ah, no surprise there – November 29th was the worst day for arrivals and departures.

You can continue playing with this data. For example, here’s something else to try: Copy the MonthName Year line chart from this Power View sheet and paste it on the sheet with the map. Then, as you click airports on the map, you can see how they compare month to month.

Themes and formatting

Try playing with the themes on the Power View tab, too. The Power View sheet in the image at the beginning uses the Composite theme with a Light2 Solid background. See what you come up with! Read more about formatting Power View reports.