Как обновить power query в excel 2016
Перейти к содержимому

Как обновить power query в excel 2016

  • автор:

Управление параметрами и разрешениями источника данных (Power Query)

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

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

Совместное использование данных книги с другими пользователями

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

Просмотр недавно использовавшихся источников данных

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

    Выполните одно из следующих действий:

в Excel выберите данные > последние источники.

В Редактор Power Query Выберите главная > последние источники.

Область недавних источников

Откроется диалоговое окно Последние источники .

Откроется диалоговое окно Навигатор . Каждый источник данных имеет свой набор параметров.

Управление учетными данными источника данных

  1. Чтобы открыть диалоговое окно Параметры источника данных , выполните одно из следующих действий. В Power Query Выберите Файл >Главная >Параметры источника данных.

Примечание . Пароль не шифруется при отправке. Некоторые источники данных не поддерживают определенные типы учетных данных. Анонимные Любой пользователь может использовать источник данных, и учетные данные не требуются.

Windows Введите имя пользователя и пароль Windows.

Базовый введите имя пользователя и пароль.

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

Настройка более строгой безопасности

  1. Чтобы открыть диалоговое окно Параметры запроса , выполните одно из следующих действий. В Редактор Power Query выберите Параметры> файлов и Параметры >параметры запроса.

В разделе Проверка отзыва сертификата Выберите или снимите флажок Включить отзыв сертификата проверка.

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

В разделе Уровень предупреждения веб-просмотра выберите

:Нет Никогда не отображать предупреждение перед отображением веб-предварительного просмотра.

Умеренной Отображение предупреждения перед отображением веб-просмотра, если URL-адрес не был введен явным образом или не утвержден как доверенный во время сеанса.

Строгий Перед отображением веб-предварительного просмотра всегда отображается предупреждение.

В разделе Утвержденные службы проверки подлинности ADFS обратите внимание на следующее:

При входе иногда предлагается утвердить неизвестные службы проверки подлинности. Отобразится список утвержденных неизвестных служб проверки подлинности. При удалении записи из этого списка вы удаляете утверждение и снова будет предложено войти.

Настройка проверки подлинности службы Excel

Для службы Excel используйте диалоговое окно Параметры проверки подлинности службы Excel:

  1. Выберите Запросы > данных & Connections.
  2. Перейдите на вкладку Запросы , чтобы отобразить запросы в книге.
  3. Щелкните правой кнопкой мыши нужный запрос и выберите Свойства.
  4. В диалоговом окне Свойства запроса выберите вкладку Определение .

Управление запросами (Power Query)

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

Сведения о запросах и подключениях

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

Не все запросы могут быть загружены на лист. Это может произойти, когда вы импортируете данные из внешнего источника данных, формируете данные в Редактор Power Query, выбираете Главная > загрузить в, а затем используете диалоговое окно Импорт данных, чтобы поместить запрос в файл модели данных или подключения к данным Office (ODC).

Использование команд управления запросами

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

    Выполните одно из следующих действий:

в Excel выберите запросы> данных & Connections вкладке> запросы.

Edit изменяет запрос в Редактор Power Query. Доступно только на вкладке Запросы & Connections области Запросы.

Удалить Удаляет запрос.

Переименовать Изменяет имя запроса.

Обновить Возвращает актуальные данные из внешних источников данных. Доступно только на вкладке Запросы & Connections области Запросы. Дополнительные сведения см. в разделе Обновление подключения к внешним данным в Excel.

Загрузка в Отображает диалоговое окно Импорт данных , в котором можно выбрать способ просмотра данных, куда их нужно поместить, и добавить в модель данных. Доступно только на вкладке Запросы & Connections области Запросы.

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

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

Объединить Объединение столбцов в запросе с соответствующими столбцами в других запросах. Дополнительные сведения о слиянии см. в разделе Запросы слияния. Доступно только на вкладке Запросы & Connections области Запросы.

Добавить Добавление столбцов в запрос с соответствующими столбцами в других запросах. Дополнительные сведения о добавлении см. в разделе Добавление запросов. Доступно только на вкладке Запросы & Connections области Запросы.

Переместить в группу Перемещает запрос в группу в списке или, если групп нет, выберите Создать группу , чтобы создать ее. Присвойте ему понятное имя, например Бюджет или Маркетинг. Используйте группы, чтобы упорядочить многие запросы по категориям. Вы даже можете создавать группы в группах.

Переместить вверх Перемещает запрос вверх по списку запросов.

Перемещение вниз Перемещает запрос вниз в списке запросов.

Создание функции отображает диалоговое окно Создание функции . Доступно только в области Запросы в Редактор Power Query. Дополнительные сведения см. в справочнике по Power Query функции M.

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

Расширенный редактор Открывает окно Расширенный редактор. Дополнительные сведения см. в статье Создание формул Power Query в Excel. Доступно только в области Запросы в Редактор Power Query.

Экспорт файла подключения Сохраняет запрос как файл подключения ODC. ODC-файл содержит определение запроса, сведения о подключении к источнику данных и все шаги преобразования. Это полезно, если вы хотите поделиться запросами с другими пользователями и книгами. Кроме того, можно использовать команду Свойства на вкладке Контекст запроса на ленте Power Query. Дополнительные сведения см. в статье Создание, изменение и управление подключениями к внешним данным. Доступно только на вкладке Запросы & Connections области Запросы.

Показать Обзор Отображает всплывающее окно запроса с предварительным просмотром данных и другими полезными сведениями. Доступно только на вкладке Запросы & Connections области Запросы.

Управление наведением имени запроса во всплывающем элементе запроса

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

  1. Выберите Data >Get Data >Query Options (Получить данные > параметры запроса).
  2. В левой части экрана в разделе ГЛОБАЛЬНЫЙ выберите Общие.
  3. Справа в разделе Предварительный просмотр запросов очистите поле проверка рядом с полем Показать обзор при наведении указателя мыши. Действие по умолчанию — отображение всплывающего элемента при наведении указателя мыши.

Excel для Интернета включает в себя технологию Power Query (также называемую get & Transform) для предоставления более широких возможностей при работе с импортированными источниками данных. Запросы и связанные сведения можно просмотреть в области задач Запросы.

  • Ознакомьтесь с дополнительными объявлениями об улучшениях Excel для веб-интеграции и интеграции с Power Query.
  • Просмотр запросов доступен всем подписчикам Office 365. Управление запросами и группами доступно всем подписчикам Office 365 с планами «Бизнес» или «Корпоративный».

Просмотр области задач «Запросы»

Запросы и связанные сведения можно просмотреть на панели Запросы.

  1. Убедитесь, что вы находитесь в режиме редактирования (выберите Просмотр >Редактирование).
  2. Выберите Запросы >данных.

Result (Результат)

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

Представление запроса и область задач

Управление запросами в группах

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

  1. Убедитесь, что вы находитесь в режиме редактирования (выберите Просмотр >Редактирование).
  2. Выберите Запросы >данных.
  3. Выполните одно или несколько из указанных ниже действий. Создание группы
    1. В правом верхнем углу области Запросы выберите Дополнительные параметры . >Новая группа.
    2. Введите имя, при необходимости описание, а затем нажмите кнопку Сохранить.

    Перемещение запроса или группы в другую группу

    1. Рядом с именем запроса или группы выберите Дополнительные параметры. >Переместить в группу.
    2. Выберите группу, в которую необходимо переместить, и нажмите кнопку Сохранить.

    Перемещение группы на верхний уровень

    • Рядом с именем группы выберите Дополнительные параметры. >Перейти на верхний уровень.

    Развертывание или свертывание групп

    • Чтобы развернуть или свернуть все группы, в правом верхнем углу области Запросы выберите Дополнительные параметры . >Развернуть все или Свернуть все.
    • Чтобы развернуть или свернуть определенную группу, щелкните стрелку рядом с именем группы.

    Обновление всех запросов в группе

    • Рядом с именем группы выберите Дополнительные параметры. >Обновить. Примечание Обновление работает только для поддерживаемых источников данных. Дополнительные сведения см. в разделе Power Query источников данных в версиях Excel.

    Разгруппирование запросов в группе

    • Рядом с именем группы выберите Дополнительные параметры. >Разгруппировать. Примечание Это действие удаляет группу, но не запросы в этой группе.

    Переименование группы

    1. Рядом с именем группы выберите Дополнительные параметры. >переименовать.
    2. Введите новое имя и нажмите клавишу RETURN.

    Удаление группы

    • Рядом с именем группы выберите Дополнительные параметры. >Удалить группу.

    Изменение свойств группы

    1. Рядом с именем группы выберите Дополнительные параметры. >Свойства.
    2. Переименуйте группу или измените ее описание, а затем нажмите кнопку Сохранить.

    Совет Чтобы увидеть описание группы, наведите на нее указатель мыши.

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

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

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

    Примечание: Power Query в Excel для Windows использует время выполнения Microsoft Edge WebView2. Вы можете скачать Microsoft Edge WebView2 здесь.

    Четыре этапа Power Query

    Существует четыре этапа использования 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 отслеживает все, что вы делаете с данными, записывая и помечая каждое преобразование или шаг, применяемые к данным. Независимо от того, является ли преобразование соединением данных, удалением столбца, слиянием или изменением типа данных, каждое преобразование можно просмотреть и изменить в разделе ПРИМЕНЕННЫЕ ШАГИ области Параметры запроса .

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

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

    Объединить

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

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

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

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

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

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

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

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

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

    Поддержка приложений для Power Query в Excel

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

    Журнал Power Query в Excel

    Интеграция get & transform Data (теперь называется Power Query) в Excel за прошедшие годы пережила ряд изменений.

    Excel 2010 и 2013 для Windows

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

    Лента Power Query Excel 2013

    Microsoft 365

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

    Лента Excel 2016 Power Query

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

    Этот новый интерфейс также заменил старые устаревшие мастеры импорта данных в команде 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 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.

    Создание, загрузка и изменение запроса в Excel (Power Query)

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

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

    Выбор ячейки в запросе для отображения вкладки

    Сведения об интеграции Power Query с Excel

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

    Знакомый лист Excel, лента и сетка

    Лента Редактор Power Query и предварительный просмотр данных

    Типичный лист Excel

    Типичное представление Редактор Power Query

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

    Переименование вкладок листа Рекомендуется осмысленно переименовать вкладки листа, особенно если их много. Особенно важно прояснить разницу между листом данных и листом, загруженным из Редактор Power Query. Даже если у вас есть только два листа, один из которых содержит таблицу Excel с именем Sheet1, а другой — запрос, созданный путем импорта таблицы Excel с именем Table1, легко запутаться. Рекомендуется всегда изменять имена вкладок по умолчанию на имена, которые вам нужны. Например, переименуйте Лист1 в DataTable , а Table1в QueryTable. Теперь ясно, на какой вкладке есть данные, а на какой — запрос.

    Создание запроса

    Можно создать запрос на основе импортированных данных или создать пустой запрос.

    Создание запроса на основе импортированных данных

    Это самый распространенный способ создания запроса.

    1. Импортируйте некоторые данные. Дополнительные сведения см. в разделе Импорт данных из внешних источников данных.
    2. Выделите ячейку в данных, а затем выберите Запрос >Изменить.

    Создание пустого запроса

    Вы можете просто начать с нуля. Это можно сделать двумя способами.

    • Выберите Данные > Получить >данныхиз других источников >пустой запрос.
    • Выберите Данные >Получить данные >запустить Редактор Power Query.

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

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

    • Выберите Новый источник , чтобы добавить источник данных. Эта команда похожа на команду Data >Get Data на ленте Excel.
    • Выберите Последние источники, чтобы выбрать источник данных, с которым вы работали. Эта команда похожа на команду Data >Recent Sources на ленте Excel.
    • Выберите Ввести данные , чтобы вручную ввести данные. Вы можете выбрать эту команду, чтобы опробовать Редактор Power Query независимо от внешнего источника данных.

    Загрузка запроса

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

    Загрузка запроса из Редактор Power Query

    В Редактор Power Query выполните одно из следующих действий:

    • Чтобы загрузить на лист, выберите Главная >Закрыть & Загрузить >Закрыть & Загрузить.
    • Чтобы загрузить в модель данных, выберите Главная >Закрыть & Загрузить >Закрыть & Загрузить.

    Совет Иногда команда Load To неактивна или отключена. Это может произойти при первом создании запроса в книге. В этом случае выберите Закрыть & загрузить, на новом листе выберите >запросы & Connections > вкладку Запросы, щелкните запрос правой кнопкой мыши и выберите команду Загрузить в. Кроме того, на ленте Редактор Power Query выберите Запрос > загрузить.

    Загрузка запроса из области «Запросы и Connections»

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

    1. В Excel выберите Data >Запросы & Connections, а затем перейдите на вкладку Запросы.
    2. В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите команду Загрузить в. Откроется диалоговое окно Импорт данных.
    3. Выберите способ импорта данных, а затем нажмите кнопку ОК. Для получения дополнительных сведений об использовании этого диалогового окна выберите вопросительный знак (?).

    Изменение запроса с листа

    Существует несколько способов изменения запроса, загруженного на лист.

    Изменение запроса из данных на листе Excel

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

    Изменение запроса на панели «Запросы» & Connections

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

    1. В Excel выберите Data >Запросы & Connections, а затем перейдите на вкладку Запросы.
    2. В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.

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

    • В Excel выберите Данные >& Connections > вкладке Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства, выберите вкладку Определение в диалоговом окне Свойства, а затем выберите Изменить запрос.

    Совет Если вы находитесь на листе с запросом, выберите Данные > Свойства, перейдите на вкладку Определение в диалоговом окне Свойства , а затем выберите Изменить запрос.

    Изменение запроса таблицы в модели данных

    Модель данных обычно содержит несколько таблиц, упорядоченных в связи. Вы загружаете запрос в модель данных с помощью команды Загрузить в, чтобы отобразить диалоговое окно Импорт данных, а затем выберите поле Добавить эти данные в режим данныхl проверка. Дополнительные сведения о моделях данных см. в разделах Узнайте, какие источники данных используются в модели данных книги, Создание модели данных в Excel и Использование нескольких таблиц для создания сводной таблицы.

    1. Чтобы открыть модель данных, выберите Power Pivot >Управление.
    2. В нижней части окна Power Pivot выберите вкладку листа нужной таблицы.

    Result (Результат)

    Запрос на листе и таблица в модели данных обновляются.

    Загрузка запроса в модель данных занимает необычно много времени

    Если вы заметили, что загрузка запроса в модель данных занимает гораздо больше времени, чем загрузка на лист, проверка шаги Power Query, чтобы узнать, фильтруется ли текстовый столбец или структурированный столбец списка с помощью оператора Contains. Это действие приводит к повторному перечислению Excel по всему набору данных для каждой строки. Кроме того, Excel не может эффективно использовать многопоточное выполнение. В качестве обходного решения попробуйте использовать другой оператор, например Equals или Begins With.

    Корпорация Майкрософт знает об этой проблеме, и она расследуется.

    Настройка параметров загрузки запроса

    Вы можете загрузить Power Query:

    • На лист. В Редактор Power Query выберите Главная >Закрыть & Загрузить >Закрыть & Загрузить.
    • В модель данных. В Редактор Power Query выберите Главная >Закрыть & Загрузить >Закрыть & Загрузить.

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

    Глобальные параметры, применяемые ко всем книгам

    1. В power Редактор запросов выберите Параметры и параметрыфайла > >Параметры запроса.
    2. В диалоговом окне Параметры запроса в левой части в разделе Global (Глобальный ) выберите Загрузка данных.
    3. В разделе Параметры загрузки запросов по умолчанию выполните следующие действия.
      • Выберите Использовать стандартные параметры загрузки.
      • Выберите Укажите настраиваемые параметры загрузки по умолчанию, а затем выберите или снимите флажок Загрузить на лист или Загрузить в модель данных.

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

    Параметры книги, которые применяются только к текущей книге

    1. В диалоговом окне Параметры запроса в левой части раздела ТЕКУЩАЯ КНИГА выберите Загрузка данных.
    2. Выполните одно или несколько из указанных ниже действий.
    3. В разделе Обнаружение типов выберите или снимите флажок Обнаружение типов столбцов и заголовков для неструктурированных источников.

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *