Formato de fecha de Power Query (Cómo + 5 escenarios complicados)

Una cosa que continuamente causa problemas a todos los profesionales de datos son las fechas. Con el tiempo, Microsoft nos ha brindado una gran cantidad de herramientas y funciones de Excel para manejar fechas, pero todavía nos sorprenden. Power Query es una herramienta para manipular y limpiar datos, y Tiene muchas funciones para gestionar fechas. El formato de fecha de Power Query es flexible y se puede convertir a muchos otros formatos numéricos y de texto.

Para esta publicación, comencemos analizando los conceptos básicos de los formatos de fecha de Power Query. Luego, una vez que hayamos comprendido eso, podemos pasar a ver algunos escenarios complicados de formatos de fecha.

Tabla de contenido

• Transformaciones simples de formato de fecha de Power Query
• Tipo de datos de fecha
• Extraer información adicional de una fecha
• Código M para transformaciones de fecha.
• Transformaciones complicadas
• Ejemplos
• Configuración regional
• Tratar con formatos de fecha desconocidos
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0043 Formatos de fecha de Power Query.zip

Tener acceso

Transformaciones simples de formato de fecha de Power Query

En esta sección, analizamos el proceso básico y las transformaciones simples para trabajar con fechas.

Tipo de datos de fecha

Cuando ingresamos nuestros datos en Power Query, primero debemos asegurarnos de que las columnas de fecha tengan el tipo de datos correcto. A veces, la detección automática de tipos de datos de Power Query falla, por lo que debemos verificar que todas las columnas tengan el formato correcto.

Para cambiar una columna específica a un formato de fecha, tenemos muchas opciones:

• Haga clic en el icono de tipo de datos en el encabezado de la columna y seleccione Fecha
• Seleccione la columna, luego haga clic en Transformar fecha de tipo de datos en la cinta
• Haga clic derecho en el encabezado de la columna, luego haga clic en Cambiar tipo Fecha
• Cambie el tipo de datos aplicado en el código M para escribir fecha

Cambiar el tipo de datos es el paso más importante. Hasta que las columnas se reconozcan como fechas, Power Query no puede usarlas como fechas.

Extraer información adicional de una fecha

De una columna de fecha podemos extraer otra información. Los formatos de fecha de Power Query disponibles en la cinta se muestran a continuación.

Están disponibles en las cintas Transformar y Agregar columnas de Power Query. La cinta Transformar convierte la columna seleccionada mientras que Agregar columna crea una nueva columna basada en la selección.

Los siguientes son ejemplos de los tipos de información que podemos extraer de la columna de fecha (Año, Días del mes, Semana del año, Nombre del día, Día del año)

Código M para transformaciones de fecha.

Cada una de las transformaciones anteriores aplica una función de Power Query para extraer la información de la fecha. Por ejemplo, el código M para agregar las columnas Año y Día del año es.

Año

= Table.AddColumn(#"Changed Type", "Year", each Date.Year([#"Date (YYYY-MM-DD)"]), Int64.Type)

La función Fecha.Año ​​extrae el Año de una fecha.

Día del año

= Table.AddColumn(#"Inserted Day Name", "Day of Year", each Date.DayOfYear([#"Date (YYYY-MM-DD)"]), Int64.Type)

La función Date.DayOfYear calcula el número del día del año a partir de una fecha.

Información adicional:

• Obtenga más información sobre las funciones de Power Query aquí: Fórmulas de Power Query (cómo usarlas y errores que se deben evitar) .
• Los detalles de todas las funciones de fecha están aquí: https://learn.microsoft.com/en-us/powerquery-m/date-functions

Transformaciones complicadas

El mayor problema al que nos enfrentamos es intentar cambiar inicialmente una columna a un tipo de fecha. A menudo esto ocurre cuando se trabaja con formatos de fecha de diferentes países.

El formato de fecha utilizado en EE. UU. es mes – día – año . Sin embargo, en el Reino Unido es día – mes – año. Luego está el formato ISO 8601, que es año – mes – día .

Personalmente, prefiero el formato año – mes – día, ya que se puede ordenar correctamente incluso cuando es texto o numérico. Pero no puedo imaginar que el gobierno del Reino Unido esté a punto de cambiar algo en mi cuenta.

A veces, Power Query puede confundirse si utilizamos datos que contienen fechas en un formato diferente a nuestra configuración local. Por ejemplo, si el valor de la celda es 02/01/03, podría ser:

• 2 de enero de 2003 – formato estadounidense
• 1 de febrero de 2003: formato del Reino Unido
• 3 de febrero de 2001: formato ISO 8601

En esta sección, analizamos cómo forzamos a Power Query a comprender las fechas, incluso si se utilizan formatos de fecha de diferentes países.

Ejemplos

Mire la captura de pantalla a continuación. Muestra fechas en cinco formatos diferentes (sin ningún tipo de datos aplicado). He incluido el formato en el encabezado para que pueda ver cuál debe ser el formato de fecha.

NOTA: Vivo en el Reino Unido. Mi configuración regional está configurada en inglés (Reino Unido), por lo que dd.mm.aaaa es el formato esperado para las fechas. Dependiendo de la configuración de su ubicación, puede obtener resultados diferentes a los que se muestran a continuación. .

Con Power Query, traduzco cada una de las columnas a un tipo de datos de fecha.

• dd.mm.yyyy : como era de esperar, Power Query se complace en traducir una fecha en el formato de mi región local.
• mm.dd.yyyy : la primera fila es un error porque el valor 23.07.2017 como fecha del Reino Unido sería el día 7 del mes 23. Esta no es una fecha válida en mi región. En la segunda fila, el valor es 07/09/2017. Como formato del Reino Unido, este sería el noveno día del séptimo mes, que es una fecha válida. Sin embargo, aunque el formato es válido, el resultado es incorrecto. La fecha original está en mm. formato dd.yyyy, por lo que el valor correcto sería el séptimo día del noveno mes.
• aaaa.mm.dd : debido a que el año tiene un formato de 4 caracteres, Power Query ha adivinado correctamente que los dígitos restantes son el mes seguido del día.
• aa.mm.dd : los datos de este ejemplo incluyen el año con solo dos dígitos. Power Query no puede determinar qué dígitos se relacionan con el año, el mes o el día. Ha aplicado mi formato local. Sin embargo, esto ha mezclado el día y el año, lo que significa que cada valor es incorrecto. Este es el resultado más peligroso, ya que si no nos damos cuenta y continuamos, podría causar estragos más adelante.
• aaaammdd : no hay separadores en este ejemplo. Power Query no ha reconocido esto como ninguna fecha, por lo que cada valor se muestra como un error.

Power Query nos brinda muchas herramientas para resolver cada uno de estos formatos de fecha.

Configuración regional

Si la fecha tiene un formato de país válido, podemos indicarle explícitamente a Power Query qué país usar.

Haga clic en el icono de tipo de datos y seleccione Usando configuración regional... en el menú.

En el cuadro de diálogo Cambiar tipo con configuración regional , seleccione las siguientes opciones:

• Tipo de datos: Fecha
• Configuración regional: la región para el formato de fecha (para mm.dd.yyyy , seleccioné inglés (Estados Unidos) )

Luego haga clic en Aceptar para cerrar la ventana.

Power Query ahora muestra la fecha correcta.

En la sección anterior, Power Query ya obtuvo el resultado correcto para el formato aaaa.mm.dd, por lo que podemos pasar a la siguiente columna.

Para cambiar el formato aa.mm.dd, podemos seleccionar inglés (Canadá) como formato de fecha local. En la parte inferior de la ventana, se muestra una muestra de los formatos que se reconocen correctamente.

El formato aaaa-mm-dd es el primer elemento de la lista de entradas de muestra (que se muestra en el cuadro morado de arriba). Este orden coincide con nuestros datos de ejemplo. Al seleccionar esta opción se generan los valores correctos para esta columna. Power Query es lo suficientemente inteligente como para reconocer los separadores y crear la fecha correcta.

Puede que sea necesario explorar un poco para descubrir qué configuración regional (si corresponde) coincide con el formato que tenemos.

Si bien el formato aaaammdd está en el mismo orden que el inglés (Canadá), debido a la falta de separadores, Power Query no reconoce esto como una fecha. Volveremos a este ejemplo en un momento.

Cambiar la configuración regional del libro de trabajo

Si un libro va a tener muchas fechas en un formato de región específico, podemos cambiar la configuración regional para todo el libro.

Haga clic en Opciones de archivo y configuración Opciones de consulta

En el cuadro de diálogo Opciones de consulta, seleccione Configuración regional [Configuración regional requerida] Aceptar

Esta configuración se aplica únicamente al libro actual.

Una vez configurada la configuración regional, solo necesitamos usar el tipo de datos de fecha estándar y Power Query aplicará esa configuración regional a cada campo.

Tratar con formatos de fecha desconocidos

Nos quedamos con el complicado formato aaaammdd. Hay algunas opciones que podemos probar aquí. Las dos que vamos a ver son:

• Fórmulas de consulta de energía
• Columna de ejemplos

Usar fórmulas de Power Query

Nuestra fecha está en un formato estándar, por lo que podemos aplicar fórmulas básicas de código M para calcular el resultado.

Haga clic en Agregar columna Columna personalizada

En el cuadro de diálogo Columna personalizada, ingresé la siguiente fórmula.

=Text.End(Text.From([yyyymmdd]),2)  "/"      Text.Middle(Text.From([yyyymmdd]),4,2)      "/"  Text.Start(Text.From([yyyymmdd]),4)

Esto se construye usando 4 funciones:

• Text.From(): convierte el valor del campo en texto
• Text.Start(): similar a la función IZQUIERDA de Excel para devolver los primeros caracteres de una cadena de texto
• Text.End() – similar a la función DERECHA de Excel para devolver los últimos caracteres de una cadena de texto
• Text.Middle(): similar a la función MID de Excel para extraer caracteres del medio de una cadena de texto

Con esta fórmula, hemos construido una cadena de texto que genera la fecha en formato del Reino Unido. Power Query ahora puede reconocerla fácilmente como una fecha.

Esta fórmula es específica para este escenario. Cambie la fórmula para que coincida con sus requisitos.

Usando columnas de ejemplos

Columna de ejemplos es una función que ayuda a estandarizar formatos con estructuras complejas.

Seleccione la columna y luego haga clic en Agregar columna Columna de ejemplos (menú desplegable) De selección

En la columna del extremo derecho, podemos comenzar a escribir la fecha que queremos ver y luego presionar Enter . Para el valor 20170723 , ingresé 23/07/2017 como se muestra en la captura de pantalla a continuación.

Power Query hace su magia y crea valores para todas nuestras filas. Compruebe que sean correctos. Si no, corrija los que estén incorrectos; Cada vez que proporcionamos más detalles, Power Query ajusta su transformación sugerida.

Esta es una herramienta avanzada. Si no hay suficiente estandarización en el valor, Power Query puede calcular fácilmente los resultados incorrectos. Pero para nuestro escenario, es razonablemente sencillo.

Una vez satisfecho con el formato, haga clic en Aceptar .

Conclusión

Hemos visto cómo utilizar el formato de fecha de Power Query para extraer información adicional de una fecha. Estas son transformaciones rápidas y fáciles. Sin embargo, antes de que podamos realizar esas transformaciones, necesitamos que la columna sea del tipo de datos de fecha.

Usando diferentes formatos de fecha, pudimos obtener fechas estandarizadas de todos ellos. Por lo tanto, no importa de dónde provengan los datos, podemos convertirlos y usarlos en nuestra región local.

---