Table.ToList
Joins each row into one text value, using a combiner you choose, and returns the list of those values.
Table.ToList(table as table, optional combiner as nullable function) as listReturns: A list of text values, one for each row.
What it does
Table.ToList gives each row as one piece of text. The combiner sets how the values in the row are joined, such as with a comma or a bar. The result is a list of lines, which can be written to a file, pasted into an email or joined into a single paragraph with Text.Combine.
Syntax and arguments
- table: the table to convert. Required.
- combiner: a function that joins the values of each row, such as
Combiner.CombineTextByDelimiter(" | "). Optional, but use it so you control the layout.
Example
A list of open items is turned into one line of text for each item, so the lines can be pasted into a note to the controller.
let
Source = Excel.CurrentWorkbook(){[Name = "OpenItems"]}[Content],
AsText = Table.TransformColumnTypes(Source, {{"Vendor", type text}, {"Reference", type text}, {"Status", type text}}),
Lines = Table.ToList(AsText, Combiner.CombineTextByDelimiter(" | "))
in
Lines
Each row becomes a line such as a vendor, a reference and a status, separated by a bar. The text columns are set first, so the combiner can join them.
Example: a second, different use
The lines are joined into one paragraph for a status note, separated by semicolons.
let
Source = Excel.CurrentWorkbook(){[Name = "OpenItems"]}[Content],
AsText = Table.TransformColumnTypes(Source, {{"Vendor", type text}, {"Status", type text}}),
Lines = Table.ToList(AsText, Combiner.CombineTextByDelimiter(": ")),
Paragraph = Text.Combine(Lines, "; ")
in
Paragraph
Things to watch
- The combiner works on text. Convert numbers and dates to text first, or the join fails.
- Blank values may leave extra separators in the line. Check a few rows.
- The result is text, not a table, so it cannot be filtered or summed afterwards.
- Query folding: this step does not fold, since it builds text in Power Query.
In business terms
Status notes, memo lines and text for an email are often built from a table of open items. Doing it in the query keeps the wording the same every time, and the text can be checked against the table.