Загрузка и преобразование данных (ETL технологии) с использованием Microsoft Power Query

Введение

•       Слушатель узнает назначение надстройки 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