Приручи данные с помощью Power Query в Excel и Power BI: использование Power Query для получения и преобразования исходных данных

Оглавление
Предисловие от издательства
	Отзывы и пожелания
	Список опечаток
	Нарушение авторских прав
Предисловие
	Как Power Query изменил НАШИ жизни
		История Кена: «Кофе и Power Query»
		История Мигеля: новый старт
	Благодарности от авторов
		Благодарности от Кена
		Благодарности от Мигеля
		Наши преданные читатели
		И наконец…
Глава 0. Революция данных
	Общий сценарий для аналитиков данных
	Преимущества и опасности черной магии
	Будущее изменилось
	Почему Power Query – это магия?
		Извлечение
		Преобразование
		Загрузка
	Возможности Power Query и интеграция с другими продуктами
		Компоненты Power Query
	Цикл обновлений Power Query
		Power Query Online
		Microsoft 365
		Excel 2016/2019/2021
		Excel 2010 & 2013
		Power BI Desktop
	Как использовать эту книгу
		Где найти Power Query?
		Excel 365
		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
		Применение шага 1 к нашему примеру
	Шаг 2: объединение файлов
		Методология шага 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 Functio
		Обновление запроса Timesheet
	Создание настраиваемых функций вручную
		Построение сценария разового применения
		Преобразование запроса в функцию
		Вызов функции
		Отладка настраиваемых функций
		Восстановление функциональности
	Таблицы динамических параметров
		Проблема с динамическими путями к файлам
		Реализация таблицы динамических параметров
		Создание таблицы параметров
		Реализация функции fnGetParameter
		Вызов функции
	Применение таблиц параметров
Глава 18. Техники работы с датой и временем
	Определение границ календаря
		Динамическое создание границ календаря
		Корректировка начальной и конечной дат для нестандартных финансовых периодов
		Корректировка начальной и конечной дат для 364-дневного календаря
	Календари с последовательными датами
		Создание календаря
		Обогащение календаря за счет дополнительных столбцов
		Столбцы для финансовых периодов в 12-месячном календаре
		Столбцы-идентификаторы периодов для 364-дневного календаря
		Столбцы финансовых периодов для календарей 4-4-5  (и их разновидностей)
		Что находится в файле с примерами?
	Заполнение особых диапазонов даты и времени
		Заполнение определенного количества дат
		Заполнение определенного количества часов по каждой дате
		Заполнение определенного количества дат с заданными интервалами
	Разнесение данных на основе таблиц с датами
		Разнесение данных по дням
		Разнесение данных по целым месяцам
		Разнесение данных по заданному количеству месяцев от начальной даты
		Заключительные штрихи к разнесению данных
Глава 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
Предметный указатель