Alternativa de valores de texto de tabla dinámica

Una limitación de una tabla dinámica tradicional es que no podemos colocar campos de texto en el área de diseño de valores. Bueno, técnicamente podemos, pero obtenemos un recuento en lugar de ver el valor del texto. En esta publicación, demostraré una forma alternativa de mostrar valores de texto que usa Power Query (en lugar de una tabla dinámica). Dependiendo del tipo de informe que esté creando, esta puede ser una buena opción.

Objetivo: tabla dinámica con valores de texto

Antes de entrar en detalles, revisemos nuestro objetivo.

Hemos exportado algunos datos de nuestro sistema. En esta ilustración, es una lista de nuestros Clientes junto con el Personal asignado para realizar cada tipo de Declaración de Impuestos. Se parece a esto:

Nos gustaría verlo de una manera que sea más fácil de leer. Entonces, nos gustaría una fila por cliente y una columna para cada tipo de declaración de impuestos. Queremos ver al miembro del personal asignado en el informe. Nuestro objetivo es ver algo como esto:

Una opción que me viene a la mente es utilizar una tabla dinámica. Entonces, usamos el comando Insertar Tabla dinámica . Luego, insertamos Cliente en el área de Filas, Retorno en el área de Columnas y Personal en el área de Valores. La tabla dinámica resultante se ve así:

Vemos el número 1 en las celdas donde queremos ver las iniciales del personal. Cuando inspeccionamos el campo Valores, vemos que su valor predeterminado es Contar:

Usamos el menú desplegable para ver si hay otras configuraciones de campo de valor que podamos usar, vemos Min y Max y nos llenamos de esperanza:

Así que intentamos con Max y drats… todavía no vemos las iniciales del personal:

Una opción aquí es seguir con una tabla dinámica, pero usar el modelo de datos y escribir una medida usando la función CONCATENATEX DAX. Esta es una opción muy interesante que podemos usar para mostrar valores de texto en una tabla dinámica y nos permite unir una cadena de valores separados por comas si es necesario. Dependiendo del formato de su informe y de sus datos, esa opción puede ser una excelente opción. Más información aquí .

Pero, en nuestro caso, sólo contamos con un personal por devolución de cliente. Entonces, exploremos una opción que requiere solo un par de clics del mouse y usa Power Query en lugar de una tabla dinámica.

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

Crearemos el informe deseado en tres pasos:

• Obtener datos en Power Query
• Transformación de pivote
• Cargar en Excel

Repasemos estos pasos uno por uno.

Obtener datos en Power Query

Primero necesitamos ingresar los datos en Power Query. Podemos hacer esto seleccionando la tabla de datos y haciendo clic en Datos Desde hoja (o dependiendo de su versión de Excel, Datos Desde tabla/rango ). Los datos se extraen en el editor de Power Query:

Con los datos en Power Query, es hora de nuestro siguiente paso.

Transformación de pivote

A continuación, seleccionamos la columna que contiene los valores que queremos usar como encabezados de columna. Este es el mismo campo que colocaríamos en el área de diseño de Columnas de una tabla dinámica. En nuestro caso, queremos una columna para cada valor en la columna Devolución, por lo que seleccionamos la columna Devolución.

Luego hacemos clic en Transformar Columna dinámica . En el cuadro de diálogo Columna dinámica resultante, seleccionamos Personal como Columna de valores porque contiene los valores que queremos mostrar.

Ahora, la siguiente configuración es importante y es diferente a la forma en que funciona una tabla dinámica.

A continuación, hacemos clic en Opciones avanzadas Máximo (o Mínimo) para mostrar el valor de texto en lugar del Recuento predeterminado:

Con una tabla dinámica, si usamos Max o Min en un valor de texto, se muestra 0. SIN EMBARGO, con Power Query, se muestra el valor de texto máximo o mínimo (considérelo como ordenar y luego elegir el primero o el último de la lista). También podemos usar la opción No agregar cuando no hay múltiples valores. Dado que solo tenemos un valor de texto único por devolución de cliente, somos buenos con Max, Min o Don’t Aggregate. (Si tuviéramos varios empleados por cliente, Don’t Aggregate devolverá un error, por lo que necesitaremos realizar transformaciones adicionales… y hablaré de eso en una próxima publicación).

Entonces, hacemos clic en Aceptar y bam:

Sí… ¡funcionó! Todo lo que queda es recuperar los datos en Excel.

Cargar en Excel

Para recuperar los datos en Excel, hacemos clic en Inicio Cerrar y cargar en… y los enviamos a una tabla en una hoja nueva o existente. Bam:

Sí… ¡lo logramos! Y la buena noticia es que si los valores de nuestros datos de origen cambian, o se agregan o asignan nuevas devoluciones de clientes, simplemente podemos hacer clic derecho en la tabla de resultados verde y seleccionar Actualizar. Se actualizará instantáneamente con cualquier cambio.

Conclusión

La clave de esta técnica es que utilizamos la función agregada Min o Max en la transformación de columna dinámica. Es importante tenerlo en cuenta porque es diferente a la forma en que funcionan las tablas dinámicas.

Oh… una cosa más… usar Min/Max en Power Query también funciona con la transformación Agrupar por.

Si tiene alguna idea o sugerencia, compártala publicando un comentario a continuación… ¡gracias!

Notas: además de utilizar la transformación de columna dinámica, Power Query también puede desvincular columnas como se muestra en esta publicación.

Archivo de muestra

Valores de texto.xlsxDescargar