https://www.myonlinetraininghub.com/power-query-variables-3-ways
Автор оригинала: Mynda Treacy
Переменные Power Query - отличный способ хранения данных, которые можно использовать многократно и которые можно легко обновлять в одном месте. Существует несколько способов создания переменных.
1. С рабочего листа, что означает, что вам даже не нужно открывать редактор запросов, чтобы обновить переменную. Это здорово, если вы передаете файл кому-то другому для ведения.
2. Вы можете объявить переменную внутри редактора запросов. Это затрудняет пользователям простое изменение переменных и сохраняет все данные в одном месте.
3. Вы можете получить переменную из другого запроса, сделав ее динамической. По мере изменения данных в запросе переменная автоматически обновляется при обновлении на основе последних данных.
Смотрите видео:
Загрузите рабочую книгу:
https://d13ot9o61jdzpp.cloudfront.net/files/pq_variables.xlsx
Переменные Power Query с рабочего листа
Мы рассмотрим переменные Power Query, хранящиеся на рабочем листе. Здесь у меня есть таблица зарплат сотрудников (TblSalaries):
Я хочу рассчитать бонус с помощью Power Query, и я хочу, чтобы пользователь этого файла мог корректировать процент бонуса, не открывая Power Query. Мы сохранили переменную bonus в отдельной таблице на рабочем листе (BonusRate):
Мы загрузили обе эти таблицы в Power Query. В таблице BonusRate просто щелкаем правой кнопкой мыши Drill Down на ячейке ставки:
Я переименую этот запрос в ‘SheetBonusRate’ для целей этого руководства, чтобы мы знали, что он поступает из листа. На практике вы, скорее всего, просто назовете это ‘бонусной ставкой’.
В редакторе запросов у меня теперь есть два запроса: мой QuerySalaries и переменная SheetBonusRate:
Который использует переменную SheetBonusRate для расчета бонусов:
И моя таблица QuerySalaries теперь выглядит следующим образом:
Я закрою загрузку в таблицу в файле Excel:
Если я хочу обновить бонусную ставку, я просто ввожу новую ставку в ячейку E5 (1), а затем щелкаю правой кнопкой мыши таблицу QuerySalaries и Refresh (2):
И точно так же таблица обновляется новыми суммами бонусов:
Управление переменными запроса в редакторе запросов
Если вы предпочитаете хранить свои переменные в редакторе запросов, вы можете просто добавить их с помощью расширенного редактора. В приведенном ниже примере у меня есть данные о продажах (TblSales), и мне нужно добавить столбец для расчета налога с продаж.
Чтобы добавить переменную в редакторе Power Query, перейдите на главную вкладку Расширенный редактор:
После ‘let’ введите имя переменной* и значение переменной, за которыми следует запятая:
*Если в имени вашей переменной есть пробелы, вы должны объявить его с помощью # и заключить в двойные кавычки, например: #"Tax Rate"
Если ваша переменная является текстовой, она также должна быть заключена в двойные кавычки.
Теперь вы можете увидеть переменную в списке Примененных шагов, и в дальнейшем вы можете редактировать ее в строке формул:
Я могу использовать переменную в вычисляемом столбце, сославшись на имя:
Примечание: если в имени вашей переменной есть пробелы, вы должны указывать хэш и двойные кавычки следующим образом:
= [Sale] * #"Tax Rate"
Динамические переменные Power Query
Наконец, вы можете получить переменную из другого запроса, сделав ее динамической. По мере изменения данных в запросе переменная автоматически обновляется при обновлении на основе последних данных.
В приведенном ниже примере мои исходные данные содержат продажи по продуктам (TblProdSales):
Я хочу добавить столбец, который вычисляет процент от общего объема продаж, и, очевидно, это будет меняться с появлением новых данных за каждый месяц.
В редакторе запросов мне нужно продублировать QueryProdSales – щелкните правой кнопкой мыши название запроса Duplicate:
Затем выберите столбец Продажи > Вкладка Преобразование > Статистика > Сумма:
Это возвращает единственное значение, т.е. вашу переменную TotalSales. Переименуйте запрос соответствующим образом:
Затем снова в запросе QueryProdSales добавьте пользовательский столбец, который вычисляет процент от общего объема продаж:
Что приводит к:
Когда вы добавляете или заменяете данные в исходной таблице (Tblproductsales) и обновляете запрос, он динамически обновляется.