Перейти к содержимому

Функция Table.FromList M Power Query

Недавно столкнулся с неожиданной ошибкой в работе функции Table.FromList() языка M Power Query. Простой код…

Запрос1[1]

… не работает, и возвращает ошибку:

Рис. 1. Неожиданное поведение функции Table.FromList()

Скачать заметку в формате Word или pdf, примеры в формате Excel

Интересно, а что будет, если подсунуть движку М логические значения или списки!?

Запрос2

Запрос3

Аналогичные ошибки. Похоже, Table.FromList() ожидает, что список состоит из текстовых элементов. Следующий код отрабатывает корректно:

Запрос4

Рис.2. Текстовые элементы списка работают корректно

Официальная справка Microsoft сообщает, что функция имеет один обязательный и четыре необязательных параметра:

Далее сказано, что функция Table.FromList() преобразует список list в таблицу путем применения заданной функции разбиения splitter к каждому элементу в списке. По умолчанию предполагается, что список представляет собой список текстовых значений, разделенных запятыми (выделение моё). Необязательный параметр columns может иметь следующие значения: число столбцов, список имен столбцов или type table (тип таблицы). При необходимости также можно указать default и extraValues.

Обязательный параметр Список

Продолжим эксперименты. Что если предложить движку в качестве разделителя пробел без опциональных параметров!?

Запрос5

… не работает:

Рис. 3. Разделитель пробел не работает

Как и сказано в документации Microsoft, по умолчанию Table.FromList() ожидает запятую в качестве разделителя.

Итак, Table.FromList() с одним параметром – списком – работает только, с текстовыми элементами списка, разделенными запятыми.

Скопируйте следующий код, и вставьте его в Расширенный редактор:

Запрос6

Если в редакторе PQ вы последовательно выберите шаги запроса, сможете увидеть в каком виде возвращаются таблицы.

Рис. 4. Особенности работы функции Table.FromList() с одним параметром

Первые три списка содержат по два текстовых элемента, 4-й и 5-й – по три элемента. Количество элементов списка задает число строк таблицы. Число столбцов определяется тем, на сколько частей разделяется каждый элемент. tbl_1 разбивается на три столбца, поскольку внутри каждого элемента списка используется разделитель по умолчанию – запятая. tbl_2 и tbl_3 представлены одним столбцом, так как внутри элементов списка нет разделителя по умолчанию – запятой. tbl_4 и tbl_5 демонстрируют еще одну особенность. Список преобразуется в таблицу с числом столбцов, на которое разбивается первый элемент списка. В tbl_4 первый элемент "A,B,C" разбивается на три столбца, а третий элемент "F,G,H,I", который хотел бы разбиться на четыре столбца, не знает, как это сделать, и возвращает ошибку. tbl_5 отражается без ошибок, потому что элемент списка с максимальным после разбиения числом частей является первым.

Обратите также внимание на имена столбцов: Column1, Column2, … Поскольку мы не указали список имен столбцов в параметре columns функции Table.FromList(), движок М присвоил имена по умолчанию.

Второй параметр Splitter – функция-разделитель

Второй опциональный параметр функции Table.FromList() splitter является функцией, выполняющей разделение внутри элементов списка. В качестве splitter может выступать одна из стандартных библиотечных функций М, пользовательская функция или null.

Посмотрим, как отрабатывает преобразование списка в таблицу интерфейс редактора Power Query. Создайте пустой запрос и введите список:

Запрос7

В редактора Power Query пройдите Средства для списков –> Преобразование –> В таблицу:

Рис. 5. Преобразование списка в таблицу

В открывшемся окне оставьте параметры по умолчанию:

Рис. 6. Параметры преобразования в таблицу

Нажмите Ok. PQ преобразует список в таблицу, и отразит код операции в строке формул:

Можно видеть, что интерфейс управляет двумя параметрами функции Table.FromList() – splitter и extraValues. Параметры columns и default получают значения по умолчанию – null, и в интерфейсе их изменить не получиться.

Разделители стандартной библиотеки М

Рис. 7. Функции-разделители стандартной библиотеки М

Для удобства обзора функций и констант языка М я вывел их все на лист Excel, используя…

Запрос8

См. лист Библиотека, приложенного Excel-файла.

Splitter.SplitByNothing

Не имеет аргументов. Не разбивает текст, возвращает элементы списка, как есть. Основное отличие от null или отсутствия аргумента в функции Table.FromList() в том, что разделитель Splitter.SplitByNothing справляется с нетекстовыми элементами списка.

Запрос9

Splitter.SplitTextByDelimiter

Разбивает текст на список в соответствии с указанным разделителем. Имеет два аргумента:

Опциональный аргумент quoteStyle

quoteStyle может принимать два значения. Официальная документации весьма лаконична:

Рис. 8. Справка Microsoft по QuoteStyle.Type

Описание Microsoft является, как минимум странным… Эксперименты показывают, что выбор QuoteStyle.Type влияет на два аспекта:

  • как движок реагирует на разделители внутри кавычек;
  • как отражаются экранированные кавычки.

Напомню. В языке M Power Query кавычки » (двойные кавычки) используются для обозначения текстовых литералов. Они всегда используются парами: первые кавычки начинают текстовую строку, вторые – её завершают. Если нужно использовать сам знак кавычек, как часть текстовой строки, кавычки нужно экранировать.

Например,

В этом примере двойные кавычки » внутри строки экранированы удвоением. Это указывает, что они являются частью текстового значения, а не символами, обозначающими начало и конец строки.

При отображении текстовой строки кавычки пропадают, а при отображении экранированных кавычек, они преобразуются в обычные ""–>".

Запрос10

Рис. 9. Кавычки в коде и на экране предварительного просмотра

Итак в языке М QuoteStyle.Type может принимать два значения:

  • None или 0: кавычки не влияют на разбор текста; все разделители сыграют свою роль; экранированные кавычки преобразуются в обычные двойные;
  • Csv или 1: разделители внутри экранированных кавычек считаются элементами текстовой строки и не участвуют в разделении текста; сами кавычки (и обычные ", и экранированные "") при разборе пропадают.

Запрос11

Рис. 10. Влияние параметра QuoteStyle.Type на обработку кавычек

В отсутствие экранированных кавычек (tbl_1 и tbl_2) параметры QuoteStyle.None и QuoteStyle.Csv ведут себя одинаково. При наличии экранированных кавычек (tbl_3) QuoteStyle.None «видит» разделитель внутри экранированных кавычек, и разбивает текст на 4 столбца. QuoteStyle.Csv (tbl_4) считает разделитель внутри экранированных кавычек элементом текста, и разбивает строку на три столбца.

Разделитель может состоять из нескольких символов. Два последовательных разделителя в разделяемом тексте приводят к созданию одной пустой ячейки. Если разделитель стоит в начале текста, создается одна пустая ячейка:

Запрос12

Рис. 11. Разделитель из нескольких символов и в начале текста

Учтите, что при импорте текста, например, из Excel, внешние и экранированные кавычки не нужны. Наличие таких кавычек будет говорить о том, что сам текст содержит кавычки. Сравните:

Рис. 12. Импорт текста из таблицы Excel

Здесь синяя таблица – исходная, импортируемая в PQ. А две зеленых – результат разбиения исходной таблицы на столбцы с помощью функции Table.SplitColumn и функции-разделителя Splitter.SplitTextByDelimiter с параметрами QuoteStyle.None и QuoteStyle.Csv.

Запрос13

Splitter.SplitTextByAnyDelimiter

В справке Microsoft указано, что функция разбивает текст по всем указанным в списке разделителям. Имеет один обязательный и два необязательных параметра:

Опциональный параметр startAtEnd

Новый параметр startAtEnd по умолчанию равен false, и поиск разделителей стартует с начала текста. Если startAtEnd = true, поиск разделителей ведется с конца текста. Поскольку в разбиении участвует любой разделитель из списка, мне представляется, что параметр startAtEnd для функции Splitter.SplitTextByAnyDelimiter() не играет никакой роли…

Рис. 13. Функция-разделитель Splitter.SplitTextByAnyDelimiter

Запрос15

Splitter.SplitTextByEachDelimiter

В справке Microsoft указано, что функция разбивает текст по всем заданным разделителям в последовательности.

Как обычно официальная документация слишком лаконична, а два приведенных в документации примера, как назло подобраны так, что не проясняют ситуацию.

На самом деле разделители используются последовательно в том порядке, как указаны в списке. Например, список разделителей {",", ";", ","} говорит, что в тексте произойдет разделение максимум на 4 столбца: сначала по первой найденной запятой, далее по точке с запятой, потом по вновь найденной запятой. Для этой функции становится понятным наличие параметра startAtEnd. Теперь направление разбора становится важным!

Рис. 14. Функция-разделитель Splitter.SplitTextByEachDelimiter

Запрос17

Splitter.SplitTextByLengths

В справке Microsoft указано, что функция разбивает текст на список по каждой указанной длине.

Здесь описание функции и примеры в документах Microsoft адекватны)) Если текст длиннее суммы элементов в списке разделителей, часть текста после разделения будет потеряна.

Рис. 15. Разделение текстовой строки по числу символов

Запрос19

Splitter.SplitTextByPositions

Функция разбивает текст на текстовый список по всем указанным позициям.

И здесь Microsoft хорошо справился с описанием и примерами. Помните, что счет позиций в текстовой строке начинается с нуля. Изучите примеры разбиения строки AABBBCCCCDDDDD:

Рис. 16. Разбиение по позициям

Запрос21

Splitter.SplitTextByRanges

Разбивает текст на текстовый список по заданным значениям смещения и длины. Длина null указывает, что все остальные входные данные должны быть включены. Я использую две версии Power Query. В одной null работает, в другой выдает ошибку – Не удается преобразовать значение null в тип Number.

В Excel, когда мы используем функцию ПСТР (А1; 5; 3) мы переходим к пятому символу, а затем берем его и следующие два символа:

Рис. 17. Параметры функции ПСТР в Excel

Аналогично работает Splitter.SplitTextByRanges. Функция принимает аргумент, который выглядит например так: { { 0, 2 } , { 3, 1 } }. Каждая пара значений определяет положение и число символов, как и ПСТР(). Изучите приведенные ниже примеры:

Рис. 18. Разбиение по диапазонам

Обратите внимание, что подстроки могут перекрываться.

Splitter.SplitTextByRepeatedLengths

Разбивает текст на текстовый список в цикле, после каждого отрезка указанной длины.

Эта функция похожа на Splitter.SplitTextByLengths, за исключением того, что все длины имеют одинаковое значение. Например…

… разделит текст на группы по три символа. В последнем столбце символов может быть меньше.

Рис. 19. Разбиение на одинаковое число символов

Splitter.SplitTextByWhiteSpace

Разбивает текст на текстовый список по пробелам.

Аналогична функции Splitter.SplitTextByDelimiter, когда разделителем является пробел. При quoteStyle.Type = quoteStyle.Csv пробелы внутри экранированных кавычек не служат разделителями:

Рис. 20. Разделение текстовой строки по пробелам

В дополнение к обычному пробелу (код ASCII 32), пробелами считается довольно много иных символов. Они подробно рассмотрены в документации Microsoft. Вот наиболее интересные:

  • табуляция, код символа ASCII 9, или Escape-последовательность #(tab),
  • перевод строки, код символа ASCII 10 или #(lf),
  • возврат каретки, код символа ASCII 13 или #(cr).

Точный набор символов, которые считаются разделителями пробелов, может различаться в зависимости от версии Power Query или среды приложения.

Splitter.SplitTextByCharacterTransition

Разделяет текст на текстовый список в соответствии с переходом от одного типа символов к другому. Параметры before и after могут быть либо списком символов, либо функциями, которые принимают символы и возвращают значения true или false. Если символ из первого списка стоит перед каким-либо символом из второго списка, текст будет разделен в этом месте. Вместо символов из списков before и after можно использовать логические значения. Разделение сработает если оба значения будут true.

Интерфейс редактора Power Query использует несколько предустановленных возможностей функции Splitter.SplitTextByCharacterTransition:

Рис. 21. Разделители в интерфейсе редактора Power Query

Поэкспериментируйте с этими четырьмя опциями. Посмотрите, какой код формирует каждая из них. Например, для перехода с не цифр на цифры:

Запрос26

Здесь параметр before определяется пользовательской функцией. Её часть – библиотечная функция List.Contains() – указывает, содержит ли список {"0".."9"} значение с. Если значение найдено в списке, возвращается true; в противном случае — false. Оператор not переворачивает логическое значение. Таким образом, аргумент before вернет true, если оцениваемый символ не цифра, и false, если цифра. Параметр after = {"0".."9"}. Разделитель сработает, если после не цифры идет цифра. Ровно то, что нам и требовалось. Пример такого разделения см. (1) на рис. 22.

Рис. 22. Разделение по переходу от одних символов к другим

Разделение в (2) происходит каждый раз, когда левый символ равен А или В, а правый – В или С. Разделение в (3) происходит на основе пользовательской функции:

Запрос28

Если левый символ <= "B", а правый > "B", текстовая строка будет разделена. Учитывайте, что коды ПРОПИСНЫХ букв меньше кода строчных.

Рис. 23. Символы в порядке возрастания их кодов

Функция Record.FieldValues в роли разделителя

Если список состоит из записей, то в качестве разделителя можно использовать функцию Record.FieldValues. В документации Microsoft сказано, что функция Record.FieldValues возвращает список значений полей записи, и имеет синтаксис:

Запрос29

Запрос 29 вернет таблицу, состоящую из значений полей записи:

Рис. 24. Функция-разделитель Record.FieldValues

Опциональные параметры columns, default и extraValues

Документация Microsoft описывает эти параметры предельно лаконично. Необязательный columns может иметь следующие значения: число столбцов, список имен столбцов или тип таблицы. При необходимости также можно указать default и extraValues.

Понимай, как хочешь!

Мы рассмотрим эти параметры совместно, так как они взаимоувязаны.

columns  может быть:

  • числом – количество столбцов, на которые будут разбиты элементы списка,
  • списком имен столбцов,
  • type table []; запись содержащая одну или несколько пар полей: имя столбца = тип столбца.

Аргумент columns по умолчанию равен null.

Если после разделения элементов списка их недостаточно, чтобы заполнить все предоставленные им столбцы, default задаст значение, которое будет возвращено. По умолчанию default = null.

Если после разбиения элементов списка у нас недостаточно столбцов для их размещения, аргумент extraValues определит, что будет отображаться:

  • ExtraValues.Ignore – разделенные элементы заполнят все предоставленные столбцы, а те элементы, для которых столбцов не хватило, будут отброшены (проигнорированы),
  • ExtraValues.List – последний столбец будет содержать все избыточные значения в виде списка,
  • ExtraValues.Error – все столбцы для такой строки будут содержать ошибку.

Значением по умолчанию является ExtraValues.Error. Часто это полезно, поскольку обращает внимание, что столбцов недостаточно. ExtraValues.Ignore «прячет» ошибку. Значения выглядят нормально, и нехватку столбцом можно не заметить.

Указание числа столбцов

В следующем коде элементы списка {«abcd»,»abc»,»abcde»} разделяются на три или четыре столбца:

Запрос30

Источник – список из трех элементов, т.е. таблица будет содержать три строки. С помощью функции Splitter.SplitTextByRepeatedLengths(1, false) каждый элемент списка разделяется по одному символу. Изучите результаты Запроса 30:

Рис. 25. Параметр columns – число

Как заметил buchlotnik, число столбцов не обязательно константа – его можно и вычислить. Например…

Запрос30а

… вернет таблицу с тремя столбцами. Источник состоит из трех списков, больше всего элементов в последнем списке, в котором число элементов – три.

Указание имен столбцов

Имена столбцов перечисляются, как текстовые литералы элементы списка. В Запросе 31 имена столбцов {"A", "B", "C", "D"}

Запрос31

Рис. 26. Параметр columns – список имен столбцов

Указание имен и типов столбцов

Чтобы задать имена и типы столбцов, в аргументе columns используется синтаксис

Например:

Запрос33

Сравните, простое именование столбцов (слева) и именование столбцов с одновременным заданием типов:

Рис. 27. Параметр columns – запись с именами и типами столбцов

Дополнение от 1 июня 2023 г. Как указано в документации Microsoft, в качестве разделителя может выступать пользовательская функция. buchlotnik в своем телеграмм-канале приводит несколько примеров таких функций.
Запрос34

Здесь разделить – пользовательская функция (x)=>Text.Split(x,";"). Она берет на вход по одному элементу списка, и разделяет их с помощью библиотечной функции Text.Split().

Запрос37

И не спрашивайте меня, почему здесь достаточно простого синтаксиса Text.ToList, а не строгого (x)=>Text.ToList(x) или (_)=>Text.ToList(_).

В комментариях buchlotnik предложил решение проблемы, озвученной в начале заметки. С ошибкой функции Table.FromList() при обработке списка чисел {10, 20, 30, 40, 50} справится пользовательская функция (x)=>x. Сравните два варианта её использования:

Рис. 28. Разделитель – пользовательская функция (x)=>x и (x)=>{x}

Пользовательскую функцию можно дополнить параметром type table:

Запрос38

Сама по себе эта возможность уже обсуждалась выше. Любопытно другое. В Запросе 38 типы столбцов установлены в соответствии с типами данных, а вот сами значения остались текстовыми. Обратите внимание, на рис. 29 числа отформатированы влево, как текст:

Рис. 29. Тип столбцов – численный, а сами значения текстовые

Поскольку разделитель может быть любой функцией, возвращающей список, buchlotnik предлагает более сложную пользовательскую функцию, которая не только разделит элементы списка по разделителю, но и преобразует числа как текст в настоящие числа:

Запрос39

Рис. 30. Тип значений и столбцов – число

Здесь числа в столбцах выключены вправо. А тип десятичных чисел number заменен на целые Int64.Type.

Использованные материалы

Форум MrExcel.com. https://www.mrexcel.com/board/threads/table-fromlist.1204740/

Форум Microsoft Power BI Community. https://community.powerbi.com/t5/Power-Query/How-to-create-table-from-list-while-specifying-column-name-as/td-p/2249421

Rick de Groot. Create Tables from Scratch in Power Query M (40+ Examples). https://gorilla.bi/power-query/creating-tables/

Chris Webb. An In-Depth Look At The Csv.Document M Function. https://blog.crossjoin.co.uk/2018/03/09/an-in-depth-look-at-the-csv-document-m-function/

Splitter functions in Power Query. https://bizkapish.com/power-query/splitter-functions-in-power-query/

Запросы Chat GPT. https://chat.openai.com/

[1] Номер в заметке соответствует номеру запроса в приложенном Excel-файле.

4 комментария для “Функция Table.FromList M Power Query”

  1. Table.FromList({10, 20, 30, 40, 50},(x)=>{x})
    или
    Table.FromList({{10, 20, 30, 40, 50}},(x)=>x)
    в зависимости от желаемого.

    Равно как и
    Table.FromList({{1, 2}, {2, 3}},(x)=>x)
    и вообще (x)=>x реально много чего решает )))

  2. «Похоже, Table.FromList() ожидает, что список состоит из текстовых элементов.»
    — это в корне не верно.
    Правильно будет утверждать, что если функция разделителя не указана (null или опущена со всеми остальными опциональными параметрами), по умолчанию _к каждому элементу списка_ применяется функция разделителя Splitter.SplitTextByDelimiter(",")

  3. Сергей Багузин

    buchlotnik (Михаил), спасибо! Круто)) Подписался на ваш канал. Дополню заметку вашими идеями.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *