Power View – Create table

Creating a table with Power View is easier than creating a Pivot Table. So if you don’t feel comfortable with Pivot Tables it could be a good idea to start with Power View.

 
 

Create a Data Model

Before you start Power View, your workbook must have a Data Model

PowerView_Create_Table_1The simplest way to have a data model is to click Insert > Table. This will reformat your data into an official (structured) Excel table, naming all the parts which are needed for the Data Model to be able to use it. But you can also have a data model from external database or Power Pivot.    

Creating a data model doesn’t mean you have to have the data in your workbook. When you add data from the menu Data (Access, Text, From Other Sources), you can specify to create a connection only, but also add this data to the Data Model (this is the same Model used by Power Pivot).

PowerView_Create_Table_2

PowerView_Create_Table_3

So now, even if no data is visible in your workbook, you can use it in different tools like Power View and Power Pivot (since they consume data in your Data Model.) This is made possible by the Vertipaq engine, which is what drives the Model and built into Excel 2013.

Data of your Data Model

Data of your Data Model and not in any worksheet

Open Power View

Download the following file to follow along with the example. Since this file already has a Data Model you can click on Insert > Power View

Menu Insert > Power View

Menu Insert > Power View

PowerView_Create_Table_6  A new worksheet is added in your workbook. This new worksheet is based on the SilverLight technology. It uses more memory than a classic worksheet but you can create interactive dashboards just by dragging & dropping objects on to it.

Create your first table in Power View

When your Power View worksheet opens, you can see all the fields of your differences sources of data on the right panel and also some data from your worksheets.

PowerView_Create_Table_7

You can easily see the columns which have values, as they all have a Sigma symbol before the name of the field.

The initial table is just there to show what you can do. It’s better if you remove it and create your own table via the drag and drop interface like with a Pivot Table. (To do that, select the table and press DEL.)

Now, let’s create a new table where we will display the bonus by type and by city. So you just have to drag these 3 items from the Panel to the Fields zone, and it will create a new table for you!

PowerView_Create_Table_8

  PowerView_Create_Table_10 

At this point, the table is the same as a Pivot Table but when you go to the Table menu and click Matrix you convert your table into a hierarchical presentation

PowerView_Create_Table_11

Drill-Down / Drill-Up

PowerView_Create_Table_12Once your have converted your table to a matrix, you can also convert it to let you drill between the fields of your table, allowing you to see the records that make up the totals.  To do this, go to Show Levels > Rows – Enable Drill Down one level at the Time.

PowerView_Create_Table_13Your table will change to present only the first level of your table (removing all the Type fields.)

If you double-click the City name, or if you click on the arrow, the table will now drill in to show you the Type that makes up the City values!  (Bangkok shown here.)

PowerView_Create_Table_14And if you want to drill back up, you just have to click on the arrow close to the word ‘Flat’

.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>