Глава 10. Куда загружаются запросы Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Хотя большинство примеров в предыдущих главах заканчивалось загрузкой запроса Power Query в Таблицы Excel, это – не единственный вариант для размещения данных. По большому счету, у вас есть три варианта загрузки, некоторые из которых можно использовать в комбинации: Таблицы Excel, только подключение, модель Данных PowerPivot.

Рис. 10.1. Два запроса только для подключения, готовые к дальнейшему использованию

Скачать заметку в формате Word или pdf, примеры в формате архива

Таблицы Excel

Это метод загрузка по умолчанию. Когда вы нажимаете кнопку Закрыть и загрузить в редакторе Power Query, Excel создает новую Таблицу на новом листе для хранения данных запроса. Вновь созданная Таблица наследует имя запроса с несколькими изменениями:

• Пробелы преобразуются в подчеркивания.
• Если имя запроса уже используется в книге Excel для ранее созданной Таблицы или именованного диапазона, новое имя будет дополнено суффиксом _2

И вам не следует давать запросу имя, совпадающее с функцией Excel. Excel интерпретирует именованные диапазоны перед функциями, поэтому наличие Таблицы с именем ОКРУГЛ или ЛЕВСИМВ приведет к тому, что все ячейки, использующие эти функции, вернут ошибки.

Запросы только для подключения

Запросы для подключения настраиваются таким образом, чтобы избежать размещения данных на листе. Может возникнуть вопрос, зачем нужны такие запросы? Один из ответов был дан в главе 9: такие запросы позволяют не размещать ненужные данные на листах Excel. Но позже запросы можно объединить, и разместить в Таблице объединенный запрос.

Загрузка в Таблицу и создание запроса только для подключения являются взаимоисключающими опциями.

Откройте файл Load Destinations.xlsx, и используя инструкции из главы 9, сначала создайте два подключения к таблицам продаж за май и июнь (рис. 10.1). Теперь можно объединить два запроса:

• Щелкните правой кнопкой мыши запрос Sales—May –> Добавить
• В окне Добавление выберите Sales—Jun (рис. 10.2), нажмите Ok
• Переименовать запрос Sales
• Щелкните правой кнопкой мыши столбец Date –> Преобразование –> Только дата
• Главная –> Закрыть и загрузить

Рис. 10.2. Окно Добавление

Power Query создает новую таблицу, которая содержит все данные.

Рис. 10.3. Таблица в Excel на основе запроса, созданного добавлением из двух запросов только для подключения

Загрузка в модель данных Power Pivot

• В Excel в области Запросы и подключения щелкните правой кнопкой мыши запрос Sales
• В редакторе Power Query кликните стрелку вниз на кнопке Закрыть и загрузить
• Выберите Только создать подключение, установите флажок Добавить эти данные в модель данных (рис. 10.4)
• Нажмите Ok

Рис. 10.4. Изменение параметров для загрузки запроса в модель данных Power Pivot

Появится окно с предупреждением о возможной потере данных. Это происходит потому, что ранее данные размещались в Таблице на листе Excel. А теперь в запросе остается только подключение. Поскольку ровно на это направлены ваши изменения, подтвердите, что всё Ok. Перейдите на вкладку Power Pivot –> Управление:

Рис. 10.5. Запрос Sales, загруженный в модель данных Power Pivot

Изменения в запросе Power Query плавно перетекают в Power Pivot. Добавьте новый столбец в таблицу Sales в Power Query, чтобы увидеть, как Power Pivot обрабатывает его:

• Вернитесь в Excel, в области Запросы и подключения наведите мышку на запрос Sales, во всплывающем окне Sales, кликните Изменить
• В редакторе Power Query выберите столбец Date, перейдите на вкладку Добавление столбца –> Дата –> Месяц –> Месяц (рис. 10.6)
• Главная –> Закрыть и загрузить
• Вернитесь в окно Power Pivot

Рис. 10.6. Добавление столбца Месяц; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Появился новый столбец – Месяц. Даже не потребовалось нажимать Обновить:

Рис. 10.7. Столбец, добавленный в Power Query, автоматически отобразился в модель данных Power Pivot

Изменение параметров загрузки запросов по умолчанию

Если вы обнаружите, что вы делаете много изменений при загрузке запросов, вы можете установить новые опции по умолчанию. В Excel 2016+ пройдите Данные –> Получить данные –> Параметры запроса. Выберите Указать пользовательские параметры загрузки по умолчанию, а затем настройте эти параметры по своему усмотрению. Хитрость в том, что, сняв флажок Загрузить в лист, вы создаете запрос только для подключения. Также можно выбрать Загрузить в модель данных.

Рис. 10.8. Изменение настроек загрузки по умолчанию