Align relationship column data types

1. Identify the data types of the columns used to define relationships.
2. If there are any mismatches, such as a text joined with whole number, implement revisions to the data types.
3. In this example, Account is a dimension table with a one-to-many relationship to the Finance fact table on the AccountKey column per Figure 145.

• Both columns store whole number values but in this case the AccountKey column from Account is defined as a text data type.
• AccountKey is stored in the model as a text data type reflecting the type of the query.

1. Use the Table.TransformColumnTypes() function to revise the type.

1. Close and apply the revised query and the data type in the model will also be revised to whole number.

• With the possible exception of the Date dimension, the relationship columns will generally be whole numbers, as this type supports both precision and slowly changing dimensions for historical tracking.
• If it is necessary to use text data types for relationship columns, either ensure the data will conform to a defined format for both fact and dimension columns, or pro-actively apply transformations to these columns to enforce a single standard.