Text.At

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Text.At

Understanding Text.At Function

The Text.At function is used to extract a character from a text string at a specified position. The function takes two arguments: the text string and the position of the character to be extracted. The position argument can be a number or a dynamic value.

The syntax of the Text.At function is as follows:

Text.At(text as text, position as number) as text

The function returns a single character as a string.

The M Code Behind the Text.At Function

The Text.At function is a simple function that can be easily implemented in M code. The following M code demonstrates the logic behind the Text.At function:

let

TextAt = (text as text, position as number) =>

let

len = Text.Length(text),

char = if position >= 0 and position <= len then Text.Range(text, position, 1) else ""

in

char

in

TextAt

The M code defines a custom function called TextAt that takes two arguments: the text string and the position of the character to be extracted. The function then calculates the length of the text string and checks if the specified position is within the bounds of the text string. If the position is valid, the function uses the Text.Range function to extract the character at the specified position. If the position is invalid, the function returns an empty string.

Examples of Using Text.At Function

The Text.At function can be used in a variety of scenarios. Here are some examples of how the function can be used:

Example 1: Extracting the First Character of a Text String

Suppose you have a text string that contains a person’s name, and you want to extract the first character of the name. You can use the Text.At function to achieve this. Here is the M code to extract the first character of a text string:

let

Source = "John Smith",

FirstChar = Text.At(Source, 0)

in

FirstChar

The M code defines a variable called Source that contains the text string “John Smith”. The Text.At function is then used to extract the first character of the text string, which is “J”.

Example 2: Extracting Characters at Dynamic Positions

Suppose you have a dataset that contains a column of text strings, and you want to extract characters from each string at dynamic positions. You can use the Text.At function in conjunction with other M functions to achieve this. Here is the M code to extract characters from a column of text strings at dynamic positions:

let

Source = #table({"Text"}, {{"John Smith"}, {"Jane Doe"}, {"Mike Johnson"}}),

Positions = {0, 1, 2},

ExtractedChars = Table.AddColumn(Source, "ExtractedChars", each Text.At([Text], Positions{[Index]}))

in

ExtractedChars

The M code defines a table called Source that contains a column of text strings. The Positions variable contains an array of positions to extract from each text string. The Table.AddColumn function is then used to add a new column to the table that extracts characters from each text string at dynamic positions.

The Text.At function is a powerful and versatile function available in the M language. It can be used to extract characters from text strings at specific positions and can be used in a variety of scenarios. Understanding the M code behind the Text.At function can help you better optimize your data transformation workflows 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)