Data types in Power Query vs data types in Power BI column tools / properties

[YukiK]

I see that we can change data types in either Power Query or in column tools in Power BI after the data model has been loaded.

I was wondering what's the best practice around this. What I mean by that is we can change data types from text to int in Power Query but the same can be accomplished in column tools, but what are the pros and cons of using either method? Or there isn't much difference?

I don't know why there are two ways to change data types in the first place, but it would be great if you have any idea or knowledge to share on this.

Thanks!

[Stachu · Accepted solution]

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)

[v-stephen-msft]

Hi @YukiK ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Stephen Tao

[v-stephen-msft]

Hi @YukiK ,

Both the column tool and power query can change the data type. 

In power query, it is to allow you to easily modify the data type when transforming the data. Data types in Power Query are used to classify values to have a more structured dataset. Data types are defined at the field level—values inside a field are set to conform to the data type of the field.

The column tool is to allow you to easily modify the data type when modeling data and creating reports, without repeating the cumbersome operations such as PowerQuery.

The above is my understanding.😀

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jennratten · Accepted solution]

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) 

DataTypes in Power Query

Data Type Conversions and Ascribed Types

[Stachu · Accepted solution]

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)