Создание, загрузка и изменение запроса в Excel (Power Query)
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше
Power Query предлагает несколько способов создания и загрузки power querys в книгу. Вы также можете настроить параметры загрузки запроса по умолчанию в окне Параметры запроса.
Совет Чтобы упросить, сформированы ли данные на этом плане с помощью Power Query, выберите ячейку с данными, а если появится вкладка Лента контекстного запроса, данные загружаются из Power Query.
Знать, в какой среде вы сейчас? Power Query хорошо интегрирован с пользовательским интерфейсом Excel, особенно при импорте данных, работе с подключениями и редактировании таблиц, таблиц с Excel и именовых диапазонов. Чтобы избежать путаницы, важно в любой момент времени знать, в какой среде вы находитесь, в какой Excel или Power Query.
|
Знакомые Excel, лента и сетка |
Лента редактора Power Query и предварительный просмотр данных |
|
|
Например, работа с данными на Excel значительно отличается от Power Query. Кроме того, подключенные данные, которые вы видите на Excel, могут не работать с Power Query для их обработки. Это происходит только при загрузке данных на таблицу или в модель данных из Power Query.
Переименование я вкладок на таблицах Ямы лучше переименовывать по своему смыслу, особенно если их много. Особенно важно пояснить разницу между данными и данными, загруженными из редактора Power Query. Даже если у вас всего два листа: с таблицей Excel «Лист1»и запросом, созданным путем импорта таблицы Excel Таблица1,ее легко запутать. Всегда имеет смысл изменить названия ярлыков по умолчанию на более понятное. Например, переименуйте Лист1 в Таблицу данных и Таблицу1 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.
Вы можете создать запрос из импортируемых данных или пустой запрос.
Создание запроса из импортируемых данных
Это самый распространенный способ создания запроса.
-
Импорт некоторых данных. Дополнительные сведения см. в том, как импортировать данные из внешних источников.
-
Выберем ячейку в данных и выберите запрос> изменить.
Создание пустого запроса
Вы можете начать с нуля. Это можно сделать двумя способами.
-
Выберите Данные> Получить данные >из других источников > пустой запрос.
-
Выберите Данные> получить данные >запуск редактора Power Query.
На этом этапе вы можете вручную добавлять шаги и формулы, если хорошо знаете язык формул Power Query M.
Кроме того, можно выбрать команду На главная в группе Новый запрос. Выполните одно из указанных ниже действий.
-
Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> «Получить данные» на Excel ленте.
-
Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как> последние источники на Excel ленте.
-
Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.
Предположим, что запрос действителен и не имеет ошибок, его можно загрузить обратно на таблицу или в модель данных.
Загрузка запроса из редактора Power Query
В редакторе Power Query сделайте следующее:
-
Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузить.
-
Чтобы загрузить данные в модель данных, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузка.
В диалоговом окне Импорт данных выберите добавить эти данные в модельданных.
Совет Иногда команда «Загрузить в» неатривна или отключена. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку Закрыть & Загрузить, на новом > запросы данных & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите загрузить в . Кроме того, на ленте редактора Power Query выберите запрос> Загрузить в.
Загрузка запроса из области «Запросы и подключения»
В Excel может потребоваться загрузить запрос на другой таблицу или в модель данных.
-
В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
-
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите загрузить в. Появится диалоговое окно Импорт данных.
-
Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).
Существует несколько способов изменить запрос, загруженный на таблицу.
Изменение запроса на Excel данных
-
Чтобы изменить запрос, найдите ранее загруженную из редактора Power Query, выйдите ячейку в данных и выберите запрос > Изменить.
Изменение запроса в области «Запросы & подключения»
Возможно, вы & области Запросы и подключения удобнее использовать, если в одной книге много запросов и вы хотите быстро найти его.
-
В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
-
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.
Изменение запроса в диалоговом окне «Свойства запроса»
-
В Excel выберите Data> Data & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства ,выберите вкладку Определение в диалоговом окне Свойства и нажмите кнопку Изменить запрос.
Совет Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос.
Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.
-
Чтобы открыть модель данных, выберите Power Pivot > Управление.
-
В нижней части окна Power Pivot выберите вкладку нужной таблицы.
Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.
-
Обратите внимание на имя таблицы.
-
Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.
-
Выберите > подключения & свойства >Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.
-
Завершив внесение изменений в редакторе Power Query, выберите файл > Закрыть & загрузить.
Результат
Запрос на этом и в таблице в модели данных обновляются.
Если вы заметили, что загрузка запроса в модель данных занимает намного больше времени, чем загрузка на лист, проверьте действия Power Query, чтобы узнать, фильтруется ли текстовый столбец или структурированный столбец списка с помощью оператора Contains. Это действие приводит Excel повторно прогонять весь набор данных для каждой строки. Более того, Excel не могут эффективно использовать многопрочитанные выполнения. В качестве обходного решения попробуйте использовать другой оператор, например Равно или Начинает с.
Корпорация Майкрософт знает об этой проблеме и находится в стадии исследования.
Вы можете загрузить Power Query:
-
На один из них. В редакторе Power Query выберите Home> Close & Load > Close & Load.
-
В модель данных. В редакторе Power Query выберите Home> Close & Load > Close & Load To.
По умолчанию Power Query загружает запросы на новый таблицу при загрузке одного запроса и одновременно загружает несколько запросов в модель данных. Вы можете изменить поведение по умолчанию для всех книг или только для текущей книги. При настройке этих параметров Power Query не меняет результаты запроса на месте или в данных модели данных и примечаниях.
Кроме того, динамически переопределять стандартные параметры запроса можно в диалоговом окне Импорт, которое отображается после выбора параметра & Загрузитьв.
Глобальные параметры, которые применяются во всех книгах
-
В редакторе Power Query выберите Файл > параметры и параметры >параметры запроса.
-
В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.
-
В разделе Загрузка Параметры запроса по умолчанию сделайте следующее:
-
Выберите Использовать стандартные параметры загрузки.
-
Выберите Указать настраиваемые параметры загрузкипо умолчанию, а затем выберите или отобирать параметр Загрузить на таблицу или Загрузить в модель данных.
-
Совет В нижней части диалогового окна можно выбрать восстановить параметры по умолчанию, чтобы вернуться к настройкам по умолчанию.
Параметры книги, которые применяются только к текущей книге
-
В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.
-
Выполните одно или несколько из указанных ниже действий.
-
В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.
По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.
-
В области Связивыберите или отоберем создать связи между таблицами при первом добавлении в модель данных.
По умолчанию перед загрузкой в модель данных можно найти существующие связи между таблицами, например внешние ключи в реляционной базе данных, и импортировать их вместе с данными. Если вы предпочитаете делать это самостоятельно, сделайте это самостоятельно.
-
В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.
По умолчанию отношения не обновляются. При обновлении запросов, уже загруженных в модель данных, Power Query находит существующие связи между таблицами, например внешние ключи, в реляционной базе данных и обновляет их. Это может привести к удалению связей, созданных вручную после импорта данных, или к новым связям. Тем не менее, если вы хотите сделать это, выберите этот параметр.
-
В области Фоновыеданные выберите или отпустите разрешение предварительного просмотра данных для скачивания в фоновом режиме.
По умолчанию предварительный просмотр данных загружается в фоновом режиме. Если вы хотите сразу же видеть все данные, отобираем этот параметр.
-
См. также
Справка по Power Query для Excel
Управление запросами в Excel
Нужна дополнительная помощь?
|
Добрый день! Никак не могу найти на просторах интернета как же сделать так, чтобы запрос был к текущей книге без уточнения её адреса? То есть, чтобы я мог спокойно переименовать, перенести эту книгу и запрос оставался рабочим? |
|
|
Михаил Л Пользователь Сообщений: 2639 |
Кирилл Беляев, было уже много раз. Передаем путь и имя файла формулой |
|
Спасибо. Вот только я не понял что мне это дает? Я открыл ваш документ, там как был прописан ваш путь к C:UsersАдминистраторDesktopЛист Microsoft Excel (2).xlsx так и остался при открытии его с совершенно другого места. Или вы имели в виду просто использование параметризации пути с помощью умной таблицыименованного диапозона? Если так, то какая разница где мне прописывать новый путь в теле запроса или в теле ячейки? Мне ведь нужно чтобы запрос сработал с любого места и с любого имени книги без использования умных таблиц Изменено: Кирилл Беляев — 05.03.2021 07:09:16 |
|
|
Андрей VG  Пользователь Сообщений: 11878 Excel 2016, 365 |
#4 05.03.2021 07:25:28 Доброе время суток.
Полагаю, Михаил полагал, что вы сами можете докрутить, используя функцию ЯЧЕЙКА Относительный путь к данным PowerQuery |
||
|
Тогда понятно) Я просто думал что неужели нет варианта без таких костылей.. Изменено: Кирилл Беляев — 05.03.2021 07:32:45 |
|
|
buchlotnik  Пользователь Сообщений: 3863 Excel 365 Бета-канал |
#6 05.03.2021 07:34:20
не, Михаил сам докрутил — лист1, А1 — просто немножко спрятал, но диспетчер имен все видит ))) Соблюдение правил форума не освобождает от модераторского произвола |
||
|
Андрей VG Пользователь Сообщений: 11878 Excel 2016, 365 |
#7 05.03.2021 07:47:59
Привет, Михаил. |
||
Странно только как можно было не найти статью Дмитрия. Это как надо было поисковый запрос формулировать?!|
Кирилл Беляев  Пользователь Сообщений: 63 |
#8 05.03.2021 08:13:05
«power query запрос к текущему файлу» и производные. Изменено: Кирилл Беляев — 05.03.2021 08:13:35 |
||
|
Дмитрий(The_Prist) Щербаков  Пользователь Сообщений: 14181 Профессиональная разработка приложений для MS Office |
#9 05.03.2021 09:19:47
тут больше вопрос в том, что именно этим запросом требуется получить из книги Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы… |
||
|
Кирилл Беляев Пользователь Сообщений: 63 |
#10 05.03.2021 10:39:18
да, я пробывал карентворкбук, но мне какраз нужен был весь контент который таким образом непоказывается.. |
||
|
Михаил Л Пользователь Сообщений: 2639 |
#11 05.03.2021 11:24:50
А теперь нажмите Ctrl + Alt + F5 Так же можно получить все значения листа Изменено: Михаил Л — 05.03.2021 11:27:56 НА МЯСО В ПРОФИЛЕ |
||
|
Кирилл Беляев Пользователь Сообщений: 63 |
#12 05.03.2021 11:29:52 Уже разобрались спасибо) не совсем очевидно было что белым шрифтом на белом фоне что-то написано было) |
|
ADO — класс для работы с данными Excel при помощи SQL |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Зачем нужен Power Query
Как установить Power Query
Как его Настроить
Как изменить запрос
Хитрости »
30 Ноябрь 2016 47164 просмотров
В большинстве случаев создание запросов PowerQuery создается на основании файлов Excel, отличных от того файла, в котором сам запрос. Как пример — одна из статей на этом сайте: План-фактный анализ в Excel при помощи Power Query. Там идет обращение к некоторым вспомогательным файлам с данными и запрос ко всем файлам определенной папки. Т.е. мы можем знать только папку относительно файла с самим запросом и имена некоторых вспомогательных таблиц. И здесь как раз возникает нюанс: если впоследствии переместить или переслать файл с запросом (или даже полностью всю модель данных со всеми файлами) — то придется для каждого запроса к отдельному файлу менять источник. Это можно сделать либо через Параметры источника данных самого запроса:
- Для пользователей Excel 2010-2013:
Перейти на вкладку Power Query -группа Настройки(Options) —Параметры источника данных(Data Source Settings) - для пользователей 2016 и выше:
Перейти на вкладку Данные(Data) —Создать запрос(New Query) —Параметры источника данных(Data Source Settings)
либо изменив текст каждого запроса в редакторе запросов, изменив там строку источника:
И в том и в другом случае после смены каждого источника придется ждать обновления запросов. Как ни странно, но стандартно, без танцев с бубном, сделать некий относительный путь(указав лишь часть пути, как это делается в web-программировании) к источнику данных нельзя.
Как же вообще сделать возможность обновления источника данных при смене расположения файлов?
Есть два варианта:
Вариант 1
Не совсем автоматический, но способный чуть облегчить жизнь — использовать возможность создания параметров для запросов. Параметр — это некая именованная константа, которая может быть как одним значением(число, текст, дата и т.д.), так и целой таблицей(возможно впоследствии добавят возможность создания вычисляемых параметров, но на момент написания статьи это не поддерживалось). В нашем случае это будет одно значение — путь к основной папке. Чтобы создать новый параметр необходимо перейти в редактор запросов(выбрать любой запрос в книге -Правая кнопка мыши -Изменить) и на вкладке Главная выбрать Управление параметрами —Создать параметр.
В появившемся окне задаем необходимые значения:
- Обязательно — читать как Обязательный, если флажок установлен и Необязательный, если флажок снят. Отвечает за необходимость указывать значение. Если флажок установлен — то параметр не будет создан/изменен, пока мы не укажем Текущее значение. Если флажок снят — то значение параметра допускается оставить пустым.
- Имя параметра — Без комментариев, я указал здесь sPath
- по желанию добавляем описание к параметру, ровным счетом это ни на что не влияет
- Тип данных параметра — в нашем случае это Текст. Здесь лучше не рисковать, указывая Любой(Any), т.к. в этом случае можем получить ошибку запроса.
- Предлагаемые значения — выбираем Любое значение(логичнее было бы назвать этот пункт «Одно значение». Т.к. это больше подходит на мой взгляд).
- Текущее значение — это как раз то значение, которое и будет хранить наш параметр. Здесь я указываю путь к основной папке: G:Готовая модель ПланФактного анализа. Указываю со слешем на конце пути, чтобы не добавлять его в каждый источник после.
Теперь останется для каждого запроса добавить ссылку на этот параметр вместо жестко прописанного пути. Для этого в расширенном редакторе запросов(Главная -Расширенный редактор) для каждого запроса необходимо изменить переменную часть пути к файлам на наш параметр sPath:
Обращаю внимание, что после имени параметра(sPath) идет амперсанд(&). Он необходим для объединения двух текстовых строк в одну.
А теперь пара ложек дегтя для этого способа:
- Самый очевидный недостаток: при переносе файлов в любом случае необходимо будет заходить в редактор запросов (Главная —Управление параметрами —Изменить параметры) и изменять путь, указанный в параметре на новый. Так же это можно сделать напрямую из редактора запросов, раскрыв в левой части область запросов, выбрав параметр и вписав новое значение:
- И не очевидный недостаток: частенько такой подход приводит к ошибке получения данных при слиянии связанных запросов. Что делает этот способ не жизнеспособным в полной мере для большинства распространенных задач
Вариант 2
Создать еще один запрос в основной книге, на основании формул в Excel. Решение основано на возможностях встроенной функции Excel ЯЧЕЙКА(CELL). С её помощью можно получить полный путь к файлу, имя листа и книги. Чуть более подробно синтаксис этой функции и способы получения имени листа и книги я описывал в этой статье: Как получить имя листа формулой.
Итак, для начала нам необходимо на новом листе создать новую таблицу с двумя столбцами: значение и описание. В качестве значения в первой строке у нас и будет как раз формула с функцией ЯЧЕЙКА:
=ПСТР(ЯЧЕЙКА(«filename»;A1);1;ПОИСК(«[«;ЯЧЕЙКА(«filename»;A1))-1)
данная формула возвращает только путь к файлу, в котором записана. Что нам в общем-то от неё и надо.
При желании можно дописать подсказку к значению. Я это сделал для демонстрации и чтобы было понятно — что мы сможем потом дополнять эту таблицу другими переменными значениями при необходимости.
Теперь из этой таблицы необходимо сделать динамическую, или как их еще называют — умную: выделяем ячейки с данными(A1:B2) -вкладка Вставка(Insert) и выбрать Таблица(Table). Галочку «Мои данные содержат заголовки» оставляем включенной, даем понятное имя таблице — Parameters:
Далее выделяем любую ячейку внутри созданной таблицы и создаем новый запрос: вкладка Данные —Из таблицы(для пользователей PowerQuery 2013 и 2010 — вкладка PowerQuery -Из таблицы). Имя запроса у нас будет совпадать с именем таблицы — Parameters и этот запрос будет содержать как раз все значения нашей умной таблицы.
И теперь нам надо из этого запроса получить значение только одной конкретной ячейки — первой ячейки столбца «Значение»(в которой у нас формулой возвращается путь к папке). Для этого придется чуть пошаманить. Нам необходимо получить ссылку на таблицу «Parameters» и уже из неё получить значение нужной ячейки. Все это придется проделать на языке M, но звучит страшнее, чем выглядит — это всего одна строка:
Excel.CurrentWorkbook(){[Name=»Parameters»]}[Content]{0}[Значение]
Теперь разберем по шагам:
-
Excel.CurrentWorkbook() — непосредственно функция, которая получает данные обо всех умных таблицах(и именованных диапазонах) внутри книги Excel, в которой создан этот запрос (CurrentWorkbook — текущая книга).
{[Name=»Parameters»]} — так мы даем понять функции Excel.CurrentWorkbook, что нам нужны данные исключительно из объекта с именем «Parameters»(это наша умная таблица). На всякий случай уточню: получить просто ссылки на ячейки листа не получится, т.к. функция Excel.CurrentWorkbook данных о листах не получает вообще. Только именованные диапазоны и умные таблицы.
[Content] — все содержимое таблицы «Parameters»
{0} — пожалуй, самая хитрая часть для «не программистов»
Это номер строки в указанной таблице(«Parameters»). При этом номера строк в запросе начинают отсчет с 0 и заголовки при этом не учитываются. Т.е. наш параметр находится физически во второй ячейке столбца «Значение» таблицы «Parameters» на листе. Но в рамках самой таблицы это первая её строка, т.к. заголовок не учитываем. Но т.к. в таблице отсчет начинается с 1, а в запросах с 0 — то и нам надо указывать, учитывая эту особенность. Если бы мы обращались ко второй строке таблицы — указать необходимо было бы 1. И да — указывать обязательно в фигурных скобках.[Значение] — здесь в квадратных скобках указывается имя столбца(без всяких кавычек). Если бы столбец в таблице был всего один — то можно было бы его не указывать вовсе. Но т.к. у нас их больше — то указание обязательно, иначе запрос вернет всю строку — т.е. значения всех столбцов таблицы.
Т.е. строка Excel.CurrentWorkbook(){[Name=»Parameters»]}[Content]{0}[Значение] означает: из книги с запросом обратиться к таблице «Parameters»({[Name=»Parameters»]}), считать все данные([Content]) и отобрать оттуда значение первой строки({0}) столбца «Значение»([Значение])
И эта строка возвращает исключительно путь к папке — именно тот, который у нас получается в результате вычисления формулы с ЯЧЕЙКА.
Теперь, после того как разобрались что делает чудо-строка — осталось понять как это применить. Надо просто для каждого запроса перейти в редактор и в строке с источником:
Источник = Folder.Files(«C:Готовая модель ПланФактного анализаФакт»),
вместо части пути указать созданную строку запроса, добавив амперсанд(&) для объединения разных значений:
Источник = Folder.Files(Excel.CurrentWorkbook(){[Name=»Parameters»]}[Content]{0}[Значение] & «Факт»),
Все, теперь при перемещении книги с запросом или всей модели данные будут обновляться без какого-то ручного вмешательства.
В общем-то, такой подход можно использовать для задания даже имен файлов, используемых в модели. Например, можно дополнить таблицу именем файла с данными плана или путем к вспомогательным таблицам.
Скачать готовую модель план-фактного анализа с динамически изменяющимся путем к данным:
Готовая модель План-фактного анализа — относительный путь (491,0 KiB, 3 043 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика