Las trampas ocultas de Power Query

Confieso que el título de este post peca de sensacionalismo, pero es por una buena causa: mostrar que, si no prestamos la debida atención, las consultas que creamos con Power Query pueden fallar y cuando lo hacen, lo harán en el momento menos adecuado (por ejemplo cuando estamos demostrando las bondades del modelo que acabamos de construir a nuestro jefe o cliente).

Voy a mostrar a una de esas trampas: la trampa de dividir por columnas (basado en un hecho real 😊).

Esta tabla muestra en que países han realizados ventas algunos de nuestros agentes

Podemos ver que Steven a realizado ventas en 4 países , Michael en 5 y Anne también en 5.

Queremos organizar la tabla de manera que en la columna "País" aparezca un solo país por fila. Así tendremos 4 filas para Steven, 5 para Michael y 5 para Anna.

Damos los siguientes pasos:



1 - Creamos una consulta y utilizar la opción Transformar-Dividir columna de Power Query

Power Query selecciona en forma automática "coma" como delimitador y "Cada aparición del delimitador" así que apretamos "Aceptar" con este resltado

2 - Seleccionamos la columna [Vendedor] y usamos la opción Anulación de dinamización de otras columnas (Unpivot)

con este resultado

Eliminamos la columna Atributo y cargamos la consulta a una hoja del cuaderno.

Hasta aquí todo bien, ¡misión cumplida!
Ahora vamos a agregar dos vendedores a la tabla de datos: Andrew y Robert

Prestemos atención que los nuevos vendedores han hecho ventas en seis países cada uno.

Actualizamos la consulta y vemos el resultado:

La tabla sólo tiene 5 filas para los dos nuevos vendedores, a pesar que en la tabla de datos tienen 6!

Para investigar el problema vamos a editar la consulta y volver al paso aplicado Dividir columna por delimitador. Con un clic en el icono del engranaje a la derecha del paso aplicado abrimos el formulario; en la parte inferior hacemos un clic en Opciones avanzadas

Advertimos que Power Query ha definido el parámetro Número de columnas en que desea definir de acuerdo al número máximo de columnas al momento de crear la consulta. Si nos fijamos en la barra de las fórmulas o en editor avanzado vemos que Power Query a codificado el paso en forma explícita (hard coded)

Una solución posible a este problema es editar la consulta y eliminar el número que aparece en la casilla Número de columnas en que desea definir dejándola en blanco.
El  problema con esta solución es que tendremos que editar la consulta cada vez que se agregue un vendedor con un número de países mayor a los anteriores.

Otra solución posible es introducir un número suficientemente grande en la casilla Número de columnas en que desea definir. Esta es una solución del tipo "fuerza bruta" y siempre existe la posibilidad de no haber elegido un número suficientemente grande.

La verdadera solución es usar la opción Filas en Opciones avanzadas

lo que, a su vez, nos ahorra la Anulación de dinamización de otras columnas (Unpivot).

Otra medida importante es eliminar el paso Tipo Cambiado (otras de las trampas de Power Query)

Conclusiones:

• en Power Query podemos usar la interfaz de usuario para realizar gran parte de nuestras tareas de transformación de datos, pero debemos tener en cuenta que el código creado codifica en forma explícita (hard coded) los pasos aplicados.  
• Siempre abrir todo enlace de tipo Opciones avanzadas en un formulario e investigar las posibilidades ofrecidas.
• En lo posible evitar quedarnos con la opción por defecto de Power Query.