Transforma una columna en una tabla con Power Query

Mi colega Lavan acudió a mí con un problema en el trabajo y fue el caso de uso perfecto para Power Query (también conocido como Get Transform).

Todos los meses tiene que obtener algunos datos de un sistema y usarlos en su trabajo de finanzas de fin de mes.

Esta parte del sistema no tiene botón de exportación. La única forma de obtener los datos es resaltarlos en la pantalla y copiar y pegar los datos en una hoja de Excel.

En lugar de copiar en una bonita tabla como se muestra en la pantalla, se copia en una sola columna vertical. Luego necesita convertir esta única columna en una tabla para continuar con el resto de su trabajo.

En esta publicación, usaremos Power Query para crear una transformación que convierta una sola columna en una tabla.

Descargar archivo de ejemplo

Contenidos
  1. La única columna de datos
  2. Convierta la columna única en una tabla de Excel
  3. Crear una consulta desde tabla/rango
  4. Crear una columna de ID para agrupar registros de direcciones
  5. Gire los datos según el resto y la columna de ID
  6. Conclusiones

La única columna de datos

En este ejemplo, veremos algunas direcciones que están apiladas en una sola columna.

Cada registro de la columna consta de 5 filas de datos: el nombre, la dirección, la ciudad, el país y un código postal.

Nuestra transformación se basará en el hecho de que cada conjunto de registros tiene exactamente 5 filas. En este ejemplo, es 5, pero funcionará para cualquier número, la clave es que es una constante fija para cada registro.

Convierta la columna única en una tabla de Excel

Vamos a usar Power Query para transformar estos datos más tarde usando una consulta From Table/Range . Esto convertirá automáticamente nuestros datos en una tabla de Excel , pero también podríamos hacer este paso primero y nombrar nuestra tabla de manera apropiada.

Seleccione los datos y presione Ctrl + T para convertir los datos en una tabla. También puede crear una tabla desde la pestaña Insertar con el comando Tabla .

Nuestros datos tienen el encabezado de columna Información de contacto , así que marque la casilla Mi tabla tiene encabezados en el cuadro de diálogo Crear tabla .

Vaya a la pestaña Diseño de herramientas de tabla y cambie el nombre a InputData .

Crear una consulta desde tabla/rango

Seleccione su tabla InputData y vaya a la pestaña Datos y presione el botón de consulta Desde tabla/rango . Esto abrirá el editor de consultas con nuestra tabla de una sola columna cargada.

Crear una columna de ID para agrupar registros de direcciones

El objetivo aquí es crear una columna con un índice agrupado que se incrementará en 1 cada vez que haya un nuevo registro de dirección en los datos. Debido a que nuestros datos siempre tienen exactamente 5 filas por dirección, esto significa que queremos crear una columna que se incremente en 1 cada 5 filas.

El primer paso es agregar una columna de índice. Vaya a la pestaña Agregar columna del editor de consultas y seleccione el comando Índice . Presiona la pequeña flecha negra a la derecha para revelar más opciones y elige Desde 1 . Esto iniciará la columna de índice en 1 en la primera fila en lugar del valor predeterminado de 0.

Ahora podemos usar la función Number.Mod para encontrar el resto de nuestro índice cuando lo dividimos por 5 (el número de filas por registro). Vaya a la pestaña Agregar columna y seleccione el comando Columna personalizada .

Ingrese =Number.Mod([Index],5)en el editor de fórmulas de columnas personalizadas y cambie el nombre de la nueva columna a Remainder . Esta fórmula devolverá el resto de la columna de índice al dividir por 5. Esto dará como resultado una secuencia repetitiva de {1,2,3,4,0} para cada uno de los registros de direcciones en nuestros datos.

Observe que la primera fila de cada registro tiene un 1 en la columna restante.

Ahora agreguemos una columna que indique la fila inicial de un registro de dirección. Podemos agregar otra columna personalizada y crear la fórmula if [Remainder]=1 then 1 else 0en el editor de fórmulas de columnas personalizadas y cambiar el nombre de la nueva columna a Indicador .

Este contendrá un 1 cuando el registro sea el comienzo de una dirección y un 0 en cualquier otro lugar.

Ahora agreguemos una columna de ID que será la misma para todas las filas relacionadas con un registro de dirección determinado.

Podemos agregar otra columna personalizada y crear la fórmula List.Sum(List.Range(#"Added Custom1"[Indicator],0,[Index]))en el editor de fórmulas y cambiar el nombre de la nueva columna a ID .

Esto crea un total acumulado basado en la columna del indicador que será nuestro número de índice agrupado.

Gire los datos según el resto y la columna de ID

Ahora estamos listos para pivotar nuestros datos. Aquí es donde convertimos la única columna de datos en una tabla de datos de 5 columnas con una fila por registro de dirección.

Mantenga presionada la tecla Ctrl para seleccionar las columnas Índice e Indicador . Luego haga clic con el botón derecho en cualquiera de las columnas y elija Eliminar columnas en el menú.

Ahora podemos pivotar nuestros datos en función de la columna restante. Seleccione la columna Resto y vaya a la pestaña Transformar y presione el comando Columna pivote .

Ahora podemos seleccionar nuestros valores para pivotar como las direcciones en la columna Información de contacto y en las opciones avanzadas seleccionar No agregar como la función de valor agregado. Presione el botón Ok y los datos deberían tener un formato de tabla familiar.

Ahora podemos eliminar la columna ID ya que ha hecho su trabajo. También podemos cambiar el nombre de las columnas como Nombre, Dirección, Ciudad, País y Código postal respectivamente y luego Cerrar y cargar la consulta en una tabla de Excel.

Conclusiones

Ahora tenemos una tabla adecuada de datos de direcciones donde cada fila contiene una dirección y cada parte de los datos está en su propia columna.

Lo mejor de Power Query es que ahora que hemos creado esta consulta, podemos usarla cada vez que necesitemos copiar los datos de nuestro sistema.

Todo lo que tenemos que hacer es pegar los nuevos datos en la tabla InputData y presionar el botón Actualizar en la pestaña Datos .

Ahora tenemos un proceso fácilmente repetible para limpiar nuestros datos cada mes.

Deja una respuesta

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

Subir

Utilizamos cookies para asegurar que damos la mejor experiencia al usuario en nuestra web. Si sigues utilizando este sitio asumiremos que estás de acuerdo.