Type Conversion

Updated on
Rick de Groot

Data types are an important element of the M language. While often imported along with tables from databases, Power Query is also equipped with the capability to automatically recognize the data types of your values.

There are however times when you need to convert a value from one type to another. One such scenario may be to convert values to type text so that you can concatenate them into a single string. Beyond this, several other conversions are available to the user. For instance, you can extract a date from a string of text or transform a boolean value—a simple true or false—into a numerical representation.

Regardless of the scenario, knowing how to perform type conversion in Power Query is a useful skill.

Data Type Conversion Matrix

To guide you through the possibilities of type conversion, you can use the conversion matrix provided below. This matrix outlines the possible conversions and their possible side effects. It’s important to note that while some conversions are possible without losing information others may lead to a loss of detail. On the other hand, some conversions can enrich your data by adding additional information.

Power Query M Data Type Conversion Matrix

Type Conversion Examples

Below you can find examples of how different kind of values convert to other types. You can see the results in the provided images, or use the provided code to inspect it yourself.

Decimal Conversion to Other Types

Suppose you have a decimal value of 1.994433. You can convert this value to different data types with the following values as a result:

Conversion-of-Decimal-to-other-data-types-in-Power-Query-M

To inspect the conversion yourself, you can use the below query: 

let
    Value = 1.994433,
    DataType = Decimal.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Currency Conversion to Other Types

Suppose you have a currency value of 1.9944. You can convert this value to different data types with the following values as a result:

Conversion From Currency to other data types in Power Query M

To inspect the conversion yourself, you can use the below query: 

let
    Value = 1.9944,
    DataType = Currency.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Whole Number Conversion to Other Types

Suppose you have a whole number value of 2. You can convert this value to different data types with the following values as a result:

Conversion From Whole Number to other data types in Power Query M

To inspect the conversion yourself, you can use the below query: 

let
    Value = 2,
    DataType = Int64.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Percentage Conversion to Other Types

Suppose you have a percentage value of 5.688%. You can convert this value to different data types with the following values as a result:

Conversion From Percentage to other data types in Power Query

To inspect the conversion yourself, you can use the below query: 

let
    Value = 0.05688,
    DataType = Percentage.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Date/Time Conversion to Other Types

Suppose you have a DateTime value of #datetime( 2024, 12, 31, 13, 30, 20) which represents December 12, 2024 at 13:30:20. You can convert this value to different data types with the following values as a result:

Conversion From DateTime to other data types in Power Query

To inspect the conversion yourself, you can use the below query: 

let
    Value = #datetime( 2024, 12, 31, 13, 30, 20),
    DataType = DateTime.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Date Conversion to Other Types

Suppose you have a Date value of #date(2024, 12, 31) which represents December 12 2024. You can convert this value to different data types with the following values as a result:

Conversion From Date to other data types in Power Query M

To inspect the conversion yourself, you can use the below query: 

let
    Value = #date( 2024, 12, 31 ),
    DataType = Date.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Time Conversion to Other Types

Suppose you have a Time value of #time(13,45,5) which represents 13:45:05. You can convert this value to different data types with the following values as a result:

Conversion From Time to other data types in Power Query M

To inspect the conversion yourself, you can use the below query: 

let
    Value = #time(13, 45, 5),
    DataType = Time.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

DateTimeZone Conversion to Other Types

Suppose you have a DateTimeZone value you want to convert. Let’s say #datetimezone(2023, 12, 31, 13, 45, 5, 2, 0) which represents December 12, 2024 at 13:45:05 +2:00. You can convert this value to different data types with the following values as a result:

Conversion From DateTimeZone to other data types in Power Query M

To inspect the conversion yourself, you can use the below query: 

let
    Value = #datetimezone(2023, 12, 31, 13, 45, 5, 2, 0),
    DataType = DateTimeZone.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Duration Conversion to Other Types

Suppose you have a Duration value of #duration(40, 5, 30, 10) which is 40 days, 5 hours, 30 seconds and 10 seconds. You can convert this value to different data types with the following values as result:

Conversion From Duration to other data types in Power Query M

To inspect the conversion yourself, you can use the below query: 

let
    Value = #duration( 40, 5, 30, 10 ),
    DataType = Duration.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Text Conversion to Other Types

Text values can be converted to any data type. However, to successfully convert to a type the values may need to have a specific format. The below example shows some arbitrary text values that can be converted to the different types with the following values as a result:

Conversion From Text to other data types in Power Query M

To inspect the conversion yourself, you can use the below query: 

let 
  myCulture = "en-US",
  result = 
#table (
  type table [
    Decimal =      number, 
    Currency =     Currency.Type, 
    Whole Number = Int64.Type, 
    Percentage =   Percentage.Type, 
    DateTime =     DateTime.Type, 
    Date =         Date.Type, 
    Time =         Time.Type, 
    DateTimeZone = DateTimeZone.Type, 
    Duration =     Duration.Type, 
    Text =         Text.Type, 
    TrueFalse =    Logical.Type
  ], 
  {
    {
      Decimal.From ( "1.2222", myCulture ), 
      Currency.From ( "1.2222", myCulture ), 
      Int64.From ( "125" ), 
      Percentage.From ( "1.2222", myCulture ), 
      DateTime.From ( "2023-12-31 12:30:15", myCulture ), 
      Date.From ( "2024-12-31", myCulture ), 
      Time.From ( "21:30:05" ), 
      DateTimeZone.From ( "2023-12-31 12:30:15 +2", myCulture ), 
      Duration.From ( "2.05:55:20" ), 
      Text.From ( "MyText" ), 
      Logical.From ( "true" )
    }
  }
)
in
  result

True/False Conversion to Other Types

Suppose you have a Boolean value of true. You can convert this value to different data types with the following values as a result:

Conversion From Logical to other data types in Power Query M

To inspect the conversion yourself, you can use the below query: 

let
    Value = true,
    DataType = Logical.Type,
    Source = #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { Value }, 11) }  ),
    ChType = Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, DataType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Suppose you have a decimal value of 1.9944433. You can transform this value to different data types with the following values as result:

To inspect the conversion yourself, you can use the below query: 

let
    Source = let val = 1.9944 in  #table( {"Decimal", "Currency", "Whole Number", "Percentage", "Date/Time", "Date", "Time", "DateTimeZone", "Duration", "Text", "True/False"}, { List.Repeat( { val }, 11) }  ),
    ChType = let myType = Currency.Type in Table.TransformColumnTypes(Source, List.Transform( Table.ColumnNames( Source ), each {_, myType } ) ),
    Conversion = Table.TransformColumnTypes(ChType,{{"Decimal", type number}, {"Currency", Currency.Type}, {"Whole Number", Int64.Type}, {"Percentage", Percentage.Type}, {"Date/Time", type datetime}, {"Date", type date}, {"Time", type time}, {"DateTimeZone", type datetimezone}, {"Duration", type duration}, {"Text", type text}, {"True/False", type logical}}),
    TurnToRecord = Table.SingleRow( Conversion )
in
    TurnToRecord

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/type-conversion

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy