В мою жизнь вошел ChatGPT! Я даже не знаю, с чем можно сравнить первое впечатление от использования бота. Это прорыв! Пока я обращаюсь к ChatGPT в двух случаях:
- задаю вопросы, которые ранее обращал к Google,
- прошу написать код на языке М Power Query (PQ).
Примеры первого типа можно встретить в комментариях к моим недавним заметкам Онлайн калькулятор статистической мощности G*Power и Фрэнсис Гальтон. Наследственность таланта, её законы и последствия. Сейчас я опишу, как ChatGPT помог мне сделать робастный[1] запрос на языке М Power Query.
Рис. 1. Загрузка CSV-файла в умную таблицу Excel
Скачать заметку в формате Word или pdf, примеры в формате Excel и CSV-файл с исходными данными
Мы с сыном играем в Fantasy Premier League. Моя роль заключается в подготовке статистических данных для поддержки принятия решений. В частности, я использую данные с сайта fplreview (некоторые функции доступны только за плату). Прогноз выступления игроков на ближайшие восемь туров можно выгрузить в CSV-файл. Самое простое – загрузить его в Excel с помощью PQ (Данные –> Из текстового/ CSV-файла, см. рис. 1).
Чтобы данными было удобно пользоваться, я их преобразовывал в PQ и загружал в модель данных, а далее на лист Excel в виде сводной таблицы:
Рис. 2. Те же данные после обработки
Поскольку имена столбцов в CSV-файле меняются от тура к туру, запросы PQ «ломались», и мне приходилось заново импортировать данные и настраивать таблицы. Я создал запрос, который обращался к столбцам не по их именам, а по позиции в таблице. Поскольку я не очень опытный пользователь, то решил задачу с привлечением экспертов с форума planetaexcel.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
#"Добавлен пользовательский объект1" = Table.AddColumn( #"Переименованные столбцы", "FullPts", each Record.FieldValues(_){5}+ Record.FieldValues(_){7}+ Record.FieldValues(_){9}+ Record.FieldValues(_){11}+ Record.FieldValues(_){13}+ Record.FieldValues(_){15}+ Record.FieldValues(_){17}+ Record.FieldValues(_){19} ), |
Нерешенной осталась одна проблема. Когда сезон приближается к концу, остается менее 8 туров, и fplreview выдает прогноз лишь на оставшиеся туры. Год назад я не стал заморачиваться, и просто менял код от тура к туру… постепенно убирая по одному слагаемому с конца.
Сегодня мне захотелось решить эту проблему. О языке М с тех пор я узнал чуть больше, но всё же написать код с нуля не получилось. Я обратился к ChatGPT. Далее привожу мои вопросы и его ответы. Мои замечания набраны с отступом.
Вопрос 1
У меня есть список имен столбцов таблицы. Список называется ClmnMin. Как мне создать новый столбец в таблице, в котором будут суммироваться значения столбцов с именами из ClmnMin?
Ответ. Для создания нового столбца в таблице, в котором будут суммироваться значения столбцов с именами из списка ClmnMin в Power Query на языке M, можно использовать функцию "Table.AddColumn" в сочетании с функцией "List.Sum" и функцией "Record.SelectFields".
Пример. Предположим, у нас есть таблица, и список ClmnMin содержит имена столбцов, которые мы хотим суммировать. Мы хотим создать новый столбец "Сумма", в котором будут содержаться суммы значений из всех столбцов с именами из списка ClmnMin.
- Выберите таблицу и откройте редактор запросов (Query Editor).
- На вкладке "Добавить столбец" ("Add Column") выберите опцию "Создать столбец на основе формулы" ("Custom Column").
- Введите название нового столбца, например "Сумма".
- Введите формулу для суммирования значений из столбцов с именами из списка ClmnMin:
Код 1
|
1 2 3 4 5 |
= Table.AddColumn( #"Шаг предыдущего этапа", "Сумма", each List.Sum(Record.SelectFields(_, ClmnMin)) ) |
Привожу картинку с кодом из диалога. Обратите внимание на цветовую разметку:
Где #"Шаг предыдущего этапа" – ссылка на предыдущий этап запроса, т.е. на исходную таблицу, а Record.SelectFields(_, ClmnMin) – выбирает из текущей строки таблицы только те столбцы, которые перечислены в списке ClmnMin.
Нажмите клавишу Enter или выберите опцию "ОК" чтобы создать новый столбец. Теперь новый столбец "Сумма" будет содержать сумму значений из всех столбцов с именами из списка ClmnMin для каждой строки таблицы. Обратите внимание, что в функции List.Sum мы передаем список значений из выбранных столбцов в виде массива, который затем суммируется.
Вставил функцию в свой код. К сожалению, вернулась ошибка:
Рис. 4. Ошибка
Я подумал, что в Код1 функция List.Sum получает не значения, а записи. Поэтому не может их суммировать. Поэтому…
Вопрос 2.
У меня есть запись [Min33=8, Min34=14, Min35=15, Min36=18, Min37=21, Min38=19]. Как суммировать все значения этой записи?
Ответ. Чтобы суммировать все значения в записи в Power Query на языке M, вы можете использовать функцию "Record.FieldValues", чтобы получить все значения из записи, и затем применить функцию "List.Sum" к этому списку значений.
Вот пример формулы, которую вы можете использовать для суммирования всех значений в записи:
Код2
|
1 2 3 4 5 6 |
let rec = [Min33=8, Min34=14, Min35=15, Min36=18, Min37=21, Min38=19], values = Record.FieldValues(rec), sum = List.Sum(values) in sum |
Здесь мы сначала определяем запись "rec", которую нужно суммировать. Затем мы используем функцию "Record.FieldValues" для получения списка всех значений в этой записи. Далее мы применяем функцию "List.Sum" к этому списку значений, чтобы получить общую сумму.
В результате выполнения этой формулы переменная "sum" будет содержать общую сумму всех значений в записи. В данном примере результатом будет число 95 (8 + 14 + 15 + 18 + 21 + 19).
С учетом моего контекста получилось:
Код3
|
1 2 3 4 5 6 7 |
#"Добавлен пользовательский объект" = Table.AddColumn( #"Измененный тип1", "FullMin", each List.Sum( Record.FieldValues(Record.SelectFields(_, ClmnMin)) ) ), |
Вопрос 3.
Спасибо! Всё Ok))
Ответ. Пожалуйста, рад был помочь! Если у вас есть дополнительные вопросы, не стесняйтесь задавать их.
[1] Робастность – способность чего-либо (например, статистической модели, программного кода, системы или процесса) сохранять работоспособность и стабильность в условиях изменений или ошибок. Например, робастная статистическая модель будет продолжать давать точные прогнозы, даже если в данных появятся выбросы или искажения. Робастный код будет успешно обрабатывать данные, даже если в них будут пропущенные значения. – Примечание написано с использованием ChatGPT.