This is the 2nd article in a little series about advanced Power Query functions and the writing of custom functions itself, see here. This article is inspired by the following question on community.powerbi.com. I will not explain what the question on community.powerbi.com is about. If you are interested in the use case follow the previous link. Here I will just cover the syntax that allows to create an anonymous function to access an external table ( remote table for each row in the current query step.
The comments are more relevant to my future self then the code itself.
Table.AddColumn(#"Changed Type", "TheMostCurrentStartInterval"
//row is just a name, the current row will be passed to an anonymous function
, ( row ) =>
let
// storing the value of a column from the outer row inside a variable,
// basically this is not necessary as a column value from the outer row can be accessed directly (see a little below)
//theStartTime = row[StartTime],
mostCurrentIntervalStart =
Table.Sort(
Table.SelectRows(
#"Intervals"
// each is important as it allow to reference the inner row (the remote table), in this example
// the filter will applied to each row to filter the remote table
// referencing the variable from above
//, each [IntervalStart] <= theStartTime
// referencing the the value of the "outer" row directly
, each [IntervalStart] <= row[StartTime]
)
, {{"IntervalStart", Order.Descending}}
// the below syntax is referencing the column IntervalStart [] from the first row {},
// zero-based
)[IntervalStart]{0}
// the value of the step mostCurrentIntervalStart will be returned from the anonymous function
in mostCurrentIntervalStart
Kommentar schreiben