Power Query в Excel

Как использовать Power Query в Excel?

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

Шаги

По сути, есть 4 шага, и порядок этих 4 шагов в Power Query следующий:

  1. Подключить: Сначала мы подключаемся к данным, которые могут быть где-то, в облаке, в службе или локально.
  2. Преобразовать: Второй шаг — изменить форму данных в соответствии с требованиями пользователя.
  3. Комбинировать: На этом этапе мы выполняем некоторые этапы преобразования и агрегирования и объединяем данные из обоих источников для создания комбинированного отчета.
  4. Управлять: Это объединяет и добавляет столбцы в запросе с соответствующими столбцами в других запросах в книге.

Excel Power Query обладает множеством сверхмощных функций.

Предположим, у нас есть данные о покупках за последние 15 лет в 180 файлах. Теперь руководству организации потребуется консолидировать цифры, прежде чем анализировать их. Руководство может использовать любой из следующих методов:

  1. Они открывали все файлы и копировали их в один файл.
  2. С другой стороны, они могут использовать мудрое решение, которое заключается в применении формул, поскольку оно подвержено ошибкам.

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

Однако Power Query может помочь им не выполнять эту утомительную и повторяющуюся работу. Давайте разберемся с этим запросом мощности Excel на примере.

Power-Query-в-Excel

пример

Предположим, у нас есть текстовые файлы в папке с данными о продажах, и мы хотим получить эти данные в нашем файле Excel.

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

Типы файлов

Чтобы сделать то же самое, шаги будут следующими:

Шаг 1: Во-первых, нам нужно получить данные в Power Query, чтобы мы могли внести необходимые изменения в данные, чтобы импортировать их в файл Excel.

Чтобы сделать то же самое, мы выберем «Из папки» вариант из «Из файла» меню после нажатия на команду «Получить данные» из «Получить и преобразовать» группа в «Данные» таб.

Power Query Excel, пример 1.1

Шаг 2: Выберите расположение папки, просматривая.

Power Query Excel, пример 1.2

Нажмите «ОК».

Шаг 3: Откроется диалоговое окно, содержащее список всех файлов в выбранной папке с заголовками столбцов в виде «Контент» ‘Имя,’ «Расширение» «Дата доступа», ‘Дата изменена,’ ‘Дата создания,’ ‘Атрибуты’ и ‘Путь к папке.’
Power Query Excel, пример 1.3

Есть 3 варианта, т.е. Объединить, Нагрузка, и Преобразовать данные.

  • Объединить: Эта опция используется для перехода к экрану, где мы можем выбрать, какие данные объединить. Этап редактирования пропускается для этой опции и не дает нам контроля над тем, какие файлы объединять. Функция объединения объединяет каждый файл в папке, что может привести к ошибкам.
  • Нагрузка: Этот параметр просто загрузит таблицу, как показано на рисунке выше, в рабочий лист Excel вместо фактических данных в файлах.
  • Преобразовать данные: в отличие от ‘Объединить’ команда, если мы используем эту команду, то мы можем выбрать, какие файлы объединять, т.е. мы можем объединить файлы только одного типа (с одинаковым расширением).

Как и в нашем случае, мы хотим объединить только текстовые файлы (.txt); мы выберем «Преобразование данных» команда.

Power Query Excel, пример 1.4

В правой части окна мы видим «Примененные шаги». На данный момент делается только один шаг, который заключается в извлечении сведений о файлах из папки.

Шаг 4: Есть столбец с именем ‘Расширение’ где мы можем видеть, что значения в столбце записываются в обоих случаях, то есть в верхнем и нижнем регистре.

Однако нам нужно преобразовать все значения в нижний регистр, поскольку фильтр различает оба. Чтобы сделать то же самое, нам нужно выбрать столбец, а затем выбрать «Нижний регистр» из «Формат» меню команд.

Power Query Excel, пример 1.5

Шаг 5: Мы будем фильтровать данные, используя ‘Расширение’ столбец для текстовых файлов.

Power Query Excel, пример 1.6

Шаг 6: Теперь нам нужно объединить данные для обоих текстовых файлов, используя первый столбец «Контент». Мы щелкнем по значку, расположенному справа от имени столбца.

Power Query Excel, пример 1.7

Шаг 7: Диалоговое окно с заголовком ‘Объединить файлы’ откроется, где нам нужно выбрать разделитель как ‘Tab’ для текстовых файлов (файлы с расширением .txt) и может выбрать базу для определения типа данных. И нажмите «ОК».

Power Query Excel, пример 1.8

После нажатия на ‘ХОРОШО,’ мы получим объединенные данные текстовых файлов в ‘Power Query’ окно.

Power Query Excel, пример 1.9

При необходимости мы можем изменить тип данных столбцов. Для «Доход» столбец, мы изменим тип данных на ‘Валюта.’

Изменить тип данных

Мы можем увидеть шаги, применяемые к данным, используя мощный запрос в правой части окна.

Прикладные шаги

После внесения всех необходимых изменений в данные мы можем загрузить данные в рабочий лист Excel, используя «Закрыть и загрузить в» команда под ‘Закрыть’ группа в ‘Главная’ таб.

Закрыть и загрузить в команду

Нам нужно выбрать, хотим ли мы загрузить данные в виде таблицы или соединения. Затем нажмите на ‘ХОРОШО.’

Таблица или соединение

Теперь мы можем видеть данные в виде таблицы на листе.

Рабочий лист - Пример 1.14

И «Запросы к книге» панель с правой стороны, которую мы можем использовать для редактирования, дублирования, объединения, добавления запросов и для многих других целей.

Запросы к книге - пример 1.15

Excel Power Query очень полезен, поскольку мы видим, что за несколько минут было загружено 601 612 строк.

То, что нужно запомнить

  • Power Query не изменяет исходные исходные данные. Вместо изменения исходных исходных данных он записывает каждый шаг, выполняемый пользователем при подключении или преобразовании данных, и, как только пользователь завершает формирование данных, он берет уточненный набор данных и вносит его в книгу.
  • Power Query чувствителен к регистру.
  • При объединении файлов в указанной папке нам необходимо убедиться, что используется столбец «Расширение», и мы должны исключить временные файлы (с расширением «.tmp», а имена этих файлов начинаются со знака «~») как Power Query также может импортировать эти файлы.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.