Windows 11 домашняя для одного языка как сделать pro

Динамические значения через параметр в PowerQuery — язык M

Видеокурсы по Excel

В сотрудничестве с SEDUO я создал несколько видеокурсов:

  • Как работать с Excel – научитесь эффективно использовать Excel – самый популярный ( рейтинг 97 %, более 13 000 студентов, + 750 положительных отзывов ) – Получить курс
  • MaxiCourse Excel – получите лучшую работу, больше денег, станьте незаменимым ( более 6 часов просмотра, 160 уроков ) – Получить курс
  • G-таблицы. — если не нравится Excel, воспользуйтесь курсом на Google-таблицах

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

Если вас не интересует константа или функция.

Я предполагаю базовые знания PowerQuery и умелое извлечение данных из рабочей книги.

Динамические значения — теория

В Power Query часто требуется выполнить редактирование пользователем или «настроить» загруженные данные в соответствии с выбором пользователя. Например, фильтр по пользователю. Загрузите только необходимый лист. Отображать только обязательные столбцы. Но пользователь боится PowerQuery. Поэтому нужно вставить пользовательские значения из Excel (которые пользователь знает конфиденциально) в Power Query. Это можно сделать несколькими способами:

  • Использование константы — описано в отдельной статье Динамическая загрузка в PowerQuery с использованием константы на языке M
  • Использование параметра — описано в отдельной статье в процессе подготовки
  • Я буду использовать функцию , описанную в этой статье

Динамические значения с использованием функции

В этой статье я покажу, как функция в Power Query просматривает таблицу как таблицу для строки и берет значение из второго столбца ( в примере адрес псевдонима папки, имя файла, а также имялист ). Для ясности статья разделена на несколько глав:

  • Исходные данные — в основном таблица как таблица, на которую мы будем ссылаться
  • Загрузка внешних данных — для которых будет динамически загружаться адрес (имя файла)
  • Функция в M с параметром — как написать функцию, которая будет запрашивать таблицу на листе Excel
  • Функция была улучшена — здесь вы также можете выбрать имя таблицы (или примеры того, как могут работать пользовательские функции в Power Query)
  • Вставка функции в М-код для загруженной таблицы — редактирование М-кода
  • Результат – как обновить
  • Другие статьи по теме
  • Скачать
Читайте также:  Как установить гаджеты на рабочий стол для windows xp

Исходные данные

Основой являются две тетради. Одна из них будет исходной таблицей (из которой она будет загружаться динамически), другая — целевой книгой, в которой вы выбираете/заполняете, например, имя листа, адресную книгу, название месяца, а Power Query найдет соответствующий лист из выбранного файла в выбранной адресной книге. Или значение из соответствующего столбца фильтруется и т.д.

Таблица так таблица — исходные данные

Нам нужна исходная таблица, из которой функция, созданная в М-коде, будет считывать информацию. Для правильной работы таблица должна быть «отформатирована» как таблица. В таблице будет два столбца. В первом будет указано, что это такое или тип значения ( «FileName» ), а во втором столбце будет указано значение этого параметра ( «MyFile .xlsx» ). Следующей строкой может быть папка ( «Адрес» — «C:/test/» ), имя другого листа ( «Лист» — «Январь» ), и т.д.

Разумеется, таблица как таблица будет иметь собственное имя ( «Таблица параметров» ). Она не будет называться Table1 ( но, если вы не возражаете, может быть ).

Загружен внешнийданные

Я загружу книгу, содержащую листы с названиями месяцев. На каждом листе одна и та же таблица, с одинаковыми названиями столбцов, только значения разные, относящиеся к данному месяцу.

Примечание. Если вам не нужно загружать отдельные листы и вам нужен только один конкретный лист, это не имеет значения ( т.е. вы будете динамически изменять только имя файла и имя каталога ). Процедура такая же, только на последнем шаге вы опускаете ссылку на динамическое имя конкретного листа из инструкций.

Функция в M с параметром

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

Пустой запрос

В Power Query в меню Домашняя страница (Home) нажмите Новый источник > Другие источники > Пустой запрос

Появится пустой запрос, который вы переименуете в: f_Parameter ( опять же, имя может быть любым, но я выбрал это ).

Читайте также:  Как отключить ускорение загрузки windows 10

Расширенный редактор

У вас есть пустой вопрос, и у него есть имя. Теперь просто вставьте в него код. Поскольку он не помещается в одну строку, необходимо открыть расширенный редактор и вставить в него код (просто Копировать > Вставить). Только в меню Домашняя страница (Home) нажмите Расширенный редактор :

Где, функция:

А теперь вставьте функцию:

в пустой запрос

// Функция вернет значение из таблицы let Parameter=(ParameterLabel) => let // Представляем функцию, которая будет возвращать требуемое значение в таблицу // на основе параметра из столбца и значения в следующем столбце Source = Excel.CurrentWorkbook()<[Name=»Parameter Table»]> [Содержание], значение =Source<[ParameterFromColumn=ParameterLabel]>[CorrespondingValueTable] в значении параметра

Функция улучшена

Функция также может быть улучшена путем выбора имени таблицы как таблицы:

// функция с большим количеством параметров let Parameter=(TableName, ParameterLabel) => let // ParameterTable Source = Excel.CurrentWorkbook()<[Name=TableName]>[Content], value = Source<[ParameterFromColumn=ParameterLabel]>[RelevantValueTable] в значении в Parameter

Я сохранил дополнительные советы и рекомендации по динамической загрузке данных из таблиц в Excel для М-языка. Со временем я могу добавить больше советов и рекомендаций.

Вставка функции в M-код для загруженной таблицы

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

  • Основные данные из файла xlsx — в процессе подготовки
  • Сделать стол из кучи листьев
  • Вставить данные из папки

Я предполагаю, что вы загрузили таблицу. Не стесняйтесь вернуться туда, где это было прочитано:

Загружается куда

Встаньте в запросы на загруженную таблицу и только в меню Главная (Home) нажмите на Расширенный редактор и вы получите аналогичный код (в зависимости от внесенных изменений ):

let Source = Excel.Workbook(File.Contents(«D:\Skoleni\Excel Advanced\09 — PowerQuery\Power Query — источник динамических данных.xlsx»), null, true), #»Удаленные столбцы» = Таблица .RemoveColumns(Источник,<«Имя», «Элемент», «Вид», «Скрытый»>), #»Расширенные данные» = Table.ExpandTableColumn(#»Удаленные столбцы», «Данные», <«Столбец1 «, «Column2», «Column3»>, <«Data.Column1», «Data.Column2», «Data.Column3″>), #»PromoteHeaders» = Table.PromoteHeaders(#»Expanded Data» , [PromoteAllScalars=true]),#»TransformType» = Table.TransformColumnTypes(#»Заголовок с повышенным уровнем»,<<«Id», тип any>, <«Имя», тип text>, <«Mмснc», тип text>> ; ), #»Filtered Rows» = Table.SelectRows(#»Change Type», каждая ([Id] = 1 или [Id] = 2 или [Id] = 3) и ([Mmsснc] = «Январь»)) в #»Отфильтрованные строки»

Измененный код

Таблица загружается и изменяется только статическая информация, например, об адресе и файле «D:\Skoleni\Excel Pokrocile\09 — PowerQuery\Power Query — dynamic data source.xlsx» на f_Parameter(«Папка»)&f_Parameter(«Имя») .

Или f_Parameter — это имя созданной вами функции. Имя — это имя строки, и функция возвращает имя файла. Подписать & объединить каталог и имя файла. Образец для одной строки:

Читайте также:  Как сбросить пароль на планшете asus windows

Source = Excel.Workbook(File.Contents(«D:\Skoleni\Excel Advanced\09 — PowerQuery\Power Query — источник динамических данных.xlsx»), null, true), // изменить на Source = Excel. Workbook(File.Contents(f_Parameter(«Папка»)&f_Parameter(«Имя»)), null, true),

Аналогично настроить месяц и т.д.

Результирующий М-код может выглядеть, например, так:

let Source = Excel.Workbook(File.Contents(f_Parameter(«Папка»)&f_Parameter(«Имя»)), null, true), #»Removed columns» = Table.RemoveColumns(Source,<» Имя», «Элемент», «Вид», «Скрытый»>), #»Расширенные данные» = Table.ExpandTableColumn(#»Удаленные столбцы», «Данные», <«Столбец1», «Столбец2», «Столбец3 «>, <«Data.Column1», «Data.Column2», «Data.Column3»>),January_Sheet = Source<[Item=f_Parameter(«SheetName»),Kind=»Sheet»]>[ Data], #»Promote Header» = Table.PromoteHeaders(#»Expanded Data», [PromoteAllScalars=true]), #»TransformType» = Table.TransformColumnTypes(#»Promote Header»,<< «Id», type любой>,<«Имя», введите текст>, <«Mmsснc», введите текст>>), #»Отфильтрованные строки» = Table.SelectRows(#»Изменить тип», каждый ([Id] = 1 или [Id] = 2 или [Id] = 3) и ([Mмснc] = f_Parameter(«ListName»))) в #»Отфильтрованные строки»

Результат

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

Похожие статьи

Хотите узнать больше? Рекомендую статьи по теме:

Для скачивания

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

Всего

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

Статья обновлена: 19.09.2020 11:02

Если

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

Павел Ласбк — автор сайта

Microsoft Office (Word, Excel, Google Sheets , PowerPoint) Я использую с 2000 года (с 2004 года в этом домене) — В 2017 году я получил престижную награду MVP от Microsoft (пока я единственный из IR в категории Excel). Я также делюсь своими знаниями и опытом онлайн в видеороликах для SEDUO . В компаниях преподаю и консультирую , работаю в МУНИ. Я создаю этот сайт более 15 лет. Существует более 1000 бесплатных инструкций, советов и рекомендаций, включая более 250 различных шаблонов и рабочих книг.

Пока нет комментариев

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

Поделиться с друзьями
ОС советы