Cómo crear datos de gráficos con Power Query
Recientemente, en nuestra academia de formación , la leyenda de los gráficos Jon Peltier demostró la mejor estructura de datos necesaria para trabajar con gráficos en Excel. Después de esto, uno de los miembros de nuestra academia hizo esta pregunta:
Estoy intentando utilizar lo que Jon estaba enseñando en la clase magistral de gráficos para leyendas apiladas. ¿Hay alguna forma de sacar ese diseño de la consulta de energía o del pivote de energía o del modelo de datos o ????
En esta publicación, quiero responder esa pregunta y profundizar en cómo crear datos de gráficos con Power Query.
Tabla de contenido
- Estructura de datos del gráfico (TLC)
- Gráficos que utilizan tablas como fuente
- Datos de ejemplo
- Transformaciones de Power Query
- Transformaciones de Power Query para el diseño TLC+
- Pasos de transformación
- función personalizada fxRepeatValueToNull
- Actualización para nuevos datos
- Conclusión
Descargue el archivo de ejemplo: haga clic en el botón a continuación para unirse al programa Insiders y obtener acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0181 Datos del gráfico de Power Query.zip
Tener acceso
Estructura de datos del gráfico (TLC)
Los gráficos en Excel funcionan mejor cuando los datos tienen un diseño específico. Jon Peltier llama a esto el método TLC (método de la celda superior izquierda).
La siguiente imagen muestra el método TLC.
- Los nombres de las series están en la primera fila.
- Las etiquetas de categoría están en la primera columna.
- Los valores se incluyen en columnas.
Seleccione todas las celdas, incluida la celda superior izquierda, luego cree el gráfico.
Podemos llevar las etiquetas de categorías más allá creando etiquetas de varios niveles. Jon llama a esto el método TLC+.
Estos también tienen una estructura específica.
En la captura de pantalla anterior, ¿notaste cómo se agrupan los meses en trimestres y años? Bastante bonito, ¿verdad?
Para que Excel cree esto, los datos deben tener el formato exacto que se muestra en la imagen.
Introducir nuestros datos en este diseño ahora requiere un poco más de esfuerzo.
Gráficos que utilizan tablas como fuente
Para esta publicación, utilizamos tres características especiales sobre tablas, gráficos y Power Query.
- Las tablas se expanden/retraen automáticamente cuando se agregan o eliminan datos.
- Los gráficos que utilizan tablas como fuente también se expanden/retraen para nuevos puntos de datos.
- Power Query puede cargar datos en una tabla.
Esto significa que las tablas generadas por Power Query pueden ser una fuente dinámica para un rango de datos de gráfico.
Al reunir todas estas funciones, podemos actualizar los gráficos simplemente haciendo clic en Actualizar datos todos (lo cual es una bendición de la automatización).
NOTA: Normalmente, utilizamos Power Query para crear un diseño de datos; sin embargo, en esta circunstancia, creamos el diseño de información/presentación necesario para los datos del gráfico.
Creamos el diseño de datos de forma normal y luego utilizamos la transformación dinámica de Power Query para convertirlo en un diseño de datos de gráfico.
Datos de ejemplo
Para esta publicación, utilizamos los siguientes datos (puede encontrarlos en la descarga del archivo de ejemplo):
La tabla se llama ChartData y contiene datos de julio de 2023 a marzo de 2024. Agregamos el segundo trimestre de 2024 más adelante en esta publicación.
Transformaciones de Power Query
Necesitamos colocar los datos en el diseño TLC correcto para el gráfico.
Una vez que los datos de ejemplo se cargan en Power Query, podemos realizar las siguientes transformaciones.
- Asegúrese de que la columna Fecha tenga el tipo de datos de fecha.
- Cambie la columna Fecha a la fecha de fin de mes seleccionando la columna Fecha y luego haciendo clic en Transformar fecha Mes Fin de mes
- Inserte el nombre del mes seleccionando la columna Fecha y luego haciendo clic en Agregar columna Fecha Mes Nombre del mes
- Cambie el nombre del mes a 3 caracteres seleccionando la columna Nombre del mes y luego haciendo clic en Transformar Extraer Primeros caracteres Recuento: 3 Aceptar
- A continuación, debemos resumir los valores al nivel correcto de granularidad.
- Seleccione las columnas Fecha , Nombre del mes y Región .
- Haga clic en Transformar Agrupar por y luego, en el cuadro de diálogo Agrupar por , ingrese lo siguiente:
- Nuevo nombre de columna: Valor total
- Operación: Suma
- Columna: Valor
- Haga clic en Aceptar
- Necesitamos girar los datos para colocar las regiones en las columnas.
- Seleccione la columna Región y haga clic en Transformar pivote
- En el cuadro de diálogo Columna dinámica , aplique lo siguiente:
- Columna de valores: valor total
- Haga clic en Aceptar
- Para asegurarse de que los datos estén en el orden correcto, seleccione la columna Fecha y haga clic en Ordenar por inicio (AZ)
- Finalmente, elimine la columna Fecha seleccionándola y presionando la tecla Eliminar
La consulta de energía ahora tiene el siguiente aspecto:
ADVERTENCIA:
Para que esta transformación sea completamente dinámica, es esencial que los nombres de las Series (por ejemplo, Norte, Sur, Central) no aparezcan en el código M. Por lo tanto, puede que sea necesario tener un poco de cuidado para seguir los pasos en el orden correcto.
Cargue la consulta en una tabla de Excel. Luego, seleccione el conjunto de datos completo y cree un gráfico.
Bien, eso fue bastante simple para un gráfico básico.
Transformaciones de Power Query para el diseño TLC+
El diseño de TLC+ es un poco más complejo, para lo cual usaremos una función personalizada (más sobre esto más adelante).
Pasos de transformación
Los pasos de transformación son similares a los anteriores, pero hay algunos pasos adicionales:
- Asegúrese de que la columna Fecha tenga el tipo de datos de fecha.
- Cambie la columna Fecha a la fecha de fin de mes seleccionando la columna Fecha y luego haciendo clic en Transformar fecha Mes Fin de mes
- Inserte el nombre del mes seleccionando la columna Fecha y luego haciendo clic en Agregar columna Fecha Mes Nombre del mes
- Cambie el nombre del mes a 3 caracteres seleccionando la columna Nombre del mes y luego haciendo clic en Transformar Extraer Primeros caracteres Recuento: 3 Aceptar
- Inserte la columna del trimestre seleccionando la columna Fecha y luego haciendo clic en Agregar columna Fecha Trimestre Trimestre del año
- Prefije la letra Q al trimestre seleccionando la columna Cuarto y luego haciendo clic en Transformar valor de prefijo: Q Aceptar
- Agregue una columna de año seleccionando la columna Fecha y luego haciendo clic en Agregar columna Fecha Año Año
- A continuación, debemos resumir los valores al nivel correcto de granularidad.
- Seleccione las columnas Fecha , Año , Trimestre , Nombre del mes y Región .
- Haga clic en Transformar Agrupar por y luego, en el cuadro de diálogo Agrupar por , ingrese lo siguiente:
- Nuevo nombre de columna: Valor total
- Operación: Suma
- Columna: Valor
- Haga clic en Aceptar
- Necesitamos girar los datos para colocar las regiones en las columnas.
- Seleccione la columna Región y haga clic en Transformar pivote
- En el cuadro de diálogo Columna dinámica , aplique lo siguiente:
- Columna de valores: valor total
- Haga clic en Aceptar
- Para asegurarse de que los datos estén en el orden correcto, seleccione la columna Fecha y haga clic en Ordenar por inicio (AZ)
- Finalmente, elimine la columna Fecha seleccionándola y presionando la tecla Eliminar
En Power Query, la ventana de vista previa muestra lo siguiente:
Desafortunadamente, las columnas Año y Trimestre contienen valores repetidos, por lo que este no tiene el diseño correcto para TLC+. Necesitamos encontrar una manera de reemplazar los valores repetidos con nulos.
Power Query tiene una transformación para completar valores nulos. Pero no hay ninguna transformación que deba vaciarse para crear valores nulos. Entonces, usaremos una función personalizada.
función personalizada fxRepeatValueToNull
En Power Query, copie el código siguiente en una consulta en blanco y llámela fxRepeatValueToNull .
(Table as table, ColumnNamesList as list) as table =/*DOCUMENTATION:----------------------------------------------------------------------------------------------------PURPOSE:Changes repeat values in a column to nullSYNTAX: fxRepeatValueToNull ( Table, ColumnNamesList)- Table (table) - Table or step to perform the transformation on- ColumnNamesList (list of text) - List of column names to perform the transformation onNOTES:- (None)AUTHOR: Mark Proctor / Excel Off The GridDATE: 2023-10-02VERSION: 1.0*/let fxRepeatRowToNullColumn = (Table, ColumnName) = let //Get list of values TableColumn = Table.Column(Table,ColumnName), //Get Data type for the column getDataType = Value.Type(TableColumn{0}), //Create a list with repeat values converted to null listWithRepeatsNull = List.Accumulate( {1..List.Count(TableColumn) - 1}, {TableColumn{0}}, (state, current) = List.Combine({ state, if Table.Column(Table,ColumnName){current} = TableColumn{current -1} then {null} else {TableColumn{current}} } )), //Remove original column from Table RemoveOriginalColumn = Table.RemoveColumns(Table,{ColumnName}), //Convert Table to columns TableToColumns = Table.ToColumns(RemoveOriginalColumn) {listWithRepeatsNull}, //Rejoin columns with new column added JoinColumns = Table.FromColumns( TableToColumns, Table.ColumnNames(RemoveOriginalColumn) {ColumnName}), //Change column order to original order ReorderColumnsToOriginal = Table.ReorderColumns(JoinColumns,Table.ColumnNames(Table)), //Reapply data type to the replacement column ApplyDataType = Table.TransformColumnTypes(ReorderColumnsToOriginal,{{ColumnName, getDataType}}) in ApplyDataType, //Apply sub function to all columns in the list MultiColumnApply = List.Accumulate( ColumnNamesList, Table, (state, current) = fxRepeatRowToNullColumn(state,current) )in MultiColumnApply
Bien, ahora es el momento de usar la función personalizada.
Regrese a la consulta con los datos del gráfico TLC+. Haga clic en el ícono fx al lado de la barra de fórmulas e ingrese lo siguiente:
= fxRepeatValueToNull(#"Removed Columns",{"Year","Quarter"})
- #”Columnas eliminadas”: el nombre del paso anterior
- {“Año”,”Trimestre”} es la lista de nombres de columnas para las que queremos convertir valores repetidos a nulos
Los datos en Power Query ahora tienen este aspecto.
Eso es exactamente lo que queremos.
Ahora, carguemos esto en Excel y creemos el gráfico.
¡Asombroso! El gráfico ahora tiene etiquetas de ejes de varios niveles.
Actualización para nuevos datos
Entonces, ¿qué pasa si obtenemos nuevos datos?
Si está trabajando con el ejemplo, ya hay datos adicionales disponibles. Simplemente agréguelo a la Tabla en la pestaña Datos. Los datos incluyen nuevos puntos de datos para el segundo trimestre de 2024 y también una nueva región, Este.
Haga clic en Actualizar datos todos .
¡Ta-dah! El gráfico se expande automáticamente para los nuevos puntos de datos y la nueva serie.
Conclusión
Usando una tabla generada por Power Query, podemos crear el diseño perfecto para trabajar con gráficos.
Al agregar algo de magia de Power Query (en forma de la función personalizada fxRepeatValueToNull), podemos incluso generar el diseño TLC+ para etiquetas de categorías anidadas.
Lo mejor de todo es que para actualizar los gráficos, solo necesitamos hacer clic en Actualizar datos todos. ¡¡ASOMBROSO!!
Artículos Relacionados:
- Cómo agregar la columna de mes, trimestre o año fiscal en Power Query
- Cómo crear leyendas de gráficos dinámicos en Excel
- Cree títulos de gráficos dinámicos con formato personalizado
Deja una respuesta