Forum Discussion
Data types in Power Query vs data types in Power BI column tools / properties
Tables in the tablular model do not always come from Power Query. The first DAX release was in Excel Power Pivot (https://en.wikipedia.org/wiki/Data_analysis_expressions#History) and as you do not really have typed columns there there was a need to specify this in the model itself.
Personally I always assign the types in Power Query:- it is self-documenting, as there is a separate step for it
- there are some additional types e.g. duration which allows for M specific time calculations while DAX handles it as a Decimal
- you have more control over the conversion, especially for dates (e.g. when the source uses multiple date formats)
When using Power BI desktop, I always set data types in Power Query as opposed to loading untyped data to the data model. This allows Power Query to classify values into a more structured dataset which results in a more efficient data model. When using dataflows in the Power BI service, the dataflow cannot be saved until all columns have been assigned types.
That being said, there are other considerations when assigning data types, such as any other applications that may be integrated and how they will be infered. Here are some good resources:
Power BI Type Mappings (Power Query desktop, dataflows, DAX and others)
When using Power BI desktop, I always set data types in Power Query as opposed to loading untyped data to the data model. This allows Power Query to classify values into a more structured dataset which results in a more efficient data model. When using dataflows in the Power BI service, the dataflow cannot be saved until all columns have been assigned types.
That being said, there are other considerations when assigning data types, such as any other applications that may be integrated and how they will be infered. Here are some good resources:
Power BI Type Mappings (Power Query desktop, dataflows, DAX and others)