Tables in Power BI reports and dashboards

APPLIES TO: noPower BI service for consumers yesPower BI service for designers & developers yesPower BI Desktop noRequires Pro or Premium license

Note

These visuals can be created and viewed in both Power BI Desktop and the Power BI service. The steps and illustrations in this article are from Power BI Desktop.

A table is a grid that contains related data in a logical series of rows and columns. It may also contain headers and a row for totals. Tables work well with quantitative comparisons where you're looking at many values for a single category. For example, this table displays five different measures for Category.

Screenshot of a table that displays five different measures for Category.

Create tables in reports and cross-highlight elements within the table with other visuals on the same report page. You can select rows, columns, and even individual cells and cross-highlight. You can also copy and paste individual cells and multiple cell selections into other applications.

When to use a table

Tables are a great choice:

  • To see and compare detailed data and exact values (instead of visual representations).

  • To display data in a tabular format.

  • To display numerical data by categories.

Prerequisite

This tutorial uses the Retail Analysis sample PBIX file.

  1. From the upper left section of the menubar, select File > Open

  2. Find your copy of the Retail Analysis sample PBIX file

  3. Open the Retail Analysis sample PBIX file in report view Screenshot of the report view icon..

  4. Select Screenshot of the yellow tab. to add a new page.

Note

Sharing your report with a Power BI colleague requires that you both have individual Power BI Pro licenses or that the report is saved in Premium capacity.

Create a table

You'll create the table pictured at the beginning of the article to display sales values by item category.

  1. From the Fields pane, select Item > Category.

    Power BI automatically creates a table that lists all the categories.

    result of adding Category

  2. Select Sales > Average Unit Price and Sales > Last Year Sales

  3. Then select Sales > This Year Sales and select all three options: Value, Goal, and Status.

  4. In the Visualizations pane, locate the Values well and select the values until the order of your chart columns matches the first image on this page. Drag the values in the well if needed. Your Values well will look like this:

    Values well

Format the table

There are many ways to format a table. Only a few are covered here. A great way to learn about the other formatting options is to open the Format pane (paint roller icon paint roller) and explore.

  • Try formatting the table grid. Here you'll add a blue vertical grid, add space to the rows, and increase the outline and text size.

    Grid card

    table showing results

  • For the column headers, change the background color, add an outline, and increase the font size.

    Column headers card

    headers formatting in table

  • You can even apply formatting to individual columns and column headers. Start by expanding Field formatting and selecting the column to format from the drop-down. Depending on the column values, Field formatting lets you set things like: display units, font color, number of decimal places, background, alignment, and more. Once you've adjusted the settings, decide whether to apply those settings to the header and totals row as well.

    Field formatting for This year sales

    Field formatting for This year sales in the table

  • After some additional formatting, here is our final table.

    table with all formatting so far

Conditional formatting

Conditional formatting is one type of formatting. Power BI can apply conditional formatting to any of the fields that you added to the Values well of the Visualizations pane.

Visualization pane

With conditional formatting for tables, you can specify icons, URLs, cell background colors, and font colors based on cell values, including using gradient colors.

  1. In the Format pane, open the Conditional formatting card.

    Conditional formatting card

  2. Select a field to format, and turn the slider for Background color to On. Power BI applies a gradient based on the values in the column. To change the default colors, select Advanced controls.

    If you select the Diverging option, you can configure an optional Center value as well.

    Background color scales screen

    Let's apply some custom formatting to our Average Unit Price values. Select Diverging, add some colors, and select OK.

    table showing diverging colors

  3. Add a new field to the table that has both positive and negative values. Select Sales > Total Sales Variance.

    shows a new field far right

  4. Add data bar conditional formatting by turning the Data bars slider to On.

    Conditional formatting card with data bars set to On

  5. To customize the data bars, select Advanced controls. In the dialog that appears, set colors for Positive bar and Negative bar, select the Show bar only option, and make any other changes you'd like.

    checkmark for Show bar only

  6. Select OK.

    Data bars replace the numerical values in the table, making it easier to scan.

    same table but with bars in last column

  7. Add visual cues to your table with conditional icons. In the Conditional formatting card, select This year sales from the dropdown. Turn the Icons slider to On. To customize the icons, select Advanced controls.

    Table with Icons added

Copy values from Power BI tables for use in other applications

Your table or matrix may have content that you'd like to use in other applications, like Dynamics CRM, Excel, and even other Power BI reports. In Power BI, when you right-click inside a cell, you can copy the data in a single cell or a selection of cells onto your clipboard, and paste it into the other applications.

To copy the value of a single cell:

  1. Select the cell you want to copy.

  2. Right-click inside the cell.

  3. Select Copy > Copy value.

    copy options

    With the unformatted cell value on your clipboard, you can paste it into another application.

To copy more than a single cell:

  1. Select a range of cells or use Ctrl to select one or more cells.

  2. Right-click inside one of the cells you selected.

  3. Select Copy > Copy selection.

    copy options

Adjust the column width of a table

Sometimes Power BI will truncate a column heading in a report and on a dashboard. To show the entire column name, hover over the space to the right of the heading to reveal the double arrows, select, and drag.

video closeup of resizing column

Considerations and troubleshooting

  • When applying column formatting, you can only choose one alignment option per column: Auto, Left, Center, Right. Usually, a column contains all text or all numbers, and not a mix. In cases where a column contains both numbers and text, Auto will align left for text and right for numbers. This behavior supports languages where you read left-to-right.

  • If the text data in your table's cells or headers contain new line characters, those characters will be ignored unless you toggle on the 'Word Wrap' option in the element's associated formatting pane card.

Next steps