Power Query no convierte «1:01:05 p. m.» a hora: el desplegable ofrece culturas que luego no sabe leer

Nuevo caso de la comunidad, y de los que dejan mal sabor: una columna de horas que parece limpia y que Power Query se niega a convertir. El fichero lo escupe un equipo de medición y la hora llega como texto, con esta pinta:

``
1:01:05 p. m..934
`

Oswaldo llegó al grupo con los deberes hechos. Había encontrado un carácter 160 (NBSP, el espacio duro) metido dentro del designador, lo había reemplazado, y se había asegurado de que no quedaba nada raro añadiendo una columna con el código de cada carácter:

`
= Table.AddColumn(Origen, "Códigos", each List.Transform(Text.ToList([Hora]), Character.ToNumber))
`

Aun así, el cambio de tipo a Hora seguía fallando. Y ahí aparece lo que convierte esto en algo más que un problema de limpieza: probó también Cambiar tipo → Con configuración regional y eligió es-PA, que es su cultura y que en el propio desplegable anuncia el formato hh:mm:ss a. m., exactamente el que tenía entre manos. Devuelve null.

Sobre la mesa quedaron tres explicaciones. Rafa apuntó al espacio: a. m. lleva un espacio en medio, y por eso no se reconoce. Oswaldo defendía que es-PA tenía que valer, porque el formato de ejemplo que enseña la interfaz era el suyo. Y la tercera, la que acabó ganando: el designador que cada cultura acepta de verdad es más estrecho que el que la interfaz enseña.

Separarlas exigía medirlo, así que se montó en Power Query una tabla con las 23 culturas es-* y cuatro formas de escribir el mismo dato, pidiendo la conversión en todas las combinaciones:

| Designador | Culturas que lo convierten |
|---|---|
| pm | Todas menos es-PA, es-PR y es-US |
| pm. | Todas menos es-PA, es-PR y es-US |
| p. m. | Solo es-MX |
| p.m. | Ninguna |

Oswaldo lo verificó en su propio fichero: con es-MX, y solo con es-MX, la conversión salía.

El cuadro enseña las tres cosas a la vez. Que el espacio no era el villano: p. m. se convierte perfectamente, pero solo en una cultura de veintitrés. Que es-PA no falla por culpa de quien la eligió, sino que ofrece un formato que después no sabe leer — es la misma casilla que promete hh:mm:ss a. m. y devuelve null en las cuatro escrituras. Y que la variante sin espacios, p.m., no la lee absolutamente nadie.

Ese último detalle es el que cierra el caso, porque es justo donde estaba metido él sin saberlo. Al abrir su libro, el texto guardado sigue siendo 1:01:05 p. + espacio duro + m..934: diecisiete caracteres, con el NBSP todavía dentro. Su consulta llevaba un paso que lo borraba:

`
= Table.ReplaceValue(Origen, "#(00A0)", "", Replacer.ReplaceText, {"Hora"})
`

Y borrarlo, en lugar de sustituirlo por un espacio normal, es lo que dejaba el dato en p.m.: la única escritura que no lee ninguna cultura. Estaba limpiando el texto hacia el único sitio sin salida.

De modo que la salida buena no es afinar la cultura, es no depender de ella. Normalizar el designador a AM o PM y convertir con en-US, que es lo que el propio Oswaldo ya había comprobado que funcionaba:

`
= Table.ReplaceValue(Origen, "a. m.", "AM", Replacer.ReplaceText, {"Hora"})
`

Un par de cosas que merece la pena llevarse. La primera, el truco de los códigos de carácter: Text.ToList más Character.ToNumber` te enseña qué hay dentro de un texto de verdad, y ahí saltan a la vista el 160, los tabuladores y los guiones raros que no se ven mirando la celda. La segunda, más incómoda: el formato que muestra el desplegable de configuración regional no es una promesa de que se vaya a parsear. Son dos listas distintas, la de lo que la interfaz ofrece y la de lo que el motor acepta, y no hay forma de saber dónde no coinciden salvo probándolo.

Y la tercera, que es la de siempre: cuando un dato viene sucio, limpiarlo mal se parece muchísimo a limpiarlo bien hasta que dejas de mirar la celda y miras los caracteres.

Cuando el desplegable te ofrece algo que luego no sabe leer

Hay errores de Power Query que se arreglan leyendo el mensaje, y hay otros en los que el mensaje no ayuda porque el problema no está en tu dato: está en que la interfaz te ha ofrecido una opción que el motor no soporta. Este caso de la comunidad es del segundo tipo, y acabó con una tabla de pruebas de 23 culturas para poder demostrarlo.

El problema

Un equipo de medición exporta un fichero con la hora guardada como texto, con este aspecto:

1:01:05 p. m..934

El cambio de tipo a Hora falla. Lo primero que se sospecha, con razón, es basura invisible dentro del texto, y efectivamente la había: un carácter 160, el espacio duro o NBSP, escondido entre p. y m.. Pero una vez localizado y tratado, la conversión seguía sin salir.

El siguiente movimiento es el que todo el mundo hace: Cambiar tipo → Con configuración regional, eligiendo la cultura propia. Y aquí está el fondo del asunto. La cultura es-PA aparece en el desplegable anunciando el formato hh:mm:ss a. m., que es exactamente el formato del dato. Se elige, y devuelve null.

Cómo se separó la causa real

Circulaban tres explicaciones a la vez: que el espacio dentro de a. m. era el culpable, que es-PA debía funcionar porque su formato de ejemplo coincidía, y que el designador que cada cultura acepta realmente es más estrecho que el que la interfaz enseña.

Para elegir entre ellas no vale discutir, hay que medir. Se montó en Power Query una tabla que cruza las 23 culturas es-* con cuatro maneras de escribir el mismo dato, y se pidió la conversión en todas las combinaciones. El resultado:

DesignadorCulturas que lo convierten
pmTodas menos es-PA, es-PR y es-US
pm.Todas menos es-PA, es-PR y es-US
p. m.Solo es-MX
p.m.Ninguna

Tres conclusiones de un vistazo. El espacio no era el villano: p. m. se convierte perfectamente, pero únicamente en una cultura de veintitrés. La cultura es-PA no falla por culpa de quien la eligió, sino que ofrece un formato que después no sabe leer, y devuelve null en las cuatro escrituras. Y la variante sin espacios, p.m., no la acepta ninguna.

Ese último dato es el que cerró el caso, porque es donde había acabado el fichero sin que nadie se diera cuenta. La consulta llevaba un paso que borraba el espacio duro en lugar de sustituirlo:

= Table.ReplaceValue(Origen, "#(00A0)", "", Replacer.ReplaceText, {"Hora"})

Al borrarlo, p. m. se convertía en p.m., que es justo la única escritura que no lee absolutamente ninguna cultura. La limpieza estaba empujando el dato hacia el único sitio sin salida.

Solución paso a paso

La salida buena no es afinar la cultura hasta dar con la que acepta tu texto, porque eso ata la consulta a una configuración concreta y se rompe en cuanto el libro cambia de manos. La salida es no depender de la cultura:

  1. Mira qué hay dentro del texto de verdad, en vez de fiarte de lo que se ve en la celda. Una columna calculada con el código de cada carácter delata el 160, los tabuladores y los guiones raros:
= Table.AddColumn(Origen, "Códigos", each List.Transform(Text.ToList([Hora]), Character.ToNumber))
  1. Normaliza el designador a AM o PM, sustituyendo el texto completo en lugar de borrar caracteres sueltos:
= Table.ReplaceValue(Origen, "a. m.", "AM", Replacer.ReplaceText, {"Hora"})
  1. Convierte con en-US, que acepta AM y PM sin discusión y no depende de dónde esté el usuario que abra el fichero.

Funciones clave

  • Table.TransformColumnTypes — el paso de cambio de tipo. Admite un tercer argumento de cultura, y es ahí donde se elige la configuración regional que decide cómo se interpreta el texto.
  • Table.ReplaceValue con Replacer.ReplaceText — sustituye texto dentro de una columna. Los caracteres invisibles se escriben con su escape: el espacio duro es #(00A0).
  • Text.ToList — parte un texto en la lista de sus caracteres.
  • Character.ToNumber — devuelve el código de un carácter. Combinada con la anterior dentro de List.Transform, es la forma más rápida de ver qué contiene un texto sucio sin salir de Power Query.

Conclusión

La lección incómoda de este caso es que el formato que muestra el desplegable de configuración regional no es una promesa de que se vaya a parsear. Son dos listas distintas, la de lo que la interfaz ofrece y la de lo que el motor acepta, y no existe forma de saber dónde dejan de coincidir salvo probándolo. Por eso la recomendación práctica es normalizar el dato y usar una cultura predecible, en vez de buscar la que casualmente encaje con el texto que te llegó hoy.

Y la otra lección es más general: limpiar mal un dato se parece muchísimo a limpiarlo bien hasta que dejas de mirar la celda y miras los caracteres. Este caso salió del grupo de WhatsApp de la comunidad de InflueXcel, donde alguien planteó el problema con su fichero encima de la mesa y entre varios se acabó midiendo en lugar de suponiendo.

Más casos con estas funciones

Más contenido de Excel en InflueXcel