Manejo de errores (SI.ERROR) de archivos de Excel en Power BI / Power Query

Si aún no has leído las dos primeras publicaciones (Parte 1 | Parte 2) de esta serie, te invito a que lo hagas antes de leer esta.

También te recomiendo que revises esta publicación en Auditoría de Errores de Consultas para que puedas comprender mejor qué tipos de errores puedes encontrar en Power BI / Power Query.

Este es un artículosobre cómo usar el manejo de errores, similar a un IFERROR en DAX y Excel, pero para Power Query (usando su lenguaje M).

¿Cómo funciona el manejo de errores en Excel y DAX?

En Excel y en DAX tenemos la función IFERROR que funciona así:

=IFERROR( valor, valor_si_error)

Tomado directamente de la documentación oficial de DAX:

Evalúa una expresión y devuelve un valor específico si la expresión devuelve un error; de lo contrario, devuelve el valor de la expresión en sí.

Es una función bastante simple y directa en DAX y Excel, donde puedes ingresar tu fórmula en el parámetro «valor» y luego, si obtienes un error, puede definir cuál debería ser el valor de salida en el parámetro «valor_si_error» .

La idea general es que puedes «atrapar» un error y usar un valor diferente cuando encuentra un error.

¿Cómo funciona el manejo de errores en Power BI / Power Query?

En Power Query el código es algo diferente. Vamos a verlo en acción y luego hablar más sobre ello.

Imagina que tenemos un libro de Excel con una tabla como esta.

:

Lo que nos gustaría crear es una nueva columna que debe multiplicar los valores de las columnas [Price] y [Amount] para crear una nueva columna Subtotal.

Una advertencia, como probablemente puedes ver, es que esta hoja de cálculo tiene algunas celdas con errores en la columna [Price]. En el caso de que encontremos un error en la columna Precio, debemos usar el valor del [List Price] en lugar del valor del [Price].

Lo primero que debemos hacer es importar esa tabla desde Excel. Si deseas seguir adelante, puede descargar el libro haciendo clic en el botón a continuación:

Importando datos desde el Excel Workbook

Usaré Power BI Desktop para esto, pero también puedes usar Excel.

Lo primero que debemos hacer es seleccionar el conector de Excel y conectarnos a nuestro archivo:

y una vez que obtengas la ventana «Navegación» o «Navigator», puedes seleccionar la tabla que dice «Sample»:

Observa cómo hay muchos errores en esa columna [Price] solo en la vista previa. Vamos a presionar el botón «Editar» o «Edit» para que podamos ir a la ventana del Editor de Power Query.

Using error handling in Power BI /Power Query

Ahora que tenemos nuestros datos en la ventana del Power Query Editor:

lo que queremos hacer es crear una columna personalizada, por lo que simplemente vamos al menú «Agregar Columna» o «Add Column»  y presionamos «Columna Personalizada» o «Custom Column».

Allí, intentamos crear una columna simple que multiplique la columna [Price] por la columna [Amount]:

y como puedes ver, nuestra columna [Subtotal] tiene algunos errores.

Sabemos que en Excel y DAX podemos usar IFERROR, pero ¿qué podemos usar en Power Query?

Para Power Query, debemos hacer clic en modificar el código de columna personalizada (solo haz clic en el icono de engranaje que se encuentra al lado del Added Custom Step) y agregarle las siguientes piezas:

try [Price]*[Amount] otherwise [Amount]*[List Price]

Necesitamos usar las palabras clave «try» y «otherwise». Es bastante fácil de leer, pero solo dice que intentes evaluar la expresión ([Price] * [Amount]) y si eso da un error, uses la expresión definida después de la declaración de lo contrario.

El resultado de eso se verá así:

¡bastante simple! casi tan simple como la función IFERROR en DAX y Excel, donde intellisense te explica un poco cómo usar esa función, pero en Power Query necesitas entender cómo funciona esto para poder usarla. No se encuentra en ningún lugar de la Interfaz de usuario de Power Query, por lo que debes escribir el código manualmente.

Entendiendo errores

El archivo que estoy usando es bastante simple. He tenido experiencias donde algunos usuarios / clientes necesitan saber con certeza cuándo y dónde se encuentra un error específico en un libro de Excel.

Lo que sucede con Power Query es que solo marca los errores encontrados como «Error» pero ¿qué sucede si se necesita saber POR QUÉ se muestra como un error?

Volvamos a nuestra carga inicial del archivo. Recuerda que, en la mayoría de los casos, Power Query intentará agregar automáticamente un paso de «Tipo de Datos Cambiado» o «Changed Type», así que, ¿qué sucede si eliminamos ese paso?

Bueno, eliminé el paso y sigo viendo los errores y eso es porque el error no fue ocasionado por una conversión de tipo de datos, sino que es un error de origen, lo que significa que el error proviene directamente del Libro de Excel.

En archivos de Excel con vastas cantidades de filas, es difícil saber si hay algún error. El hacer un «Reemplazar errores» o «Replace Errors» no nos dirá por qué ocurrieron esos errores. NECESITAMOS saber cuál es el error de la fuente porque queremos manejar cada tipo de error de manera diferente.

Mensaje de error y motivo de error

Para averiguar cuál es la razón por la que hay un error, necesitamos usar la declaración «try» nuevamente.

Ten en cuenta que solo uso «try» y no la declaración «otherwise». Esto me dará una nueva columna con valores de registro (record). Podemos expandir esos registros:

el campo más importante de esos registros es el campo «Error», que puede ser un valor nulo o un valor de registro:

y después de expandir esa columna y eliminar algunas otras que no necesitamos, acabo con esto:

He resaltado algunas celdas del campo más importante después de todo este proceso, que es el «Message» que me dice exactamente la razón por la que esto es un error.

Más tarde, puedo usar esto para mi ventaja y enfocar errores específicos de manera diferente u obtener un informe de TODOS los errores encontrados en una serie de archivos que utiliza mi departamento / grupo. Esto es extremadamente útil si estás intentando validar todo y asegurarte de que no haya errores en la fuente.

No olvides que estos mismos principios funcionan tanto para los errores de nivel de Paso como para los valores de celda.