Руководство по Power Query

Очистка данных и получение данных из нескольких источников — самая большая проблема для пользователей Excel. Чтобы облегчить все это, Microsoft представила «Power Query» для Excel. Итак, если вы новичок в Power Query, мы проведем вас через это руководство.

Power Query — это дополнительный инструмент, доступный для Excel начиная с версии Excel 2016 и более поздних версий, но вы можете использовать его для версий Excel 2013 и 2010 также в качестве надстройки. Когда мы получаем данные из внешних источников для Excel, данные будут в уродливом формате и почти не смогут работать с этими данными, мы знаем, что вы испытали это!

Но с помощью Power Query в Excel мы можем получить доступ, преобразовать и очистить все уродливые данные в удобочитаемый формат. Мало того, что Power Query можно использовать для написания запросов, мы можем повторно использовать эти запросы для получения обновленных данных, просто нажав кнопку «Обновить».

Со всеми вышеперечисленными функциями Power Query по-прежнему удобен для пользователя, и на самом деле вы можете изучить это намного проще, чем вы изначально изучили MS Excel.

В отличие от VBA, для этого не обязательно иметь опыт работы с языком программирования.

Заметка: В версии Excel 2016 запрос мощности доступен как «Получить и преобразовать» на вкладке «Данные», но в версиях Excel 2010 и 2013 он является надстройкой. Вы можете использовать эту ссылку, чтобы скачать Надстройка Power Query.

Убедитесь, что вы загрузили версию (32-разрядную или 64-разрядную), соответствующую разрядной версии MS Excel. После установки вы должны увидеть это как отдельную вкладку.

Как работать с Power Query?

Работа с Excel Power Query — это просто удовольствие, потому что в нем есть удобные для пользователя параметры, а также в нем так много функций, что мы попытаемся привести здесь некоторые примеры.

Пример — импорт данных из текстового файла

Получение данных из текстового файла является обычным делом, и каждый столбец разделяется значением разделителя. Например, посмотрите на приведенную ниже таблицу данных.

Мы будем использовать мощный запрос, чтобы импортировать эти данные и преобразовать их в формат, с которым Excel любит работать.

Шаг 1 — Перейдите на вкладку «Данные» и в разделе «Получить данные» нажмите «Из файла», затем нажмите «Из файла». «Из текста / CSV ».

Шаг 2 — Теперь он попросит вас выбрать файл, который вы хотите импортировать, поэтому выберите файл и нажмите «ОК».

Шаг 3 — Это отобразит предварительный просмотр данных перед их загрузкой в ​​модель запроса, и это будет выглядеть так.

Как вы можете видеть выше, он автоматически определил разделитель как «запятую» и разделил данные на несколько столбцов.

Шаг 4 — Нажмите «Загрузить» внизу, и данные будут загружены в файл Excel в формате таблицы Excel.

Как вы можете видеть с правой стороны, у нас есть окно под названием «Запросы и соединения», поэтому это предполагает, что данные импортируются с помощью запроса мощности.

Шаг # 5 — После загрузки данных в Excel подключенный текстовый файл не должен быть поврежден в Excel, поэтому перейдите в текстовый файл и добавьте две дополнительные строки данных.

Шаг # 6 — Теперь перейдите в Excel и выберите таблицу, и в ней появятся еще две вкладки как «Дизайн запросов и таблиц».

Шаг # 7 — В разделе «Запрос» нажмите кнопку «Обновить», и данные будут обновлены с обновленными двумя новыми строками.

Шаг # 8 — Чтобы применить эти изменения, нажмите «Изменить запрос» на вкладке «Запрос».

Шаг # 9 — Чтобы сделать первую строку заголовком на вкладке HOME, щелкните «Использовать первую строку как заголовок».

Шаг № 10 — Нажмите на «Закрыть и загрузить»На вкладке ГЛАВНАЯ, и данные вернутся в превосходное состояние с измененными изменениями.

Не меняя фактического положения данных, запрос мощности изменил данные.

Введение в окно Power Query

Когда вы смотрите на окно запроса мощности, вы, должно быть, сбиты с толку, позвольте познакомить вас с окном запроса мощности.

1. Лента — Это похоже на наши ленты MS Excel, под каждой лентой у нас есть несколько функций для работы.
2. Список запросов — Это все таблицы, импортированные в эту книгу для улучшения результатов.
3. Панель формул — Это похоже на нашу панель формул в Excel, но здесь это M-код.
4. Предварительный просмотр данных — Это предварительный просмотр данных выбранной таблицы запроса.
5. Свойства — Это свойства выбранной таблицы.
6. Прикладные шаги — Это самый важный, здесь отображаются все примененные шаги в запросе мощности. Мы можем отменить действия, удалив запросы.

Это вводное руководство по превосходной модели Power Query, и у нас есть много других дел, связанных с Power Query, и мы увидим их в следующих статьях.

[expert_bq id=»1570″]С помощью Антисоединения слева в Power Query выявили полсотни накопленных огрехов несоответствие наименований, различие ссылок, отсутствие материалов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Начали с нехитрого: спасались от задвоения и снижали долю ручных операций. С помощью «Антисоединения слева» в Power Query выявили полсотни накопленных огрехов: несоответствие наименований, различие ссылок, отсутствие материалов.

Руководство по Power Query.

1. Лента — Это похоже на наши ленты MS Excel, под каждой лентой у нас есть несколько функций для работы.
2. Список запросов — Это все таблицы, импортированные в эту книгу для улучшения результатов.
3. Панель формул — Это похоже на нашу панель формул в Excel, но здесь это M-код.
4. Предварительный просмотр данных — Это предварительный просмотр данных выбранной таблицы запроса.
5. Свойства — Это свойства выбранной таблицы.
6. Прикладные шаги — Это самый важный, здесь отображаются все примененные шаги в запросе мощности. Мы можем отменить действия, удалив запросы.

Выборочность распределения ссылок по листам заставила отмести функции ВПР и ИНДЕКС. В результате, ручным дублированием строк создали ненужную работу и наплодили оплошностей. Файл разросся настолько, что еженедельное обновление стало отнимать больше часа.