· 

Retrieve values from remote tables inside a query step (anonymous function / inline function)

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

Kommentare: 0