Как использовать Power Query в Excel: полное руководство

Power Query — это мощный инструмент бизнес-аналитики в Excel, который позволяет импортировать, очищать и преобразовывать данные в соответствии с вашими потребностями. Если вы когда-либо тратили часы на очистку данных (у нас) или изо всех сил пытались объединить данные из разных источников (у нас тоже есть), эта функция сделает вашу жизнь проще.

Лучший способ научиться использовать Power Query в Excel — выполнить некоторые распространенные задачи, такие как импорт данных и использование редактора Power Query для преобразования результирующих таблиц и объединения данных.

Эта статья поможет вам начать работу, показывая четкие примеры Power Query с пошаговыми руководствами . Освоив Power Query, вы сможете поднять свою работу на новый уровень.

Вы готовы? Давай сделаем это.

Оглавление

• Как узнать, есть ли у вас доступ к Power Query
• Как импортировать данные с помощью Power Query
• Как использовать редактор Power Query
  • Предварительный просмотр данных
  • Характеристики
• Как работать с несколькими источниками данных
• Как добавить данные с помощью Power Query
• Как объединить данные с помощью Power Query
• 5 способов преобразования данных с помощью Power Query
  • 1. Расширение и сжатие данных
  • 2. Сортировка данных
  • 3. Форматирование данных
  • 4. Добавление и удаление столбцов
  • 5. Изменение типов данных
• Работа с формулами и языком M
  • Панель формул
  • Написание и изменение M-кода
• Что такое синтаксис языка M?
• Как обновить подключения к данным
• Как интегрировать Power Query с моделью данных
• Как автоматизировать с помощью Power Query и VBA
• Дополнительные функции Power Query
• Сотрудничество и обмен
• Давайте завершим это

Как узнать, есть ли у вас доступ к Power Query

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

Вот краткий обзор Power Query по версиям:

• Excel 2010 и Excel 2013: доступны в виде надстройки, которую необходимо загрузить и установить.

• Excel 2016: интегрирован в Excel как «Получить и преобразовать».

• Excel 2019: улучшена производительность и функциональность.

• Excel 2021 и Excel для Microsoft 365: интеграция с облачными источниками данных.

• Excel для Mac: доступно только в Microsoft 365 версии 16.69 и более поздних.

Чтобы убедиться, что инструмент интегрирован в вашу книгу Excel (2016 или более поздней версии), перейдите на вкладку «Данные» и найдите раздел «Получить и преобразовать данные» на этом рисунке:

Если у вас есть Excel 2010 или 2013 в Windows, вы можете загрузить , установить и включить надстройку Power Query.

Как импортировать данные с помощью Power Query

Инструмент Power Query позволяет импортировать и подключаться к широкому спектру источников данных:

• Файлы, включая форматы CSV, XML и JSON.

• Веб-URL или конечные точки API

• Базы данных, включая Access, SQL Server, Oracle и большинство современных баз данных.

• Службы Azure, файлы SharePoint и веб-каналы OData.

Самый простой источник данных для начала — это простой CSV-файл с несколькими строками данных. Создайте текстовый файл и добавьте эти четыре имени и фамилии, разделенные запятой:

• Джо, Блоги

• Энн, Райан

• Джеймс, Стюарт

• Мэри, Браун

Обязательно сохраните файл с расширением .CSV. Ваш файл будет выглядеть так:

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

1. Перейдите на вкладку Данные.

2. Нажмите кнопку «Получить файлы» на вкладке «Получить и преобразовать данные».

3. Выберите файл примера.

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

Совет: закройте другие приложения, чтобы ускорить работу.

Power Query отобразит окно «Импорт текста/CSV» с предварительным просмотром ваших данных.

Если данные выглядят правильно, вы можете перейти к загрузке или преобразованию данных:

• Кнопка «Загрузить» загрузит данные непосредственно в Excel.

• кнопка «Преобразовать данные» позволяет изменить данные перед их загрузкой.

Вы заметили, что в примере файла не было заголовка? Если он загружен прямо в Excel, заголовки будут «Столбец 1» и «Столбец 2».

Вместо этого вы можете загрузить данные в редактор Power Query и внести некоторые изменения. Для этого нажмите кнопку «Преобразовать данные».

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

Как использовать редактор Power Query

В этом разделе мы рассмотрим некоторые важные аспекты работы с редактором Power Query.

Предварительный просмотр данных

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

Когда вы закончите, Excel загрузит преобразованные данные на лист.

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

Для этого:

1. Щелкните правой кнопкой мыши заголовок столбца.

2. Нажмите «Переименовать» в раскрывающемся меню.

3. Введите предпочтительные имена в поля заголовка.

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

Характеристики

Установка свойств для ваших запросов необходима для организации и отслеживания вашей работы.

Панель «Параметры запроса» находится справа от окна Power Query.

1. Щелкните ссылку «Все свойства».

2. Измените имя запроса, чтобы оно было описательным.

3. Добавьте описание, чтобы предоставить больше информации.

Легко забыть, для чего были созданы запросы прошлой недели. В этом случае имя запроса было изменено и добавлено описание, указывающее цель запроса.

Если вы хотите узнать больше о доступных параметрах, ознакомьтесь с этим обзором редактора Power Query .

Как работать с несколькими источниками данных

Редактор Power Query предлагает два основных метода объединения данных из нескольких источников: добавление и объединение.

Добавление объединяет таблицы по вертикали, располагая их друг над другом. Слияние позволяет объединять таблицы на основе общих столбцов.

Чтобы увидеть, как это работает, создайте второй небольшой CSV-файл с той же структурой, что и первый. На этот раз поместите в файл два имени (первое и последнее):

• Джо, Блоги

• Лаура, Кейн

Обратите внимание, что одно из этих имен повторяется из предыдущего файла.

Теперь выполните следующие действия, чтобы перенести данные в книгу Microsoft Excel.

1. Закройте окно редактора Power Query и сохраните изменения.

2. Добавьте новый рабочий лист.

3. Выполните действия, описанные в предыдущем разделе, чтобы импортировать второй файл.

4. Нажмите кнопку «Преобразовать».

5. Переименуйте заголовки столбцов в «Имя» и «Фамилия».

На правой панели рабочего листа Excel отображаются текущие запросы и подключения. Теперь вы должны увидеть два запроса на панели.

Как добавить данные с помощью Power Query

В этом разделе вы добавите второй образец CSV-файла к первому.

Выполните следующие действия, чтобы добавить данные:

1. Щелкните правой кнопкой мыши первую таблицу (имена клиентов) на панели «Запросы и подключения».

2. Выберите «Изменить», чтобы открыть таблицу в редакторе Power Query.

3. Нажмите «Добавить запросы» в разделе «Объединить».

4. Выберите вторую таблицу из раскрывающегося списка таблиц для добавления.

В результате в первой таблице появятся две дополнительные строки.

Обратите внимание, что одна строка повторяется — так работает операция добавления. Он добавляет данные из одной таблицы в другую, независимо от того, существуют ли данные уже.

Вы можете удалить повторяющиеся данные в редакторе Power Query, выполнив следующие действия:

1. Нажмите на заголовок столбца «Имя».

2. Удерживайте клавишу «Ctrl» (или клавишу «Cmd» на Mac).

3. Нажмите на оба заголовка столбца.

4. Нажмите «Удалить строки» на вкладке «Главная» ленты редактора Power Query.

5. Выберите «Удалить дубликаты» в раскрывающемся меню.

Как объединить данные с помощью Power Query

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

Предположим, у вас есть два файла с разделителями-запятыми, один из которых содержит номера телефонов клиентов, а другой — адреса. Два файла выглядят так:

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

В случае нашего примера данных оба столбца (Имя и Фамилия) должны быть выбраны в качестве совпадающих столбцов. Удерживая нажатой клавишу Ctrl , выберите несколько столбцов.

Следуй этим шагам:

1. Щелкните правой кнопкой мыши первую таблицу на панели «Запросы и подключения».

2. Выберите «Изменить», чтобы открыть таблицу в редакторе Power Query.

3. Выберите «Объединить запросы» в группе «Объединить».

4. Выберите вторую таблицу в раскрывающемся списке таблиц.

5. Нажмите на первый столбец в первой таблице.

6. Удерживая нажатой клавишу Ctrl, щелкните второй столбец в первой таблице.

7. Удерживая нажатой клавишу Ctrl, щелкните оба столбца во второй таблице.

8. Оставьте оператор соединения по умолчанию как «Левый внешний».

9. Нажмите «ОК», чтобы применить изменения.

Оператор соединения «Left Outer» гарантирует удаление повторяющихся данных.

В редакторе Power Query вы увидите новый столбец с именем «Таблица» или «Вложенная таблица», содержащий объединенные данные из второго запроса.

1. Нажмите на значок развертывания (две стрелки) в заголовке этого столбца.

2. Снимите флажок «Использовать исходный столбец в качестве префикса» (это позволяет избежать длинных имен столбцов).

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

4. Нажмите «Закрыть и загрузить».

В объединенной таблице было четыре столбца с объединенными сведениями о телефоне и адресе. Возможно, вам еще предстоит немного навести порядок.

Например, вы можете захотеть отредактировать имя таблицы (Merge1) и столбец адреса на картинке ниже.

5 способов преобразования данных с помощью Power Query

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

Некоторые из наиболее распространенных способов преобразования данных:

• Расширение и сжатие данных

• Сортировка данных

• Форматирование данных

• Добавление и удаление столбцов

• Изменение типов данных

1. Расширение и сжатие данных

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

В предыдущем разделе показано, как расширить, включив данные из связанных таблиц.

Вы также можете сжать его, чтобы включить только сводные данные на основе выбранной вами агрегации, такой как сумма, количество или процент.

2. Сортировка данных

Для сортировки данных можно быстро изменить порядок по возрастанию или убыванию значений.

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

3. Форматирование данных

Вкладка «Преобразование» в редакторе Power Query предоставляет множество параметров.

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

4. Добавление и удаление столбцов

Power Query упрощает процесс добавления, удаления и изменения столбцов.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

For example, you may need to convert decimal points to percentages or convert text-based percentages to numeric ones for easier calculation and comparison.

Working with Formulas and M Language

When using Power Query in Excel, you’ll encounter the M Language, which is the heart of creating advanced formulas, manipulating data, and producing powerful queries. In this section, you’ll learn about:

• the Formula Bar

• M code

• the essential elements of M Language syntax

Formula Bar

The Formula Bar in Excel’s Power Query editor is the area where you’ll input your formulas and M code.

To use it, go to the Power Query editor by selecting a query from the Queries & Connections pane. The Formula Bar is located above the data preview pane.

Writing And Modifying M Code

Чтобы создавать универсальные и мощные запросы в Excel, вам потребуется написать и изменить язык Power Query, известный как M-код. Вот несколько советов о том, как начать:

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

• Комбинируйте код M с таблицей Excel, сводными таблицами и внешними источниками данных, такими как Azure и текстовые файлы, для создания динамических и автоматизированных отчетов.

• Настройте параметры запроса, чтобы уточнить параметры загрузки данных и повысить производительность.

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

Чтобы просмотреть код запроса, выполните следующие действия.

1. Откройте запрос в редакторе Power Query.

2. Нажмите «Расширенный редактор» на вкладке «Главная».

Это код нашего образца объединенной таблицы (ваш может выглядеть иначе):

Что такое синтаксис языка M?

Понимание синтаксиса языка M имеет решающее значение для создания эффективных формул Power Query. Помните о следующих моментах:

• M Language чувствителен к регистру, поэтому будьте осторожны с заглавными буквами.

• Используйте квадратные скобки [] для ссылок на столбцы, записи и списки.

• Чтобы сослаться на предыдущий шаг в запросе, используйте имя его переменной (например, # «Имя шага»).

• Выведите шаг формулы запроса с оператором «in».

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

Посмотрите видео ниже, пришло время достать чистящую щетку и очистить ваши беспорядочные данные.

Как обновить подключения к данным

Одной из ключевых функций Power Query является возможность легко обновить подключение к Power Query.

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

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

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

1. Щелкните правой кнопкой мыши запрос, который вы хотите обновить.

2. Выберите Обновить в контекстном меню.

Это обновит вашу книгу последними данными из подключенных источников данных.

Как интегрировать Power Query с моделью данных

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

Модель данных — это, по сути, скрытая база данных, содержащая импортированные данные и связи между таблицами.

Это часть функции Power Pivot, встроенной в текущие версии Excel. Возможно, вам придется установить Power Pivot в качестве надстройки, чтобы получить к ней доступ в предыдущих версиях).

Вы можете интегрировать подключения к данным Power Query с моделью данных, выполнив следующие действия:

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

2. Вместо того, чтобы загружать его непосредственно в книгу, выберите «Загрузить в» в меню «Загрузить».

3. Установите флажок Добавить эти данные в модель данных в диалоговом окне Импорт данных.

Как автоматизировать с помощью Power Query и VBA

Благодаря интеграции VBA (Visual Basic для приложений) вы можете создавать настраиваемые подключения к данным и автоматизировать этапы преобразования данных в Power Query.

Вы можете использовать имеющиеся у вас навыки работы с VBA для создания расширенных моделей данных путем включения данных с таких платформ, как Microsoft Access, SharePoint и Analysis Services.

Хотя Power Query не имеет встроенной поддержки VBA, вы можете использовать код VBA для открытия, обновления и косвенного управления запросами Power Query.

Это позволяет создавать мощные автоматизированные рабочие процессы данных и оптимизировать процесс анализа данных.

Вот пример кода, который обновляет запрос мощности в текущей книге.

:

Dim wb как рабочая книга, pq как объект

Установите wb = Эта рабочая книга

Установите pq ​​= wb.Queries («Имена клиентов»)

 pq.Обновить

Вот код в макросе Excel VBA:

Дополнительные функции Power Query

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

Встроенные инструменты анализа данных включают в себя:

• Условные столбцы для применения сложной логики к вашим преобразованиям данных.

• Функции группировки и агрегирования для осмысленного обобщения данных.

• Операции Pivot и Unpivot для изменения формы данных для облегчения анализа.

Вы найдете многие из этих функций на вкладке «Преобразование» в редакторе Power Query.

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

Сотрудничество и обмен

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

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

Вы также можете экспортировать M-код для одного запроса, выполнив следующие действия:

1. Отредактируйте запрос в редакторе Power Query.

2. Нажмите «Расширенный редактор» на вкладке «Главная».

3. Скопируйте весь М-код в окно расширенного редактора.

4. Откройте текстовый редактор (например, Блокнот) и вставьте скопированный М-код в текстовый редактор.

5. Сохраните текстовый файл и отправьте его своему коллеге.

Дав��йте завершим это

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

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

Power Query в конечном счете экономит ваше время и повышает эффективность работы с файлами Excel.