cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gazius Frequent Visitor
Frequent Visitor

Column Type Data Type clarification in Power Query

I'm trying to figure out what data types are available for the Column Type field, and I've yet to come across anything in the documententation that helps with my question. I'm trying to add a column, and I want to specify the column type to always come back as an integer, rather than a decimal number.  However, by default the column type comes back as "number". Is there a way to figure out what are valid options for that field, as Int64 and variations on how to spell or call it don't seem to be accepted.

 

For reference, I'm looking at https://msdn.microsoft.com/en-us/library/mt260727.aspx which mentions optional column type but provides no links to what those might be.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ChrisHaas Established Member
Established Member

Re: Column Type Data Type clarification

Ahhh, in that case, add this to the end of the code in the Advanced Editor for that step:

 

, Int64.Type

 

other common types you'll use are:

 

"type text"

"type date"

"type datetime"

"type number"

Currency.Type

Percentage.Type

 

An easy way to see what the exact M code is to create a temporary step that changes the data type.  Then you'll see in the Advanced Editor exactly how M writes it (type number vs. Int64.Type...never figured out why).  Then you can simply use that same spelling as the 3rd parameter of Table.AddColumn().

View solution in original post

4 REPLIES 4
ChrisHaas Established Member
Established Member

Re: Column Type Data Type clarification

If you want your measure to return a sum with no decimals, you can simply use:

 

[Measure] := SUM( TableName[ColumnName] )

 

Then go to the Modeling tab and choose "Whole Number" as the formatting option.

 

You can also change the data type of columns in Query Editor.

Gazius Frequent Visitor
Frequent Visitor

Re: Column Type Data Type clarification

Chris, sorry if I wasn't clear but I'm working with M, not DAX. 

 

When I'm using something like Table.AddColumn, the third argument is an optional column type. I want to specify that column type to be Int64 rather than number. 

Highlighted
ChrisHaas Established Member
Established Member

Re: Column Type Data Type clarification

Ahhh, in that case, add this to the end of the code in the Advanced Editor for that step:

 

, Int64.Type

 

other common types you'll use are:

 

"type text"

"type date"

"type datetime"

"type number"

Currency.Type

Percentage.Type

 

An easy way to see what the exact M code is to create a temporary step that changes the data type.  Then you'll see in the Advanced Editor exactly how M writes it (type number vs. Int64.Type...never figured out why).  Then you can simply use that same spelling as the 3rd parameter of Table.AddColumn().

View solution in original post

ChrisHaas Established Member
Established Member

Re: Column Type Data Type clarification

So the M language has recently standardized the syntax on these data types:

 

Text.Type

DateTime.Type

Date.Type

Time.Type

Number.Type

 

for the above list, you can still use "type text" or "type datetime" (the original list I provided).  

 

Int64.Type, Percentage.Type, and Currency.Type only work with this formal Name.Type structure.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 55 members 1,294 guests
Please welcome our newest community members: