Эскобар М., Пульс К. Приручи данные с помощью Power Query в Excel и Power BI

ДMK, 2022. — 574 c.Уму непостижимо, как легко и просто можно очистить данные с помощью Power Query в Excel или Power BI! Наши данные редко появляются на свет в готовом к работе виде, и у нас могут уходить долгие часы на их очистку, фильтрацию и преобразование. Power Query помогает сократить этот процесс при первичной обработке данных, а все последующие обновления позволяет свести к простому нажатию на кнопку. Когда дело касается импорта, очистки и преобразования исходных данных для дальнейшего анализа, освоить Power Query бывает гораздо легче, чем выучить формулы Excel или язык программирования VBA. Нет сомнений, что Power Query навсегда изменит подход специалистов Excel к работе с данными. Если у вас есть Excel, значит, у вас уже есть Power Query – этот инструмент встроен в Excel 2016 и выше.Предисловие от издательства
Предисловие
Как Power Query изменил НАШИ жизни
История Кена: «Кофе и Power Query
История Мигеля: новый старт
Благодарности от авторов
Благодарности от Кена
Благодарности от Мигеля
Наши преданные читатели
И наконец
Глава 0. Революция данных
Общий сценарий для аналитиков данных
Преимущества и опасности черной магии
Будущее изменилось
Почему Power Query – это магия
Извлечение
Преобразование
Загрузка
Возможности Power Query и интеграция с другими продуктами
Компоненты Power Query
Цикл обновлений Power Query
Power Query Online
Microsoft
Excel
Excel
Power BI Desktop
Как использовать эту книгу
Где найти Power Query
Excel
Power BI Desktop
Предыдущие версии Excel
Подключение к данным
Особые пометки
Сопроводительные файлы
Глава 1. Основы Power Query
Перед началом
Изменение настроек Power Query по умолчанию в Excel
Изменение настроек Power Query по умолчанию в Power BI
Извлечение
Настройки подключения (выбор данных)
Аутентификация
Предварительный просмотр
Выбор назначения запроса
Преобразование
Редактор Power Query
Преобразования по умолчанию
Источник (Source)
Повышенные заголовки (Promoted Headers)
Измененный тип (Changed Type)
Создание и изменение преобразований
Загрузка
Установка типов данных
Переименование запроса
Загрузка запроса в Excel
Загрузка запроса в Power BI
Обновление запросов
Редактирование запросов
Запуск редактора Power Query в Power BI
Запуск редактора Power Query в Excel
Просмотр шагов
Настройка шагов
Влияние Power Query
Глава 2. Управление запросами
Использование архитектуры со множеством запросов
Разделение запросов на E, T и L
Преимущества совмещения запросов
Преимущества разделения запросов
Влияние разделения запросов на производительность
Ссылки на запросы
Создание базового запроса
Ссылочные запросы
Визуализация дерева зависимостей запросов
Просмотр зависимостей при помощи Monkey Tools
Выбор места загрузки запроса
Выбор места загрузки запроса в Power BI
Выбор места загрузки запроса в Excel
Изменение места назначения
Организация запросов
Создание папок в Power Query
Перенос запросов в группы
Изменение порядка следования запросов и групп
Создание подпапок запросов
Разделение существующих запросов
Заключительные мысли об архитектуре запросов
Глава 3. Типы данных и ошибки
Типы и форматы данных
Форматы
Типы данных
Как устанавливать формат данных в Power Query
Порядок шагов имеет значение
Важность определения типов данных
Распространенные ошибки в Power Query
Ошибки на уровне шага
Ошибки источников данных
Ошибки вида «столбец X не найден
Ошибки значений
Обнаружение ошибок
Ошибки из-за неправильного приведения типов
Ошибки по причине несовместимости типов данных
Проверка запросов на ошибки
Обнаружение источника ошибок
Исправление исходного запроса
Удаление запроса с ошибками
Заключительные мысли о типах данных и ошибках
Глава 4. Перенос запросов между Excel и Power BI
Перенос запросов между решениями
Перенос запросов Excel в новую рабочую книгу
Перенос запросов из Excel в Power BI
Перенос запросов из Power BI в Excel
Перенос запросов из Power BI в новый проект Power BI
Импорт запросов из Excel в Power BI
Только внешние источники данных
Импорт модели данных Excel в Power BI
Импорт данных на основе таблиц Excel – копирование
Таблицы Excel – сохранение подключения
Заключительные мысли о переносе запросов между решениями
Глава 5. Импортирование из плоских файлов
Понимание процесса импорта данных
Определение системных настроек
Как программа интерпретирует плоские данные
Импортирование файлов с разделителями
Источник данных
Извлечение данных
Задача
Использование локали для установки корректных типов данных
Импортирование файлов без разделителей
Подключение к файлу
Очистка файлов без разделителей
Разделение столбцов по позиции
Прелесть ошибок в Power Query
Удаление лишних столбцов
Объединение столбцов
Разделение столбцов по разделителю
Исключение дублирующихся пробелов
Минута славы Power Query
Глава 6. Импортирование из файлов Excel
Данные в активной рабочей книге
Подключение к таблицам Excel
Подключение к табличным диапазонам
Подключение к именованным диапазонам
Динамические именованные диапазоны
Подключение к рабочим листам Excel из той же книги
Данные из других рабочих книг
Подключение к файлу Excel
Подключение к таблицам
Подключение к именованным диапазонам
Подключение к рабочим листам
Заключительные мысли о подключении к данным Excel
Глава 7. Простые техники преобразования данных
Снимаем проклятие сводных данных
Подготовка данных
Отмена свертывания других столбцов
Повторное сведение данных при помощи сводной таблицы
Есть ли жизнь после обновления данных
Разница между различными типами отмены свертывания
Сведение столбца
Разделение столбцов
Разделение столбца на несколько столбцов
Разделение столбца на строки
Разделение на столбцы с отменой свертывания против
разделения на строки
Фильтрация и сортировка
Фильтрация значений
Применение контекстных фильтров
Сортировка данных
Группирование данных
Глава 8. Добавление данных
Базовые операции по добавлению данных
Добавление двух таблиц
Добавление дополнительных таблиц
Объединение запросов с разными заголовками
Добавление таблиц и диапазонов в текущем файле
Консолидация таблиц
Консолидация диапазонов и рабочих листов
Используйте =Excel.CurrentWorkbook() с осторожностью
Заключительные мысли о добавлении запросов
Глава 9. Объединение файлов
Практический пример
Описание процесса
Методология объединения файлов
Архитектура запросов при объединении файлов
Шаг 0: подключение к папке
Подключение к локальной/сетевой папке
Подключение к папке SharePoint
Подключение к OneDrive для бизнеса
Подключение к другим файловым системам
Шаг 1: фильтрация и страховка на будущее
Методология шага
Применение шага 1 к нашему примеру
Шаг 2: объединение файлов
Методология шага
Применение шага 2 к нашему примеру
Шаг 3: преобразование данных в запросе примера
Почему нужно использовать запрос «Преобразовать пример
файла
Использование запроса «Преобразовать пример файла
Шаг 4: преобразование данных в мастер-запросе
Исправление ошибки на уровне шага в мастер-запросе
Сохранение свойств файлов
Добавление дополнительных шагов
Обновление
Использование данных
Добавление новых файлов
Повышение эффективности с помощью сохранения верхних строк
Глава 10. Объединение данных
Основы объединения данных
Создание подготовительных запросов
Выполнение объединения запросов
Типы соединений
Внешнее соединение слева
Внешнее соединение справа
Полное внешнее соединение
Внутреннее соединение
Антисоединение слева
Антисоединение справа
Полное антисоединение
Декартовы произведения
Методология
Пример
Случайные декартовы произведения
Объединения с приблизительными совпадениями
Методология
Пример
Поиск нечетких соответствий
Основы нечеткого поиска
Таблицы преобразования
Управление порогом подобия
Стратегии поддержки решений с нечетким поиском
Глава 11. Источники данных в интернете
Подключение к файлам данных в интернете
Подключение к веб-страницам
Подключение к данным на веб-странице
Естественные и предлагаемые таблицы
Добавление таблиц с использованием примеров
Подключение к страницам без таблиц
Предостережения при работе с данными из интернета
Сбор данных
Целостность данных
Надежность решения
Глава 12. Реляционные источники данных
Подключение к базам данных
Соединение с БД
Управление учетными данными
Не можете подключиться к нашей базе данных
Использование навигатора
Исследование данных
Свертывание запросов
Что такое свертывание запросов
Какие технологии поддерживает механизм свертывания запросов
Распространенные мифы относительно свертывания запросов
Уровни конфиденциальности
Объявление уровней конфиденциальности данных
Управление уровнями конфиденциальности данных
Конфиденциальность и производительность
Отключение движка конфиденциальности
Оптимизация
Глава 13. Преобразование табличных данных
Сложные шаблоны сведения данных
Сведение сгруппированных данных
Сведение вертикально сгруппированных данных
Сведение горизонтально сгруппированных данных
Сложные шаблоны отмены свертывания данных
Отмена свертывания данных с подкатегориями
Эффективная отмена свертывания данных с подкатегориями
Изменение запроса отмены свертывания данных
с подкатегориями
Сохранение значений null при отмене свертывания данных
Продвинутые техники группирования данных
Процент от целого
Ранжирование данных
Нумерация сгруппированных строк (номера строк по секциям)
Глава 14. Условная логика в Power Query
Основы условной логики
Описание набора данных
Подключение к данным
Создание условной логики при помощи интерфейса пользователя
Условная проверка в ручном режиме
Воспроизведение функции Excel ЕСЛИОШИБКА (IFERROR)
Работа с несколькими условиями
Сравнение со следующей/предыдущей строкой
Столбцы из примеров
Глава 15. Значения в Power Query
Типы значений в Power Query
Таблицы
Списки
Синтаксис
Создание списков
Преобразование списка в таблицу
Создание списка из столбца таблицы
Создание списка списков
Записи
Синтаксис
Создание записи
Преобразование записи в таблицу
Создание нескольких записей
Преобразование нескольких записей в таблицу
Доступ к записям таблиц по позиции (индексирование строк)
Доступ к записям таблиц по критерию
Создание записей из каждой строки таблицы
Значения
Двоичные данные
Ошибки
Ошибки на уровне строки
Ошибки на уровне шага
Функции
Ключевые слова в Power Query
#binary
#date, #datetime и #datetimezone
#time
#duration
type
#table
Глава 16. Изучаем язык M
Структура запроса на языке M
Структура запроса
Область определения запроса и идентификаторы
Обобщенные идентификаторы
Комментарии к коду
Собираем все воедино
Понимание процесса выполнения запроса
Что такое ленивое вычисление
План выполнения запроса
Итераторы (построчное выполнение)
Ремарка по поводу рекурсивных функций в Power Query
Ключевые слова each и
Другие техники
Получение первого значения из столбца таблицы
Замена на null при ошибке навигации
Создание динамического списка заголовков типизированных
столбцов
Создание динамического списка заголовков нетипизированных
столбцов
Глава 17. Параметры и пользовательские функции
Воссоздание метода объединения файлов
Создание примера файла (Sample File)
Создание параметра Sample File Parameter
Создание преобразования файла (Transform Sample)
Создание функции Transform Function
Вызов функции Transform Function
Обновление функции Transform Function
Ключевые выводы
Создание настраиваемых функций с помощью параметров
Создание параметра FilePath
Создание запроса Timesheet Transform
Создание функции Timesheet Function
Обновление запроса Timesheet
Создание настраиваемых функций вручную
Построение сценария разового применения
Преобразование запроса в функцию
Вызов функции
Отладка настраиваемых функций
Восстановление функциональности
Таблицы динамических параметров
Проблема с динамическими путями к файлам
Реализация таблицы динамических параметров
Создание таблицы параметров
Реализация функции fnGetParameter
Вызов функции
Применение таблиц параметров
Глава 18. Техники работы с датой и временем
Определение границ календаря
Динамическое создание границ календаря
Корректировка начальной и конечной дат для нестандартных
финансовых периодов
Корректировка начальной и конечной дат для 364-дневного
календаря
Календари с последовательными датами
Создание календаря
Обогащение календаря за счет дополнительных столбцов
Столбцы для финансовых периодов в 12-месячном календаре
Столбцы-идентификаторы периодов для 364-дневного календаря
Столбцы финансовых периодов для календарей
(и их разновидностей)
Что находится в файле с примерами
Заполнение особых диапазонов даты и времени
Заполнение определенного количества дат
Заполнение определенного количества часов по каждой дате
Заполнение определенного количества дат с заданными
интервалами
Разнесение данных на основе таблиц с датами
Разнесение данных по дням
Разнесение данных по целым месяцам
Разнесение данных по заданному количеству месяцев
от начальной даты
Заключительные штрихи к разнесению данных
Глава 19. Оптимизация запросов
Оптимизация настроек Power Query
Глобальные параметры загрузки данных
Глобальные параметры редактора Power Query
Глобальные параметры безопасности
Глобальные параметры конфиденциальности
Настройки текущей книги (файла) – фоновые данные
Настройки текущей книги (файла) – другие
Использование функций буферизации
Форсирование вычисления значения
Буферизация вычисления значения
Снижение временных лагов во время разработки
Стратегия уменьшения временных лагов
Пример борьбы с временными лагами при разработке
Адаптация решения для снижения временных лагов
Изменение данных в предпросмотре
Ошибка Formula Firewall
Ошибка Formula.Firewall №1: несовместимость уровней
конфиденциальности
Ошибка Formula.Firewall № 2: доступ к источнику данных
Вызов ошибки перестроения сочетания данных
Перестроение сочетания данных против создания цепочек
запросов
Перестроение сочетания данных против выравнивания запросов
Перестроение сочетания данных при передаче значений в SQL
Заключительные мысли об ошибках Formula.Firewall
Глава 20. Автоматизация обновлений
Варианты автоматического обновления в Excel
Обновления в Excel без VBA
Фоновое обновление
Обновление каждые X минут
Обновление при открытии рабочей книги
Быстрая загрузка данных
Автоматизация обновлений запросов в Excel с помощью VBA
Обновление одного подключения
Обновление в определенном порядке
Обновление всех запросов
Проблемы с синхронным обновлением
Расписание обновлений в Power BI
Предметный указатель