Value.FromText

Value.FromText is a Power Query M function that decodes a text value and interprets it as a value with an appropriate type, such as a number, logical value, null value, datetime value, duration value, or text value. The function returns the decoded value, and an optional culture parameter may also be provided.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Value.FromText(
   text as any,
   optional culture as nullable text,
) as any

Argument	Attribute	Description
text
culture	optional	The culture argument enables the specification of a Culture code (e.g., “nl-NL” or “en-US”) to align transformations with local formatting conventions. If this argument is omitted, functions default to Culture.Current, which reflects the system’s regional settings.

Description

Decodes a value from a textual representation, text, and interprets it as a value with an appropriate type. Value.FromText takes a text value and returns a number, a logical value, a null value, a datetime value, a duration value, or a text value. The empty text value is interpreted as a null value. An optional culture may also be provided (for example, “en-US”).

Examples

You’re working with a table that contains a single column of text values. Here’s what it looks like:

You can use the Value.FromText function to convert these values into a type it recognizes. You can add the following formula to the above table in a separate column called ‘ValuesFromText’:

Value.FromText( [Column1] )

To inspect the type Power Query returns, we can store the type of value in a table type and return its information using the Type.TableSchema function. Here’s the logic:

let
  // Retrieve the type of the value stored in the field [ValuesFromText]
  Valuetype = Value.Type([ValuesFromText]),

  // Define a table type with a single column "Col1" that has the same type as Valuetype
  TableType = type table [Col1 = Valuetype],

  // Get the schema of the defined table type
  TypeSchema = Type.TableSchema(TableType),

  // Extract the type name of the first column (Col1) from the schema
  TypeReturned = TypeSchema[TypeName]{0} 
in 
  // Return the extracted type name
  TypeReturned

When we store this logic in a column called TypeAsText, the outcome of this operation is:

To try this yourself, you can paste the following query into the Advanced Editor:

let
  Source = #table(
    type table [Column1 = text], 
    {
      {"250"}, 
      {"true"}, 
      {"null"}, 
      {"29-06-1992"},
      {"29-06-1992 15:05"},
      {"15:05"},
      {"29-06-1992 15:05 +02:00"},
      {""},
      {"4.05:10:00"}, 
      {"Dog"}
    }
  ),

  // Add a new column "ValuesFromText" by converting text values using Value.FromText
  Add_Values = Table.AddColumn(
    Source, 
    "ValuesFromText", 
    each Value.FromText([Column1]), 
    type any
  ),

  // Add a new column "TypeAsText" to extract the type name of each value
  AddType = Table.AddColumn(
    Add_Values, 
    "TypeAsText", 
    each 
      let
        Valuetype = Value.Type([ValuesFromText]),
        TableType = type table [Col1 = Valuetype],
        TypeSchema = Type.TableSchema(TableType),
        TypeReturned = TypeSchema[TypeName]{0}
      in 
        TypeReturned, 
    type text
  )
in
  AddType

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