создание параметра power query
Использование параметров
Параметр служит способом простого хранения и управления значением, которое можно использовать повторно.
Параметры обеспечивают гибкость для динамического изменения выходных данных запросов в зависимости от их значения и могут использоваться для:
Создание параметра
Power Query предоставляет два простых способа создания параметров:
Из существующего запроса — Можно легко щелкнуть правой кнопкой мыши запрос, значение которого является простой неструктурированной константой, такой как, но не ограничена, датой, текстом или числом, и выбрать команду преобразовать в параметр.
Можно также преобразовать параметр в запрос, щелкнув правой кнопкой мыши параметр и выбрав преобразовать в запрос, как показано на следующем рисунке.
Свойства параметра
Параметр сохраняет значение, которое можно использовать для преобразований в Power Query. Помимо имени параметра и значения, которое он хранит, у него также есть другие свойства, которые предоставляют метаданные. Ниже приведены свойства параметра.
Имя — Укажите имя этого параметра, которое позволит легко распознать его и отличать от других параметров, которые вы можете создать.
Описание — Описание отображается рядом с именем параметра при отображении сведений о параметрах, что помогает пользователям, которые задают значение параметра, понять его назначение и его семантику.
Обязательное требование — Флажок указывает, могут ли следующие пользователи указывать, должно ли быть указано значение параметра.
Предлагаемые значения — Предоставляет пользователю предложение выбрать значение для текущего значения из доступных параметров:
Любое значение — Текущим значением может быть любое значение, указанное вручную.
Вы по-прежнему можете вручную ввести любое значение, которое требуется передать в параметр. Список предлагаемых значений служит только в качестве простых предложений.
Запрос — Использует запрос списка (запрос, вывод которого представляет собой список) для предоставления списка предлагаемых значений, которые позже можно выбрать для текущего значения.
Текущее значение — Значение, которое будет храниться в этом параметре.
Где использовать параметры
Параметр может использоваться различными способами, но чаще всего используется в двух сценариях:
В следующих разделах вы увидите пример для этих двух сценариев.
Аргумент Step
В настоящее время эта функция недоступна в Power Query в сети.
Например, можно просмотреть следующие запросы заказов с полями OrderID, Units и Margin.
Можно создать новый параметр с минимальным полем имени с типом десятичного числа и текущим значением 0,2, как показано на следующем рисунке.
Можно открыть запрос заказы и в поле поля выбрать параметр больше чем фильтр.
После нажатия кнопки ОК можно увидеть, что таблица отфильтрована с использованием текущего значения параметра.
Если изменить Текущее значение параметра минимального поля на 0,3, можно сразу же увидеть, как обновляется запрос заказов и отображаются только те строки, в которых маржа превышает 30%.
Множественные преобразования в Power Query предлагают эту возможность выбора параметра из раскрывающегося списка. Поэтому рекомендуется всегда искать и использовать преимущества параметров, которые могут предложить вам.
Аргумент пользовательской функции
Вы можете присвоить имя этой новой функции. В демонстрационных целях имя этой новой функции будет иметь значение MyFunction. После нажатия кнопки ОК в области запросы будет создана новая группа с именем новой функции. В этой группе вы найдете параметры, используемые для функции, запрос, который использовался для создания функции, и саму функцию.
Дополнительные сведения о создании пользовательских функций см. в статье Создание пользовательской функции.
Параметризация путей к данным в Power Query
Если вы уже начали использовать в работе инструменты бесплатной надстройки Power Query в Microsoft Excel, то очень скоро столкнётесь с одной узкоспециальной, но весьма частой и надоедливой проблемой, связанной с постоянно ломающимися ссылками на исходные данные. Суть проблемы в том, что если в своём запросе вы ссылаетесь на внешние файлы или папки, то Power Query жёстко прописывает абсолютный путь к ним в тексте запроса. У вас на компьютере всё работает прекрасно, но если вы решите отправить файл с запросом своим коллегам, то их ждёт разочарование, т.к. у них на компьютере путь к исходным данным уже другой, и наш запрос работать не будет.
Что же сделать в такой ситуации? Давайте рассмотрим этот случай подробнее на следующем примере.
Постановка задачи
Предположим, что у нас в папке E:\Отчеты по продажам лежит файл Топ-100 товаров.xls, представляющий собой выгрузку из нашей корпоративной базы данных или ERP-системы (1С, SAP и т.п.) Этот файл содержит информацию о наиболее популярных товарных позициях и выглядит внутри примерно так:
С ходу, наверное, понятно, что работать с ним в Excel в таком виде практически невозможно: будут мешать пустые строки через одну с данными, объединенные ячейки, лишние столбцы, многоуровневая шапка и т.д.
Поэтому рядом с этим файлом в той же папке мы создаём ещё один новый файл Обработчик.xlsx, в котором создадим запрос Power Query, который будет загружать страшненькие данные из исходного файла-выгрузки Топ-100 товаров.xls, и приводить их в порядок:
Создаем запрос к внешнему файлу
Приведём их в нормальный вид:
В итоге у нас должна получиться следующая, гораздо более приятная, картина:
Находим путь к файлу в запросе
В открывшемся окне во второй строке сразу же обнаруживается жёстко прописанный путь к нашему исходному файлу выгрузки. Если мы сможем заменить эту текстовую строку на параметр, переменную или ссылку на ячейку листа Excel, где этот путь будет заранее прописан, то мы сможем впоследствии легко его менять.
Добавляем умную таблицу с путём к файлу
Закроем пока Power Query и вернёмся в наш файл Обработчик.xlsx. Добавим новый пустой лист и сделаем на нём маленькую «умную» таблицу, в единственной ячейке которой будет записан полный путь к нашему файлу исходных данных:
Если предположить, что файл с исходными данными всегда лежит в той же папке, что и наш Обработчик, то путь, который нам нужен можно сформировать следующей формулой:
или в английской версии:
. где функция ЛЕВСИМВ (LEFT) берёт из полной ссылки кусок текста до открывающей квадратной скобки (т.е. путь к текущей папке), а затем к нему приклеивается имя и расширение нашего исходного файла с данными.
Параметризуем путь в запросе
Excel.CurrentWorkbook() <[Name="Параметры"]>[Content] <0>[Путь к исходным данным]
Давайте разберемся из чего она состоит:
Вот и всё, собственно.
Осталось нажать на Готово и проверить как работает наш запрос. Теперь при пересылке всей папки с обоими файлами внутри на другой ПК запрос будет сохранять работоспособность и определять путь к данным автоматически.
Глава 23. Таблицы динамических параметров в Power Query
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
В главе 21 мы рассмотрели, как пользовательские функции применяются для предварительной обработки наборов данных перед объединением файлов. В этой главе мы добавим «вишенку на торт». Вспомните пример из предыдущей главы, по извлечению расписаний из папки. Допустим, вы сохранили файл консолидации в H:\Payroll, и в течение нескольких месяцев хранили расписания во вложенной папке H:\Payroll\Timesheets. Если вы передадите свое решение кому-то другому, на новом компьютере название папки, скорее всего, будет иным. Нельзя ли настроить решение так, чтобы исходный путь не играл роли, а было лишь важно, что данные хранятся во вложенной папке …\Timesheets относительно файла консолидации?
Рис. 23.1. Таблица параметров
Скачать заметку в формате Word или pdf, примеры в формате архива
В настоящее время в языке M нет функции, позволяющей определить путь к используемой книге. Чтобы реализовать описанный выше сценарий, нужно:
Откройте файл Parameter Tables.xlsx. Он является продолжением файла расписаний, с которым вы работали несколько последних глав. В настоящее время все пути к файлам с исходными данными жестко закодированы.
Создание таблицы параметров
На листе Info создайте заготовку таблицы, как показано на рис. 23.1. Следующие названия должны быть такими же, как на рисунке: заголовок первого столбца Parameter, заголовок второго столбца Value, имя таблицы Parameters. Это позволит использовать заготовленный текст функции.
Теперь вам каким-либо способом нужно указать путь к папке Source Files на вашем ПК. Это может быть текст или значение, возвращаемое формулой в Excel. Мы предложим вам решение на основе функции =ЯЧЕЙКА( » имяфайла » ). Введите следующую формулу в ячейку B8:
=ЛЕВСИМВ(ЯЧЕЙКА( » имяфайла » );НАЙТИ( » [ » ;ЯЧЕЙКА( » имяфайла » );1)-1)& » Source Files\ «
Если вы еще не сохранили файл, эта функция вернет ошибку, поскольку Excel не может определить, где находится книга. Сохранение файла решит эту проблему:
Рис. 23.2. Динамически возвращаемый путь к папке Source Files на основе формулы Excel
Несколько слов о том, как работает формула. Фрагмент ЯЧЕЙКА( » имяфайла » ) возвращает путь к файлу, имя файла и имя активного листа – D:\Dropbox\!Сайт\6_Эффективность\Power Query\23\[Parameter Tables.xlsx]Info. Функция НАЙТИ() ищет первое вхождение символа [ в пути к файлу. Функция ЛЕВСИМВ() возвращает текст – имя файла до символа [. Поскольку сам символ [ нужно исключить, уменьшаем число извлекаемых символов на единицу. К извлеченному тексту с помощью знака конкатенации & добавляем Source Files\
Функция fnGetParameter
Теперь нужно предоставить Power Query возможность считывания значения в ячейке В8. Это можно сделать с помощью пользовательской функции:
Функция подключается к таблице параметров в книге Excel, и извлекает путь к папке Source Files. Откройте файл fnGetParameter.txt и скопируйте его содержимое в буфер. В Excel пройдите по меню Данные –> Получить данные –> Из других источников –> Пустой запрос. В редакторе Power Query перейдите на вкладку Главная –> Расширенный редактор. Выделите все строки кода и нажмите Ctrl+V, чтобы вставить содержимое текстового файла из буфера обмена. Нажмите Готово. Измените имя функции на fnGetParameter. Главная –> Закрыть и загрузить. Напомню, что для функций используется единственный тип загрузки – Только создать подключение.
Вызов функции fnGetParameter
Итак, у вас есть актуальный путь к папке с исходными файлами. Этот путь обновляется при открытии на каждом новом ПК. Вам осталось модернизировать запрос. В файле Excel пройдите по меню Данные –> Запросы и подключения. В области Запросы и подключения кликните правой кнопкой мыши на запросе Timesheets –> Изменить. Запрос возвращает ошибку, так как путь к папке прописан жестко:
Рис. 23.3. Разверните панель навигатора
Кликните стрелку Развернуть панель навигатора. Навигатор позволяет выбрать любой из ваших запросов/функций, так что удобно переключаться между ними, внося изменения и проверяя эффекты, которые изменения оказывают на другие запросы (см. ниже рис. 23.5). Щелкните правой кнопкой мыши запрос Timesheets –> Расширенный редактор. Вставьте строку кода сразу после let:
fullfilepath = fnGetParameter( » File Path » ),
Не забудьте про запятую в конце строки:
Рис. 23.4. Добавлена строка с вызовом функции fnGetParameter
Вы создали новую переменную fullfilepath для хранения значения из строки File Path таблицы Excel. Кстати, это делать не обязательно. Вы можете использовать вызов функции fnGetParameter вместо переменной в следующей строке. Однако, создав отдельную строку, вы облегчите отладку запроса. Нажмите Готово. Выберите шаг fullfilepath в области ПРИМЕНЕННЫЕ ШАГИ. Вам легко проверить, что путь указан верно (не будь отдельного шага с определением переменной, увидеть путь в коде Power Query было бы невозможно):
Рис. 23.5. Переменная fullfilepath правильно определяет путь к папке; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Теперь, когда вы убедились, что функция возвращает правильный путь с помощью формулы Excel, вы можете подставить переменную вместо жестко заданного пути к файлу. Щелкните правой кнопкой мыши запрос Timesheets –> Расширенный редактор. Вместо…
Power Query. Введение. Знакомство с Power Query
Описание
В этом уроке вы узнаете:
В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.
Power Query — это встроенный инструмент Excel для получения и преобразования данных (Get and Transform).
Специалистам по Excel приходится каждый день тратить уйму времени на преобразование данных. Мы работаем с разными источниками и данные далеко не всегда приходят в удобном виде.
Например, каждый месяц вы получаете таблицу с ответами на вопросы теста сотрудников компании, где вы работаете в таком виде:
Первый столбец — это дата прохождения теста. 2 следующих столбца — это информация о сотруднике и точке продаж. Далее множество столбцов с ответами на вопросы, а вопрос находится в заголовках столбцов. Ваша задача — получить такую таблицу:
Для каждого сотрудника здесь указано количество ответов, количество верных ответов, результат теста и перечень вопросов с неверными ответами.
В Excel эта задача решается долго и тяжело. Если тесты происходят, например, раз в неделю, то каждую неделю вам придется тратить уйму времени на обработку файлов. Более того, чтобы решить эту задачу стандартными возможностями Excel нужен достаточно высокий уровень владения.
Как вы думаете, сколько времени уйдет на решение этой задачи в Excel Power Query? Всего несколько минут. Более того, вам достаточно решить эту задачу всего 1 раз. А когда придут новые данные достаточно будет всего лишь нажать «Обновить».
Решение
Запуск Power Query
Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.
Если у вас Excel 2010 или 2013, то Power Query нужно сначала скачать. Перейдите по ссылке https://www.microsoft.com/ru-ru/download/details.aspx?id=39379 и нажмите «Скачать». Потом отметьте галочкой файл для вашей разрядности операционной системы (64 или 32 бит). После скачивания файла запустите установку.
Теперь в главном меню Excel должна появиться еще одна вкладка. Если вкладка не появилась, то перейдите в меню Файл — Параметры — Настройки — Управление — Надстройки СОМ. В открывшемся окне отметьте галочкой пункт Power Query.
Добавить кнопки Power Query на панель быстрого доступа
Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:
Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.
Нажмите «Настройка панели быстрого доступа». В появившемся окне справа снизу найдите кнопку «Экспорт/импорт». Нажмите ее и укажите путь к скачанному файлу.
Предварительные настройки Power Query
Откройте редактор запросов любым удобным способом:
В окне Редактора запросов нажмите Файл — Параметры и настройки — Параметры запроса. В пункте Загрузка данных сделайте настройки как на картинке:
Теперь перейдите в пункт Редактор Power Query и поставьте галочку «Отобразить редактор запросов».
Далее переходим в пункт Конфиденциальность и выбираем «Всегда игнорировать уровни конфиденциальности».
Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.
Создаем первый запрос Power Query
Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:
Перед вам появится следующее окно редактора запросов Power Query:
Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.
Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.
По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.
Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.
Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.
Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!
Использование пользовательских функций
Если Вы найдетесь в ситуации, когда необходимо применить тот же набор преобразований к разным запросам или значениям, создайте Power Query настраиваемую функцию, которую можно использовать многократно, как можно более эффективно. Power Query пользовательской функцией является сопоставление набора входных значений с одним выходным значением и создается из собственных функций и операторов M.
Хотя вы можете вручную создать собственную Power Query настраиваемую функцию с помощью кода, как показано в описании функций Power Query M, пользовательский интерфейс Power Query предлагает функции для ускорения, упрощения и улучшения процесса создания настраиваемой функции и управления ею. Эта статья посвящена этой возможности только в Power Query пользовательском интерфейсе и о том, как максимально эффективно их использование.
В этой статье описано, как создать пользовательскую функцию с Power Query помощью стандартных преобразований, доступных в Power Query пользовательском интерфейсе. Он посвящен основным концепциям создания пользовательских функций и ссылок на дополнительные статьи в Power Query документации по для получения дополнительных сведений о конкретных преобразованиях, упоминаемых в этой статье.
Создание пользовательской функции из ссылки на таблицу
Чтобы перейти к этому примеру, скачайте примеры файлов, которые используются в этой статье, по следующей ссылке для загрузки. Для простоты эта статья будет использовать соединитель папок. Дополнительные сведения о соединителе папки см. в разделе Папка. Цель этого примера — создать пользовательскую функцию, которая может быть применена ко всем файлам в этой папке, прежде чем объединять все данные из всех файлов в одну таблицу.
Этот параметр позволяет эффективно создать новый запрос с шагом навигации непосредственно к этому файлу в виде двоичного файла, а имя этого нового запроса — путь к выбранному файлу. Переименуйте этот запрос, чтобы он был файлом выборки.
Создайте новый параметр с параметром name File. Используйте запрос Sample File в качестве текущего значения, как показано на следующем рисунке.
Пользовательские функции можно создавать с помощью любого типа параметров. Нет необходимости, чтобы какая-либо пользовательская функция имела двоичный файл в качестве параметра.
Можно создать пользовательскую функцию без параметра. Обычно это происходит в сценариях, где входные данные могут выводиться из среды, в которой вызывается функция. Например, функция, которая принимает текущую дату и время среды, и создает определенную текстовую строку из этих значений.
После создания функции вы заметите, что для вас будет создана новая группа с именем функции. Эта новая группа будет содержать:
Применение преобразований к образцу запроса
Первое преобразование, которое должно произойти в этом запросе, — это тот, который будет интерпретировать двоичный код. Можно щелкнуть правой кнопкой мыши двоичный файл на панели предварительного просмотра и выбрать параметр CSV для интерпретации двоичного файла в CSV-файле.
Формат всех CSV-файлов в папке совпадает. Все они имеют заголовок, охватывающий первые четыре строки. Заголовки столбцов расположены в пяти строках, а данные начинаются со строки на шесть вниз, как показано на следующем рисунке.
Удаление первых четырех строк — Это действие приведет к отсечениям строк, которые считаются частью заголовка файла.
Дополнительные сведения о том, как удалить строки или отфильтровать таблицу по положению строки, см. в разделе Фильтрация по положению строки.
Продвижение заголовков — Заголовки последней таблицы теперь находятся в первой строке таблицы. Их можно повысить, как показано на следующем рисунке.
Power Query по умолчанию автоматически добавит новый измененный шаг типа после повышения уровня заголовков столбцов, которые будут автоматически определять типы данных для каждого столбца. Запрос образца файла преобразования будет выглядеть следующим образом.
Дополнительные сведения о повышении и понижении уровня заголовков см. в разделе повышение уровня и понижение уровня заголовков столбцов.
Вызов пользовательской функции в качестве нового столбца
Теперь, когда пользовательская функция создана и все шаги преобразования включены, можно вернуться к исходному запросу, в котором имеется список файлов из папки. На вкладке Добавление столбца на ленте выберите команду вызвать пользовательскую функцию из группы » Общие «. В окне вызвать пользовательскую функцию введите выходную таблицу в качестве нового имени столбца. В раскрывающемся списке запрос функции выберите имя функции ( файл преобразования). После выбора функции в раскрывающемся меню отображается параметр для функции, и можно выбрать столбец из таблицы, который будет использоваться в качестве аргумента для этой функции. Выберите столбец содержимого в качестве значения или аргумента, который должен быть передан для параметра File.
Дополнительные сведения о выборе или удалении столбцов из таблицы см. в разделе Выбор или удаление столбцов.
Добавить новый параметр в существующую пользовательскую функцию
Imagine, что у вас есть новое требование на основе того, что вы создали. Для нового требования необходимо, чтобы перед объединением файлов фильтровать данные в них, чтобы получить только те строки, в которых страна совпадает с Панама.
Чтобы это требование было выполнено, создайте новый параметр с названием Marketing с типом данных Text. Для текущего значения введите значение Панама.
С помощью этого нового параметра выберите запрос преобразовать образец файла и отфильтруйте поле Country (страна ), используя значение параметра » рыночный «.
Дополнительные сведения о фильтрации столбцов по значениям см. в разделе значения фильтров.
Создание пользовательской функции из многократно используемого фрагмента логики
При наличии нескольких запросов или значений, для которых требуется одинаковый набор преобразований, можно создать пользовательскую функцию, которая выступает в качестве многократно используемой части логики. Позже эту пользовательскую функцию можно вызвать по выбранным запросам или значениям. Эта пользовательская функция может сэкономить время и помочь вам в управлении набором преобразований в центральном расположении, которое можно изменить в любой момент.
Например, представьте запрос, содержащий несколько кодов в виде текстовой строки, и необходимо создать функцию, которая будет декодировать эти значения.
Начните с параметра со значением, которое служит примером. В этом случае это будет значение PTY-CM1090-слабое.
Из этого параметра вы создадите новый запрос, в котором будут применяться необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-слабый на несколько компонентов:
Ниже показан код M для этого набора преобразований.
Затем можно преобразовать этот запрос в функцию, щелкнув запрос правой кнопкой мыши и выбрав создать функцию. Наконец, можно вызвать пользовательскую функцию в любой запрос или значения, как показано на следующем рисунке.
После нескольких преобразований можно увидеть, что вы достигли желаемого результата и использовали логику для такого преобразования из пользовательской функции.