Value.FromText

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Value.FromText

Understanding Value.FromText Function

The Value.FromText function is used to convert a text value to a number, date, or time value. It takes one argument, which is the text value that needs to be converted. The function returns the value in the desired data type based on the format of the text value.

The syntax for the Value.FromText function is as follows:

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

The `text` parameter is mandatory and represents the text value that needs to be converted. The `culture` parameter is optional and represents the culture-specific information that can be used to convert the text value.

M Code Behind Value.FromText Function

The M code behind the Value.FromText function is as follows:

let

Source = (text as text, optional culture as nullable text) =>

let

defaultCulture = "en-US",

cultureInfo = if culture <> null then Culture.FromName(culture) else Culture.FromName(defaultCulture),

result = if cultureInfo = null then Number.FromText(text)

else try Number.FromText(text, cultureInfo) otherwise null

in

result

in

Source

The M code consists of a function named `Source` that takes two parameters, `text` and `culture`. The code first checks if the `culture` parameter is null or not. If the `culture` parameter is not null, it uses the `Culture.FromName` function to get the culture-specific information. If the `culture` parameter is null, it uses the default culture, which is “en-US.”

The code then uses the `Number.FromText` function to convert the `text` parameter to a number value. If the culture-specific information is available, the `Number.FromText` function uses it to convert the text value; otherwise, it uses the default culture.

Finally, the code returns the converted value as the result. If the conversion fails, the function returns null.

Usage of Value.FromText Function

The Value.FromText function can be used in various scenarios when working with text data. Here are some examples:

Converting Text to Number

Suppose you have a text value “1234” that needs to be converted to a number. You can use the Value.FromText function as follows:

Value.FromText("1234")

The function will return the number value 1234.

Converting Text to Date

Suppose you have a text value “2022-09-30” that needs to be converted to a date. You can use the Value.FromText function as follows:

Value.FromText("2022-09-30", "en-US")

The function will return the date value September 30, 2022, in the format specified by the default culture “en-US.”

Converting Text with Custom Format to Number

Suppose you have a text value “1,234.56” that needs to be converted to a number. You can use the Value.FromText function with a custom format as follows:

Value.FromText("1,234.56", "en-US", {"en-US"}, "Number.Decimal")

The function will return the number value 1234.56, with the format specified by the “Number.Decimal” format string.

The Value.FromText function is a powerful tool in Power Query that can be used to convert text data to various data types. The M code behind the function is straightforward and can be used as a reference when building custom functions. By understanding the function’s syntax and usage, users can effectively transform their data and perform complex analysis in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)