Hace unos cuantos meses publiqué un artículo (ver) donde exponía como poder elegir con una validación de celdas en nuestra hoja de cálculo, el origen distinto de nuestra consulta de Power Query.
Aplicando el caso para un cliente, hace unos días, pensé en si sería posible replicar el comportamiento de la función de Excel: INDIRECTO, para transformar un texto en algo comprensible para Excel, i.e., en una referencia válida.
Y entre la biblioteca de lenguaje M encontré un término conocido .Evaluate. Quienes hayáis programado alguna vez en VBA para Excel reconocerán la función (o si lo haces en DAX).
La función M Expression.Evaluate es muy sencilla de entender...
Expression.Evaluate(document as text, optional environment as nullable record) as any
La cual nos devuelve el resultado de 'evaluar' la expresión dada en el primer argumento... con los identificadores disponibles.
Seguro que al grabar los pasos de tu consulta, y entrar en el editor avanzado, habrás visto como los nombres asignados a cada paso en ocasiones aparecen con símbolos 'raros': almohadilla (#), comillas simples ('), comillas dobles ("), corchetes ([]), etc...
Lo que ocurre cuando el nombre del paso contiene caracteres especiales (como el típico espacio en blanco).
Este aspecto será importante para asegurarnos del correcto funcionamiento en todo momento!!!.
Otro punto relevante de la función es el argumento de 'entorno' ('enviroment')... que para nosotros al movernos dentro del contexto de las consultas de Power Query, será '#shared' (echa un vistazo a este otro post)
Es decir, pedimos a la función que evalue el texto dado, dentro de todo lo existente en nuestro editor de consultas: funciones, expresiones, nombres de otras consultas, parámetros, etc...
Igualmente podemos acotar algo más la búsqueda empleando la expresión de 'entorno' como '#sections', que solo busca entre las consultas, tablas, etc... creadas en nuestro editor; i.e., deja fuera la equivalencia con funciones u otras expresiones de M.
Por otra parte, la función M Expression.Identifier:
Expression.Identifier(name as text) as text
será la encargada de transformar una sencilla cadena de texto a su representación exacta en lenguaje M.
Empecemos recordando el punto de partida de nuestra entrada anterior.
Tenemos algunas tablas en nuestro libro (en realidad en cualquier origen: OneDrive, Intranet, Local, etc..).
En una celda seleccionaremos, con una validación de datos, el nombre de la fuente u origen que deseamos alimente nuestra consulta.
Con la consulta editada siguiente:
por supuesto la consulta funcionaba (y funciona) perfectamente... pero nos limita el crecimiento de las fuentes u orígenes, quiero decir, si ahora quisiera incorporar una nueva tabla origen, deberíamos acceder y modificar el lenguaje M la consulta en cuestión.
Aquí es donde empieza a jugar Expression.Evaluate.
Crearemos una nueva consulta en blanco con el siguiente código M:
Conseguimos con:
Expression.Evaluate( Expression.Identifier(ndOrigen), #shared)
que se lea e interprete el parámetro 'ndOrigen', que recuerda viene de la celda validada en la hoja de cálculo, como una expresión en lenguaje M.
En realidad en mi ejemplo no sería necesario aplicar la función Expression.Identifier, ya que siempre trato de evitar los espacios en blanco y otros caracteres raros...
Pero si ahora incorporaramos una nueva tabla con nombre 'Tabla4', la cargamos al editor y transformamos como se hizo con las tres previas, y renombramos la consulta como 'Tabla 4' (ojo al espacio en blanco!!); y si no hubieras anidado la función .Identifier dentro de .Evaluate, la consulta nos fallaría:
Pero con la expresión completa, tal cual la definimos inicialmente, el resultado es perfecto!:
Y todo gracias a que Expression.Identifier devuelve el texto de nuestro parámetro/celda con los caracteres necesarios para que la consulta sepa a qué referirse. Evaluate hace el resto.
= Expression.Identifier (ndOrigen) devuelve #"Tabla 4"
Finalmente comentar algo que noté con el fichero del cliente, con un volumen alto de datos...
La velocidad de carga es superior, bastante!, a la velocidad del método previo, con condicionales: if.. then.. else.... Un punto MUY interesante.
Aplicando el caso para un cliente, hace unos días, pensé en si sería posible replicar el comportamiento de la función de Excel: INDIRECTO, para transformar un texto en algo comprensible para Excel, i.e., en una referencia válida.
Y entre la biblioteca de lenguaje M encontré un término conocido .Evaluate. Quienes hayáis programado alguna vez en VBA para Excel reconocerán la función (o si lo haces en DAX).
La función M Expression.Evaluate es muy sencilla de entender...
Expression.Evaluate(document as text, optional environment as nullable record) as any
La cual nos devuelve el resultado de 'evaluar' la expresión dada en el primer argumento... con los identificadores disponibles.
Seguro que al grabar los pasos de tu consulta, y entrar en el editor avanzado, habrás visto como los nombres asignados a cada paso en ocasiones aparecen con símbolos 'raros': almohadilla (#), comillas simples ('), comillas dobles ("), corchetes ([]), etc...
Lo que ocurre cuando el nombre del paso contiene caracteres especiales (como el típico espacio en blanco).
Este aspecto será importante para asegurarnos del correcto funcionamiento en todo momento!!!.
Otro punto relevante de la función es el argumento de 'entorno' ('enviroment')... que para nosotros al movernos dentro del contexto de las consultas de Power Query, será '#shared' (echa un vistazo a este otro post)
Es decir, pedimos a la función que evalue el texto dado, dentro de todo lo existente en nuestro editor de consultas: funciones, expresiones, nombres de otras consultas, parámetros, etc...
Igualmente podemos acotar algo más la búsqueda empleando la expresión de 'entorno' como '#sections', que solo busca entre las consultas, tablas, etc... creadas en nuestro editor; i.e., deja fuera la equivalencia con funciones u otras expresiones de M.
Por otra parte, la función M Expression.Identifier:
Expression.Identifier(name as text) as text
será la encargada de transformar una sencilla cadena de texto a su representación exacta en lenguaje M.
Empecemos recordando el punto de partida de nuestra entrada anterior.
Tenemos algunas tablas en nuestro libro (en realidad en cualquier origen: OneDrive, Intranet, Local, etc..).
En una celda seleccionaremos, con una validación de datos, el nombre de la fuente u origen que deseamos alimente nuestra consulta.
Con la consulta editada siguiente:
let
fuente = if ndOrigen="Tabla1" then Tabla1 else
if ndOrigen="Tabla2" then Tabla2 else
Tabla3,
#"Consultas combinadas" = Table.NestedJoin(fuente, {"Fecha"}, TblColor, {"Año"}, "TblColor", JoinKind.LeftOuter),
#"Se expandió TblColor" = Table.ExpandTableColumn(#"Consultas combinadas", "TblColor", {"Color"}, {"Color"})
in
#"Se expandió TblColor"
por supuesto la consulta funcionaba (y funciona) perfectamente... pero nos limita el crecimiento de las fuentes u orígenes, quiero decir, si ahora quisiera incorporar una nueva tabla origen, deberíamos acceder y modificar el lenguaje M la consulta en cuestión.
Aquí es donde empieza a jugar Expression.Evaluate.
Crearemos una nueva consulta en blanco con el siguiente código M:
let
// con #shared comprobamos la existencia de la expresión en todo el conjunto de expresiones de Power Query
// con #sections comprobamos la existencia de la expresión solo en el conjunto de consultas creadas en Power Query
fuente=Expression.Evaluate( Expression.Identifier(ndOrigen), #shared),
#"Consultas combinadas" = Table.NestedJoin(fuente, {"Fecha"}, TblColor, {"Año"}, "TblColor", JoinKind.LeftOuter),
#"Se expandió TblColor" = Table.ExpandTableColumn(#"Consultas combinadas", "TblColor", {"Color"}, {"Color"})
in
#"Se expandió TblColor"
Conseguimos con:
Expression.Evaluate( Expression.Identifier(ndOrigen), #shared)
que se lea e interprete el parámetro 'ndOrigen', que recuerda viene de la celda validada en la hoja de cálculo, como una expresión en lenguaje M.
En realidad en mi ejemplo no sería necesario aplicar la función Expression.Identifier, ya que siempre trato de evitar los espacios en blanco y otros caracteres raros...
Pero si ahora incorporaramos una nueva tabla con nombre 'Tabla4', la cargamos al editor y transformamos como se hizo con las tres previas, y renombramos la consulta como 'Tabla 4' (ojo al espacio en blanco!!); y si no hubieras anidado la función .Identifier dentro de .Evaluate, la consulta nos fallaría:
Pero con la expresión completa, tal cual la definimos inicialmente, el resultado es perfecto!:
Y todo gracias a que Expression.Identifier devuelve el texto de nuestro parámetro/celda con los caracteres necesarios para que la consulta sepa a qué referirse. Evaluate hace el resto.
= Expression.Identifier (ndOrigen) devuelve #"Tabla 4"
Finalmente comentar algo que noté con el fichero del cliente, con un volumen alto de datos...
La velocidad de carga es superior, bastante!, a la velocidad del método previo, con condicionales: if.. then.. else.... Un punto MUY interesante.
