Optimización de Power Query para consultas masivas a API REST

Interesante caso de optimización en Power Query. Randolfo, desde Guatemala, necesitaba consultar el estado de más de 100 envíos de paquetería a través de la API REST de Cargo Expreso, y su función tardaba demasiado.

La función original usaba un patrón común pero ineficiente para este volumen: en cada iteración, convertía el JSON de respuesta con Record.ToTable + Table.Transpose, promovía encabezados y aplicaba tipos a todas las columnas. Con 100+ guías, el rendimiento se resentía notablemente.

La función original:

``
let
fxCargoExpresoEstado = (guia as text) =>
let
Td = Number.ToText(Number.From(DateTime.LocalNow()));
Url = Json.Document(Web.Contents(
"https://tracking.caexlogistics.com/TrackingMov/api/Tracking/Get?guia=" & guia & "&_=" & Td));
tracking = Url[tracking];
datosGuia = tracking[datosGuia];
#"Convertido en tabla" = Record.ToTable(datosGuia);
#"Tabla transpuesta" = Table.Transpose(#"Convertido en tabla");
#"Encabezados promovidos" = Table.PromoteHeaders(#"Tabla transpuesta");
#"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos"; ...);
#"Columnas quitadas" = Table.RemoveColumns(#"Tipo cambiado"; ...)
in
#"Columnas quitadas"
in
fxCargoExpresoEstado
`

Nacho propuso varias mejoras clave:

- Sacar el Table.TransformColumnTypes fuera de la función — aplicar tipos solo una vez al resultado final, no en cada iteración
- Evitar el combo Record.ToTable + Table.Transpose dentro del bucle — usar Table.FromRecords fuera de la función para consolidar
- Seleccionar solo los campos necesarios directamente desde el Record, en vez de traer todo y quitar columnas después

Gerson reescribió la consulta completa aplicando estas ideas y añadiendo mejoras adicionales:

- Web.Contents con parámetro Query separado (mejor para el motor de privacidad de PQ)
- try ... otherwise null en cada paso para evitar que un error en una guía rompa toda la consulta
- Table.Combine en vez de Table.ExpandTableColumn para consolidar los resultados
- Tipo de retorno explícito (as table`) en la función

El resultado: una consulta más rápida, robusta y mantenible. De 38 a 24 segundos en las pruebas, que si actualizas varias veces al día, se nota.

El ZIP incluye ambos archivos: el original de Randolfo y la versión optimizada de Gerson para comparar.

Optimizar Power Query para cientos de llamadas a una API REST

Randolfo, desde Guatemala, necesitaba consultar el estado de más de cien envíos a través de la API REST de su transportista. Su función funcionaba, pero tardaba demasiado. El hilo se convirtió en una pequeña clase magistral de rendimiento en Power Query.

El problema: trabajo repetido en cada iteración

La función original seguía un patrón muy común pero pesado cuando se repite cien veces: en cada llamada convertía el JSON con Record.ToTable y Table.Transpose, promovía encabezados y aplicaba tipos a todas las columnas. Multiplicado por cien guías, el motor se arrastraba.

let
    fxEstado = (guia as text) =>
    let
        Td = Number.ToText(Number.From(DateTime.LocalNow())),
        Url = Json.Document(Web.Contents("https://.../Get?guia=" & guia & "&_=" & Td)),
        datosGuia = Url[tracking][datosGuia],
        Tabla = Table.Transpose(Record.ToTable(datosGuia)),
        Encabezados = Table.PromoteHeaders(Tabla),
        Tipos = Table.TransformColumnTypes(Encabezados, {})
    in
        Tipos
in
    fxEstado

Las mejoras de Nacho

Nacho señaló tres cuellos de botella claros:

  • Sacar Table.TransformColumnTypes fuera de la función: aplica los tipos una sola vez al resultado final, no en cada guía.
  • Evitar el combo Record.ToTable y Table.Transpose dentro del bucle: consolida con Table.FromRecords fuera de la función.
  • Seleccionar solo los campos necesarios directamente del Record, en lugar de traerlo todo y quitar columnas después.

La reescritura de Gerson

Gerson aplicó esas ideas y sumó mejoras de robustez:

  • Web.Contents con el parámetro Query separado, que le sienta mejor al motor de privacidad de Power Query.
  • try ... otherwise null en cada paso, para que un error en una sola guía no tumbe toda la consulta.
  • Table.Combine en lugar de Table.ExpandTableColumn para juntar los resultados.
  • Tipo de retorno explícito (as table) en la función.

El resultado no es solo más rápido, también más robusto y mantenible: de 38 a 24 segundos en las pruebas. Si actualizas varias veces al día, ese recorte se nota mucho.

Funciones clave

  • Web.Contents con Query: llamadas más limpias y amigables con el motor de privacidad.
  • Table.FromRecords y Table.Combine: consolidan los resultados de forma eficiente.
  • try ... otherwise: aísla errores por guía sin romper la consulta completa.
  • Table.TransformColumnTypes una sola vez: tipar al final, no en cada iteración.

Conclusión

La lección se aplica a cualquier consulta que itere sobre muchas llamadas: haz el mínimo trabajo dentro del bucle y deja el tipado y la consolidación para el final. El fichero del caso incluye la versión original y la optimizada para compararlas paso a paso. Otro caso de la comunidad de InflueXcel donde afinar Power Query marca la diferencia entre esperar o trabajar.

Más casos con estas funciones

Más contenido de Excel en InflueXcel