[Академия Excel] Продвинутый Power Query. Тариф Оптимальный (Михаил Музыкин)

​

ПОДРОБНАЯ ПРОГРАММА КУРСА

Программа занятий

Постарались описать простыми словами содержимое каждой темы

1. Как вообще писать код на М

• Разбираем самые основы - значения, литералы значений, выражение, операторы, среда, переменная, функция, тип и всякие другие страшные слова.
• Учимся писать простые выражения, выясняем, что параметры можно задавать просто кодом.
• Учимся использовать функции типа #date или DateTime.LocalNow() для создания констант.
• Далее рассматриваем операторы (арифметика, логика, условные) и начинаем писать собственные функции.
• Разбираем абстрактные типы –table, function – и заодно выясняем, зачем нам столько разных скобок.
• После чего выясняем, что «запрос с шагами» - это просто одна большая формула. По этому поводу разбираем как устроена конструкция let in, выясняем, что такое «синтаксис на записях» и область видимости переменных.

По итогу первого занятия у вас появится понимание того, как вообще устроен код на М, как его читать и где править.

2. Что это за списки, на которых быстрее?

В рамках вебинара рассматриваем тип данных список (list).

Выясняем, что списки у нас везде – список имён столбцов таблицы, список значений конкретного столбца, список искомых значений и т.п. и учимся с ними работать:
- получаем списки
- генерируем списки (чисел, дат, символов, отдельно посмотрим на List.Generate)
- учимся собирать один список из нескольких (Combine, Union, Difference)
- учимся выбирать нужное – первые/последние элементы, каждый третий, выбор по условию и т.д.

Далее поработаем с List.Transform – главная функция вообще и в частности – соорудим календарь, получим таблицы книги по списку, вытащим конкретные элементы из списка по списку и вообще запомним, что при работе с таблицами без этой функции никуда.

Ещё мы не обойдём внимание List.Accumulate и осуществим множественную замену текста, а закончим превращением списка в таблицу.

Главная задача занятия – научить видеть списки в своих задачах и показать, как с ними работать не просто с использованием штатных функций, а именно с написанием пользовательских функций (x)=> в нужных местах.

3. Таблицы вдоль и поперек – и это не фигура речи, а разное представление (основная тема - table)

На данном вебинаре смотрим на таблицу и выясняем, что это с точки зрения языка М (список записей, список строк, список столбцов, нужное подчеркнуть или все варианты верные).

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

Занимаемся выбором нужных столбцов, преобразованием типов, преобразованием столбцов просто и всё это делаем так, чтобы при добавлении/удалении столбцов в источнике у нас ничего не поломалось.

Отдельно решим классическую задачку по получению данных из предыдущих или последующих строк – для одного столбца и для нескольких (и выясним раз и навсегда, что индексы нам не нужны).

А на закуску нам останется Table.Group, про которую мы выясним, что это функция пяти аргументов, напишем список агрегаций без привязки к конкретным столбцам, выясним, что такое GroupKind и когда он нам нужен Local и конечно, научимся писать пятый аргумент в виде своей пользовательской функции (сгруппируем таблицу с пропусками, сгруппируем по определённым признакам, условиям и т.д.).

Научитесь применять «списочные» подходы к реальным таблицам. Увидите на примерах что таблица - это не жёсткая структура и что при работе с ней можно (и нужно) писать гибкий код.

4. Записи или как перестать бояться квадратных скобок в коде (основная тема – record)

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

Далее рассмотрим запись как строку таблицы и займемся функциями, где это важно (Table.SelectRows, Table.AddColumn): научимся работать с множеством условий, напишем функции для добавления столбца (в том числе без привязки к именам) и т.п.

Снова вернёмся к List.Generate, но уже в полном синтаксисе, на записях, и решим задачу вычисления накопленной суммы (а заодно и скользящего среднего).

Наконец, выясним, что такое «словарь на записях» и чем он нам поможет при множественных заменах, создании агрегаций вроде СЧЁТЕСЛИ, СУММЕСЛИ. Применим его при сложных группировках (например, группировка по списку категорий, которые находятся в одном столбце с наименованием товара).

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

5. Работа с файлами и папками – просто кодом, просто в запросе, просто так можно (основная тема Excel.* + Folder.*)

В рамках данного вебинара от отдельных таблиц мы перейдём к файлам в целом.
Здесь поработаем с файлом “изнутри” с использованием Excel.CurrentWorkbook, соберём все таблицы.

Далее поработаем с ним же «снаружи», вооружившись Excel.Workbook, выясним насколько это интереснее и полезнее – соберём информацию со всех листов и узнаем, зачем нужны дополнительные аргументы.

Ну и разобравшись с начинкой отдельно файла перейдём к чтению папок целиком:
- выясним разницу между Folder.Files и Folder.Contents
- напишем общий шаблон подключения к папке
- поработаем с файлами xlsx – соберем одно- и многолистовые файлы, с привязкой к именам листов и без
- модифицируем шаблон и вытащим csv, txt, pdf – выясним, что шаблон остаётся тем же
- и далее займёмся написанием пользовательских функций – соберем данные из смеси xlsx и txt, применим разные преобразования в зависимости от содержимого файла, посмотрим на общую логику кода – как его писать «на лету».

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