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

Как обновить запрос в power query

  • автор:

Создание, загрузка и изменение запроса в 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. В разделе Обнаружение типов выберите или снимите флажок Обнаружение типов столбцов и заголовков для неструктурированных источников.

Создание запроса параметров (Power Query)

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

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

Примечание Если вам нужен другой способ создания запросов параметров, см. статью Создание запроса параметров в Microsoft Query.

Создание параметра

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

  1. Выберите Данные >Получить данные >другие источники >запустить Редактор Power Query.
  2. В Редактор Power Query выберите Главная >Управление параметрами > Новые параметры.
  3. В диалоговом окне Управление параметром выберите Создать.
  4. При необходимости задайте следующие параметры:

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

Любое значение. В запросе к параметру можно ввести любое значение любого типа данных.

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

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

Например, поле состояния проблем может содержать три значения: . Необходимо создать запрос списка заранее, открыв Расширенный редактор (выберите Главная > Расширенный редактор), удалив шаблон кода, введя список значений в формате списка запросов и выбрав Готово.

Изменение источника данных с помощью параметра

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

Шаг 1. Создание запроса параметров

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

  1. Выберите Главная >Управление параметрами >Новый параметр.
  2. В диалоговом окне Управление параметром введите следующие сведения:

Имя CSVFileDrop
Описание Альтернативное расположение удаления файла
Обязательные Да
Тип Текст
Предлагаемые значения Любое значение
Текущее значение C:\DataFilesCSV1

Шаг 2. Добавление параметра в запрос данных

  1. Чтобы задать имя папки в качестве параметра, в разделе Параметры запроса в разделе Шаги запроса выберите Источник, а затем — Изменить параметры.
  2. Убедитесь, что для параметра Путь к файлузадано значение Параметр, а затем выберите только что созданный параметр в раскрывающемся списке.
  3. Нажмите кнопку ОК.

Шаг 3. Обновление значения параметра

Расположение папки только что изменилось, поэтому теперь можно просто обновить запрос параметров.

  1. Выберите Подключения> данных& запросы > вкладку Запросы , щелкните правой кнопкой мыши запрос параметров и выберите изменить.
  2. Введите новое расположение в поле Текущее значение , например C:\DataFilesCSV2.
  3. Выберите Главная >Закрыть & Загрузить.
  4. Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные >Обновить все).

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

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

  1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
  2. Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например Фильтры даты и времени >After. Откроется диалоговое окно Фильтрация строк .

Ввод параметра в диалоговом окне

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

Использование значения ячейки для фильтрации данных

В этом примере значение в параметре запроса считывается из ячейки в книге. Вам не нужно изменять запрос параметров, просто обновите значение ячейки. Например, вы хотите отфильтровать столбец по первой букве, но легко изменить значение на любую букву с A на Я.

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

MyFilter
G

Теперь запрос использует значение в созданной таблице Excel для фильтрации результатов запроса. Чтобы использовать новое значение, измените содержимое ячейки в исходной таблице Excel на шаге 1, измените значение «G» на «V», а затем обновите запрос.

Управление использованием запросов параметров

Вы можете указать, разрешены ли запросы параметров.

  1. В Редактор Power Query выберите Параметры> файлови Параметры >Параметры запроса >Редактор Power Query.
  2. В области слева в разделе Глобальный выберите Редактор Power Query.
  3. В области справа в разделе Параметры выберите или снимите флажок Всегда разрешать параметризацию в диалоговых окнах источника данных и преобразования.

Ссылка на запросы Power Query

Эта статья предназначена для моделирователя данных, работающего с Power BI Desktop. Он предоставляет рекомендации при определении запросов Power Query, ссылающихся на другие запросы.

Давайте ясно о том, что это означает: когда запрос ссылается на второй запрос, это так, как будто шаги во втором запросе объединяются и выполняются до этого, шаги в первом запросе.

Рассмотрим несколько запросов: запрос1 источников данных из веб-службы и его загрузка отключена. Запрос2, Запрос3 и Запрос4 все ссылочные запросы1 и их выходные данные загружаются в модель данных.

Diagram showing the Query Dependencies view, displaying queries described in the previous paragraph.

При обновлении модели данных часто предполагается, что Power Query извлекает результат Query1 и используется повторно с помощью ссылочных запросов. Это неправильное мышление. На самом деле Power Query выполняет запрос2, запрос3 и запрос4 отдельно.

Вы можете подумать, что Запрос2 содержит шаги Query1 , внедренные в него. Это также относится к запросу 3 и Query4. На следующей схеме представлено более четкое представление о том, как выполняются запросы.

Diagram showing a modified version of the Query Dependencies view, displaying Query 2, Query 3, and Query 4.

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

Использование функции Table.Buffer в Query1 не приведет к устранению дополнительных сведений. Эта функция буферизирует таблицу в память, а буферная таблица может использоваться только в рамках одного выполнения запроса. Таким образом, в примере, если запрос1 буферичен при выполнении запроса 2 , буферированные данные не могут использоваться при выполнении запроса3 и query4 . Они сами буферизируют данные в два раза больше. (Это может привести к снижению производительности, так как таблица будет буферирована каждым запросом ссылки.)

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

Рекомендации

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

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

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

В примере, если запрос1 перепроектирован как сущность потока данных, Query2, Query3 и Query4 может использовать его в качестве источника данных. С помощью этой структуры сущность, исходная запросом 1 , будет оцениваться только один раз.

Связанный контент

Дополнительные сведения, связанные с этой статьей, проверка следующие ресурсы:

  • Подготовка данных самообслуживания в Power BI
  • Creating and using dataflows in Power BI (Создание и использование потоков данных в Power BI)
  • Вопросы? Задайте их в сообществе Power BI.
  • Есть предложения? Участие в разработке идей по улучшению Power BI

Добавление запросов

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

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

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

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

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

Добавление команд запросов.

Для операции добавления требуется не менее двух таблиц. Диалоговое окно «Добавление» имеет два режима:

  • Две таблицы: объединение двух запросов к таблицам. Этот режим используется по умолчанию.
  • Три или более таблиц: позволяет объединять произвольное количество запросов к таблицам.

Таблицы будут добавлены в том порядке, в котором они выбраны, начиная с основной таблицы для режима «Две таблицы» и из основной таблицы в таблицах для добавления списка для режима «Три или более таблиц «.

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

В примере в этой статье мы будем использовать следующие две таблицы с примерами данных:

  • Онлайн-продажи: продажи, сделанные через интернет-канал. Пример таблицы онлайн-продаж с именем канала (online), датой, идентификатором клиента и столбцами единиц.
  • Продажи магазина: продажи, сделанные с помощью физических расположений компании. Пример таблицы продаж магазина с датами, единицами, рефератором, идентификатором клиента и столбцами имени канала (магазин).

Чтобы добавить эти таблицы, сначала выберите таблицу онлайн-продаж . На вкладке «Главная» выберите «Добавить запросы«, создающие новый шаг в запросе онлайн-продаж. Таблица «Продажи в Интернете» будет основной таблицей. Таблица, добавляемая к основной таблице, будет магазин продаж.

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

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

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

Таблица с объединенными общими столбцами и данными, за исключением столбца ссылщика, содержащего значения NULL для строк продаж в Интернете.

Добавление трех или более таблиц

В этом примере необходимо добавить не только таблицы продаж в Интернете и Магазин продаж, но и новую таблицу с именем Кто lesale Sales.

Пример таблицы оптовых продаж с именем канала (оптовая торговля), датой, идентификатором клиента и столбцами единиц.

Новый подход для этого примера — выбрать запросы добавления в качестве новых, а затем в диалоговом окне «Добавить» нажмите кнопку «Три или несколько таблиц «. В списке доступных таблиц выберите каждую таблицу, которую нужно добавить, и нажмите кнопку «Добавить«. После отображения всех таблиц в списке добавления таблиц нажмите кнопку «ОК«.

Три или более таблиц.

После нажатия кнопки «ОК» будет создан новый запрос со всеми добавленными таблицами.

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

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