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.

Power View sheet using Windows Azure Marketplace data with map, bar, and line charts

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.

Download data from Microsoft Azure Marketplace

In Excel, on the Power Pivot tab > Manage Data Model.

Don't see a Power Pivot tab? Start Power Pivot in Microsoft Excel 2013 add-in.

Click Get External Data > From Data Service > From Microsoft Azure Marketplace.
In the Table Import Wizard, under Type > Data, and under Price > Free.
Search for "air carriers".
Next to US Air Carrier Delays, click Subscribe.
Sign in with a Microsoft account. If you don't have one, follow the steps to create one.

This window shows some sample data.

Scroll to the end of the sample data and click Select Query.
You can type a friendly name for the connection, and then click Next.
Click Preview and Filter.
In this window you can pick which columns you want to import, by unchecking the check boxes. We want all of them, so click OK, and then click Finish.

The download process may take some time. The dataset has more than 2.4 million rows.

Click OK.

You have just created a Data Model in Excel.

Create a Power View sheet with a clustered chart

In Excel on the Insert tab > Power View Reports.

There in the Field List is the On_Time_Performance table.

Click the arrow next to the On_Time_Performance table.

Those are the fields (columns) in the table.

Check the Carrier box.

This shows a list of the abbreviations for carriers.

Check the ArrDelayMinutes box.

You get an aggregate number per carrier.

In the Values box, click the arrow next to ArrDelayMinutes.

You see that you're getting a sum of minute delays per carrier.

On the Design tab, click the arrow under Bar Chart and click Clustered Bar.
Resize the chart to make it taller: Drag the handle on the lower edge when the cursor changes to a pointing hand.
In the Field List, drag DepDelayMinutes to the Values box under ArrDelayMinutes.

Now we see some carriers are better at on-time arrivals, while others are better at on-time departures.

Change the sort order

Hover over the chart to see sort by in the upper-left corner. Click Carrier.

Now it's sorted by ArrDelayMinutes.

Again in the upper-left corner, click asc. Now it's sorted descending instead.

Clearly WN has the largest number.

Change the aggregate from a sum to an average

But maybe summing the delays isn't right: An airline with more flights might be on time more often, but still have a larger total.

In the Values box, click the arrow next to ArrDelayMinutes and change it from Sum to Average.
Do the same for DepDelayMinutes.
Resort by ArrDelayMinutes.

That changes the picture completely: WN has nowhere near the worst on-time delay average. Now we see on average XE is the worst.

Get airline data

There's just one problem: Who knows the carrier abbreviations? Which airline is XE? But of course, everything is on the web.

Go to this site with a list of the two-letter airline codes: http://www.airfarewatchdog.com/pages/3799702/airline-letter-codes/.
Select the two columns of data and copy it.
In your Excel workbook, click a blank worksheet and type AirlineCode in cell A1 and AirlineName in cell B1.
Click in cell A2 and paste the data.
Format the data as a table (Ctrl + T) and name it Airlines.
Rename the tab Airlines, too.

Relate tables in Power View

Go back to the Power View sheet in your Excel workbook.
You see the Airlines table is already in the Field List.

If you don't see it, click All in the Field List.

Make sure the bar chart is selected and remove Carrier from the Axis box.

This shows the overall average arrival and departure delay for all carriers.

Expand the Airlines table and check the AirlineName box.

Now the chart has airline names. But there's a problem: All the values are the same. Power View displays a message in the Field List: "Relationships between tables may be needed."

Click Create.
In Table, click On_Time_Performance.
In Column (Foreign), click Carrier.
In Related Table, click Airlines.
In Related Column (Primary), click. AirlineCode.

This creates a relationship between the two tables on the AirlineCode and Carrier fields.

Click OK.
Sort the table again so the ArrDelayMinutes field is sorted descending.

Look: The numbers in the table aren't all the same anymore, and we can see the names of the airlines instead of the carrier codes. So XE is the code for ExpressJet.

Filter the chart

Notice the (Blank) value in the list of airlines? Let's get rid of it by filtering the data.

With the airlines bar chart selected, in the Filters Area click Chart.

Tip: Can't see the Filters Area? On the Power View tab > View > Filters Area.

Note the Filters Area is already populated with the fields in the chart. The filters in the Chart filter will filter only this chart, and not any other visualization on the sheet.
Click AirlineName, check the (All) box, and then uncheck the (Blank) box.
(Blank) is gone from the chart.

Change the chart layout

Make sure the column chart is selected.
On the Layout tab > Legend > Show Legend at Top.

This layout makes better use of space.

With the chart selected, click the sizing handle on the right side and make the chart narrower.
Now you have room for another visualization.

Create another chart

Click the blank sheet to start another visualization.

Tip: This is key: As long as a visualization is selected, any fields you select are added to that visualization, instead of starting a new one.

In the Field List, in the On_Time_Performance table, check the Origin box.

Origin is another list of codes – these ones are for airports.

In the On_Time_Performance table, check the DepDelayMinutes box.
In the Fields box, click the arrow next to DepDelayMinutes and click Average.

The number has a lot of decimal places.

Click in the Average of DepDelayMinutes column and on the Design tab > Decrease Decimal.

Now the number has two decimal places.

On the Design tab, click the arrow under Bar Chart > Stacked Bar.
Drag the top and bottom sizing handles to show as many bars in the chart as possible.

Change the sort order of the chart

Hover over the chart to see sort by in the upper-left corner. Click Origin.

Now it's sorted by DepDelayMinutes.

Again in the upper-left corner, click asc. Now it's sorted descending instead.

Clearly JLN has the worst on-time arrival record. Where's that? Once again we have interesting data, but we can't make sense of it because we don't know most of the airport codes.

Get airport code data

Go to http://www.airportcodes.us/us-airports.htm.
On the web site, select and copy the four columns of data—Code, Name, City, and State—without the table heading, "US Airports, Sorted by Airport Code".
In Excel, click the plus (+) sign to add a blank worksheet.
Click in cell A1 and paste the data.
Rename the columns:
- Code = AirportCode
- Name = AirportName
- City = AirportCity
- State = AirportState
Format the data as a table (Ctrl + T) and name it Airports.
Rename the tab Airports, too.

Relate the Airports table to the other tables

Go back to the Power View sheet in your Excel workbook.
The Airports table is already in the Field List.

If you don't see it, click All in the Field List.
Make sure the column chart is selected and remove Origin from the Axis box.
This shows the overall average departure delay for all airports.
Expand the Airports table and check the AirportName box.

Now the chart has airline names. But again, all the values are the same and you see the message in the Field List: "Relationships between tables may be needed."

Let's try a different way to create relationships.
On the Power Pivot tab > Manage Data Model.
In the Power Pivot window, on the Home tab > Diagram View.

Now you see the three tables in your model. There's a line from On_Time_Performance to Airlines, but not to Airports.
Find the Origin field in the On_Time_Performance table, and drag from it to the AirportCode field in the Airports table.
Go back to the Power View sheet in Excel.
Click OK for the message about the changed Data Model.

The numbers in the table aren't all the same anymore, and we can see the names of the airports instead of the carrier codes.
Sort the chart descending by Average of DepDelayMinutes.

So JLN is the code for Joplin Regional Airport.

Read more about creating relationships between tables.

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.

fill handle

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.

Drill up arrow in Power View chart visualizations

Double-click November 2011.

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

More fun with airline delay data

In this tutorial, you've:

Gotten data from sources on the web.
Created relationships.
Created a variety of different charts, plus a map.
Filtered and cross-filtered data.
Created calculated fields in Power Pivot.
Worked with time data in Power Pivot and Power View.
Added drill-down to a chart.

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.

Related information

Charts and other visualizations in Power View

Filtering, highlighting, and slicers in Power View

Maps in Power View

What's new in Power View in Excel 2013 and in SharePoint Server

Power View: Explore, visualize, and present your data

Microsoft Office Tutorials

Tuesday, June 27, 2017