Power Query - Actualización de abril 2016

Los consuetudinarios lectores de este blog conocen mi entusiasmo por las nuevas herramientas que Microsoft ha incorporado a Excel, en particular por Power Query.

Power Query se halla en estado de desarrollo constante y casi cada mes recibimos actualizaciones de Microsoft.

La del mes de abril incluye, entre otras, nuevas opciones que reseñaré aquí:

• Posibilidad de anexar (append) varias tablas en una única operación.
• Ordenar alfabéticamente las columnas existentes en la opción Elegir columnas.
• Crear una columna de porcentaje con un solo clic.

Anexar varias tablas en una única operación

Hasta la versión anterior de Power Query si queríamos anexar más de dos tablas, teníamos que hacerlo en forma repetitiva o manipulando el código de la consulta en el editor. A partir de ahora podemos hacerlo en una única operación. Por ejemplo, si tenemos estas tres tablas de ventas

la opción Anexar (append) abre este nuevo diálogo

Ordenar alfabéticamente columnas existentes

Una gran ventaja cuando trabajamos con muchas columnas (más de las que se ven en el ejemplo)

Crear una columna de porcentaje con un solo clic.

La ventaja de esta nueva opción es que no necesitamos duplicar la columna sobre la cual queremos calcular el porcentaje. Lo que haremos es seleccionar la columna, y en la pestaña Add Column elegir la opción Percentage en Standard

Suma condicional de fechas en Excel

Este post viene a colación de una consulta sobre cómo totalizar valores por fechas con más de una condición, más específicamente, entre dos fechas.
Excel pone a nuestra disposición varias herramientas: fórmulas, tablas y tablas dinámicas. Hacer el cálculo con tablas o tablas dinámicas no ofrece mayores inconvenientes. Por ejemplo, si hemos organizado los datos en forma de Tabla, podemos usar el filtro y la opción de Fila de Totales para ver el total de los valores del primer semestre del año

En una tabla dinámica creada con los mismos datos aplicamos también el filtro de fechas

Si queremos o tenemos que usar fórmulas, nos toparemos con dos "bemoles":

1. la forma en que Excel maneja la fechas dentro de las fórmulas (tema que ya tratamos en el pasado remoto de este blog);
2. el uso de operadores del tipo "mayor que", "igual", etc. dentro de fórmulas.

Si queremos hacer con funciones el cálculo mostrado más arriba podemos usar SUMAPRODUCTO o SUMAR.SI.CONJUNTO, ya que tenemos dos condiciones.

Con SUMAPRODUCTO usamos 

=SUMAPRODUCTO((B3:B62>=FECHA(2016,1,1))*(B3:B62<=FECHA(2016,3,31))*C3:C62)

La función FECHA convierte la fecha del criterio, por ejemplo 01/01/2016 en su número de serie (42370) ya que esa es la forma en que Excel maneja las fechas.

Si queremos usar SUMAR.SI.CONJUNTO usamos

=SUMAR.SI.CONJUNTO(C3:C62,B3:B62,">="&FECHA(2016,1,1),B3:B62,"<="&FECHA(2016,3,31))

donde podemos ver que concatenamos el operador ">=" con el numeral de la fecha usando el operador &.

Código para lista de valores del operador IN de SQL en Excel

En un post reciente en PowerPivot(Pro), Matt Allington recomienda adquirir conocimientos básicos de SQL para agilizar y mejorar nuestro trabajo con el PowerPivot. Sin lugar a dudas un buen consejo pero no sólo para los usuarios de PowerPivot sino para todo usuario de Excel (en esta nota he mostrado un uso de SQL con Excel).

En este post no voy a hablar de como usar SQL y supongo que sólo interesará a aquellos lectores que ya hacen algún uso de este lenguaje.

En la nota mencionada Matt publica un código de Vba para crear la lista de valores de un operador IN (devuelve  aquellos registros cuyo campo indicado coincide con alguno de una lista).

Para usar este operador hay que crear una lista de valores, texto o números, separados por comas.

Una de las formas de hacerlo es usando la función CONCATENAR, pero cuando se trata de varios miembros a unir, la tarea se vuelve irritántemente tediosa.

Así que, como Matt, también yo he creado un código que uso en mi trabajo diario y que hasta ahora no había pensado en publicarlo. El código es el siguiente

Sub cadena_for_SQL()
  
    Dim cell As Range, strTmp As String, string_for_SQL As String
    Dim SQLString As DataObject
    Dim Answ
  
    Answ = MsgBox("Yes para texto - No para numeros", vbYesNo, "SQL String")
  
    Set SQLString = New DataObject
  
    For Each cell In Selection
        If Answ = vbYes Then
            strTmp = strTmp & "'" & cell.Value & "',"
        Else
            strTmp = strTmp & cell.Value & ","
        End If
    Next cell
  
    string_for_SQL = "IN " & "(" & Left(strTmp, Len(strTmp) - 1) & ")"
  
    With SQLString
        .SetText string_for_SQL
        .PutInClipboard
    End With
  
End Sub

Empezamos por seleccionar el rango que contiene los valores a integrar a la lista del operador IN y
luego activamos la macro; aparecerá un mensaje que nos pide determinar si los valores deben integrarse como texto o como números

Elegimos la opción deseada y la macro copiará la lista al Clipboard. De esta manera todo lo que nos queda por hacer es pegar la lista en el lugar deseado (por lo general, el editor de SQL).

Este video muestra el uso del código en sus dos versiones (texto y valores numéricos)