Código M en columnas personalizadas de Power Query | BI de energía

En el blog de hoy, presentaré la solución al LuckyTemplates , que incluye un marco simple y generalizable para incorporar código M en una columna personalizada de Power Query. Puede ver el video completo de este tutorial en la parte inferior de este blog.

Ser capaz de crear columnas en Power Query usando código M aumenta su capacidad para resolver problemas más complejos.

Antes de entrar en la solución, revisemos la naturaleza del problema. He dado un gran conjunto de datos de Kaggle de 93 años de datos de Oscar. Queríamos seleccionar a la mejor actriz, a las ganadoras y, en particular, a aquellas mujeres que habían ganado más de un premio a la mejor actriz y luego visualizar usando la línea de tiempo de Queryon el año en el que ganaron su segundo premio.

Tenía varios objetivos al armar este problema. ¿En qué queremos que se centre el problema y cuáles queremos que sean los objetivos principales?

Y en este, había tres de ellos.

El primero fue aprender o desarrollar alguna práctica con el constructo Agrupar por y Todas las filas . Es algo en lo que realmente me he centrado porque resuelve una amplia gama de problemas de manera bastante simple y, a menudo, elimina la necesidad de un DAX muy complejo.

El segundo objetivo es particularmente para las personas que no estaban acostumbradas a trabajar en M. Quería crear un problema que desarrollara cierta comodidad al escribir código M básico . Quería forzar ese modo de pensar sobre cómo es desarrollar código M básico y el proceso para hacerlo.

Según un capítulo del libro de Gil Raviv sobre Power Query, alrededor del 40% de los problemas en Power Query se pueden resolver simplemente a través de la interfaz de usuario. Pero eso, con la adición de un código M básico y la capacidad de crear columnas personalizadas, en realidad no es tan difícil. Puede aumentar ese porcentaje en más del 80%. Puede duplicar sus capacidades de resolución de problemas con algunas habilidades fundamentales y simples.

Lo tercero que quiero hacer es presentar el visual personalizado de Queryon Timeline a aquellos que no están familiarizados con él. Es uno que realmente he llegado a amar. Lo he usado en varios casos diferentes y creo que proporciona una forma realmente única de visualizar datos.

Tabla de contenido

• Enfoque de solución al problema de la semana #9
• Uso de Agrupar por en Power Query
• Incorporación del código Table.AddIndexColumn M
• Visualización mediante Queryon Timeline Custom Visual
• Conclusión

Enfoque de solución al problema de la semana #9

Al pensar en este problema, quería hacer un experimento mental. ¿Qué pasaría si en lugar de un archivo CSV, solo le diera 93 hojas de papel (una con la ganadora de la Mejor Actriz de cada año), cuál sería la solución?

Su solución probablemente se parecería a lo que tengo a continuación.

Creo que todo el mundo tendría una solución relativamente similar a eso. Una vez que tenga esa solución conceptual, solo es cuestión de descubrir cómo hacer que Power Query ejecute cada uno de esos pasos.

Entonces, tomaré esto como un esquema general y luego explicaré cómo operacionalizan esta solución conceptual en algo en Power Query. El enfoque que vamos a utilizar es lo que Melissa, nuestra gurú residente de Power Query, denomina aprender mediante Google.

Ahora, si volvemos a nuestro modelo conceptual, los dos primeros pasos incluyen clasificar las papeletas en grupos por actriz y descartar cualquier grupo que tenga una sola papeleta. Entonces, queremos hacer un conteo. Si no sabemos cómo hacerlo, podemos ir a Google e intentar buscar un término. En este ejemplo, probé "grupo en consulta de energía". Con eso, podemos encontrar todo lo que necesitamos saber sobre cómo hacer esta agrupación.

Uso de Agrupar por en Power Query

Luego podemos acceder a Power Query y comenzar a poner en funcionamiento nuestra solución. Lo que hice es que en la tabla de este Oscar, revisé y saqué el archivo CSV y filtré hasta los ganadores de la Mejor Actriz para cada año. Entonces, solo tenemos las 93 filas de solo Nombre y el Año de la película.

Si vamos a Power Query y vamos a esa tabla, lo primero que queremos hacer es este Agrupar por .

Siempre me gustó ir a Avanzado solo porque proporciona una interfaz ligeramente mejor, incluso si lo que estamos haciendo es bastante sencillo.

Queremos agrupar por Nombre , luego tengamos una columna, AllData , y esta será nuestra Todas las filas. En lugar de devolver solo el agregado, devolverá la agregación, pero con todas las filas que queremos que permanezcan intactas.

Tendremos otra columna llamada Count y la queremos cuando vayamos a Count Rows . Queremos encontrar el número de papelitos en cada pila. Luego, hacemos clic en Aceptar.

Luego podemos hacer clic en este espacio en blanco en la tabla y obtenemos solo un recibo.

Pero luego, para los que tienen varios recuentos, verá que obtenemos todos los registros.

A continuación, queremos filtrar los unos (1). Entonces, básicamente estamos tirando a la basura todas esas pilas que solo tienen un nombre. Seleccione 1 y haga clic en Aceptar.

Y ahora nos encontramos con los 14 ganadores de premios múltiples. Y ahora nos encontramos con los 14 ganadores de premios múltiples. Si tomamos y expandimos eso, podemos quitar Name porque ya tenemos Name aquí y solo mantenemos year_film .

Ahora estamos en 30 filas porque algunas de ellas van a tener múltiples ganancias. E incluso podemos deshacernos de la columna Contar porque ya eliminamos los 1 con solo uno.

Ahora vamos a pasar a la siguiente fase de nuestro modelo conceptual, que consiste en ordenar los recibos en cada grupo por fecha ascendente y luego elegir el segundo día que ocurre dentro de cada grupo.

Ordenar es fácil. Podemos volver a Power Query y hacer una ordenación ascendente por actriz y luego por fecha.

Y te mostrará un uno y un dos.

Así que estamos bien ordenados, y ahora queremos tener un índice o una clasificación que tenga para cada uno, y luego poder tomar el segundo en cada pila.

Incorporación del código Table.AddIndexColumn M

Y ahora volvemos a nuestro enfoque de "aprendizaje en Google", en el que solo podemos buscar rango o índice, pero también podemos ir a la Guía de referencia de Microsoft . Creé una herramienta externa que uso todo el tiempo que simplemente lo lleva de LuckyTemplates a la Guía de referencia de Microsoft.

En la Guía de referencia de Microsoft, todo está agrupado por función y tiene un cuadro de filtro donde puedes buscar lo que necesites. En este caso, escribimos Rango o Índice en el cuadro de filtro. No nos da nada para Rank, pero para Index tenemos esta Table.AddIndexColumn , que es relevante y aplicable a lo que estamos buscando. También proporciona algunos ejemplos de cómo usar esta función.

Vayamos a Power Query y reagrupemos nuestra tabla por Actriz.

Con eso, obtenemos el grupo de 14 ganadores por Tabla. Y si entramos en cualquiera de estas tablas, digamos, Frances McDormand, podemos ver que la tabla está ordenada por fecha.

Y luego, aquí está el gran paso, que es agregar una columna personalizada de Power Query e ingresar nuestro código M. Tendremos Table.AddIndexColumn , luego agregaremos el campo AllData . Llamaremos a nuestra nueva columna (como texto) aquí como Índice , y comenzaremos nuestro Índice en uno (1) y lo incrementaremos en uno (1).

Y obtenemos este índice perfecto aquí.

Ahora, podemos eliminar nuestra columna AllData aquí ya que todo se recoge en Custom . A continuación, podemos simplemente expandir la columna Personalizada, quitar el Nombre, porque ya tenemos el Nombre aquí también. Con eso, lo que tenemos es ese índice numerado para cada uno.

Por último, podemos hacer lo que hicimos en Count, que es filtrar todos los que están en la segunda victoria.

Y ahí vamos. Ahí está nuestra solución.

Visualización mediante Queryon Timeline Custom Visual

Hay un par de otras cosas que queremos hacer aquí, que se basan en la implementación de Queryon Visual . Vamos a hacer una combinación y simplemente agregar la URL de la imagen.

Así que fusionaremos las consultas, abriremos la tabla Actrices y nos fusionaremos en Name . Obtenemos coincidencias de 14 a 14 filas, lo cual es bueno.

Y luego, en la columna Actrices, nos expandiremos y nos expandiremos a la URL.

Y ahora tenemos esta columna con las URL.

Hay otra cosa que viene con el conocimiento de la línea de tiempo de Queryon. Queryon funciona mejor cuando se trata de fechas completas en lugar de años . Entonces, esto es algo que solo viene con la experimentación, pero podemos tomar la columna Año y crear una Columna a partir de ejemplos . Lo que queremos hacer aquí es convertir los años en fechas.

Eso va a mejorar el formato. Eso es algo que no esperaría que supieras desde el principio, pero viene con la práctica con Queryon. Hacemos clic en Aceptar, y eso entra como un valor de fecha. Podemos eliminar la tabla de índice porque ya no la necesitamos, y luego podemos cambiar el nombre de la columna year_film a Year .

Pasemos ahora al aspecto de visualización de esto y quiero resaltar la entrada de uno de los miembros de LuckyTemplates, José, quien realmente hizo un gran trabajo visualizando.

Volviendo a Power Query para la visualización, vale la pena mencionar que Microsoft implementó recientemente algunos cambios en la forma en que certifican las imágenes personalizadas . Como parte de esa certificación, restringieron el uso de imágenes URL externas.

Si realmente utiliza la versión de Queryon Timeline que se encuentra en el origen de la aplicación (versión 1.05), no podrá extraer estas imágenes de URL. Entonces, ahora tiene la opción de usar este 1.04, manteniendo las imágenes URL, donde tienen en su sitio una forma de incrustar imágenes binarias 64 directamente en el archivo PBIX.

Ahora mencioné cambiar ese año en un campo de fecha, por lo que debemos conectarlo a nuestro modelo de datos.

Cambiémoslo de uno a uno a uno a muchos , y una sola dirección en el filtro Cruz, y luego activemos esta relación .

A continuación, para el campo URL de la imagen, debemos asegurarnos de que en la categoría Datos, no se muestre como Sin categoría, sino como URL de la imagen .

Ahora que tenemos todo bien preparado, simplemente arrastramos y soltamos los campos Nombre , Fecha y URL . El resto se vuelve realmente fácil de limpiar en las opciones de formato.

Tabla de Power Query: Cómo fusionar columnas dinámicamente
Cómo fusionar consultas en LuckyTemplates
Extraer valores de registros y listas en Power Query

Conclusión

Con suerte, te entusiasmé al agregar el código M a tu repertorio. Le mostré cómo configurar un modelo conceptual e implementar el enfoque Learn by Google. Lo guié a través de los pasos para incorporar el código M en una columna personalizada y visualizar los resultados usando Queryon Timeline.

Espero que lo hayas encontrado útil. Mire el video completo a continuación para obtener más detalles sobre el tutorial y consulte los enlaces a continuación para obtener más contenido relacionado.

¡Mis mejores deseos!