Como aplanar ("despivotear") una tabla de datos con dos niveles

Power Query es, sin lugar a dudas, la más útil de las herramientas que Microsoft agregó a Excel desde las tablas dinámicas. Mi primer post sobre Power Query mostró como  aplanar ("despivotear") tablas de datos con más facilidad que la técnica que usabamos antes de la aparición de esta herramienta.

La necesidad de "aplanar" o "despivotear" una tabla de datos surge cuando queremos organizar los datos de manera que tal que podamos explotar todas las posibilidades de las tablas dinámicas.

Una tabla de datos "clásica" tiene un solo nivel, es decir, una única línea de encabezamientos y una única columna de etiquetas, como el ejemplo que mostré en la nota mencionada

Pero también existen tablas con más de un "nivel", como ésta:

donde tenemos un nivel para el año y un segundo para los meses. Como si esto no fuera suficiente, tenemos una columna para las zonas y una segunda para las sucursales. Es decir, los datos que queremos "despivotear" están encapsulados entre dos filas y dos columnas de etiquetas. Para empeorar la situación tenemos tambien celdas combinadas.

Para poder usar todo el potencial de las tablas dinámicas necesitamos que los datos estén organizados de esta manera

Veamos como Power Query nos permite realizar la tarea con facilidad. El primer paso es cargar la tabla en la ventana del Power Query, para lo cual selecionamos alguna de las celdas de la tabla y usamos la opción "From Table"

Un detalle importante es quitar la marca de la opción "My table has headers" ("mi tabla tiene encabezamientos").
La ventana del Power Query se verá así

Empezamos por eliminar las columnas de Totales  (columna 6 y columna 10) para lo cual las seleccionamos, primero la 6 y luego la 10 apretando el botón Ctrl, y usando la opción Remove

Power Query no tiene la posibilidad de eliminar filas selectivamente, así que para eliminar los totales de las zonas, invertimos la tabla usando la opción Transpose

Ahora podemos eliminar los totales de las zonas. Antes de invertir nuevamente la tabla, vamos a completar las etiquetas que faltan en la columna 1 (año) usando la opción Fill

con este resultado

Vamos a combinar estas columnas en una única usando la opción Merge Columns, para lo cual debemos primero selecionar ambas columnas. Como separador usamos "espacio" y le damos a la nueva columna el nombre "Período"



El resultado es es siguiente

Ahora volvemos a invertir la tabla y rellanos los vacíos en la primer columna a la derecha (Zonas)

Ahora tenemos que promover la fila 1 al área de los encabezados, lo que hacemos con "Use First Row as Headers"

Nuestro próximo paso es "despivotear" (aplanar) las columnas que contienen los datos (2014 1, 2014 2, etc.) seleccionándolas  y usando el comando "Unpivot"

Esto genera dos columnas: Attribute, que contiene las etiquetas de las columnas seleccionadas y Value que contiene los valores (las ventas en nuestro ejemplo) de la tabla. También podemos ver que la primer columna no tiene encabezado.

Antes de ponner encabezados vamos a separar ("Split") la columna Attribute en dos: Mes y Año. Para eso usamos "Split Column by Delimiter"

Ahora ponemos los encabezamientos "Zona", "Sucursal" (en lugar de "Año Mes"),,, Año (en lugar de Attribute.1), Mes (en lugar de Attribute.2) y Ventas (en lugar de Value).

Podemos ver quel os nuevos campos Año y Mes son valores Texto, lo cual es inconveniente para nuestros objetivos. Para convertirlos en valores numéricos seleccionamos las collomnas y usamos el comando "Data Type"

con lo que concluimos nuestra tarea. Todo lo que nos queda por hacer es cargar la consulta como tabla en una hoja de Excel

Podemos hacer otra transformación combinando los campos Mes y Año com Merge Columns y usando "/" como separador

creando la columna "Fecha". Ahora podemos seleccionar esta columna y cambiar el tipo de dato a "Date" (fecha), removemos las columnas Año y Mes y  movemos la nueva columna Fecha a la derecha de Sucursal

Extraer números de cadenas de texto

Hace ya varios años atrás publiqué un post mostrando técnicas para extraer números o letras de cadenas alfanuméricas. A pesar del tiempo transcurrido sigo recibiendo consultas, en particular sobre cómo extraer valores numéricos de cadenas de texto. En el post mencionado mostraba como extraer todos los valores numéricos (o todas las letras), pero muchos lectores consultan sobre cómo extraer algún número en particular.
En este post veremos tres funciones UDF (funciones definidas por el usuario) útiles para ese tipo de tareas.

Función para extraer el primer número de una cadena alfanumérica

Esta función tiene un único argumento, la celda que contiene la cadena de texto de donde queremos extraer el número.

Function ext_primer_num(celda As Range)
    Dim iX As Integer, resultado As String, temp As String

    For iX = 1 To Len(celda)
        temp = Mid(celda, iX, 1)
        If IsNumeric(temp) Then
        resultado = resultado & temp
        End If
    Next iX
  
        ext_primer_num = CDec(Left(resultado, 1))
  
End Function

Función para extraer el último número de una cadena alfanumérica

Como la anterior esta función tienen como único argumento la celda que contiene la cadena de texto.

Function ext_ultimo_num(celda As Range)
    Dim iX As Integer, resultado As String, temp As String

    For iX = 1 To Len(celda)
        temp = Mid(celda, iX, 1)
        If IsNumeric(temp) Then
        resultado = resultado & temp
        End If
    Next iX
  
        ext_ultimo_num = CDec(Right(resultado, 1))
  
End Function


Función para extraer un número número de una cadena alfanumérica

Esta función tiene dos argumentos: la celda que contiene la cadena de texto y el número de orden del valor numérico a extraer.

En este ejemplo ponemos en la celda C2 el número de orden buscado (en nuestro caso, el tercer valor numérico en la cadena). En la celda C10 obtenemos el error #¡VALOR! ya que la cadena contiene sólo dos valores numéricos.

Function ext_num(celda As Range, num_orden As Integer)
    Dim iX As Integer, resultado As String, temp As String

    For iX = 1 To Len(celda)
        temp = Mid(celda, iX, 1)
        If IsNumeric(temp) Then
        resultado = resultado & temp
        End If
    Next iX
  
        ext_num = CDec(Mid(resultado, num_orden, 1))
  
End Function

Recordemos que para poder usar estas funciones debemos copiar el código a un módulo común del editor de Vba. Podemos guardarlas en el cuaderno donde queremos usarlas o en el Personal, para que estén disponibles para todos los cuadernos.

Gráficos Excel - hacer que el eje horizontal cruce por el mínimo del vertical

Después de casi  un mes de descanso (vacaciones, fiestas, un poco más de vacaciones), vuelvo a la carga con una nota sencilla pero que espero resulte útil.

Supongamos este gráfico

Por definición Excel ubica el eje de las catergorías (el eje horizontal, X) de manera que cruza el eje de los valores (el vertical, Y) en el cero.
Como tenemos valores negativos, éstos aparecen por debajo dell eje de las categorías. En ciertos casos, en particular si tenemos un gráfico con muchos datos, podemos querer que el eje de las X cruce el eje de las Y por el valor mínimo de éste último, es decir, ubicarlo en la base del gráfico.

Para definir la ubicación del eje de las X tenemos que abrir el menú de formato del eje de las Y

pero vemos que si bien podemos definir que el eje horizontal cruce por el valor máximo del eje de las Y, la posibilidad opuesta, que cruce por el mínimo, no existe.

La solución es sencilla: en la ventanilla "Valor del Eje" ponemos un valor mucho más pequeño que el menor de los valores de la serie

Si queremos visualizar la línea del cero, para resaltar los valores que caen por debajo, podemos agregar una serie auxiliar a nuestro gráfico

También podemos lograr este efecto

Seleccionando la serie auxiliar y cambiando el tipo de gráfico a Área y cambiando la definición de la posición del eje de las X a "En marcas de graduación"