На этой странице находятся ссылки на все уроки моего бесплатного курса по Power Query для Excel. Если вы изучите все уроки, то станете очень продвинутым пользователем Power Query. Желаю вам успехов в освоении Power Query!
Внимание!!! Старица находится в процессе разработки.
Введение
Во вводном модуле вы узнаете:
- Зачем нужен Power Query
- К каким источникам данных может подключаться Power Query
- Как можно использовать результирующие таблицы
- Базовые преобразования данных
- Классификация функций Power Query
Основы
В модуле Основы мы разберем основные операции, которые специалистам по Excel приходится выполнять чаще всего.
| Урок | Описание |
|---|---|
| Подключение Excel | Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов. |
| Подключение CSV/TXT, таблиц, диапазонов | Подключаемся к к файлам CSV/TXT, Excel |
| Подключение XML | Подключаемся к источнику в формате XML |
| Объединение таблиц по горизонтали | Учимся объединять таблицы по горизонтали — JOIN, merge |
| Виды объединения таблиц по горизонтали | Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN |
| Виды объединения таблиц по горизонтали 2 | Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN |
| Группировка | Изучаем операцию группировки с агрегированием — GROUP BY |
| Объединить таблицы с агрегированием | Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY |
| Объединить таблицы по вертикали | Учимся объединять две таблицы по вертикали — combine |
| Объединение таблиц по вертикали, когда не совпадают заголовки столбцов | Как объединить две таблицы по вертикали, если названия столбцов не совпадают |
| Объединить по вертикали все таблицы одной книги друг за другом | Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel |
| Объединить по вертикали все файлы в папке | Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке |
| Консолидация множества таблиц пользовательской функцией | Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции |
| Анпивот (Unpivot) | Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными |
| Многоуровневый анпивот (Анпивот с подкатегориями) | Более сложный вариант Анпивота — в строках находится несколько измерений |
| Скученные данные | Данные собраны в одном столбце, нужно правильно его разбить на несколько |
| Скученные данные 2 | Разбираем еще один пример скученных данных |
| Создание параметра | Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query |
| Таблица параметров | Создадим целую таблицу параметров и будем их использовать в запросах Power Query |
| Поиск ключевых слов | Научимся искать ключевые слова в текстовом поле |
| Поиск ключевых слов 2 | Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию |
| Ссылка на другую строку | Как сослаться на другую строку |
| Ссылка на другую строку 2 | Как сослаться на другую строку, используя объединение по горизонтали |
| Уникальные значения двух столбцов | Как получить уникальные значения из двух столбцов |
| Деление на справочник и факт | Разделим один датасет на два датасета: справочник и факт |
| Импорт из PDF, Импорт из множества PDF | Научимся импортировать таблицы из одного PDF файла и из всех PDF файлов в папке |
Формулы М
В этом модуле мы будем изучать язык формул Power Query или M Language.
| Урок | Описание |
|---|---|
| Введение 1 | На примере поиска без учета регистра я показываю преимущества изучения формул Power Query |
| Введение 2 | Знакомимся с основами языка формул М: ключевые слова let, in, разбираем как устроены функции |
| Введение 3 | Зачем изучать язык формул, когда существует удобный пользовательский интерфейс |
| Объекты Power Query | Знакомимся с основными объектами Power Query: Table, List, Record |
| Объект Table | Изучаем объект Table — таблица |
| Объект Record | Изучаем объект Record — запись, каждая строка таблицы — это запись |
| Объект List 1 | Изучаем объект List — список, каждый столбец таблицы является списком |
| Объект List 2 | Продолжаем изучать объект List — список |
| Ссылки 1 | Как ссылаться на список и запись и отдельные элементы списка или записи |
| Ссылки 2 | Решаем практическую задачу по объединению таблиц и отрабатываем ссылки на строки и столбцы таблицы |
| Ссылки 3 | В процессе решения практической задачи отрабатываем ссылки на элементы записи и таблицы |
| Ссылки 4 | Закрепляем навык делать ссылки на практическом примере из жизни |
| Работа с датой, временем и длительностью, #datetime, #duration | Основы работы с датой и временем |
| Power Query аналоги текстовых функций Excel | Изучаем аналоги текстовых Excel функций ЛЕВСИМВ, ПРАВСИМВ, ДЛСТР, НАЙТИ, ПСТР |
| Группировка, Пивот, Анпивот, Сортировка | Выполняем знакомые операции, но уже без помощи пользовательского интерфейса, а ручным вводом формул |
| Вычисления в Power Query | Вычисляем процент от общей суммы, процентное изменение относительно предыдущего дня, создаем рейтинг |
| Пользовательские функции | Учимся создавать пользовательские функции на примере нарастающего итога |
| Агрегирование текста, группировка | Выполняем группировку с агрегированием текстовых значений |
| Функция Table.TransformColumns | Table.TransformColumns позволяет нам применить любую функцию преобразования к столбцу |
| Скользящее среднее в Power Query, List.Range | Изучаем функцию List.Range на примере вычисления скользящего среднего |
| Разгруппировка, скрытые возможности Table.ReplaceValues | Разгруппировка строки — разбить число на N равных частей; Вводим функцию внутри Table.ReplaceValues |
| Нюансы консолидации и List.PositionOf | Нужно выполнить объединение по вертикали с предварительной обработкой, но количество лишних строк сверху всегда разное |
| Удалить лишние пробелы в текстовом столбце, Text.Split | Изучим функцию Text.Split и удалим с помощью нее лишние пробелы |
| Обработка заголовков в двух строках, Функция List.Zip, Практика List.x, Record.x | Каждый заголовок разбит на две строки, нужно превратить эти две строки в одну объединенную |
| Фильтрация таблицы списком | Есть список интересующих нас категорий, нужно из таблицы выбрать только эти значения |
| Разделить столбец на строки, Расширенные возможности Text.Trim | Разберем на практическом примере, что еще может чистить функция Text.Trim помимо лишних пробелов по краям |
| Нарастающий итог 2 | Вычислим нарастающий итог функциями List.Range и List.Accumulate |
| Создание пользовательской функции Switch | Повторим пользовательские функции на примере создания функции Switch |
| Информация о форматах ячеек, Чтение Zip файла | Распакуем XLSX файл и получим данные о формате ячеек |
| Множественная текстовая замена с List.Generate | Ищем определенный перечень текстовых фрагментов и заменяем на другой. |
| Минимум в диапазоне строк |
Ошибки
В модуле вы научитесь избегать ошибок и составлять универсальные запросы.
| Урок | Описание |
|---|---|
| Введение | Определимся, что мы будем назвать ошибками и ловушками |
| Как развернуть все столбцы | В данные добавился новый столбец, но у вас он не разворачивается, потому что вы хардкодом прописали список для экстракта |
| Измененный тип, Неверная фильтрация в UI | Запрос, который раньше работал, вдруг, работать перестал. Возможно, дело в ошибке «Измененный тип»; В результирующей таблице меньше строк, чем должно быть. Возможно, вы попали в ловушку неверной фильтрации |
| Неверное количество столбцов при импорте CSV | Вы импортировали CSV файл, все было хорошо, но когда в источник добавились новые столбцы вы их не увидели в Power Query |
| Подключиться к последнему файлу | Каждую неделю в сетевую папку для вас добавляют новый файл источник. Вам нужно автоматически подключаться только к самому свежему файлу |
| Консолидация и MissingField.Type | Вы хотите выполнить массовое объединение таблиц по вертикали, но не во всех таблицах присутствует полный список столбцов. Что делать? |
| Автоматически удалить пустые столбцы | В вашем источнике часто присутствуют лишние пустые столбцы; Научимся автоматически удалять их |
| Ошибка Formula.Firewall | Разберем 2 способа избежать ошибки Formula.Firewall |
| Разные имена листов | |
| Разные имена листов 2 | |
| Разные названия столбцов | |
| Удалить лишние пробелы 2 | Удалим лишние пробелы в текстовых столбцах с помощью функцию Text.SplitAny и Text.Combine |
| Доступ к веб-содержимому | При подключении к веб-страницам постоянно появляется окно «Доступ к веб-содержимому»; Как от этого окна избавиться? |
| Импорт плохо структурированного TXT | TXT источник плохо структурирован и столбцы не распознаются автоматически |
| Не совпадает сумма при округлении | Выполнив округление общая сумма перестала совпадать на незначительную величину |
| Фиксировать ширину столбцов Excel | Настроенная вами ширина столбцов Excel сбивается после каждого обновления запроса. |
Веб-запросы
Разберем множество практических примеров подключения к веб-страницам и web api.
| Урок | Описание |
|---|---|
| Веб-запросы. Текстовый документ, веб-страница, практика List.Zip | На одной веб-странице находятся данные без заголовков, а заголовки для нее на другой странице. |
| Веб-запросы. Неразмеченный текст | Текс находится на веб-странице и он плохо размечен. Нужно преобразовать его в таблицу. |
| Веб-запросы. Многостраничное извлечение | Как извлечь таблицу из множества веб-страниц и объединить их всех по вертикали. |
| Веб-запросы. Получение котировок Yahoo Finance | Получим историю котировок любой акции с сайта Yahoo Finance. |
| Веб-запросы. JSON, получение котировок Yahoo Finance 2 | Получим историю котировок любой акции при помощи API Yahoo Finance. |
| Веб-запросы. HTML, получить данные по облигациям | Получим данные по облигациям из множества веб-страниц и все объединим в одну таблицу. |
| Получить котировки Московской биржи | Получим историю котировок с сайта Московской биржи. |
| YouTube Data API | Получить статистику по любому YouTube видео и каналу прямо на лист Excel. |
| Подключение к личному OneDrive | Как подключиться к файлу или папке из личного OneDrive. |
| Подключиться к книге на Google Drive | Как подключиться к файлу, который лежит в облаке Google Drive |
Практика
Отработаем изученные функции на реальных практических примерах из жизни.
| Урок | Описание |
|---|---|
| Динамическая таблица дат | Создаем саморасширяющуюся таблицу календаря |
| Продажи год назад | Отработаем SELF JOIN на примере вычисления продаж прошлого года |
| Количество позиций в текстовой строке | В ячейках столбца находится перечень товаров в чеке через точку с запятой; Сделаем так, чтобы каждая позиция находилась в своей отдельной строке |
| Консолидация листов и книг одновременно | Объединим по вертикали все таблицы, находящиеся на разных листах разных книг Excel |
| Собрать разбитую строку | Каждая строка данных разбита на несколько строк. Нужно привести данные в порядок. |
| Обработка типичной выписки | Отработаем несколько приемов, обработав банковскую выписку. |
| Прирост населения Китая | На примере анализа прироста населения Китая отработаем несколько приемов. |
| Нужные столбцы нужной книги | Как выбрать нужную книгу среди множества и из этой книги получить только определенные столбцы. |
| Объединить разбитую строку | Ряды данных разбиты на части. Нужно правильно соединить ряды обратно. |
| Группировка, пивот, анпивот, условия | Практическое применение группировки, пивота, анпивота и условной логики |
| Процент от суммы по категории в Power Query и в Power Pivot | Учимся вычислять процент от общей суммы в Power Query и в Power Pivot. |
| Выполнить пивот и пронумеровать столбцы | Делаем пивот и столбцы автоматически пронумеровываем. |
| Интересная консолидация | |
| Преобразовать строки в столбцы | |
| Повторяющиеся значения в строке | |
| Нарастающий итог 3 | |
| Минимальное значение в диапазоне строк | |
| Нарастающий итог 4. Группировки | |
| Функция List.Contains и создание пользовательской функции | |
| Скученные данные 3 |
Power Query + VBA
| Урок | Описание |
|---|---|
| Указать путь к файлу при помощи VBA | Укажем путь к файлу, выбрав его в диалоговом окне. |
| Массовая обработка файлов | По одному обработаем файлы из перечня. |
| Обновить запросы выборочно | С помощью VBA обновить только определенные Power Query запросы в книге Excel. |
Трюки и советы
В этом модуле мы изучим множество полезных трюков, которые помогут вам сэкономить время и силы при работе в Excel.
| Урок | Описание |
|---|---|
| Таблица навигации по функциям Power Query | Создадим таблицу, в которой будет полный перечень функций Power Query с категориями, описаниями и примерами. |
| Удалить все запросы и модель данных из книги | Вы узнаете как моментально удалить все запросы и модель данных из книги за несколько кликов. |
| Открыть еще 1 Excel и еще 3 трюка | В этом уроке мы разберем как открыть еще одно окно Excel, когда запущен редактор Power Query, как скопировать запросы из одной книги в другую, как быстро удалить шаги запроса от выбранного и до последнего и как быстро перейти к нужному столбцу в редакторе Power Query |
| Подключение к ZIP файлу | Подключимся к файлу-источнику, который находится внутри ZIP архива |
| Подключение к WORD документу | Импортируем таблицы из MS Word документа |
Погружение в M
В этом модуле мы углубимся в язык М.
| Урок | Описание |
|---|---|
| Простые выражения и let | Что такое let, одна или несколько переменных в in, вложенные блоки let. |
| Создание функций М | Функции без выражения let, функции внутри выражения let |
| Создание функций М 2 | Функция как параметр функции, ключевое слово each |
| Функции и идентификаторы | Идентификаторы с кавычками и без, допустимые и недопустимые идентификаторы |
| Текстовый тип данных | Что из себя представляет текстовый тип данных в Power Query. |