Оператор Let в Power BI и Power Query
Текст представляет собой адаптированный перевод статьи Chris Webb (Крис Вебб),
оригинал – Understanding Let Expressions In M For Power BI And Power Query.
Рассматриваются англоязычные версии ПО.
оригинал – Understanding Let Expressions In M For Power BI And Power Query.
Рассматриваются англоязычные версии ПО.
Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.
Оператор Let в Power BI и Power Query
Когда вы начинаете писать код загрузки данных в Power Query или Power BI, то первым делом открываете пункт Advanced Editor для запроса, уже созданного в пользовательском интерфейсе. Открыв редактор кода, мы увидим пугающий кусок кода (отсутствие цветовой кодировки делает его ещё более пугающим), который хотелось бы понять. Первый шаг на пути к пониманию – разобраться как работает оператор let.
Каждый запрос, создаваемый в Power BI Desktop или Power Query, это одно выражение, которое возвращает одну переменную/значение. Обычно, хотя и не всегда, это таблица, которую можно загрузить в модель данных. В качестве иллюстрации откроем Power BI Desktop (интерфейс во многом похож на Power Query), нажмём кнопку Edit Queries чтобы вызвать окошко button Query Editor и выберем New Source > Blank Query и создать новый запрос.
Каждый запрос, создаваемый в Power BI Desktop или Power Query, это одно выражение, которое возвращает одну переменную/значение. Обычно, хотя и не всегда, это таблица, которую можно загрузить в модель данных. В качестве иллюстрации откроем Power BI Desktop (интерфейс во многом похож на Power Query), нажмём кнопку Edit Queries чтобы вызвать окошко button Query Editor и выберем New Source > Blank Query и создать новый запрос.
Переходим на вкладку View, щелкаем Advanced Editor, чтобы вызвать диалоговое окно Advanced Editor:
На самом деле это не совсем пустой запрос, т.к. кое-какой код присутствует. Удалите всё из окна редактора и введите следующее выражение:
"Hello " & "World"
Нажмите кнопку Done, в результате запрос вернёт текст «Hello World»:
Обратите внимание на значок ABC слева от имени Query1. Он указывает, что запрос возвращает текстовое значение. Поздравляем, вы написали на языке М печально знаменитую программу «Hello World»!
Возможно вы гадаете, как страшный кусок кода реального запроса в редакторе может быть одним выражением. Но это действительно так. Именно для этого и нужен оператор let. Он позволяет разбить одно выражение на несколько частей. Откройте Advanced Editor и введите:
Возможно вы гадаете, как страшный кусок кода реального запроса в редакторе может быть одним выражением. Но это действительно так. Именно для этого и нужен оператор let. Он позволяет разбить одно выражение на несколько частей. Откройте Advanced Editor и введите:
let
step1 = 3,
step2 = 7,
step3 = step1 * step2
in
step3
Даже ничего не зная о языке М, нетрудно догадаться, что данный код возвращает число 21 (значок 123 рядом с именем запроса указывает на возвращаемый тип):
В языке М оператор let состоит из двух секций. После let идёт список переменных, каждая из которых имеет имя и соответствующее ей выражение. В нашем примере это три переменных: step1, step2 и step3. Переменные могут ссылаться на другие переменные, в данном случае step3 ссылается на две step1 и step2. Переменные могут хранить данные любого типа: числа, текст, даты, или даже сложных типов, такие, как записи, списки, таблицы. Все три наши переменные числового типа. Обычно Query Editor показывает переменные как шаги в панели Applied Steps на правой части экрана.
Значение, возвращаемое let, передаётся в секцию in. У нас in возвращает переменную step3, равную 21.
Важно понимать, что in может ссылаться на любую из списка переменных или ни на одну из них. Также важно понимать, что, хотя список переменных выглядит как код процедуры, он остаётся списком, где переменные могут идти в любом порядке. Пользовательский интерфейс будет всегда генерировать код, в котором каждая переменная/шаг опирается на значение, возвращаемое предыдущей переменной/шагом. Но, когда вы сами пишете код, переменные могут идти в любом порядке. Следующий код так же вернёт значение 21:
Важно понимать, что in может ссылаться на любую из списка переменных или ни на одну из них. Также важно понимать, что, хотя список переменных выглядит как код процедуры, он остаётся списком, где переменные могут идти в любом порядке. Пользовательский интерфейс будет всегда генерировать код, в котором каждая переменная/шаг опирается на значение, возвращаемое предыдущей переменной/шагом. Но, когда вы сами пишете код, переменные могут идти в любом порядке. Следующий код так же вернёт значение 21:
let
step3 = step1 * step2,
step2 = 7,
step1 = 3
in
step3
Секция in возвращает значение переменной step3, для вычисления которой требуются переменные step2 и step1. Порядок переменных «неправильный» (в Applied Steps не отражаются имена всех переменных). Что важно, так это цепочка зависимостей, которая может быть восстановлена из секции in.
В этом примере запрос вернёт 7:
В этом примере запрос вернёт 7:
let
step3 = step1 * step2,
step2 = 7,
step1 = 3
in
step2
В данном случае из всего выражения в let требуется вычислить только переменную step2. Аналогично, запрос
let
step3 = step1 * step2,
step2 = 7,
step1 = 3
in
"Hello" & " World"
… вернёт текстовое значение «Hello World" и не потребует вычисления переменных step1, step2 или step3.
Стоит отметить, что если имена переменных содержат пробел, то их следует заключать в двойные кавычки и ставить хэш-символ # перед ними. В следующем запросе все имена переменных содержат пробелы в именах, возвращает он, как нетрудно догадаться, значение 21:
Стоит отметить, что если имена переменных содержат пробел, то их следует заключать в двойные кавычки и ставить хэш-символ # перед ними. В следующем запросе все имена переменных содержат пробелы в именах, возвращает он, как нетрудно догадаться, значение 21:
let
#"this is step 1" = 3,
#"this is step 2" = 7,
#"this is step 3" = #"this is step 1" * #"this is step 2"
in
#"this is step 3"
Как всё это применить к запросам, сгенерированным пользовательским интерфейсом? Вот код запроса, который подключается к серверу SQL и получает отфильтрованные данные из таблицы DimDate базы данных Adventure Works DW:
let
Source = Sql.Database("localhost", "adventure works dw"),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_DimDate,
each ([DayNumberOfWeek] = 1))
in
#"Filtered Rows"Даже не разбираясь, что делает запрос, можно увидеть, что объявляются три переменные #"Filtered Rows", dbo_DimDate и Source, и возвращается значение переменной #"Filtered Rows". Также понятно, что для вычисления #"Filtered Rows", должна быть вычислена переменная dbo_DimDate, а для её вычисления нужно вычислить Source. Переменная Source подключается к базе данных Adventure Works DW на сервере SQL. Переменная dbo_DimDate получает данные из таблицы DimDate этой базы. А #"Filtered Rows" берёт таблицу, возвращаемую dbo_DimDate, и отфильтровывает строки, в которых значение столбца DayNumberOfWeek равно 1.
Это всё, что нужно знать об операторе let.