La evaluación perezosa y el plegado de consultas en Power BI / Power Query

¿Cómo funcionan las cosas en el mundo? Los humanos son seres curiosos.

Queremos saber cómo funcionan las cosas y quizás dominarlas para nuestros propósitos personales.

En este caso, tenemos curiosidad por saber de qué forma funciona el Modelo de Evaluación en Power BI y Power Query cuando estamos ejecutando consultas y obteniendo datos.

Puedes leer los documentos oficiales del equipo de Microsoft aquí, pero en este artículo del blog cubriré lo más básico para que puedas tener una visión general del funcionamiento de Power Query/Power BI y de esta manera puedas optimizar tus consultas para dominar mejor su potente motor.

¿Cómo se evalúan las consultas en Power BI / Power Query?

El proceso de evaluación no es visible para el usuario final. Power BI/Power Query hace un trabajo maravilloso al proveer lo que pareciera ser una experiencia fluida para el usuario final en donde éste puede realizar todas las transformaciones de datos que desee contra casi cualquier fuente de datos.

La misma interfaz de usuario. Las mismas funciones. El mismo formato de salida. Todo a través de una única herramienta.

Cuando haces clic en cualquiera de los botones dentro del Editor de Power Query, la herramienta automáticamente creará un trozo de código por ti. Este código se llama paso, y puedes incluso ver a tu derecha la línea del tiempo que incluye todos los pasos que has realizado.

Puedes ver esta pieza de código en el “Editor Avanzado” y también en la barra de fórmulas

(Ejemplo de los Pasos Aplicados al transformar una columna numérica utilizando una operación de tipo “redondeo”)

El código que ves ahí fue generado con tus clics y el lenguaje que utiliza se llama M.

Recomiendo que leas este artículo antes de continuar.

El modelo de Evaluación en la Práctica

Imagina esta situación, tienes una tabla dentro de una base de datos a la que te quieres conectar. La tabla tiene más de 30 millones de registros, pero únicamente quieres un sub grupo de ellos y únicamente ciertas columnas también. Utilizaré la base de datos AdventureWorks por si acaso quieres seguir los pasos mientras lees.

¿Cómo puedes lograr eso en Power Query sin descargar los 30 millones de filas?

Deberías empezar por crear una consulta siguiendo estos pasos:

Conectarte a la base de datos – Es una Base de Datos de SQL Server, entonces seleccionamos esa opción

Seleccionamos la tabla – el nombre de la tabla es SalesOrderHeader, y tiene 30 millones de filas con información de ventas.
Hacemos un Filtro en la tabla – hacemos un filtro en la columna OrderDate para obtener únicamente los valores del año en curso

Quitamos algunas columnas – únicamente queremos mantener un sub grupo de las columnas que están en la tabla original. Las que queremos mantener se muestran a continuación

Hasta este punto, únicamente hemos utilizado el ratón y nunca hemos tenido que utilizar el teclado excepto para ingresar las credenciales para entrar al servidor.

Después de utilizar la interfaz de usuario, aquí está el código final que Power Query creó para nosotros:

let
     Source = Sql.Database("azuredb.powerqueryworkshop.com", "adventureworks2012"),
     Sales_SalesOrderHeader = Source{[Schema="Sales",Item="SalesOrderHeader"]}[Data],
     #"Removed Other Columns" = Table.SelectColumns(Sales_SalesOrderHeader,{"SalesOrderID", "OrderDate", "AccountNumber", "CustomerID", "SalesPersonID", "TerritoryID", "TaxAmt", "Freight", "TotalDue"})
in
     #"Removed Other Columns"

Prestemos mucha atención al primer paso de esta consulta y veamos la columna que dice [Data]:

en ese paso, ves TODAS las tablas, vistas y funciones de esa base de datos específica y en la columna (Data) puedes ver que tiene los datos de la tabla específica, de la vista o de las funciones.

Hice clic en el espacio en blanco dentro de la celda resaltada para obtener una vista previa de ese valor en el panel de abajo. Esa es una vista previa de la Tabla dentro de la celda.

Un momento… ¿Entonces dices que en ese primer paso el Power Query carga TODOS los datos de TODOS los objetos dentro de esa base de datos?

¡Eso suena aterrador! Pero no… así no es como sucede. Echa un vistazo al segundo paso (Navegación):

¿Ves? Hemos navegado a una tabla específica dentro de esa base de datos.

Esto es confuso. ¿Por qué Power Query carga TODOS los datos y luego clasifica o “navega” a SOLO esa tabla en específico?

Bueno, tampoco es así como las cosas suceden en realidad.

Estás frente a la belleza del Modelo de Evaluación. Esas otras tablas que no seleccionamos no serán evaluadas. Únicamente las que navegamos serán evaluadas.

En el lenguaje de fórmulas de Power Query o lenguaje M, algunos valores se evalúan de forma perezosa, lo que significa que incluso ni siquiera sean evaluados en tu consulta y eso es exactamente lo que sucede con esta consulta específica.

Power Query no evalúa TODAS las tablas, vistas y funciones de esa base de datos. Evalúa únicamente la tabla SalesOrderHeader – el resto son evaluadas perezosamente.

Nota: Puedes leer este artículo completo en caso te interese aprender más sobre el paso “Navegar”.

¿Qué es la Evaluación Perezosa (Lazy Evaluation)?

En pocas palabras, una evaluación perezosa es cuando la evaluación de un valor específico es diferida hasta que sus resultados sean requeridos por otros cómputos.

Esto significa que algunos valores no serán evaluados en absoluto – puede que Power Query muestre indicadores en la ventana de Vista Previa, pero eso no significa que hayan sido evaluados o calculados.

Cada vez que veas una columna con valores de colores como Lista, Tabla o incluso Registros, debes tomar en cuenta que puede que esos valores hayan sido evaluados perezosamente.

(estos son usualmente referencias, punteros o indicadores)

Esa es la razón principal por la cual cuando intentas ver la Vista Previa de una de esas celdas, puede que tome mucho tiempo en evaluar esa Vista Previa – porque no estaba allí antes y necesita ser evaluada.

Si regresamos a nuestro ejemplo previo, esos valores de la Columna Data se muestran en esa vista previa específica como indicadores a un resultado posible, pero ese resultado no será evaluado hasta que le digas a Power Query que necesitas evaluarlo.

Esto no sucede únicamente con los valores de Lista, Tabla o Registros. Dondequiera que Power Query pueda crear un indicador, lo hará.

Correcto – incluso funciones de Fuentes de Datos pueden considerarse como evaluadas perezosamente de vez en cuando.

Déjame darte un ejemplo de una situación que un amigo de Europa tuvo hace algunos meses y cómo la Evaluación Perezosa jugó un papel en ella.

La Evaluación Perezosa en las funciones de Fuentes de Datos

Mi amigo tenía esta pieza de código:

try Folder.Files(«C:MyNonExistingfolder») otherwise Folder.Files(«C:users»)

esencia, lo que quería lograr era una condicional sencilla. Implementó un IFERROR (SI.ERROR) básico en donde, si la carpeta inicial no existía, caería de vuelta en una diferente.

La función Try es esencialmente otra forma de hacer un IFERROR (SI.ERROR). El primer argumento es el valor válido, y el segundo es el resultado alternativo.

Ahora, yo escribí el código así a propósito ya que la carpeta “C:\MyNonExistingfolder “ bueno… no existe. PERO ¿por qué obtenemos esa carpeta en lugar de la otra que realmente existe? (“C:\users”)

¿El problema? la evaluación perezosa o Lazy evaluation.

Si tratas de hacer únicamente:

try Folder.Files(«C:MyNonExistingfolder»)}

tendrás este resultado:

el cual básicamente dice que no hay error. Incluso cuando la Carpeta no existe. Esto sucede porque hay una evaluación perezosa y el folder no ha sido evaluado en absoluto.

¿Qué debemos hacer?¡Forzar la evaluación!

¿Cómo forzamos una evaluación?Usando cualquiera de las funciones Buffer

Para nuestro caso, utilizaremos la función Table.Buffer para forzar la evaluación de nuestro código

¡Y listo! Ahora obtenemos el resultado que buscábamos. Y así es como se fuerza la evaluación de un valor.

¿Qué es el Plegado de Consultas (Query Folding)?

Ahora vamos a una de las cosas más geniales sobre este Modelo de Evaluación: Query Folding o en español el Plegado de consultas.

En pocas palabras, el plegado de consultas es el proceso en el que el código M se traduce al idioma nativo de la consulta de la fuente de datos.

Esto sucede de forma automática. No tienes que hacer nada en absoluto. Power Query intentará hacer su mejor esfuerzo para optimizar todo lo que hayas hecho y luego herle el “plegado”.

Hay algunos artículos interesantes en la web que ya han cubierto este tema con anterioridad:

¿Qué hay de diferente en esta entrada de blog?Hacemos un acercamiento al tema puramente práctico.

El Plegado de consultas en práctica

Usemos la misma consulta que vimos anteriormente y elaboremosla un poco más.

La situación es esta, nuestra base de datos tiene MILLONES de registros, pero únicamente queremos un subgrupo de ellos y por ello hicimos el paso “Filas filtradas” y luego quitamos algunas columnas.

¿Cómo puede Power Query decirle a mi base de datos que recupere únicamente un subgrupo de datos? ¡El plegado de consultas es la respuesta!

Si fueras paso a paso, pensarás que Power Query hace lo siguiente:

Se conecta a la la base de datos
Hace una evaluación perezosa de algunas tablas hasta conectarse a una tabla específica (SalesOrderHeader)
Descarga una copia de esa tabla al disco duro local y luego procede a hacer transformaciones como filtrar y quitar filas de manera local.

Si piensas eso estás equivocado.

En lugar de eso lo que sucede es que Power Query toma todos tus pasos y los traduce a una Consulta Nativa única para tu base de datos, por lo que tu servidor/base de datos recibe únicamente la instrucción de enviarnos los datos de salida que buscamos SIN hacer ninguna transformación local.

¿CÓMO HACE POWER QUERY TODO ESO? Es casi mágico. Nadie fuera de Microsoft sabe realmente cómo funciona, pero no tienes que hacer nada para hacer que funcione. ESTO es el plegado de consultas en muy pocas palabras.

¿Cómo puedo saber si el plegado de consultas se está ejecutando?

Hay 2 cosas que deben ocurrir para que el plegado de consultas funcione:

Tu Fuente de Datos debe tener su propio Lenguaje de Consulta Nativo. (Ejemplos: bases de datos y OData)
Tus pasos deben ser compatibles con el plegado de datos.

El primero es mucho más fácil de determinar que el segundo. Si te conectas a un archivo Excel, CSV, TXT, JSON o cualquier otro tipo de archivo de forma directa, no podrás aprovechar las ventajas del plegado de consultas ya que no tienen su propio lenguaje de consulta.

Por otro lado, una Base de Datos en SQL, una MySQL, Oracle, Postre o cualquier otra base de datos sí tiene su propio lenguaje de consulta, Power Query sí podrá traducir tus pasos en lenguaje de consulta nativo para esa fuente de datos.

El segundo es normalmente todo un misterio. Microsoft ni siquiera nos dice qué es plegable y qué no. Principalmente esto es debido a que depende de un número de factores y variables, pero hay 2 métodos para identificar si el plegado de datos se está llevando a cabo:

Usar una herramienta de monitoreo para tus fuentes de datos – para que te diga el tipo de instrucciones que recibes de Power BI/ Power Query
A través de Power Query – hay un par de formas de ver si Power Query fue capaz de encontrar una Consulta Nativa específica para tu fuente

Me enfocaré en la última, ya que la primera depende de tus fuentes de datos. Por ejemplo, para SQL Server puedes usar el perfilador SQL Server, para OData incluso puedes usar una herramienta como Fiddler que sirve como un proxy para averiguar cuáles son las solicitudes por enviar.

Método 1: Clic derecho en el Paso

(Declaración SQL creada por Power Query y empujada a la fuente de datos)

Esta es la forma más fácil de hacer que funcione, puedes simplemente hacer clic derecho en uno de los pasos de tu consulta y si el botón Ver consulta nativa no está en gris, puedes estar seguro de que el plegado de datos está siendo aplicado.

¿Qué sucede si el botón está en gris? En ese punto no podemos descartar que el plegado de datos está sucediendo. A veces, Power Query puede ejecutar la consulta nativa sin siquiera mostrarla.

Método 2: Usar la función Value.Metadata

Esta es una forma más avanzada de averiguar si tu consulta tiene el plegado de consultas activo, pero aun así es bastante sencilla.

Lo único que debes hacer es ir a la barra de fórmulas y envolver el código de tu paso con la función Value.Metadata.

Lo que normalmente hago es hacer clic en el botón fx en la barra de fórmulas para crear un paso personalizado y luego envuelvo todo con Value.Metadata de esta forma: