Important: In Excel for Microsoft 365 and Excel 2021, Power View is removed on October 12, 2021. As an alternative, you can use the interactive visual experience provided by Power BI Desktop, which you can download for free. You can also easily Import Excel workbooks into Power BI Desktop.
If you are using Power View, you can set table behavior properties to change the grouping behavior of detail rows and provides a better default placement of identifying information (such as names, photo IDs, or logo images) in tile containers, cards, and charts.
-
In the ribbon, go to Power Pivot > Manage.
Notes:
-
If you don't have Power Pivot enabled, then go to File > Options > Add-ins. Select the COM Add-ins option from the Manage drop-down box, then press Go. Select Microsoft Power Pivot for Excel, and press OK.
-
If you don't see Power Pivot in the list, then see: Where is Power Pivot?
-
-
Select the table you want to configure at the bottom of the Power Pivot window.
-
Click Advanced > Table Behavior.
-
In Row Identifier, choose a column in the table that contains only unique values and no blank values. Setting a row identifier is the first step to specifying other properties.
-
In Keep Unique Rows, select columns that should be treated as unique even if they are duplicates (for example, employee first name and last name, for cases where two or more employees share the same name).
-
The Default Label column provides a display name to represent row data (for example, employee name in an employee record).
In some Power View visualizations such as cards, the default label is displayed in a larger font. If you’ve also set a default image, the default label appears under the default image in the tabs of a tile container.
-
In Default Image, select a column provides an image that represents the row data (for example, a photo ID in an employee record).
In this article
Why set table behavior properties?
Power View groups items automatically based on the fields and presentation format you’re using. In most cases, the default grouping produces an optimum result. But for some tables, primarily those that contain detail data, the default grouping behavior will sometimes group rows that should not be grouped (for example, employee or customer records that should be listed individually, particularly when two or more people share the same first and last name). For such tables, you can set properties that cause rows to be listed individually rather than grouped together.
Note: Don’t change the default behavior on tables that act as a lookup table (such as a date table, a product category table, or a department table, where the table consists of a relatively small number of rows and columns), or summary tables containing rows that are only interesting when summarized (for example, census data that rolls up by gender, age, or geography). For lookup and summary tables, the default grouping behavior produces the best result.
Default fields
You can set which fields are added to Power View automatically, if someone clicks the table name in the Power View field list. A table could have a lot of fields, but some of them are probably used more often than others.
-
In the Power Pivot window > Advanced tab > Default Field Set.
-
Select a field in Fields in the table and click Add.
-
It is moved to Default fields, in order.
-
After you have all the fields you want, you can change the order in which they appear. Select a field and click Move Up or Move Down.
The Row Identifier property
Within the table, the row identifier specifies a single column that contains only unique values and no blank values. The Row Identifier property is used to change grouping so a group is not based on a row’s field composition, but rather on a fixed column that is always used to uniquely identify a row, regardless of the fields used in a particular report layout.
Setting this property changes the default grouping behavior from dynamic grouping based on the columns present in the view, to a fixed grouping behavior that summarizes based on the row identifier. Changing the default grouping behavior is relevant for report layouts, such as a matrix, that would otherwise group (or show subtotals) for each column in the row.
Setting a row identifier enables the following additional properties: Keep Unique Rows property, Default Label property, and Default Image property, each of which affects field behavior in Power View.
You can also use Row Identifier by itself, as a standalone property, to enable the following:
-
Use of binary images in a report. By removing ambiguity around row uniqueness, Power View can determine how to assign default images and default labels for a given row.
-
Remove unwanted subtotals from a matrix report. Default grouping at the field level creates a subtotal for each field. If you only want a single subtotal that is calculated at the row level, setting the Row Identifier produces this result.
You cannot set a Row Identifier for tables marked as date tables. For date tables, the row identifier is specified when you mark the table. For more information, see Mark as Date Table Dialog Box.
The Keep Unique Rows property
This property allows you to specify which columns convey identity information (such as an employee name or a product code) in way that distinguishes one row from another. In cases where rows appear to be identical (such as two customers with the same name), the columns you specify for this property repeat in the report table.
Depending on which columns you add to a report, you might find rows that are treated as identical rows because the values in each row appear to be the same (for example two customers named Jon Yang). This might occur because other columns that provide differentiation (such as a middle name, address, or birth date) are not in the report view. In such a scenario, the default behavior is to collapse the identical rows into a single row, summarizing any calculated values into a single larger result from the combined rows.
By setting the Keep Unique Rows property, you can designate one or more columns that should always repeat, even if there are duplicate instances, whenever you add that column to the report view. Calculated values associated with the row will now be allocated based on each individual row rather than rolled up to single row.
Note: Because the columns the end user selects can affect the grouping, which changes the filter context for expression calculations, model designers must take care to create measures that return the correct results.
Default labels
This property specifies a label that appears prominently in a card or chart, or together with the default image in the navigation strip of a tile report. When used with a default image, the default label appears under the image. When choosing a default label, pick the column that conveys the most information about the row (for example, a name).
In the tab strip layout for a tile container, with the navigation strip across the top, the default label appears in the title area below an image, as defined by the Default Image property. For example, if you have a list of employees, you might tile employee information, using their photo ID as the default image and Employee Name as the default label. The default label column always appears under the image in the tab strip navigation of a tiles container, even if you do not explicitly select it in the report field list.
In the cover flow layout of a tile container, with the navigation across the bottom of the tiles, the default image appears without the default label.
In a card layout, the default label appears in a larger font in the title area at the top of each card. For example, if you have a list of employees, you might make cards with employee information, using their photo ID as the default image and Employee Name as the default label.
Default images
This property specifies an image that is displayed by default in the tab strip navigation of a tile report, or prominently under the default label at the left of a card. A default image should be visual content. Examples include a photo ID in the employees table, a customer logo in a customer table, or a country shape in a geography table.
Note: Images can be sourced from URL addresses to an image file on a web server, or as binary data embedded in the workbook. If the image is based on a URL, be sure to also set the column as an image type so that Power View retrieves the image rather than displaying the URL as text data in the report.
Optimizing for specific layouts
This section describes the effect of setting table behavior properties from the standpoint of a particular presentation format and characteristics of the data. If you are trying to fine tune the layout of a matrix report, for example, you can use this information to understand how to improve a matrix presentation using table behavior properties in the model.
Images are missing
Properties you set in the model determine whether images are visualized in a report, or represented as text values in the report. In the following example, images are missing from the report. Where an image should appear, the URL location of the image file appears instead. This behavior occurs because text in the model is interpreted as text in the report. You can change this behavior by setting properties on a column that contains an image URL. The property instructs Power View to use the column value as a URL rather than display it as text.
To indicate a column contains image URLs
-
In the PowerPivot window > Advanced tab > Reporting Properties section, check to see that Data Category is set to Image URL (Suggested).
-
If not, click the drop-down arrow for Data Category > More Categories> Image URL.
To indicate a column contains binary images
-
In the PowerPivot window > Advanced tab > Reporting Properties section> Table Behavior.
-
For Row Identifier, select a column containing unique values.
Tables are missing one or more rows
Sometimes the default grouping behavior results in an outcome that is opposite of what you intended; specifically, detail rows that are present in the model do not appear in the report. By default, Power View groups on the columns you add to the view. If you add Country Name to the report, each country appears once in the view, even though the underlying table might contain thousands of rows that include multiple instances of each country name. In this case, the default grouping behavior produces the correct result.
However, consider a different example where you might want multiple instances of a row to appear, because in fact the underlying rows contain data about different entities. In this example, assume you have two customers both named Jon Yang. Using the default grouping behavior, only one instance of Jon Yang will appear in the report. Moreover, because only one instance appears in the list, the measure Annual Income is the sum of that value for both customers. In this situation, where customers who share the same name are actually different people, the default grouping behavior produces an incorrect result.
To change the default grouping behavior, set the Row Identifier and Keep Unique Rows properties. In Keep Unique Rows, choose the Last Name column so this value is repeated for a row, even if it already appears in a different row. After you change the properties and republish the workbook, you can create the same report, only this time you will see both customers named Jon Yang, with the Annual Income correctly allocated to each one.
Matrix layout is too crowded
When you present a detail table in a matrix, the default grouping provides a summarized value for each column. Depending on your objectives, this might be more summarizations than you want. To change this behavior, you can set Row Identifier. No additional properties need to be set; just setting row identifier is sufficient to change the grouping so that summarizations are calculated for each row based on its unique row identifier.
Compare the following before and after images that show the effect of setting this property for a matrix layout.
Before: Default grouping based on fields in matrix
Chart showing too many items and levels on the axis
Chart reports that show detail data should use the row identifier as an axis. Without a row identifier, the axis is indeterminate, resulting in a best-guess layout that might not make sense. To change this behavior, you can set Row Identifier. No additional properties need to be set; just setting row identifier is sufficient to change the grouping so that summarizations are calculated for each row based on its unique row identifier.
Compare the following before and after images that show the effect of setting this property for a chart layout. It is the same report, with identical fields and presentation. The only difference is the bottom image shows a report after Row Identifier was set on the Items table.
Next Steps
After you have evaluated the tables in your model and set table behavior properties on those containing detail rows that should always appear as individual items, you can further optimize the model through additional properties or settings. For more information about Power View, see Tutorial: Optimize your Data Model for Power View reporting.
Notes: Video copyrights:
-
Olympics Dataset © Guardian News & Media Ltd.
-
Flag images courtesy of CIA Factbook (cia.gov)
-
Population data courtesy of UNData (data.un.org ) on Microsoft Azure Marketplace
-
Olympic Sport Pictograms by Thadius856 and Parutakupiu, distributed according to the license on Wikimedia Commons (creativecommons.org/licenses/by-sa/3.0/)
See Also
PowerPivot: Powerful data analysis and data modeling in Excel
Tutorial: PivotTable data analysis using a Data Model in Excel 2013
Power View and PowerPivot videos
