Cómo crear datos de gráficos con Power Query

Índice
  1. Estructura de datos del gráfico (TLC)
  2. Gráficos que utilizan tablas como fuente
  3. Datos de ejemplo
  4. Transformaciones de Power Query
  5. Transformaciones de Power Query para el diseño TLC+
    1. Pasos de transformación
    2. función personalizada fxRepeatValueToNull
  6. Actualización para nuevos datos
  7. Conclusión

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.

Diseño del gráfico 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.

Diseño de gráfico TLC+

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):

Datos 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.

  1. Asegúrese de que la columna Fecha tenga el tipo de datos de fecha.
  2. 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
  3. Inserte el nombre del mes seleccionando la columna Fecha y luego haciendo clic en Agregar columna Fecha Mes Nombre del mes
  4. 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
  5. 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
  6. 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
  7. Para asegurarse de que los datos estén en el orden correcto, seleccione la columna Fecha y haga clic en Ordenar por inicio (AZ)
  8. Finalmente, elimine la columna Fecha seleccionándola y presionando la tecla Eliminar

La consulta de energía ahora tiene el siguiente aspecto:

Diseño de datos de Power Query

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.

Gráfico que utiliza Power Query como fuente

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:

  1. Asegúrese de que la columna Fecha tenga el tipo de datos de fecha.
  2. 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
  3. Inserte el nombre del mes seleccionando la columna Fecha y luego haciendo clic en Agregar columna Fecha Mes Nombre del mes
  4. 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
  5. Inserte la columna del trimestre seleccionando la columna Fecha y luego haciendo clic en Agregar columna Fecha Trimestre Trimestre del año
  6. Prefije la letra Q al trimestre seleccionando la columna Cuarto y luego haciendo clic en Transformar valor de prefijo: Q Aceptar
  7. Agregue una columna de año seleccionando la columna Fecha y luego haciendo clic en Agregar columna Fecha Año Año
  8. 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
  9. 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
  10. Para asegurarse de que los datos estén en el orden correcto, seleccione la columna Fecha y haga clic en Ordenar por inicio (AZ)
  11. Finalmente, elimine la columna Fecha seleccionándola y presionando la tecla Eliminar

En Power Query, la ventana de vista previa muestra lo siguiente:

Diseño de datos de Power Query para TLC+

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.

Eliminar valores repetidos en los datos del gráfico de Power Query

Eso es exactamente lo que queremos.

Ahora, carguemos esto en Excel y creemos el gráfico.

Gráfico con etiquetas anidadas

¡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 .

Fuente del gráfico nuevos datos

¡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

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Este sitio web utiliza cookies para mejorar la experiencia del usuario y garantizar un funcionamiento eficiente. Al utilizar nuestro sitio web, aceptas todas las cookies de acuerdo con nuestra política de cookies. Leer mas...