Excel power query управление параметрами

Обновлено: 07.07.2024

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

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

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

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

Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами в Microsoft Query.

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

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

В редакторе Power Query выберите Главная > Параметры > Параметры.

В диалоговом окне Управление параметрами выберите Новый.

При необходимости за установите следующее:

Это должно отразить функцию параметра, но оставить его как можно более кратким.

Описание

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

Обязательные

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

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

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

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

После создания параметра запрос списка отобразится в его значениях.

Этот параметр определяет тип данных параметра.

Предлагаемые значения

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

Значение по умолчанию

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

Текущее значение

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

Чтобы создать параметр, выберите ОК.

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

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

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

Выберите Главная> Управление параметрами >Новый параметр.

В диалоговом окне Управление параметрами введите следующие сведения:

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

В главе 21 мы рассмотрели, как пользовательские функции применяются для предварительной обработки наборов данных перед объединением файлов. В этой главе мы добавим «вишенку на торт». Вспомните пример из предыдущей главы, по извлечению расписаний из папки. Допустим, вы сохранили файл консолидации в H:\Payroll, и в течение нескольких месяцев хранили расписания во вложенной папке H:\Payroll\Timesheets. Если вы передадите свое решение кому-то другому, на новом компьютере название папки, скорее всего, будет иным. Нельзя ли настроить решение так, чтобы исходный путь не играл роли, а было лишь важно, что данные хранятся во вложенной папке …\Timesheets относительно файла консолидации?

Рис. 23.1. Таблица параметров

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

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

Откройте файл 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 –> Расширенный редактор. Вместо…

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.

Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.

И вот под катом вы можете найти подробности всего этого великолепия возможностей.

Совместимость и технические подробности

Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).

Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.

Как оно работает

После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.

В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.

Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.

Возможности Power Query

У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.

Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.

Объединение файлов лежащих в папке

Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

Текстовые функции

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

Числовые функции

К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:

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

Функции для работы с датами, временем и продолжительностью

К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:

Автоматическое определение формата вписанной даты (в excel c этим большая боль)
Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.

Unpivot — Pivot

В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка

Операция Merge — смерть ВПР

Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.

Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.

Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка

Подключение к различным базам данных. Query Folding.

Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.

А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.

Язык программирования “М”

Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.

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

Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:

Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.

Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api

Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.

Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным :)

Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.

Еще раз про повторяемость и про варианты применения

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

Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.

разбираю семантику для Толстых проектов,
Делаю частотные словари,
Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
Делаю аудит контекстной рекламы на данных из K50 статистика,
И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды

Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.

Пару слов про локализацию

На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.

Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.

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

Power Query в меню Excel

В зависимости от того, какая у вас версия Excel, вид надстройки Power Query может выглядеть по-разному. В Excel 2010 и 2013 надстройка появляется в виде отдельной вкладки «Power Query» (если у вас такой вкладки нет, прочитайте, как ее установить ).

Работа с данными в Power Query

Power Query умеет:

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

Таким образом, Power Query – это полноценный ETL-инструмент (Extract, Transform, Load).

Подключение к данным в Power Query

В Power Query можно подключать данные из самых разных источников: таблицы в самом файле и других Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в интернете, файлы xml и json, pdf-файлы, данные из канала OData и так далее. А также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.

Чтобы посмотреть, какие именно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (или Создать запрос, если у вас не новая версия Excel).

Для примера добавим в Power Query данные из таблицы.

— в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
— для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).

В открывшемся окне поставьте галочку «Таблица с заголовками».

Таблица с данными при этом превратится в «умную» smart-таблицу.

Откроется окно редактора запросов, в котором будет наша таблица. Нажимаем кнопку в меню Главная → Закрыть и загрузить. Готово!

Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.

Редактор запросов Power Query

Разберем подробнее интерфейс редактора запросов Power Query.

Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.

Итак, в редакторе Power Query есть:

Лента редактора запросов для вкладок меню: Главная, Преобразование, Добавить столбец, Просмотр.
Перечень созданных запросов, который можно свернуть / развернуть.
Строка формул.
Название самого запроса.
Примененные шаги запроса: записанные шаги получения или преобразования данных. Их можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.
Область предварительного просмотра, в которой выводится результат преобразования данных для каждого шага.
Меню для данных, которое открывается при нажатии правой кнопкой мышки.
При выборе правой кнопкой мыши названия шага появляется его контекстное меню.

Преобразование данных

Посмотрим на простом примере, как преобразовать данные в Power Query.

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

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

выделите таблицу (можно выделить таблицу целиком или одну из ячеек);
выберите в меню Данные → Из таблицы (Из таблицы/диапазона);
в появившемся окне поставьте галочку рядом с «Таблица с заголовками» → ОК;
в открывшемся редакторе запросов выделите столбцы «показатель» и «город», нажав мышкой на названия столбцов с зажатым Ctrl;
в меню нажмите Преобразование → Заполнить → Вниз.

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

Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.

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

Кроме простых операций с данными, Power Query умеет выполнять и другие действия: сортировать, фильтровать, заменять, группировать, заполнять пустые значения, удалять дубликаты, работать с текстом и числами, выполнять простые вычисления, транспонировать таблицы и разворачивать их столбцы, объединять данные и многое-многое другое.

Читайте также: