cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YukiK
Resolver IV
Resolver IV

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

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!

2 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

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)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

jennratten
Responsive Resident
Responsive Resident

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

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

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
Community Support
Community Support

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.

1.png

 

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
Responsive Resident
Responsive Resident

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

View solution in original post

Stachu
Community Champion
Community Champion

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)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors