Power Query в Excel и Power BI: сбор, объединение и преобразование данных

Оглавление
Вступительное слово
Об авторе
Благодарности
Введение
	Для кого предназначена эта книга
		Что нужно знать
	Структура книги
	О сопровождающем контенте
	Требования к системе
	Как получить поддержку и оставить отзыв
		Ошибки и поддержка книги
	Будьте на связи
Глава 1. Введение в Power Query
	Знакомство с Power Query
	Краткий очерк истории становления Power Query
	Где "прячется" Power Query?
	Основные компоненты Power Query
		Интерфейс Получить данные
		Основные панели редактора Power Query
			Панель просмотра
			Лента
			Панель Запросы
			Панели Параметры запроса и Примененные шаги
			Строка формул, расширенный редактор и язык запросов М
			Окна диалога Параметры запроса в Excel и Параметры в Power BI
		УПРАЖНЕНИЕ 1.1. Знакомство с Power Query
	Резюме
Глава 2. Основные проблемы, возникающие при подготовке данных
	Извлечение значения из кодированных столбцов
	База данных AdventureWorks
		УПРАЖНЕНИЕ 2.1. Старый способ: применение формул Excel
		УПРАЖНЕНИЕ 2.2. Часть 1. Новый способ
		УПРАЖНЕНИЕ 2.2. Часть 2. Объединение таблиц поиска
		УПРАЖНЕНИЕ 2.2. Часть 3. Таблицы фактов и таблицы поиска
	Применение набора функций из раскрывающегося меню Столбец из примеров
		УПРАЖНЕНИЕ 2.3. Часть 1. Знакомство с функцией Столбец из примеров
		Практическое применение набора функций Столбец из примеров
		УПРАЖНЕНИЕ 2.3. Часть 2. Преобразование размера в группы/диапазоны
	Выборка информации из текстовых столбцов
		УПРАЖНЕНИЕ 2.4. Извлечение гиперссылок из сообщений
	Работа с датами
		УПРАЖНЕНИЕ 2.5. Обработка нескольких форматов для дат
		УПРАЖНЕНИЕ 2.6. Работа с датами при использовании двух локальных настроек
		Выборка элементов даты и времени
	Подготовка модели данных
		УПРАЖНЕНИЕ 2.7. Разделение данных на таблицы поиска и таблицы фактов
			Когда связи некорректны
		УПРАЖНЕНИЕ 2.8. Разбиение на строки значений с разделителями
	Резюме
Глава 3. Объединение данных, полученных из нескольких источников
	Объединение нескольких таблиц
		Объединение двух таблиц
		УПРАЖНЕНИЕ 3.1. Пример c таблицами Bikes и Accessories
		УПРАЖНЕНИЕ 3.2. Часть 1. Применение команды Объединить запросы в новый
		УПРАЖНЕНИЕ 3.2. Часть 2. Окна диалога Зависимости запроса и Ссылки
		Объединение трех и большего числа таблиц
		УПРАЖНЕНИЕ 3.2. Часть 3. Bikes + Accessories + Components
		УПРАЖНЕНИЕ 3.2. Часть 4. Bikes + Accessories + Components + Clothing
	Объединение большого количества таблиц
		Добавление таблиц из папки
		УПРАЖНЕНИЕ 3.3. Добавление таблицы AdventureWorks Products из папки
		Команда Импорт из папки
		Добавление листов из книги
		УПРАЖНЕНИЕ 3.4. Добавление рабочих листов — решение
		Надежный подход к объединению нескольких листов
	Резюме
Глава 4. Объединение несовпадающих таблиц
	Трудности, вызванные несоответствием таблиц
		Что такое несоответствующие таблицы?
		Особенности и риски, связанные с несоответствующими таблицами
		УПРАЖНЕНИЕ 4.1. Устранение несоответствующих названий столбцов: простейший подход
	Объединение несоответствующих таблиц из одной папки
		УПРАЖНЕНИЕ 4.2. Часть 1. Иллюстрация особенности, связанной с пропущенными значениями
		УПРАЖНЕНИЕ 4.2. Часть 2. Предположение о том же порядке столбцов и обобщение заголовка
		УПРАЖНЕНИЕ 4.3. Простая нормализация, выполняемая с помощью функции Table.TransformColumnNames
		Таблица преобразования
		УПРАЖНЕНИЕ 4.4. Методика транспонирования, реализуемого с помощью таблицы преобразования
			Преобразование транспонирования
			Последовательность объединения
			Обратное транспонирование
		УПРАЖНЕНИЕ 4.5. Отмена свертывания, слияние и повторное сведение
		УПРАЖНЕНИЕ 4.6. Транспонирование лишь имен столбцов
		УПРАЖНЕНИЕ 4.7. Применение языка M для нормализации имен столбцов
	Резюме
Глава 5. Сохранение контекста
	Сохранение контекста в именах файлов и листах
		УПРАЖНЕНИЕ 5.1. Часть 1. Методика, предусматривающая применение настраиваемого столбца
		УПРАЖНЕНИЕ 5.1. Часть 2. Обработка контекста, состоящего из имен файлов и листов
	Предварительное добавление для сохранения заголовков
		УПРАЖНЕНИЕ 5.2. Сохранение заголовков с помощью детализации
		Применение детализации к ячейке таблицы (объяснение с помощью M-формул)
		Объединение в запросе результатов детализации
		УПРАЖНЕНИЕ 5.3. Сохранение заголовков из папки
	Сохранение заголовков при постдобавлении контекста
		УПРАЖНЕНИЕ 5.4. Сохранение заголовков из рабочих листов в одной книге
	Использование подсказок контекста
		УПРАЖНЕНИЕ 5.5. Применение столбца индекса в качестве подсказки
		УПРАЖНЕНИЕ 5.6. Идентификация контекста с учетом близости ячейки
	Резюме
Глава 6. Отмена свертывания столбцов таблицы
	Идентификация плохо спроектированных таблиц
	Знакомство с преобразованием отмены свертывания столбцов
		УПРАЖНЕНИЕ 6.1. Применение преобразований отмены свертывания столбцов и отмены свертывания других столбцов
		УПРАЖНЕНИЕ 6.2. Использование команды Отменить свертывание только выделенных столбцов
	Обработка итогов
		УПРАЖНЕНИЕ 6.3. Применение преобразования отмены свертывания столбцов к общим итогам
			Удаление общих итогов
	Применение преобразования отмены свертывания столбцов на уровнях иерархии 2x2
		УПРАЖНЕНИЕ 6.4. Применение преобразования отмены свертывания таблиц для уровней иерархии 2x2 с датами
		УПРАЖНЕНИЕ 6.5. Применение преобразования отмены свертывания столбцов к таблицам с уровнями иерархии 2x2
	Обработка промежуточных итогов при работе с данными, для которых отменено свертывание столбцов
		УПРАЖНЕНИЕ 6.6. Обработка промежуточных итогов
	Резюме
Глава 7. Дополнительные вопросы, связанные с применением преобразования сведения столбцов и отмены свертывания столбцов
	Применение преобразования отмены свертывания столбцов к таблицам с несколькими уровнями иерархии
		Виртуальная сводная таблица, определение полей строк и столбцов
		УПРАЖНЕНИЕ 7.1. Применение преобразования отмены свертывания столбцов к таблице AdventureWorks с уровнями иерархии NxM
	Обобщение последовательности преобразований отмены свертывания столбцов
		УПРАЖНЕНИЕ 7.2. Начнем с конца
		УПРАЖНЕНИЕ 7.3. Создание функции FnUnpivotSummarizedTable
			Часть 1. Создание отчетов RowFields, ColumnFields и ValueField
			Часть 2. Удаление шагов Измененный тип
			Часть 3. Функции Table.ColumnNames, List.FirstN и List.Count
			Часть 4. Переименование столбцов и функция ListZip
			Часть 5. Преобразование запроса в функцию
			Часть 6. Тестирование функции
			Выводы
	Преобразование сведения в столбец
		УПРАЖНЕНИЕ 7.4. Обращение таблицы с некорректной отменой свертывания столбцов
		УПРАЖНЕНИЕ 7.5. Применение преобразования сведения в столбец к таблицам с многострочными записями
			Часть 1. Работа с фиксированным числом атрибутов и применение целочисленного деления
			Часть 2. Обработка нефиксированного числа атрибутов с помощью команд Условный столбец и Заполнение вниз
	Резюме
Глава 8. Совместная работа
	Локальные файлы, параметры и шаблоны
		Ошибка при доступе к локальным файлам
		УПРАЖНЕНИЕ 8.1. Применение параметра для имени пути
		УПРАЖНЕНИЕ 8.2. Создание шаблона Power BI
		УПРАЖНЕНИЕ 8.3. Часть 1. Применение параметров в Excel
		УПРАЖНЕНИЕ 8.3. Часть 2. Изменения в комбинациях данных
	Работа с общедоступными файлами и папками
		Импортирование данных из файлов в OneDrive for Business или SharePoint
		УПРАЖНЕНИЕ 8.4. Миграция запросов для подключения к OneDrive for Business или SharePoint
		Различия между OneDrive for Business и SharePoint Online
		УПРАЖНЕНИЕ 8.5. От локальной папки к общедоступной
	Вопросы безопасности
		Удаление всех запросов в Excel с помощью инспектора документов
	Резюме
Глава 9. Введение в язык формул M Power Query
	Изучаем возможности языка M
		Изучая "продвинутые" возможности языка М
			Этап 1. Только интерфейс пользователя
			Этап 2. Простое редактирование в строке формул
			Этап 3. Формулы M в настраиваемых столбцах
			Этап 4. Настраиваемые функции в окне расширенного редактора
			Этап 5. Расширенные вычисления (List.Accumulate, List Generate)
			Этап 6. Кодирование в произвольной форме в окне расширенного редактора
		Ресурсы в Интернете
		Локальные ресурсы
		УПРАЖНЕНИЕ 9.1. Применение переменной #shared для изучения встроенных функций
	Компоненты языка M
		УПРАЖНЕНИЕ 9.2. Hello World
			Выражение let
			Объединение выражений из нескольких запросов и областей доступа
	Типы, операторы и встроенные функции в языке M
	Основные типы данных языка M
		Числовой тип данных
		Функции времени
		Тип даты
		Тип длительности
		Текстовый тип данных
		Тип данных Null
		Логический тип данных
	Сложные типы данных
		Тип списка
		Операторы списка
		Функции List.X
		Записи
		Операторы записи
		Функции Record.X
		Табличный тип
	Условия и выражения if
		Выражение if-then-else
			Выражение if внутри выражения let
	Пользовательские функции
		Вызов функции
		Выражение each
	Дополнительные темы
	Обработка ошибок
		Отложенные и немедленные вычисления
		Циклы
		Рекурсия
		Функция List.Generate
		Функция List.Accumulate
	Резюме
Глава 10. От ловушек к робастным запросам
	Причины и следствия ловушек
		Осведомленность
		Лучшие практические методики
		Модификации кода M
	Ловушка 1. Игнорирование строки формул
		УПРАЖНЕНИЕ 10.1. Применение строки формул для обнаружения статических ссылок на имена столбцов
	Ловушка 2. Измененные типы
	Ловушка 3. Небезопасная фильтрация
		УПРАЖНЕНИЕ 10.2. Часть 1. Фильтрация товаров, окрашенных в черный цвет
		Логика, определяющая условия фильтрации
		УПРАЖНЕНИЕ 10.2. Часть 2. Поиск значений на панели Фильтр
	Ловушка 4. Переупорядочение столбцов
		УПРАЖНЕНИЕ 10.3. Часть 1. Переупорядочение подмножества столбцов
		УПРАЖНЕНИЕ 10.3. Часть 2. Пользовательская функция FnReorderSubsetOfColumns
	Ловушка 5. Удаление и выделение столбцов
		УПРАЖНЕНИЕ 10.4. Обработка случайных столбцов в таблице Wide World Importers
		Игнорирование пропущенного столбца
		Выделение или удаление столбцов на основе их расположения
		Выделение или удаление столбцов на основе их имен
	Ловушка 6. Переименование столбцов
		УПРАЖНЕНИЕ 10.5. Переименование столбцов Random в таблице Wide World Importers
		Настраиваемая функция FnRenameColumnsByIndices
		Функция Table.TransformColumnNames
	Ловушка 7. Разбиение столбца на другие столбцы
		УПРАЖНЕНИЕ 10.6. Выполнение некорректного разбиения
	Ловушка 8. Слияние столбцов
	Дополнительные ловушки и методы для создания надежных запросов
	Резюме
Глава 11. Основы текстовой аналитики
	Поиск ключевых слов в текстовых столбцах
		УПРАЖНЕНИЕ 11.1. Основная методика, применяемая для поиска ключевых слов
		Применение декартова произведения для определения ключевых слов
		УПРАЖНЕНИЕ 11.2. Реализация декартова произведения
			Часть 1. Начальная подготовка
			Часть 2. Декартово произведение
			Часть 3. Создание соответствий
			Часть 4. Повышение производительности
		УПРАЖНЕНИЕ 11.3. Определение ключевых слов с помощью настраиваемой функции
		Выбор метода: статический поиск, декартово произведение или настраиваемая функция
	Разбиение слов
		УПРАЖНЕНИЕ 11.4. Простое разбиение слов
			Часть 1. Разбиение слов с помощью пробелов
			Часть 2. Отделение слов от знаков препинания
			Часть 3. Разделение слов пробелами и удаление пунктуации
		УПРАЖНЕНИЕ 11.5. Фильтрация по стоп-словам
		УПРАЖНЕНИЕ 11.6. Поиск по ключевым словам с помощью разделенных слов
			Часть 1. Использование внутренних запросов на слияние для сопоставления столбцов
			Часть 2. Обнаружение составных ключевых слов
		УПРАЖНЕНИЕ 11.7. Создание "облаков" слов в Power BI
	Резюме
Глава 12. Расширенная текстовая аналитика: извлечение смысла
	Службы Microsoft Azure Cognitive Services
		Развертывание ключей и ресурсов API в Azure
		Развертывание API Translator Text
		Развертывание API Text Analytics
		Плюсы и минусы доступа к Cognitive Services, получаемого через Power Query
			Чрезмерные вызовы API
			Конфиденциальность и безопасность
			Неподдерживаемое обновление по расписанию
	Перевод текста
		Справочник по Translator Text API
		УПРАЖНЕНИЕ 12.1. Простой перевод
			Часть 1. Формирование контента JSON для тела запроса
			Часть 2. Создание параметра API Key
			Часть 3. Создание веб-запроса
			Совместное использование отчета без ключа API
		УПРАЖНЕНИЕ 12.2. Перевод нескольких сообщений
		Совместное использование отчета без ключа API: удобный способ
	Анализ настроений
		Вызов API Sentiment Analysis
			Подготовка данных
		УПРАЖНЕНИЕ 12.3. Реализация настраиваемой функции анализа настроений FnGetSentiment
			Настраиваемая функция FnGetSentiment
			Часть 1. Загрузка данных
			Часть 2. Подготовка сообщений
			Часть 3. Конвертирование таблицы в формат JSON
			Часть 4. Установка API Key
			Часть 5. Отправка веб-запроса
			Часть 6. Обработка ответа
			Часть 7. Создание настраиваемой функции
			Часть 8. Вызов функции FnGetSentiment
			Как избежать ошибок
		УПРАЖНЕНИЕ 12.4. Выполнение анализа настроений для больших наборов данных
	Извлечение ключевых фраз
		УПРАЖНЕНИЕ 12.5. Преобразование логики настроений в ключевые фразы
	Поддержка нескольких языков
		Замена языкового кода
		Динамическое обнаружение языков
		УПРАЖНЕНИЕ 12.6. Преобразование логики настроений в обнаружение языка
	Резюме
Глава 13. Аналитика социальных сетей
	Начало работы с соединителем Facebook
		УПРАЖНЕНИЕ 13.1. Поиск страниц, которые вам понравились
	Анализ предпочтений ваших друзей
		УПРАЖНЕНИЕ 13.2. В поисках друзей Power BI и их друзей
		УПРАЖНЕНИЕ 13.3. Найдите страницы, которые понравились вашим друзьям
	Анализ страниц Facebook
		УПРАЖНЕНИЕ 13.4. Извлечение сообщений и комментариев из страниц Facebook, основная возможность
		Сокращение обзора: фильтрация результатов по времени
		УПРАЖНЕНИЕ 13.5. Анализ вовлеченности пользователей путем подсчета комментариев и общих ресурсов
		УПРАЖНЕНИЕ 13.6. Сравнение нескольких страниц
	Резюме
Глава 14. Заключительный проект: объединяем все вместе
	УПРАЖНЕНИЕ 14.1. Спасите данные компании Wide World Importers
		Подсказки
		Часть 1. Приступим к решению
		Часть 2. Вызов функции отмены сведения столбца
		Часть 3. Последовательность свертывания столбцов для доходов 2018 года
		Часть 4. Объединение доходов за 2018 и 2015–2017 годы
	УПРАЖНЕНИЕ 14.2. Сравнение таблиц и отслеживание действий хакеров
		Подсказка
	УПРАЖНЕНИЕ 14.2. Решение
		Обнаружение следов деятельности хакеров во "взломанной" таблице
	Резюме
Предметный указатель