Carga de fechas en Excel - consulta a página WEB con Power Query

[Gabriel Raigosa]

Hola:

El la siguente pagina WEB:

List of states and territories of the United States - Wikipedia

en.wikipedia.org

esta esta tabla:



En la campo "Ratification or admission[C]" se ven las fechas asi "Mes texto dia #, año", miremos Alabama "Dec 14, 1819".

hago la consulta con Power query y esto obtengo (vista en el editor):



Campo texto (ABC), el que transformo a fecha, quedando así:



dia-mes-año ( fecha corta), la configuracion de fecha en mi sistema es dd-MM-aaaa



Luego cierro y cargo los datos a Excel y obtengo esto:



Algunas fechas llegan mal.

En las opciones de consulta del Power Query he ensayado varias configuraciones regionales



Y no logro que las fechas lleguen bien.

¿Alguien saben como puedo hacer para que las fechas lleguen bien?

Les adjunto el archivo con el que estoy probando.

¿De que forma se puede resolver este caso?, me parece que alguna vez lo resolví con una lista de meses "Ingles - Español" y varios pasos para traducirlos.

Cual seria la mejor manera de resolver este problema.

Gracias.

 

[Héctor Miguel]

no logro que las fechas lleguen bien.
Cual seria la mejor manera de resolver este problema

el "problema" lo causan las fechas anteriores a 1900

op1: deja esa columna como texto
op2: cambia al sistema de fechas 1904 (compatible con mac) y un formato numerico (mas) "personalizado"

 

[Gabriel Raigosa]

el "problema" lo causan las fechas anteriores a 1900

OK,

Gracias por el dato, con esto queda mas que claro el porque del problema.

Me quedo con la opción de dejarlo como texto, ya que no lo necesito para calculos.

Gracias.

 

[Héctor Miguel]

 

[Cacho R]

Cual seria la mejor manera de resolver este problema.

Hola! Gabriel
Si bien ya tienes una solución, te comentaré que yo tengo la misma configuración regional que tú y te aseguro que no es necesario "retocar" ninguna configuración:

• Ni la regional de la PC,
• Ni la de tu instalación del PQ

De hecho es muy sencillo y te lo comentaré para futuras situaciones similares. Mira:

let
    Data = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States")){0}[Data],
    Quito.2.filas = Table.Skip(Data,2),
    Valor.reemplazado = Table.ReplaceValue(Quito.2.filas,",","",Replacer.ReplaceText,{"Ratification or admission[C]", "Population [D][14]", "Total area[15] mi2", "Total area[15] km2", "Land area[15] mi2", "Land area[15] km2", "Water area[15] mi2", "Water area[15] km2"}),
    Tipos.cambiados = Table.TransformColumnTypes(Valor.reemplazado,{{"Ratification or admission[C]", type date}, {"Population [D][14]", Int64.Type}, {"Total area[15] mi2", Int64.Type}, {"Total area[15] km2", Int64.Type}, {"Land area[15] mi2", Int64.Type}, {"Land area[15] km2", Int64.Type}, {"Water area[15] mi2", Int64.Type}, {"Water area[15] km2", Int64.Type}, {"Number of Reps.", Int64.Type}}, "en-US")
in
    Tipos.cambiados

Es decir:

• La "coma" (como separador de miles del inglés) te está arruinando todo. Las quitamos mediante la línea: Valor.reemplazado de las columnas con fechas o numéricas.
• Seleccionas todas las columnas con fechas o numéricas y provocas una detección automática de tipos de dato (línea Tipos.cambiados).
• Nota que entre el corchete de cierre y el paréntesis de cierre (que te provoca la detección automática de tipos) he escrito: , "en-US"
• Esos pocos caracteres hacen las veces de aquellos "no deseables" cambios de configuración que aludíamos al comienzo.
• Finalmente fíjate que en la línea (o en la barra de fórmulas del Power Query) en lugar de tener: {"Ratification or admission[C]", type text} vemos {"Ratification or admission[C]", type date}

En resumen: dos "toques" manuales a la fórmula y se solucionó el problema.

Espero te sea de utilidad.
Saludos, Cacho R.

 

[Gabriel Raigosa]

Hola,

He copiado las instrucciones tal cual y el resultado es el mismo



Al usar el sistema de fechas de "1904" muestra las fechas como "Negativas"

 

[Héctor Miguel]

Al usar el sistema de fechas de "1904" muestra las fechas como "Negativas"

ademas de que las fechas ni siquiera se acercan a la fecha inidicada en la web

el "problema" sigue siendo el mismo:

el "problema" lo causan las fechas anteriores a 1900

 

[Gabriel Raigosa]

las fechas ni siquiera se acercan a la fecha inidicada en la web

Asi es, nada parecido a lo que deberia ser.

No se como queda el archivo en el PC de @Cacho R , en mi caso es como muestro en la captura.

 

[Cacho R]

Asi es, nada parecido a lo que deberia ser.

Probablemente, Gabriel, te encuentres utilizando el sistema 1904. Ello altera toda la info base: ¡Tampoco hace falta! Mira:

 

[Cacho R]

A ver, Gabriel, con lo que te pasé: ¿LLegas a ver esto?...

 

[Gabriel Raigosa]

Hola,

Dentro del Power Query se ve así:



Pero al cargar los datos al libro se ve así:



Usando el codigo enviado:

let
    Data = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States")){0}[Data],
    Quito.2.filas = Table.Skip(Data,2),
    Valor.reemplazado = Table.ReplaceValue(Quito.2.filas,",","",Replacer.ReplaceText,{"Ratification or admission[C]", "Population [D][14]", "Total area[15] mi2", "Total area[15] km2", "Land area[15] mi2", "Land area[15] km2", "Water area[15] mi2", "Water area[15] km2"}),
    Tipos.cambiados = Table.TransformColumnTypes(Valor.reemplazado,{{"Ratification or admission[C]", type date}, {"Population [D][14]", Int64.Type}, {"Total area[15] mi2", Int64.Type}, {"Total area[15] km2", Int64.Type}, {"Land area[15] mi2", Int64.Type}, {"Land area[15] km2", Int64.Type}, {"Water area[15] mi2", Int64.Type}, {"Water area[15] km2", Int64.Type}, {"Number of Reps.", Int64.Type}}, "en-US")
in
    Tipos.cambiados

 

[Gabriel Raigosa]

Lo que ya es claro es que las fechas en Excel tienen limites.

Especificaciones y límites de Excel

En Excel 2010, el tamaño máximo de una hoja de cálculo es de 1 048 576 filas por 16 384 columnas. En este artículo se describen las especificaciones y los límites de los libros, las hojas de cálculo y las características.

support.microsoft.com

Asi es no se vera como fecha el dato que está en esa WEB, la mejor opción es dejarla como texto.

 

[Cacho R]

Asi es no se vera como fecha el dato que está en esa WEB, la mejor opción es dejarla como texto.

¡Llegaste a la primera conclusión a la que quería hacerte llegar! Esto es:

• Excel tiene como límite inferior 1/Ene/1900

Ahora vamos a las cuestiones que yo supuse -erróneamente- que conocías:

• PQ no tiene esa limitación inferior, y
• El uso de un par de funciones (dentro del PQ) que te permiten manipular fechas para diversos usos: Date.DayOfYear, Date.AddYears (la aplico restando 100 años a las fechas mostradas) y Date.ToText.
• Esta última es la que te permite presentar las fechas que Excel no interpreta correctamente como texto. Nota que dicha fecha se presenta en el idioma de la Configuración Regional y con el formato que elijas y no el que te impone la página.

No dejes de mirar con atención la primera de las tablas:

• A partir de la fecha 5/marzo/1900 empiezo a restar 5 días a la fecha previa.
• ¿Todo bien?... ¡Pero no! Mira la celda A3... Esa fecha está mala... ¿Sabes por qué?... Pues porque el año 1900 no fue bisiesto.
• A consecuencia del error que comete el Excel con ese bisiesto (que no es bisiesto), PQ en la celda D3 te pone 28/feb y de allí para abajo empieza a restar 5 días.

Última observación:

• En D15 vemos 0/Enero/1900. Si fuese otro año interpretaríamos que Excel quiso decir: 31/Diciembre/1899, ¿Verdad?...
• Pero PQ te informa la "triste" realidad: debió ser 30/Diciembre/1899 con lo que "descubrimos" otro error del Excel.

Adjunto el archivo en cuestión y espero que te sirva para completar tu informe sabiendo que:

• No es necesario cambiar la configuración... de nada.
• Menos es necesario pasar al sistema 1904.
• Y que puedes presentar las fechas inglesas en el idioma de tu PC.

Saludos, Cacho R.

 

[Gabriel Raigosa]

espero que te sirva para completar tu informe

Agradezo el interes en el tema y las anotaciones.

Me queda muy claro el problema, no es un trabajo profesional o académico, resultó de una consulta casual a esa tabla y habia olvidado lo del limites de las fechas.

Gracias.

 

[Gabriel Raigosa]

Ahora vamos a las cuestiones que yo supuse -erróneamente- que conocías:

• PQ no tiene esa limitación inferior

No sabia que PQ NO tenia la limitación que si tiene la hoja de Excel y creo que eso fue lo que me confundió pues veai las fechas en PQ y no salian bien en Excel, y habia olvidado esa limitación del Excel..

Saludos.

 

[Héctor Miguel]

eso fue lo que me confundió pues veai las fechas en PQ y no salian bien en Excel, y habia olvidado esa limitación del Excel

volvemos al principio (y como bien lo finalizas en esta cita):

la mejor opción es dejarla como texto

- Early Dates in Power Query are Off by a Day

 

[Cacho R]

No sabia que PQ NO tenia la limitación que si tiene la hoja de Excel y creo que eso fue lo que me confundió...

¡Tal cual!...

 

[Dantte666]

@Cacho R espero se encuentre bien maestro, según veo hace un tiempo no anda por estos lares, le mando un abrazo, saludos a todos.

 

[silvia quispe]

@Cacho R espero se encuentre bien maestro, según veo hace un tiempo no anda por estos lares, le mando un abrazo, saludos a todos.

yo igual.. espero se encuentre bien.

saludos a todos.

 

[Dantte666]

yo igual.. espero se encuentre bien.

saludos a todos.

Veras que si, estimada @silvia quispe , el maestro es un Roble, por cierto, gusto en saludarte.