Сведения о Power Query в Excel

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

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

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

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

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

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

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

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

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

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

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

Редактор Power Query отслеживает все, что вы делаете с данными, записывая и помечая каждое преобразование или шаг, применяемые к данным. Независимо от того, является ли преобразование соединением данных, удалением столбца, слиянием или изменением типа данных, каждое преобразование можно просмотреть и изменить в разделе ПРИМЕНЕННЫЕ ШАГИ области Параметры запроса .

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

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

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

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

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

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

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

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

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

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

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

Уже более десяти лет 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 на ленте.

Microsoft 365

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

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

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

Excel 2016 и 2019 для Windows

Мы добавили тот же интерфейс Get & Transform Data на основе технологии 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 2016 и Excel 2019 для Mac отсутствует.

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

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

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

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

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

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

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

Facebook соединитель данных прекращен

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

Критические обновления Excel для Windows 

С июня 2023 г. для Power Query в Excel для Windows требуются следующие компоненты: 

• Power Query в Excel для Windows использует платформу .NET Framework, но требуется версия 4.7.2 или более поздняя. Дополнительные сведения см. в разделе Обновление платформа .NET Framework.

• Power Query в Excel для Windows требуется, чтобы среда выполнения WebView2 продолжала поддерживать веб-соединитель данных (получение данных из Интернета).  Дополнительные сведения см. в разделе Скачивание среды выполнения WebView2.