Анализ данных • 16 июля 2024 • 5 мин чтения

Power Query — это бесплатная надстройка Excel. Она преобразовывает данные различных форматов, объединяет их в один отчёт и автоматически их обновляет, чтобы информация всегда была актуальной. С помощью надстройки можно делать следующее:

● Подключать источники данных. Расширения — стандартные (XLS, TXT/CSV, PDF) и формата FOLDER, JSON, SharePoint folder и т. д. Базы данных — Amazon Redshift, Vertica, SAP Hana и другие. Полный список можно посмотреть на сайте Microsoft.
● Объединять таблицы. Создавать единый отчёт из разных таблиц и файлов.
● Редактировать данные. Очищать данные от ненужных символов, пробелов, дубликатов, ошибок, объединять или разделять столбцы, менять регистр текста, форматы ячеек и т. д.
● Обновлять данные. Все изменения в файлах, которые подключены к Power Query, автоматически обновляются в системе. Это удобно для ведения отчётов или анализа динамики показателей.
● Импортировать данные. Таблицы и отчёты из Power Query можно импортировать в Excel и Power Pivot. Кроме того, данные из Power Query можно визуализировать в виде диаграмм или преобразовать их в сводные отчёты.

Научиться работать в Excel можно на курсе «Excel для работы». Студенты учатся решать рабочие задачи с помощью табличного редактора, визуализировать результаты работы и анализировать данные. Помимо этого на курсе учат делать сводные таблицы, пользоваться горячими клавишами, фильтрами и формулами.

Способ установки Power Query зависит от версии Excel. В Excel 2010 и 2013 годов Power Query нужно устанавливать отдельно. Перед этим узнайте разрядность своего Excel — это необходимо для корректной установки Power Query:

1. Чтобы узнать разрядность, нажмите «Файл» → «Справка». В правом углу экрана под строкой «О программе Microsoft» в разделе «Версия» находится информация о разряде. В нашем случае Excel 32-разрядная.

Разрядность Excel нужно запомнить для установки Power Query. Источник

2. Теперь перейдите на официальный сайт Microsoft и нажмите на кнопку Download. Выберите версию Power Query: 32 или 64-разрядную. Нажмите «Далее».

3. Когда загрузка файла завершилась, запустите его и следуйте инструкциям на экране. После установки в шапке Excel появится новая вкладка Power Query.

После установки кнопка Power Query появится в верхнем меню Excel. Источник

С 2016 года инструмент уже встроен в стандартный набор Excel. Начиная с 2016 года Power Query называется Get & Transform. Чтобы его найти, нажмите «Данные» → в зависимости от версии Excel выберите «Создать запрос» или «Получить данные».

С 2016 года Power Query входит в стандартный набор команд Excel и находится во вкладке «Данные». Источник

В Power Query можно работать с 13 типами данных. Полный список можно посмотреть на сайте Microsoft. Расскажем про некоторые из них:

● Текстовый — строки текста. Могут быть в виде дат, чисел, времени в текстовом формате.
● Логический — логические значения TRUE (истина) и FALSE (ложь). Нужен для создания новых столбцов или расчётов.
Допустим, нужно отсортировать SEO-запросы на низкочастотные и высокочастотные. Укажем, что TRUE — больше 10 тысяч запросов, а FALSE — меньше 10 тысяч. Система обработает эти данные и распределит их по нужным столбцам.
● Числа — преобразование чисел в десятичные, целые, валюту или процент.
● Дата и время — сортировка данных по времени, дате, месяцам, годам, кварталам, неделям, дням или часовому поясу. Также можно сортировать данные на начало или конец периода — например, на конец квартала или начало года.

Кроме перечисленных выше, Power Query также поддерживает такие типы данных, как ссылки на ячейки, двоичные данные и т. д.

Для примера рассмотрим способы загрузки данных в формате XLS и CSV.

XLS:

1. Откройте в Excel нужный отчёт и кликните на любую ячейку из нужной таблицы. Затем нажмите «Данные» → «Получить данные» → «Из файла» → «Из таблицы». На листе отобразится окно с диапазоном — нажмите «Ок».

Импорт данных в Power Query происходит в несколько кликов. Источник

2. Сервис откроет окно редактора запросов, в который система перенесла данные из таблицы Excel. Чтобы документ не затерялся, ему нужно присвоить имя.

В Power Query перенеслись все данные из таблицы, включая заголовки, столбцы и данные ячеек. Источник

CSV:

1. Нажмите «Данные» → «Получить данные» → «Из файла» → «Из CSV». Откроется окно импорта данных, где нужно указать нужный файл CSV и нажать кнопку «Импорт». В нашем случае это файл с данными о продажах за январь.

Импорт CSV-файла в Power Query. Источник

2. Сервис откроет окно предпросмотра — нажмите кнопку «Изменить».

При импорте CSV-файла в Power Query откроется окно с документом. Убедитесь, что файл верный. Затем можно переходить к редактированию таблицы. Источник

3. На экране отобразится окно редактора запросов — это значит, что данные загружены и можно приступать к их преобразованию.

Так выглядит импортированный CSV-файл в редакторе Power Query. Источник

Для примера возьмём уже загруженную таблицу за январь в CSV-формате и добавим в неё данные за февраль. Таким образом данные за месяцы будут накапливаться для анализа динамики.

В Power Query данные любого формата автоматически сохраняются в Excel, что и произошло с файлом CSV. Чтобы объединить данные января и февраля, их нужно удалить из Excel и оставить только в виде подключения в Power Query. Для этого:

1. Нажмите правой кнопкой мыши на название файла «Январь» и выберите «Загрузить в…».

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

2. На экране появится окно с выбором просмотра данных. Нажмите «Только создать соединение», затем на кнопку «Загрузить».

После этого данные из Excel удалятся. Не пугайтесь, они по-прежнему находятся в CSV-файле. Теперь Power Query может подгружать и обновлять данные из этой таблицы через подключение. Источник

3. Теперь нам нужно загрузить в Power Query данные за февраль и объединить их с данными за январь. Нажмите «Данные» → «Получить данные» → «Из файла» → «Из CSV». Затем укажите новый файл и нажмите «Открыть».

В Power Query можно добавить одну или несколько таблиц для формирования отчёта. Источник

4. На экране отобразится окно редактора запросов с таблицей за февраль. Её также нужно удалить из Excel и оставить в виде подключения в Power Query, как мы это сделали с январём.

Все операции в Power Query похожи, поэтому можно быстро научиться пользоваться надстройкой. Источник

5. Когда просмотр нового файла изменён, в правой строке появятся названия двух файлов.

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

6. Теперь таблицы за два месяца нужно объединить. Нажмите «Создать запрос» → «Объединить запросы» → «Добавить».

Создание нового отчёта или документа происходит через раздел «Создать запрос». В дальнейшем добавлять в этот документ новые файлы тоже можно будет через этот раздел. Источник

7. Теперь нужно указать, какие файлы добавлять. В первом поле указываем январь, во втором — февраль и нажимаем кнопку «Ок».

В один отчёт можно добавлять одну или сразу несколько таблиц. Источник

8. На экране отобразится объединённая таблица с данными за два месяца. Система автоматически присвоит имя файла — измените его на нужное: например, «Сводный отчёт» или «Отчёт о продажах».

Информация в таблице отображается сверху вниз, от меньшего к большему или в нашем случае — от января к февралю. Источник

Мы добавили два файла в Power Query и объединили их в один отчёт. Теперь с ним можно работать, добавлять новые данные и анализировать их.

Как происходит импорт готового отчёта из Power Query в Excel, мы покажем на нашем готовом отчёте.

1. Нажмите «Закрыть и загрузить», и на экране отобразится таблица уже в Excel с тремя запросами в правом углу. В нашем случае это январь, февраль и отчёт о продажах.

Power Query отобразил все три запроса, включая отчёт. Теперь можно работать в отчёте или возвращаться к другим файлам и менять информацию. Все изменения автоматически обновятся в отчёте. Источник

В папке с объединёнными данными все файлы должны быть с одним расширением. В нашем примере это CSV. Если в папку попадёт файл другого расширения — система выдаст ошибку.

Рассмотрим принцип преобразования данных на примере изменения регистра, удаления столбцов и изменения формата ячеек. Для этого возьмём отчёт о продажах из примера выше.

Нажмите «Создать запрос» → «Из файла» → «Из папки» → «Отчёт о продажах».

Преобразование можно начать сразу, без перехода в папку. Но мы покажем, как это делать из неё. Источник

В редакторе запросов отобразилась информация по изменениям в файлах и их расширение. Источник

1. Изменить регистр букв. Допустим, нам нужно, чтобы все слова были в нижнем регистре. Нажмите правой кнопкой мыши на нужный столбец, затем кликните «Преобразование» → «Нижний регистр».

2. Удалить столбцы. Допустим, вам нужен только один столбец, чтобы не отвлекаться на другие данные. Нажмите правой кнопкой мыши на столбец, который нужно оставить. Затем выберите «Удалить другие столбцы».

Если нужно оставить несколько столбцов — просто выделите их вместе. Источник

Так выглядит таблица после удаления ненужных столбцов. Источник

3. Изменить формат ячеек. Форматы можно менять на числа, дату, время, текст и так далее. О них мы рассказывали выше. Например, чтобы установить формат даты, нажмите правой кнопкой мыши на нужный столбец, выберите «Тип изменения» и «Дата».

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