Configure table and column properties – Designing a Data Model

Configure table and column properties

Once we have our relationships sorted, we can take a peek at our table and column properties. In Power BI Desktop, you can view and make changes to these properties in Model view. You can multiselect columns or tables using the normal Windows shortcuts of Ctrl-clicking or Shift-clicking.

The properties pane is organized into three sections: General, Formatting, and Advanced.

The General section

There are many properties that can be set on a column or table. Probably the most common one is to hide a column or table for Report view. Think about a relationship, as we discussed in the previous sections. You will often end up with two columns that contain the same data. You will usually want to hide one or both of those columns. You can control hiding/unhiding a column or table with the toggle.

The most common table and column property operations are in the General section.

Figure 5.7 – The General section of the properties pane

Another cool feature you can set under the properties is synonyms. Synonyms let you query the model using natural language and let various business entities keep their own nouns. You may call it a purchase order number, another group may just call it a purchase, and another just an order number. Synonyms let everyone use their own natural language for querying.

Organizing your columns and measures into display folders can make it easier for report builders to find the columns and calculations they need to display on the report. This is done by simply typing in the name of the display folder. If you want a deeper path, you can separate each subfolder with a \ character.

The Formatting section

The Formatting area lets you control the formatting of the column when it is used in a report. Your choices here will be determined by the data type of the column. There are not many choices for text columns.

You can also change the data type of the column here. But be careful: if you set a column with text data to be a fixed decimal number, you will get an error.

Numeric formatting options

For numeric columns, you set the format to Currency, Decimal number, Whole number, Percentage, or Scientific. If those aren’t enough, you can specify a custom format. You can even choose whether you want a thousands separator. The actual separator chosen will be determined by the region format you have chosen for your PC.

Figure 5.8 – Numeric formatting options

Date formatting options

As with numeric data, you can set how you want your dates to be displayed. There is a format dropdown, just like for numbers, but it only contains date formatting options. If none of those work for you, you can define a custom date format.

Figure 5.9 – Date formatting options

Author: Noah Walker

Leave a Reply

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