Введение
• Слушатель узнает назначение надстройки Power Query;
• Какую роль Power Query играет в BI-семейства: Power Pivot, Power Map, Power BI и т. д.;
• Научимся использовать Power Query для автоматизированной подготовки данных для анализа;
• Пошагово и подробно пройдем весь процесс загрузки и обработки данных на готовых примерах из бизнеса.
Содержание программы
Возможности и ограничения Power Query
Подключения к данным в Power Query (Базы данных, Интернет и облачные хранилища, Корпоративные программы, Файлы и папки)
· Загрузка таблиц из текстового файла
· Загрузка данных из внешней книги Excel
· Универсальный способ с функцией Excel.CurrentWorkbook
· Загрузка данных из соцсетей
· Загрузка информации через Open Data Protocol (OData)
· Загрузка данных из файлов XML
· Загрузка данных в формате JSON
· Загрузка данных из PDF через Word
· Загрузка данных почты и календаря из Microsoft Exchange
Слияние запросов
· Добавление (Append)
· Объединение (Merge)
· Слияние двух таблиц
· Добавление трех и более таблиц с загрузкой в Модель данных
· Объединение таблиц: забудьте про ВПР
· Объединение по нескольким столбцам
· Подстановка сразу всех найденных значений
· Приблизительный межинтервальный просмотр (аналог ВПР или ПОИСКПОЗ)
Сравнение таблиц объединением разных типов
Сравнение таблиц с помощью условного столбца
Настройка уровней конфиденциальности источников данных
Групповая загрузка данных
· Импорт всех текстовых файлов из папки
· Сбор данных из Excel-файлов заданной папки
· Импорт всех «Динамических» таблиц из текущей книги
· Загрузка всех простых таблиц с листов текущей книги
Преобразования таблиц
Фильтрация и особенности фильтрации через поле «Поиск»
Транспонирование
Заполнение пустых ячеек
Группировка строк
· Простая группировка
· Сложная группировка
· Подсчет количества уникальных значений
· Группировка с выводом всех значений
· Извлечение уникальных значений при группировке
· Первый/последний элемент в каждой группе
Свёртывание таблиц
· Простое свёртывание таблиц
· Трансформация столбца в матрицу
· Постоянный шаг в данных
· Переменный шаг в данных
Отмена свертывания столбцов (UnPivot)
· Отмена свертывания столбцов
· Отмена свёртывания таблицы с многоуровневыми подписями
· Отмена свёртывания сразу нескольких таблиц
Операции с текстом
· Замена регистров «Как в предложении»
· Удаление лишних пробелов
· Очистка текста от непечатаемых символов
· Разделение «слипшегося» текста
· Деление на строки вместо столбцов
· Несколько строк в одной ячейке
· Удаление из строки не нужных символов
· Извлечение номеров и дат счетов из описаний платежей в банковской выписке
· Извлечение подстроки
· Склеивание текста
· Команда «Объединить столбцы»
· Склейка формулой
· Склеивание текста и чисел
· Склеивание текста и дат
· Массовая склейка функцией Text.Combine
· Столбец из примеров
· Генератор фраз декартовым произведением
· Поиск текстовых значений по заданному условию 159
· Коэффициента соответствия строковых переменных
Обработка дат и времени в PQ
· Распознавание дат средствами PQ
· Использование локали для дат других стран
· Столбцы с датами смешанного формата
· Преобразование дат
· Определение номера недели по ISO
· Конвертирование даты в текст
· Вычисление длительностей
· Разница в полных днях
· Продолжительность как тип данных
· Вычисление возраста
· Сдвиг даты на N периодов
· Поиск самой ранней и самой поздней даты
· Создание диаграммы Ганта 171
Работа с запросами
· Группировка запросов
· Защита запросов
· Просмотр зависимостей между запросами
· Копирование, дублирование и ссылка на запрос
· Поделиться запросом
· Обновление запросов по расписанию
Power Query и VBA
· Удаление запросов макросом
· Обновление запросов макросом
· Создание запроса макросом
· Загрузка «Динамических» таблиц в Power Query макросом
· Загрузка запросов Power Query в Модель Данных Power Pivot макросом
Язык М
· Основы синтаксиса языка М
· Выражения
· Оператор let
· Комментарии
· Последовательность выполнения
· Логические ветвления с if … then … else
· Простые типы данных
· Структурированные типы данных
· Справка по встроенным функциям
· Редактор М-кода Notepad++ с подсветкой синтаксиса
· Пользовательские функции
· Типы данных для аргументов и результата
· Необязательные аргументы
· Функция внутри запроса
· Функция как аргумент для другой функции
· Рекурсия
· Ключевое слово each
Обработка ошибок в запросах
· Тонкости деления
· Ошибки (Error)
· Пусто (Null)
· Бесконечности (Infinity)
· Нечисло (NaN)
· Универсальный подход
Параметризация запросов
· Добавление простых параметров к запросу
· Параметризация путей к файлам исходных данных
Преобразование запроса в функцию на примере веб-запроса курса валют
Использование специальных функций Table.Buffer и List.Buffer