Power Query: Listar ficheros de una carpeta

Hace bastante tiempo publiqué una manera, mediante programación, de recuperar los ficheros existentes en una carpeta (ver macro Excel).
Hoy veremos cómo podemos conseguir lo mismo empleando la herramienta Power Query / Obtener y transformar.

Supongamos queremos recuperar los nombres de los ficheros contenidos en una carpeta, por ejemplo
E:\excelforo
pero solo los que tengan extensión .gif

Accedemos a la ficha Datos > grupo Obtener y transformar > desplegable Nueva consulta > opción Desde un archivo > Desde una carpeta

Se abrirá una ventana donde elegir la ruta de nuestra carpeta... bien empleando el explorador (Examinar) o bien escribiéndola directamente.

Al aceptar veremos una ventana con un listado de los ficheros, con su Nombre, extensión, fechas de creación, modificación, último acceso y su ruta.

En nuestro caso, al querer segmentar la información del listado, presionaremos el botón de Editar. Esto nos abrirá el Editor de consultas...

Con el editor abierto realizaremos algunas 'operaciones':
1- aplicar un filtro sobre el campo 'Extension' con el fin de visualizar solo aquellos registros con extensión .gif (tal como queríamos).
2- de forma similar, aplicaremos sobre el campo que muestra la ruta ('Folder path') un filtro para ver únicamente los ficheros .gif en la ruta raíz elegida (E:\excelforo).. y no ver los de las subcarpetas.
3- seleccionaremos dos campos: 'Name' y 'Folder path' y haciendo clic derecho con el ratón elegiremos la opción para eliminar el resto de campos: Quitar otras columnas
4- y por último, si así lo estimamos conveniente, agregaremos una nueva columna personalizada que una o concatene las dos anteriores:
'Folder path' y 'Name'
en ese orden para tener completa la ruta y nombre de los ficheros buscados...

Para ello lo más simple será seleccionar las columnas implicadas y haciendo clic derecho con el ratón sobre ellas elegir la opción de Combinar columnas
Tendremos cuidado de no añadir ningún separador, ya que en este caso no es necesario.
Además he renombrado el campo

Este sería el aspecto final de la consulta:

Ya podemos Cerrar y cargar en... para devolver el listado a nuestra hoja de cálculo.

Seguir leyendo...

Agregar la selección actual al filtro

Una opción poco empleada ea la de Agregar la selección actual al filtro, cuando trabajamos con el Autofiltro.
Se trata de una opción que nos permite incorporar o agregar al filtro previo aplicado nuevos elementos...

Supongamos tenemos la siguiente tabla de información:

Sobre el campo ciudad aplicamos un filtro cualquiera, por ejemplo, elegimos Barcelona y Madrid.

Nos damos cuenta, que una ve aplicado, se nos olvidó (o sencillamente queremos añadirlo al filtro aplicado) otra u otras ciudades, Valencia (por ejemplo)...
será en este momento cuando haremos uso de la opción Agregar la selección actual al filtro.
Aplicamos sobre el campo y desplegamos la ventana de filtro.
Haremos uso del cuadro de búsqueda para localizar coincidencias:

Localizados los nuevos elementos a incorporar/añadir a los previamente existentes, marcaremos la opción de Agregar la selección actual al filtro, y aceptaremos.

En caso de no marcar esta opción se sustituirá el filtro aplicado el previo por el ahora desplegado.

Por supuesto podremos repetir cuantas veces queramos la acción.

Seguir leyendo...

Power Query: Columna dinámica

Seguimos con la herramienta Obtener y transformar/Power Query.
Hoy trabajaremos para conseguir, a partir de una tabla con campo 'Zona', 'Producto' e 'Importes', una nueva tabla resumen con importes acumulados de los 'productos' por cada 'zona' (a semejanza de una tabla dinámica'.

En particular nos aprovecharemos de una herramienta especialmente útil de Power Query: Columna dinámica / Unpivot.

Como siempre, desde la tabla, cargamos los datos en el editor de consultas:
Desde la hoja de cálculo accedemos a la Ficha Datos > grupo Obtener y transformar > Desde una tabla.

Ya en editor de consulta, y con nuestra consulta habilitada, seleccionamos el campo 'Zona', y navegamos hasta el menú Transformar > grupo Cualquier columna > botón Columna dinámica

Se abrirá una ventana de configuración donde ajustar la conversión.
Seleccionaremos como 'columna de valores' el campo numérico ('Importes'); y como función de valor agregado (que se empleará para agrupar los datos cruzados) la Suma.

Hemos acabado. Podemos Cerrar y Cargar para obtener el resultado mostrado al inicio de la explicación.
Tenemos un tabla de referencia cruzada por zona y producto, y resumido por suma de importes acumulados...

Y como una tabla dinámica, nuevos elementos se incorporarán automáticamente en nuestro informe resumen.

A modo de curiosidad, si entramos en el editor avanzado de la consulta veremos:

let 
    Origen = Excel.CurrentWorkbook(){[Name="Tbl_Pdtos"]}[Content],
    #"Columna dinamizada" = Table.Pivot(Origen, List.Distinct(Origen[Zona]), "Zona", "Importes", List.Sum)
in
    #"Columna dinamizada"

Donde observamos el uso de la función Table.Pivot, que es precisamente la que habilita la posibilidad de crear nuevas columnas por cada elemento del campo indicado.

Seguir leyendo...

Power Query: Listado elementos únicos

Aprenderemos algo más sobre el uso de esta gran herramienta: Obtener y transformar (Power Query)

Hoy en concreto veremos como conseguir un listado de elementos únicos, sin repetición, empleando la función Table.Distinct,
que podemos obtner desde las opciones del Editor de consultas, con un viejo conocido de la hoja de cálculo, como es el Quitar duplicados.

A partir de la tabla azul, de la izquierda (de nombre 'Tbl_Pdtos') llamaremos al Editor de consultas desde la ficha Datos > grupo Obtener y transformar > botón Desde una tabla

Con la tabla cargada, le cambiamos el nombre la consulta y la llamo 'Quitar_Duplicados'.
En el paso siguiente seleccionamos el campo 'Producto', y hacemos clic derecho para seleccionar la opción: Quitar otras columnas. Esto elimina el resto de columnas de la tabla cargada, dejando visible únicamente la del campo 'Producto'

YA con solo un campo visible, realizamos la última acción.
Desde la ficha de Inicio de editor de consultas > grupo Reducir filas > desplegable Quitar filas > Quitar duplicados

Listo. Podemos Cerrar y Cargar el resultado en nuestra hoja de cálculo... obteniendo una tabla de elemento únicos como veíamos en la primera imagen.

Si accedemos al Editor avanzado de la consulta veríamos el siguiente código:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tbl_Pdtos"]}[Content],
    #"Otras columnas quitadas" = Table.SelectColumns(Origen,{"Producto"}),
    #"Duplicados quitados" = Table.Distinct(#"Otras columnas quitadas")
in
    #"Duplicados quitados"

Comprobamos como la acción de Quitar duplicados ha dejado grabada la función Table.Distinct

Otra forma de llegar a lo mismo sería directamente insertar una consulta en blanco (ficha Inicio > grupo Nueva consulta > Nuevo origen > Otros orígenes > Consulta en blanco).
Y desde el editor avanzado escribir:

Table.Distinct(  
    Table.SelectColumns(Excel.CurrentWorkbook(){[Name="Tbl_Pdtos"]}[Content],{"Producto"})
, "Producto")

Sin duda más simple y directo.. pero con igual resultado que con el asistente.

Seguir leyendo...

BUSCARV sobre varios origenes

Muchas veces a lo largo de mis años como consultor y formador me han plantado la misma cuestión. (¿es posible hacer un BUSCARV sobre diferentes rangos?)... y en la entrada anterior analizamos una manera de recuperar un valor buscado que podría encontrarse en diferentes tablas... con un par de inconvenientes:
1-se necesitaba una acción manual de refresco de la consulta de Power Query,
2-estábamos restringidos por el número de tablas.

Hoy veremos un trabajo similar sin ninguna de las limitaciones anteriores... (solo estaremos sujetos a la que nos marca el uso de fórmulas matriciales).
Así pues empecemos a construir un solo BUSCARV sobre infinitos orígenes.

Partimos en este caso de tres tablas (pero podrían ser las que necesitáramos):

Necesitamos tener una lista con los nombres de las diferentes tablas, en mi caso los he dispuesto en el rango B13:B15:
Tbl_135
Tbl_246
Tbl_78910

A cuyo rango además he asignado el nombre definido: Lista.

Hasta este punto tenemos pues dos nombres definidos:
lista =BUSCARV_Multiple!$B$13:$B$15
vBuscado =BUSCARV_Multiple!$B$8

Estamos preparados... en la celda C8 introducimos la fórmula matricial buscada:
=BUSCARV(vBuscado;INDIRECTO(INDICE(lista;SUMA((CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0)*FILA(INDIRECTO("1:"&CONTARA(lista))));1));3;0)

(recuerda presionar Ctrl+Mayusc+Enter para validar en lugar de solo Enter).
El resultado aparece mágicamente.. dará igual en qué tabla se encuentre, si existe en alguna de ellas, obtendremos el valor deseado!.

la explicación.
La clave del asunto es el uso de la función:
CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0
que devuelve una matriz de VERDADEROS y FALSO, una por cada tabla... y tendremos un VERDADERO solo para la tabla donde se encuentre el valor buscado, por ejemplo, para el valor buscado 'p7' tendríamos la matriz:
{FALSO;FALSO;VERDADERO}
es decir, es cierto que 'p7' la encontramos en la tercera tabla...

Para convertir esa matriz {FALSO;FALSO;VERDADERO} en un tres (tercera tabla) lo multiplicamos por una matriz de constantes {1;2;3}
lo que conseguimos con la clásica fórmula
FILA(INDIRECTO("1:"&CONTARA(lista)))
que nos devolverá la matriz de naturales desde 1 hasta el número de tablas existente.

El producto elemento a elemento y su suma final nos retornará el número buscado: 3 (para este ejemplo).

Si aplicamos sobre nuestra 'lista' de Tablas la función INDICE, nuestro tres devuelve el nombre de la Tabla deseada... si sobre ella aplicamos la función INDIRECTO ya podremos trabajar sobre esa tabla igual que si la seleccionáramos.

Si ya tenemos la tabla, con la fórmula comentada:
INDIRECTO(INDICE(lista;SUMA((CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0)*FILA(INDIRECTO("1:"&CONTARA(lista))));1))

estamos en disposición de aplicar un BUSCARV como haríamos normalmente:
=BUSCARV(vBuscado; tabla_recuperada_con_nuestra_fórmula;3;0)

Objetivo logrado!.

Seguir leyendo...

Power Query: Búsqueda sobre varias tablas

Hoy aprenderemos a generar una búsqueda vertical sobre distintos orígenes/tablas empleando la herramienta Obtener y Transformar (antiguo Power Query).

En definitiva daremos respuesta a una cuestión clásica: ¿podemos realizar una especie de 'BUSCARV' sobre diferentes tablas?.

En primer lugar partiremos, para no eternizar el ejemplo, de tres tablas (a las que he llamado 'Tbl_135', 'Tbl_246' y 'Tbl_78910'):

haz clic en la imagen

Por otro lado hemos asignado un Nombre definido a una celda que contendrá el valor a buscar:
vBuscado =Hoja1!$B$8

En el siguiente paso, accederemos una vez por cada tabla, a la ficha Datos > grupo Obtener y Transformar > Desde una tabla.
Nos aseguraremos que solo cargaremos la Conexión !!

En el siguiente paso crearemos una consulta anexando las tres tablas conectadas.
Dentro del Editor de consultas en la ficha Inicio > desplegable Combinar > Anexar consultas > Anexar consultas para crear una nueva

A esta nueva consulta le he renombrado como 'TablaTotal'.

Hace tiempo publiqué cómo crear funciones personalizadas dentro del entorno de Power Query (ver).
Bien, tendremos que recordar el proceso.

Creamos una consulta en blanco:
Ficha Datos > grupo Obtener y Transformar > desplegable Nueva Consulta > Desde Otras fuentes > Consulta en blanco
Accederemos al Editor Avanzado (ir a ficha Vista dentro del Editor de consultas) e introduciremos el siguiente código:

(NombreDefinido) => 
    Excel.CurrentWorkbook(){[Name=NombreDefinido]}[Content]{0}[Column1]

Aprovechamos para cambiar el nombre de la consulta/función por 'ValorBuscado'

Por último volvemos a la configuración de nuestra TablaTotal que ahora mismo solo contiene la línea para combinar las tres tablas originales.
Editamos de forma avanzada esta consulta y la dejamos como sigue:

let
    Origen = Table.Combine({Tbl_135, Tbl_246, Tbl_78910}),
    #"Filas filtradas" = Table.SelectRows(Origen, each ([Id] = ValorBuscado("vBuscado"))),
    #"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Id", "concepto"})
in
    #"Columnas quitadas"

Lo que hemos añadido es una acción de filtrado sobre el campo [Id] de nuestra tabla única, donde filtramos por el valor que hubiera en nuestra celda de la hoja de cálculo con el nombre definido asignado 'vBuscado' (revisa primeros pasos).
#"Filas filtradas" = Table.SelectRows(Origen, each ([Id] = ValorBuscado("vBuscado"))),

Y en la última línea simplemente nos quedamos con el campo que nos interesa... el precio. Para ello eliminamos las dos columnas "Id", "concepto"
#"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Id", "concepto"})

Listo, podemos Cargar y cerrar nuestra consulta y devolver el resultado a la hoja de cálculo.

Para comprobar su correcto funcionamiento bastará que cambies el valor en la celda amarilla (B8 que tiene asignada el nombre definido - vBuscado-) y actualizar la consulta de Power Query.

Seguir leyendo...

Búsqueda múltiple de palabras en una celda

Meses atrás publiqué una entrada (ver) donde localizabamos dentro de una celda la coincidencia de palabras de otro listado.

En el post de hoy realizaremos una proceso similar, pero para búsquedas múltiples.

Tenemos un rango A1:A7 de celdas con listado de colores en cada celda, y por otro lado una tabla de colores buscados en cada una de esas celdas (tabla llamada 'TblColores'):

haz clic en la imgen

El proceso es algo laborioso... así pues vamos por partes.

En la TblColores en campo 'equiv' es calculado con la fórmula:
=POTENCIA(2;FILA([@equiv])-FILA(TblColores[[#Encabezados];[equiv]])-1)
que devuelve para cada fila de la tabla el valor devuelto por la potencia de base 2 y exponente 0, 1, 2, 3, 4, etc...
Es decir, obtenemos automáticamente los valores 1, 2, 4, 8, 16, 32, 64, 128, 256 , etc.
Estas cantidades son la base del sistema binario, necesario para la ubicación de colores buscados y encontrados, ya que la suma combinada de estos números es única.

Segundo paso. En el rango B1:B7 añadimos la fórmula:
=SUMAPRODUCTO(NO(ESERR(ENCONTRAR(TblColores[busqueda];$A1)))*TblColores[equiv])
que trabaja matricialmente localizando, si es el caso, cada color existente en la tabla 'TblColores'.
La primera parte de la fórmula
NO(ESERR(ENCONTRAR(TblColores[busqueda];$A1)))
retorna una matriz de VERDDADEROS y FALSOS según existan o no los colores buscados en la celda.
La segunda parte de la fórmula
TblColores[equiv]
se emplea para multiplicar los importes 1, 2, 4, 8, 16, 32, 64, 128, 256 (valores binarios) por esos V y F y así poder sumarlos para obtener un valor único, valor único que representa un sola combinación de dichos valores binarios.

¿Cómo sabemos cuáles son esos valores binarios?... Para descubrirlo aplicaremos la función DEC.A.BIN; y en concreto la siguiente fórmula que completa con ceros por la izquierda ese valor binario devuelto hasta completar el número de dígitos igual a los colores buscados.
En el rango C1:C7:
=SI(LARGO(DEC.A.BIN(B1))<CONTARA(TblColores[busqueda]);REPETIR(0;CONTARA(TblColores[busqueda])-LARGO(DEC.A.BIN(B1)))&DEC.A.BIN(B1);DEC.A.BIN(B1))

En el rango contiguo D1:D7 vemos el resultado simple de DEC.A.BIN(B1).

Último paso. Seleccionamos el rango E1:I1 (son cinco celdas por que como máximo quiero buscar hasta cinco colores por celda).
Y en la celda activa E1 escribo la siguiente fórmula matricial:
=SI.ERROR(TRANSPONER(INDICE(TblColores[busqueda];1+CONTARA(TblColores[busqueda])-FILA(INDIRECTO("1:"&CONTARA(TblColores[busqueda])))*EXTRAE(C1;FILA(INDIRECTO("1:"&CONTARA(TblColores[busqueda])));1);1));"")

La clave de la fórmula es el uso matricial de
EXTRAE(C1;FILA(INDIRECTO("1:"&CONTARA(TblColores[busqueda])));1)
que devuelve el número binario descompuesto en elementos individuales.
Por ejemplo para la primera cadena de texto en la celda A1, EXTRAE retorna: {"1";"1";"1"}
Esta matriz multiplicada por unos números desde 1 hasta el número de colores buscados, lo que obtenemos con:
FILA(INDIRECTO("1:"&CONTARA(TblColores[busqueda])))
Y a dicho producto resultante lo invertimos restándolo del total de colores buscados, ajustado con un +1

Asi para nuestro primer ejemplo obtendríamos la matriz:
{3;2;1}
que empleando la función INDICE nos permite recuperar los colores que responden a esas tres posiciones de la tabla de colores...

Con lo que llegamos al final deseado... por cada celda de A1:A7 tenemos listado horizontalmente los colores coincidentes buscados.

Seguir leyendo...

VBA: Eliminar celdas desde ListBox

Siguiendo con la serie de post sobre ListBox y cómo eliminar elementos, hoy veremos cómo eliminar celdas de la hoja de cálculo desde un ListBox.
Nuestros datos:

Disponemos de una Tabla en el rango A1:A7, tabla llamada 'TblPaises'.
Por otro lado, nuestro formulario contiene una etiqueta/label y un ListBox llamado: LstPais.

Con el evento _Initialize asociado al UserForm cargaremos el ListBox empleando la propiedad .RowSource.
A continuación vinculado al evento _DblClick del ListBox controlaremos la acción de eliminado de celdas en la hoja de cálculo.

Así pues añadimos las siguientes macros dentro de la ventana de código de nuestro UserForm en nuestro proyecto de VB:

Private Sub UserForm_Initialize()
'cargamos el listbox con los valores de la Tabla en la hoja de cálculo
Me.LstPais.RowSource = "TblPaises[paises]"
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub LstPais_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim sEncontrar As String, rngBusqueda As Range

'trabajamos sobre el elemento del listbox seleccionado
Select Case Me.LstPais.Value
    'en caso haya alguno seleccionado
    Case Is <> ""
        'definimos el valor/pais a encontrar
        sEncontrar = Me.LstPais.Value
        'determinamos dónde buscar 8en qué rango)
        With ActiveSheet.Range("TblPaises[paises]")
            Set rngBusqueda = .Find(what:=sEncontrar)
            'si se ha encontrado el valor/país
            If Not rngBusqueda Is Nothing Then
                'marcamos esa celda en la hoja de cálculo
                'y eliminamos desplazando el resto hacia arriba
                rngBusqueda.Delete shift:=xlUp
            End If
        End With
    'para el resto de casos salimos del procedimiento
    Case Else
        Exit Sub
End Select

'volvemos a cargar el ListBox con el resultado de la hoja de cálculo
Me.LstPais.RowSource = "TblPaises[paises]"
End Sub

Al ejecutar nuestro formulario podemos comprobar cómo al hacer doble clic sobre los elementos del ListBox se eliminan los registros de la tabla 'TblPaises' en la hoja de cálculo, tal como esperábamos...

Seguir leyendo...