С помощью Power Query (в Excel это называется Get & Transform) можно импортировать или подключиться к внешним данным, а затем сформировать эти данные, например удалить столбец, изменить тип данных или объединить таблицы в соответствии со своими потребностями. Затем можно загрузить запрос в Excel для создания диаграмм и отчетов. Периодически можно обновлять данные, чтобы сделать их актуальными. Power Query доступны в трех приложениях Excel: Excel для Windows, Excel для Mac и Excel для Интернета. Общие сведения о всех разделах справки Power Query см. в Power Query справки Excel.

Существует четыре этапа использования Power Query.

Power Query common steps

  1. Подключения    Подключение к данным в облаке, в службе или локально

  2. Преобразования    Формирование данных в соответствии со своими потребностями, в то время как исходный источник остается неизменным

  3. Объединить    Интеграция данных из нескольких источников для получения уникального представления в данных

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

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

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

После импорта можно обновить данные, чтобы добавить дополнения, изменения и удаления из внешнего источника данных. Дополнительные сведения см. в разделе "Обновление подключения к внешним данным" в Excel.  

Импорт команд в пользовательском интерфейсе

Преобразование данных означает их изменение в соответствии с требованиями к анализу данных. Например, можно удалить столбец, изменить тип данных или отфильтровать строки. Каждая из этих операций представляет собой преобразование данных. Этот процесс применения преобразований (и объединения) к одному или нескольким наборам данных также называется формированием данных.

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

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

Пользовательский интерфейс параметров запроса

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

Существует множество преобразований, которые можно выполнить из пользовательского интерфейса. Каждое преобразование записывается как шаг в фоновом режиме. Вы даже можете изменять и записывать собственные шаги с помощью Power Query языка M в Расширенный редактор.

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

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

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

  • Промежуточное добавление    Создает новый запрос для каждой операции добавления.

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

Концепция добавления запросов

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

Концепция объединения запросов

Существует два основных способа загрузки запросов в книгу:

  • В Редактор Power Query можно использовать команды "Закрыть" и "Загрузить" в группе "Закрыть" на вкладке "Главная".

  • В области "Запросы книги Excel" (выбор & подключения) можно щелкнуть запрос правой кнопкой мыши и выбрать команду "Загрузить".

Вы также можете настроить параметры загрузки с помощью диалогового окна "Параметры запроса" (выберите параметры и параметры файла> параметры > Параметры запроса), чтобы выбрать способ просмотра данных и место загрузки данных на листе или в модель данных (которая является реляционным источником данных нескольких таблиц, которые находятся в книге). 

Более десяти лет Power Query в Excel для Windows. Теперь Excel расширяет возможности Power Query в Excel для Mac и добавляет поддержку в Excel для Интернета. Это означает, что мы Power Query на трех основных платформах и демонстрируем популярность и функциональность Power Query среди клиентов Excel. Следите за будущими объявлениями о стратегии развития Microsoft 365и новых возможностях Excel для Microsoft 365.

Интеграция Get & Transform Data (теперь называется Power Query) в Excel прошла через несколько лет изменений. 

Excel 2010 и 2013 для Windows

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

Лента excel 2013 Power Query

Microsoft 365

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

Excel 2016 Power Query ленты

Этот интерфейс включает расширенные функции импорта данных, переупорядоченные команды на вкладке "Данные", новую панель "Запросы & Подключение", а также возможность эффективного формирования данных путем сортировки, изменения типов данных, разделения столбцов, агрегирования данных и т. д.

Этот новый интерфейс также заменил старые мастера импорта устаревших данных в команде "Данные " в группе "Получение внешних данных ". Однако доступ к ним по-прежнему можно получить в диалоговом окне "Параметры Excel" (выберите "Параметры файлов >"> " > Показать устаревшие мастера импорта данных)".

Excel 2016 2019 для Windows

Мы добавили то же & преобразования данных на основе Power Query технологии, что и в Microsoft 365.

Excel для Microsoft 365 для Mac

В 2019 году мы начали процесс поддержки Power Query в Excel для Mac. С тех пор мы добавили возможность обновлять Power Query из файлов TXT, CSV, XLSX, JSON и XML. Мы также добавили возможность обновлять данные из SQL Server и из таблиц & диапазонов в текущей книге.

В октябре 2019 г. мы добавили возможность обновлять существующие Power Query и использовать VBA для создания и изменения новых запросов.

В январе 2021 г. мы добавили поддержку обновления Power Query запросов из источников OData и SharePoint.

Дополнительные сведения см. в Power Query в Excel для Mac.

Примечание    В Power Query Excel 2019 для Mac Excel 2016 не поддерживается.

Каталог данных устаревания

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

  • 1 августа 2018 г. мы перестали подключать новых клиентов к Каталог данных.

  • 3 декабря 2018 г. пользователям не удалось поделиться новыми или обновленными запросами в Каталог данных.

  • 4 марта 2019 г. работа Каталог данных прекращена. После этой даты мы рекомендуем скачать общие запросы, чтобы вы могли продолжать использовать их за пределами Каталог данных, используя параметр "Открыть" в области задач "Мои Каталог данных Запросы".

Power Query нерекомендуемой надстройки

В начале 2019 года мы официально не рекомендуем использовать надстройку Power Query, которая требуется для Excel 2010 и 2013 для Windows. В качестве предоставленного решения вы по-прежнему можете использовать надстройку, но это может измениться позже.

Отключен соединитель данных Facebook

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

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

Спасибо за ваш отзыв!

×