Lines.ToText

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Lines.ToText

What is Lines.ToText?

The Lines.ToText function is a built-in M function in Power Query that converts a list of text strings into a single text string, separated by line breaks. The syntax for the function is:

Lines.ToText(list as list, optional delimiter as nullable text) as text

The function takes a list of text strings as its first argument and an optional delimiter as its second argument. If no delimiter is specified, line breaks are used by default.

Understanding the M Code Behind Lines.ToText

To better understand the M code behind the Lines.ToText function, let's take a look at an example. Suppose we have the following table in Power Query:

| Column1 |

|---------|

| Hello |

| World |

| Power |

| Query |

We can use the Lines.ToText function to convert the table into a single text string as follows:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjIwMjJR0lFIyM1NTE2NjcwVdQyNLBQsFqTmJ1ZHhCQlpiTWJycoVIWnFqQlJqWkpJSlJyfFIXqk6sLk5MSwQA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

ToList = Source[Column1],

LinesToText = Lines.ToText(ToList),

Output = LinesToText

in

Output

Let's break down the M code step by step:

1. We start by defining the source of our data. In this case, it is a table with a single column called "Column1".

2. We then extract the values from "Column1" into a list using the "ToList" variable.

3. Finally, we apply the Lines.ToText function to the list of values to create a single text string with line breaks separating each value.

Using Delimiters with Lines.ToText

As mentioned earlier, the Lines.ToText function also allows us to specify a delimiter to separate the text strings. Let's take a look at an example.

Suppose we have the following table in Power Query:

| Column1 | Column2 |

|---------|---------|

| Hello | World |

| Power | Query |

We can use the Lines.ToText function with a comma delimiter to convert the table into a single comma-separated text string as follows:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjIwMjJR0lFIyM1NTE2NjcwVdQyNLBQsFqTmJ1ZHhCQlpiTWJycoVIWnFqQlJqWkpJSlJyfFIXqk6sLk5MSwQA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),

ToList = Table.ToRows(Source),

ConvertToList = List.Transform(ToList, each Text.Combine(_, “,”)),

LinesToTextWithDelimiter = Lines.ToText(ConvertToList, “,”),

Output = LinesToTextWithDelimiter

in

Output

Let's break down the M code step by step:

1. We start by defining the source of our data. In this case, it is a table with two columns called "Column1" and "Column2".

2. We then extract the values from both columns into a list using the "ToList" variable.

3. We use the List.Transform function to convert each row of the table into a single text string with a comma separator.

4. Finally, we apply the Lines.ToText function with a comma delimiter to the list of values to create a single comma-separated text string.

In this article, we explored the M code behind the Power Query M function Lines.ToText. We learned that the function converts a list of text strings into a single text string, separated by line breaks or a specified delimiter. We also walked through examples of how to use the function in Power Query. By understanding the M code behind the Lines.ToText function, we can better leverage the power of Power Query for our data transformation needs.

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)