Introducción

El editor de consultas de Power Query nos proporciona un interfaz gráfico de usuario que facilita enormemente una gran parte de nuestras tareas de limpieza y optimización estructural de los datos, traduciendo los pasos que vamos realizando sobre una vista previa, a un flujo de instrucciones programáticas en lenguaje M que podemos ejecutar con el objetivo de automatizar dichas tareas.

Desafortunadamente, esta facilidad a la hora de realizar las tareas de ETL trae consigo la capacidad de cometer, sin que nos demos cuenta, algunos errores que pueden llevarnos a la creación de consultas vulnerables que no serán capaces de soportar los cambios en orígenes de datos dinámicos, y por lo tanto no se actualizarán.

Cada paso que se realiza utilizando la interfaz de usuario del editor de consultas genera una línea de código que se añade a la secuencia de transformaciones que se aplican a la consulta cada vez que los datos se actualizan. A medida que se construyen estos pasos, Power Query tiene que decidir sobre cómo traducirlos a código. Por ejemplo, la transformación de anular la dinamización de columnas utiliza la función Table.UnpivotOtherColumns, y hace referencia a las columnas no seleccionadas de la tabla activa, aquellas que podríamos denominar columnas de anclaje, en lugar de a las seleccionadas, que son sobre las que se realiza la anulación de la dinamización.

Son estas decisiones de «traducción» las que pueden hacer que alguna de las consultas no sea lo suficientemente robusta como para soportar cambios futuros en los orígenes de datos, y se produzca un error en el momento de actualizar nuestro modelo. Los escenarios más comunes para estos fallos de actualización se originan en los cambios de nombre, de tipo de dato, o en la eliminación de las columnas en las tablas de origen. En este artículo vamos a ver cómo evitar estos problemas construyendo nuestras consultas pensando con antelación en los cambios que pueden producirse en nuestros orígenes de datos en el futuro.

Eliminar columnas de forma segura en Power Query

Si bien la eliminación de columnas innecesarias es una parte crucial de la creación de informes eficientes (menos columnas significa menor consumo de memoria y un menor tamaño de archivo en disco), cada vez que se elimina una columna, se debilita la consulta y nos exponemos a la posibilidad de futuros fallos de actualización cuando la columna eliminada no se encuentre en la fuente de datos externa. La forma más intuitiva y directa para realizar esta transformación es usar el botón de «Quitar columnas» en las columnas seleccionadas:

Cuando utilizamos esta funcionalidad, la fórmula generada en M utiliza la función Table.RemoveColumns y referencia en el código las columnas que hemos eliminado.

Ahora, si cuando realizamos una actualización, la tabla del origen ya no incluye una de estas columnas eliminadas, la actualización fallará:

Para reducir la probabilidad de este tipo de errores, la solución más simple consiste en centrarnos en las columnas a mantener, en vez de en aquellas a eliminar. Desde el editor de consultas podemos usar el botón «Elegir columnas» y la fórmula de código autogenerada incluirá la función Table.SelectColumns, referenciando únicamente las columnas a mantener, por lo que en el caso de que alguna de las columnas que no queremos incluir en nuestro modelo sea eliminada del origen, la actualización de nuestra consulta seguirá funcionando correctamente.

Ignorar la columna no encontrada

A veces, el número de columnas a mantener en una consulta es demasiado alto, y el riesgo de eliminar algunas columnas puede ser menor que los problemas asociados a referenciar un gran número de columnas en el código.

En ese caso, tenemos la opción de utilizar un tercer argumento opcional, tanto de la función Table.RemoveColumns como de Table.SelectColumns, que nos permite ignorar los errores en lugar de no actualizar la consulta. El tercer argumento es MissingField.Ignore o MissingField.UseNull.

MissingField.Ignore ignora la columna no encontrada, mientras que MissingField.UseNull mantiene el nombre de dicha columna, pero con valores nulos. MissingField.UseNull es más práctico que su hermano MissingField.Ignore cuando lo usamos como parámetro de Table.SelectColumns, ya que nos permite asegurarnos de que los nombres de las columnas seleccionadas se incluirán en los resultados finales. Por ejemplo, si en una actualización de nuestra tabla de clientes no se encuentra la columna «Continente», al usar MissingField.UseNull obtendremos el siguiente resultado:

Elegir o eliminar columnas en función de su posición en la tabla

En muchos escenarios, donde existe una probabilidad alta de que los nombres de los campos se modifiquen en el origen, pero no su posición dentro de la tabla, eliminar o seleccionar columnas en función de su posición es más seguro que referenciarlas por su nombre dentro del código. El uso de la función Table.ColumnNames para obtener la lista de todos los nombres de las columnas y de List.Range para recuperar un subconjunto de estas, permite seleccionar cualquier subconjunto de columnas en base a su posición. Vamos a ver algún ejemplo del uso de estas 2 funciones.

Para eliminar la primera columna de la tabla podemos usar cualquiera de estas dos expresiones:

= Table.RemoveColumns(Origen, List.First(Table.ColumnNames(Origen)))
= Table.RemoveColumns(Origen, Table.ColumnNames(Origen){0})

Si usamos List.FirstN podemos especificar el número de columnas a borrar desde la primera de la tabla. Por ejemplo, la siguiente fórmula eliminaría las 3 primeras columnas:

= Table.RemoveColumns(Origen, List.FirstN(Table.ColumnNames(Origen), 3))

De forma análoga, podemos utilizar List.Last y List.LastN para eliminar el número de columnas deseadas situadas al final de la tabla. Por último, List.Range recibe una lista como primer parámetro, un desplazamiento de base cero como segundo, y el recuento de columnas a devolver como tercero. Podemos utilizar el siguiente código para elegir las columnas segunda y tercera de la tabla:

= Table.SelectColumns(Source, List.Range(Table.ColumnNames(Source), 1, 2))

Cambiar el tipo de dato de las columnas de forma segura en Power Query

El factor número uno para los fallos de actualización debido a cambios en el nombre de las columnas es el paso de «Tipo cambiado». Este se añade automáticamente en muchas ocasiones, sobre todo cuando utilizamos archivos como origen de datos, y tras aplicar el paso de promover encabezados. Power Query está diseñado para detectar implícitamente los tipos de todas las columnas de la tabla. Esto es así, porque sin la definición correcta de los tipos de dato en algunas columnas, no podríamos realizar operaciones aritméticas con columnas numéricas o aplicar las funciones de inteligencia del tiempo usando nuestras columnas de fecha. El problema que surge con esta transformación es similar al visto en la primera parte de este artículo, cualquier mínimo cambio en los nombres de las columnas o la eliminación de las mismas en la fuente de datos provocará errores de actualización, al quedar los nombres de las columnas codificados dentro de la función Table.TransformColumnTypes.

La solución más simple y común para evitar este problema es eliminar el paso de «Tipo cambiado» y especificar manualmente el tipo de dato de solamente las columnas que lo necesiten (numéricas y de fecha), con el objetivo de referenciar el menor número de columnas posible en nuestro código. De la misma forma, es recomendable definir los tipos lo más tarde posible en el proceso, principalmente por la posible mejora del rendimiento a la hora de actualizar el modelo. Debido a que esta transformación requiere movilizar ciertos recursos de cálculo del motor de M, si vamos a reducir considerablemente el número de registros mediante la aplicación de filtros, siempre será preferible definir el tipo de dato de las columnas tras esta reducción.

También, podemos configurar Power Query para que no realice automáticamente la detección y especificación del tipo de dato de cada columna. Dentro de las opciones de Power BI, en el apartado «Carga de datos» del archivo actual, debemos deseleccionar la opción de «Detectar automáticamente encabezados y tipos de columna para orígenes no estructurados»:

Desafortunadamente, no existe dicha opción en la parte de las opciones globales, por lo que hay que repetir el proceso para cada archivo pbix.

Para la función Table.TransformColumnTypes no existe el parámetro opcional MissingField que hemos usado dentro de la función Table.SelectColumns para ignorar este tipo de errores de actualización, pero como veremos en el próximo artículo, podemos imitar el comportamiento de MissingField.Ignore mediante el uso de List.Accumulate y la creación de una función personalizada en Power Query.