Alternativa de tabla dinámica con múltiples valores de texto

Si ha intentado insertar un campo de texto en el área de diseño de valores de una tabla dinámica, es posible que haya notado que la cadena de texto en sí no se muestra. En cambio, el recuento se muestra de forma predeterminada. En esta serie, hablamos de cómo mostrar los valores de texto deseados mediante Power Query en lugar de una tabla dinámica. En la primera publicación , cubrimos los pasos necesarios cuando solo hay un valor de texto. Ahora seguiremos los pasos para unir varios valores de texto y separarlos con un delimitador como una coma.

Objetivo: tabla dinámica con valores de texto

Antes de entrar en detalles, confirmemos nuestro objetivo aquí. Hemos exportado algunos datos de nuestro sistema. En esta ilustración, es el personal asignado a preparar las declaraciones de impuestos de los clientes:

Nos gustaría tenerlo en un formato como este y tener en cuenta que se pueden asignar varios miembros del personal:

En nuestra publicación anterior , intentamos utilizar una tabla dinámica. Rápidamente notamos que las tablas dinámicas tradicionales realmente no admiten el uso de texto en el área de diseño de valores, incluso cuando se usan las opciones Mín./Máx. Entonces, utilizamos Power Query como alternativa y notamos que Min/Max funciona con cadenas de texto. También podríamos usar la opción No agregar. En la publicación anterior, solo teníamos un personal asignado a cada devolución, por lo que Mín./Máx./No agregar arrojaron el mismo resultado.

En esta publicación, discutiremos cómo manejar el caso de múltiples valores de texto, cómo Min/Max/Don’t Aggregate proporciona diferentes resultados y cómo unir los valores de texto con el delimitador de su elección. Hagamos esto.

Tutorial: alternativa de Power Query a la tabla dinámica con valores de texto

Repasemos los siguientes pasos:

• Obtener datos en Power Query
• Realizar transformaciones
• Devolver datos a Excel

Lo primero es lo primero… necesitamos introducir los datos en Power Query.

Obtener datos en Power Query

En esta ilustración, nuestros datos están en una tabla de Excel, pero en la práctica, podrían estar prácticamente en cualquier lugar. Comenzamos seleccionando Datos Desde hoja . (O bien, Datos Desde tabla/rango dependiendo de su versión de Excel).

Los datos se cargan en el editor de Power Query:

Una vez completado ese paso, ahora podemos realizar nuestras transformaciones.

Realizar transformaciones

En la publicación anterior , realizamos inmediatamente una transformación de columna dinámica en este punto. Esto se debía a que no teníamos varias filas para la devolución de un cliente determinado… en otras palabras, solo había un personal asignado a cada devolución de cliente. Sin embargo, en este conjunto de datos, tenemos devoluciones de clientes con varios empleados asignados. Si siguiéramos los pasos que se muestran en la publicación anterior, veríamos los siguientes resultados según nuestra elección en el cuadro de diálogo Columna dinámica (opciones avanzadas):

• Mínimo: devolvería solo uno del personal asignado (el primero, en orden alfabético)
• Máximo: devolvería un pentagrama (el último, en orden alfabético)
• No agregar: devolvería un error (Expression.Error: había demasiados elementos en la enumeración para completar la operación)

Entonces, nuestra primera transformación NO será una columna dinámica. En cambio, nuestro primer paso es Agrupar por para reducir nuestra tabla a una fila para cada combinación única de Cliente/Devolución.

Agrupar por

Comenzamos seleccionando las columnas Cliente y Devolución (como se muestra arriba).

Luego seleccionamos Transformar Agrupar por . En el cuadro de diálogo Agrupar por resultante, definimos un nuevo nombre de columna como StaffTable y configuramos la Operación en Todas las filas como se muestra a continuación:

Hacemos clic en Aceptar y vemos una nueva columna llamada StaffTable y parece que almacena un montón de tablas:

Piense en cada tabla como una pequeña minitabla que contiene una fila para cada personal asignado a esa devolución de cliente específica. Podemos ver cada minitabla haciendo clic en el espacio vacío en cualquier celda:

Ahora podemos pasar a nuestro siguiente paso.

Lista de personal

La columna StaffTable contiene un montón de minitablas. Instintivamente entendemos que, en general, una tabla puede incluir varias filas y varias columnas. Podemos verificar esto viendo la minitabla en la captura de pantalla anterior. Confirmamos que hay dos filas (una para DAR y otra para DMK) y tres columnas (Cliente, Devolución y Personal).

Para realizar nuestro siguiente paso, necesitamos aislar la columna Personal. Mientras que una tabla puede almacenar varias columnas, una lista puede almacenar una sola columna. Entonces, nuestro objetivo es crear una lista de nombres de personal para cada devolución de cliente.

Una forma de lograr esto es crear una nueva columna personalizada haciendo clic en Agregar columna Columna personalizada . En el cuadro de diálogo resultante, le damos un nombre a nuestra nueva columna, como StaffList. Luego usamos la función Table.Column para extraer la columna “Personal” de la tabla [StaffTable] . La fórmula se muestra a continuación:

Pulsamos Aceptar y vemos una nueva columna llamada StaffList que contiene un montón de Listas:

Piense en cada Lista como una pequeña minilista que contiene una sola columna de personal asignado a cada Declaración de Cliente específica. Podemos ver una vista previa de cada lista haciendo clic en el espacio vacío en cualquier celda de la lista:

Ahora podemos pasar a nuestro siguiente paso.

Lista de extracción: valores de texto separados

Queremos convertir cada Lista en un único valor de texto que podamos enviar a Excel. Tenemos muchas opciones para combinar los valores del texto, incluida separarlos con comas, dos puntos o espacios. En nuestro caso, usaremos un espacio de coma para separar cada uno.

Seleccionamos toda la columna StaffList y luego hacemos clic en Transformar Extraer valores . En el cuadro de diálogo resultante, podemos elegir cualquier delimitador de la lista o definir el nuestro seleccionando Personalizado. Ingresé una coma seguida de un espacio:

Le damos a OK y bam:

Ahora, eliminamos la columna StaffTable seleccionándola y haciendo clic en Transformar Eliminar columnas . Y con eso, podemos dirigirnos a nuestro siguiente paso.

Columna pivote

Seleccionamos toda la columna de retorno y seleccionamos Transformar Columna dinámica . En el cuadro de diálogo Columna dinámica resultante, seleccionamos StaffList como Columna de valores . Luego ampliamos las opciones avanzadas y seleccionamos No agregar (o Mínimo o Máximo):

Le damos a OK y bam:

Finalmente, podemos enviar los resultados a Excel.

Devolver datos a Excel

Hacemos clic en Inicio Cerrar y cargar en… y enviamos los resultados a una Tabla en la hoja de cálculo de Excel deseada:

Sí… ¡lo logramos!

Y la mejor parte es que si los datos de origen tienen cambios o nuevas filas en el futuro, no es necesario que sigamos todos esos pasos nuevamente. ¡Solo necesitamos hacer clic derecho en la tabla de resultados y seleccionar Actualizar!

Conclusión

Espero que esta alternativa a un informe de tabla dinámica sea útil al intentar mostrar valores de texto. Si su libro requiere el uso de una tabla dinámica, puede unir valores de texto utilizando el modelo de datos y la función CONCATENATEX DAX. Puede encontrar más información sobre ese enfoque aquí .

Si esta publicación fue útil, o si tiene alguna sugerencia para mejorar u otras alternativas, ¡compártala publicando un comentario a continuación!

Archivo de muestra

Valores de texto2.xlsxDescargar