lunes, enero 12, 2015

Otro uso del Power Query - Dividir columna de ancho variable

Esta vez Eduardo entró a mi oficina sonriendo.

- El jefe me pidió que separe esta lista en dos columnas: una con el nombre del cliente y la otra con el país

- Usá Texto en Columnas, le dije sin mirar la lista ocupado como estaba con mis propios asuntos.
- No se puede. Fijate que el país es la última palabra en la celda pero cada celda contiene un número distinto de palabras.
- Y si no se puede por qué sonreís.
- Porque encontré la solución. Me fijé en el post que publicaste hace unos años sobre cómo extraer el último elemento de una celda  y apliqué la que mostrabas allí a mi problema.
- Ah, que bueno. Mostrame lo que hiciste.

Lo que hizo Eduardo es crear una columna auxiliar con esta fórmula

=SUSTITUIR(A2," ","#",LARGO(A2)-LARGO(SUSTITUIR(A2," ","")))

Lo que hace esta fórmula es poner un símbolo # en lugar del último espacio en la celda. De esta manera creamos un criterio para encontrar la última palabra o valor en la celda


La expresión LARGO(A2)-LARGO(SUSTITUIR(A2," ","")) calcula la cantidad de espacios entre palabras que hay en la celda y este resultado lo usamos como argumento en la función SUSTITUIR para poner el # en el último espacio.

Para poder usar Texto en Columnas, Eduardo tuvo que eliminar las fórmulas de la columna auxiliar con Copiar-Pegado Especial-Valores. Y finalmente


La misma tarea puede hacerse con Power Query. El Power Query tiene también un método para dividir columnas pero con la ventaja de ser más flexible ya que nos permite determinar la ubicación del separador (en nuestro ejemplo el espacio) si éste se repite dentro del registro (celda).

Veamos el proceso. Empezamos por convertir la lista de clientes en Tabla (Insertar-Tablas-Tabla) para poder usar el Power Query con facilidad. Activamos la pestaña del Power Query en la cinta y elegimos Excel Data-From Table

Excel exporta los datos de la tabla a la ventana del editor del Power Query. En la ventana del editor de la consulta seleccionamos la columna y apretamos el icono Split Column- By Delimiter


En el diálogo que se abre elegimos las opciones Espacio (Space) y "el último a la derecha" (At the right-most delimiter) y apretamos OK




Todo lo que nos queda por hacer es transferir el resultado a una hoja de Excel, lo que hacemos con el menú  Close&Load


Los nombres de las columnas los podemos cambiar en la ventana del editor del Power Query antes de transferir la consulta la hoja de Excel (usando Rename) o directamente en la hoja de Excel.


2 comentarios:

  1. Hola

    necesito una pequeña ayuda por favor

    tengo una grafica que sale de cierta tabla y pues la tabla esta echa en base a restas de celdas.

    El problema recae en que la celda que se debe graficar que es la del resultado , cuando no le e insetado numeros a las otras dos se vuelve cero yesto me hace la grafica cero tambien y por ende la grafica es mas dificil de leer . ¿como puedo hacer que la grafica no grafique estos valores cero o que estos ceros no aparescan en las
    celdas de resultados?.

    gracias de antemano

    ResponderBorrar
  2. Wolfgang, el objetivo de los comentarios es, como dice su nombre, comentar en relación al contenido de la nota. Si te fijas en el botón Ayuda (en la parte superior del blog) verás como ponerte en contacto comnigo para consultas no relaciondas con el tema de la nota,
    De todas maneras te sugiero ver esta nota.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.