Lines.FromText

D

Back to top

T

Back to top

#

Back to top

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

Understanding Lines.FromText

Lines.FromText is a Power Query M function that splits text into lines. When you apply Lines.FromText to a text value, it returns a list of text values, where each value represents a line in the original text. Here’s the basic syntax for the Lines.FromText function:

Lines.FromText(text as text, optional delimiter as nullable text)

The first argument, text, is the text value you want to split into lines. The second argument, delimiter, is an optional argument that specifies the character or string that separates the lines in the original text.

The M Code Behind Lines.FromText

To understand the M code behind Lines.FromText, let's take a look at a simple example. Suppose you have the following text value:

“apple#(lf)banana#(lf)orange#(lf)”

This text value represents a list of fruits, where each fruit is on a separate line. To split this text into lines, you can apply Lines.FromText as follows:

Lines.FromText(“apple#(lf)banana#(lf)orange#(lf)”)

When you apply this function, it returns a list of text values:

{“apple”, “banana”, “orange”}

Here's the M code behind Lines.FromText:

let

splitText = Text.Split(text, delimiter),

removeEmptyLines = List.Select(splitText, each _ <> “”),

removeNullLines = List.Select(removeEmptyLines, each _ <> null),

output = removeNullLines

in

output

Let's break down this code step by step:

1. The Text.Split function splits the text value into a list of text values, using the delimiter specified (or the default delimiter, which is the line break character).

2. The List.Select function removes any empty lines from the list.

3. The List.Select function removes any null lines from the list.

4. The final output is the resulting list of non-empty, non-null lines.

Using Lines.FromText to Extract Data

Now that you understand the M code behind Lines.FromText, let's see how you can use it to extract data from text values. Suppose you have the following text value:

“Name: John DoenAge: 35#(lf)Occupation: Engineer#(lf)”

This text value represents a person's information, where each line represents a different attribute (name, age, occupation). To extract this information, you can apply Lines.FromText as follows:

let

source = “Name: John Doe#(lf)Age: 35#(lf)Occupation: Engineer#(lf)”,

splitLines = Lines.FromText(source),

splitAttributes = List.Transform(splitLines, each Text.Split(_, “: “)),

output = List.Transform(splitAttributes, each {_[0], _[1]})

in

output

When you apply this function, it returns a list of lists:

{{“Name”, “John Doe”}, {“Age”, “35”}, {“Occupation”, “Engineer”}}

Here's how this code works:

1. The Lines.FromText function splits the text value into a list of lines.

2. The List.Transform function splits each line into a list of attribute and value, using the ":" character as the delimiter.

3. The List.Transform function combines each attribute and value into a single list.

4. The final output is a list of lists, where each inner list contains an attribute and its corresponding value.

The Lines.FromText function is a powerful tool in Power Query that can help you extract data from unstructured text. With its ability to split text into lines, you can easily extract information from text values. By understanding the M code behind Lines.FromText, you can customize its behavior to suit your needs and extract the data you need.

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)