Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Вычисления в PowerQuery.xlsx (131,7 КиБ, 1 905 скачиваний)
PowerQuery весьма мощный инструмент обработки данных внутри Excel, но если присмотреться – не видно даже намека на возможность использовать формулы. Нет хоть какого-то списка или значка по подобию Excel. Все потому, что PowerQuery не использует вычисления как таковые, если сравнивать с формулами в самом Excel. Но здесь есть другая возможность – добавлять целые вычисляемые столбцы. Для добавления такого столбца необходимо внутри редактора PowerQuery перейти на вкладку Добавить столбец -Пользовательский столбец. В первом окне(Имя нового столбца) задаем имя столбца, а во втором(Пользовательская формула столбца) – непосредственно формулу:
PowerQuery создаст новый столбец с заданным именем, в котором в каждой строке будут значения, вычисленные на основании заданной формулы. При этом все вычисления создаются исключительно линейно "внутри каждой строки" - сослаться на весь столбец или предыдущую строку возможности напрямую нет(как это делается в Excel).
Все доступные для использования в формуле столбцы перечислены в правом окне(Доступные столбцы). Для добавления в формулу какого-либо из столбцов можно просто щелкнуть по нему дважды левой кнопкой мыши или выделить нужный столбец и нажать кнопку Вставить.
На рисунке выше приведен пример простого объединения трех столбцов: Фамилия, Имя и Отчество при помощи формулы:
Она очень похожа на формулу Excel внутри «умной» таблицы - так же используются квадратные скобки с именами столбцов и нет никаких адресов ячеек. В результате этой формулы будет создан новый столбец ФИО, в котором будут последовательно объединены столбцы Фамилия, Имя и Отчество с пробелом в качестве разделителя.
Но это самый простой пример. На самом деле PowerQuery имеет большое количество встроенных функций, которые постоянно используются при любом нашем действии внутри редактора. Просто нам это не показывается напрямую. Но все эти функции можно посмотреть.
Во-первых они доступны в открытом доступе на сайте Microsoft: PowerQuery - функция языка M.
Во-вторых есть способ посмотреть список функций прямо из PowerQuery:
Вкладка Главная –Создать источник -Другие источники -Пустой запрос. В новом окне в строке формул записываем: =#shared и жмем Enter. Будет создан новый запрос, в котором будет выведет список всех доступных функций:
Теперь можно левой кнопкой мыши щелкнуть любую функцию из списка и появится окно с аргументами функции и её описанием.
По сути это практически тот же диспетчер функций в Excel, только разделитель аргументов здесь всегда запятая и самое главное – функции PowerQuery чувствительны к регистру. Например, если функцию
Так же как и в случае с функциями Excel, функции PowerQuery делятся на категории(Text – текстовые, Date – дата, DateTime – дата и время и т.д.). И в отличии от функций в Excel для функций PowerQuery обязательно указывать из какой категории функция используется:
Разберем пару коротких примеров формул с описанием того, что они делают:
•
•
•
•
Помимо простой вставки функции можно использовать и целые синтаксические конструкции. Правда для этого потребуется чуть больше знаний и очень желателен опыт работы с какими-либо языками программирования, т.к. именно таковым и является используемый в PowerQuery язык M. Возьмем простой пример: необходимо определить, встречается ли в тексте столбца
Довольно сложно выглядит, но более-менее понятно. В PowerQuery для выполнения той же задачи придется использовать всего одну функцию, но вдобавок к ней целую условную конструкцию:
if Text.Contains([Имя],"а") then [ФИО] else "" |
что дословно так и читается: если(
И здесь опять надо помнить про регистр:
Но помимо работы с текстом в PowerQuery часто будет необходимо вычислять и другие показатели. Например, некоторые отклонения факта от плана. На примере простой таблицы и формул в ней:
В столбце
Её достаточно легко воспроизвести в PowerQuery:
А вот в столбце
Помимо «линейных» вычислений внутри строки здесь применяется функция
То обязательно получим ошибку, т.к. обращение
В итоге получится такая формула:
Конечно, функций в PowerQuery и конкретно в языке M довольно много и хочется рассказать про все 😊 Но какие-то из них слишком хитрые и требуют особого внимания и отдельной статьи на каждую функцию. Целью же данной статьи было дать некую отправную точку для изучения этой возможности PowerQuery. Надеюсь, после прочтения статьи и просмотра видеоурока понимания работы с языком М будет чуть больше и вы сможете самостоятельно создавать свои вычисления и подбирать для них нужные функции.
Вычисления в PowerQuery.xlsx (131,7 КиБ, 1 905 скачиваний)
Так же см.:
Объединение запросов при помощи PowerQuery
Собрать данные из файлов защищенных паролем PowerQuery
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery