Dataflows: Dimensión tiempo

¡Jueves de post! Hoy os traigo la sesión que realicé el fin de semana en el Global Power Platform Bootcamp 2023. Qué no sé si sabéis pero fue un fin de semana espectacular para la comunidad de Power Platform. Tuve el honor de poder colaborar con las comunidades de Colombia, Nicaragua, Paraguay, Lima y Euskadi. Como sabéis, desde comienzos de año, soy co-organizador del grupo de Power Platform Euskadi, me gustaría agradecer a todo el equipo el que me hayan ayudado para poder estar en todas y cada una de las comunidades anteriores.

Gracias: Inés Pascual, Cristina Tarabini-Castellani, Nagore Landa, David Uyarra y Carlos Cantero

Ahora, vamos con la sesión:

¿Qué es un dataflows?

Un dataflow es una colección de tabla/s que se crean y
administran en las áreas de trabajo del servicio Power BI, es decir, en la
nube. Además pueden ser reutilizados en otros conjuntos de datos ahorrando al
usuario tiempo en el desarrollo de los modelos.

De otra forma, los dataflows son la herramienta de ETL en la nube, es decir,
nuestro Power Query Online que nos permite realizar todas nuestras
transformaciones para su posterior uso.

Ventajas de uso

Crear una lógica de transformación reutilizable que puedan
compartir muchos conjuntos de datos e informes en Power BI à Por ejemplo, nuestra
querida dimensión tiempo.

Crear una única fuente de la verdad al obligar a los analistas a conectarse a
los flujos de datos, en lugar de conectarse a los sistemas subyacentes y que
cada uno haga su proceso de ETL à
O lo que es lo mismo, creación de un almacén de datos

Impedir que los analistas tengan acceso directo al origen de datos subyacente. à Tener separado el
origen de datos del conjunto de datos.

Nos permite un control sencillo del Query Folding (el
plegado de consultas) à
Es decir, es la capacidad de una consulta de Power Query para generar una única
instrucción de consulta para recuperar y transformar los datos de origen.

….. Muchísimas más …..

Paso 1: Creamos el dataflow

Paso 2: Creamos una consulta en blanco para calcular
la fecha fin del año actual. Para ello:

let

//Extraigo el año del sistema

Año = Date.Year(DateTime.LocalNow()),

// Convierto en texto y concateno el año al texto 31/12/

Fecha = Text.From("31/12/") & Text.From(Año),

// Convierto en fecha el texto del paso anterior

FechaFin = Date.FromText(Fecha)

FechaFin

Paso 3: Creamos la fecha inicio. Podemos hacerlo de
la misma manera que la fecha fin o creamos un parámetro. A mí me gusta hacerlo
de esta manera, por el siguiente motivo. La fecha inicio la trato como fija en
el dataflow, y en función la necesidad del modelo, traigo más o menos
información, en cambio la fecha fin, tiene que ser dinámica, sino cada año
habría que modificar el dataflow.

Paso 4: Ahora lo que hacemos es generar una consulta
en blanco con el número de días existentes entre la fecha inicio y fecha fin.
¿Cómo lo calculamos? Pues muy sencillo, con la función Duration.Days que nos
extrae el número de días entre dos fechas.

¡OJO! Falta sumar uno ya que la diferencia entre las
dos fechas no incluye a una de ellas y necesitamos los días totales

let

// Calculamos el intervalo de dias

DiasIntervalo = Duration.Days( FechaFin -
FechaInicio) +1 ,

Paso 5: Creamos una lista con todos los valores que
existen desde la fecha de inicio a la fecha de fin. Para ello usamos la función
List.Dates:

La función: #durations (0,0,0,0)

// listamos las fechas del intervalo

Fecha = List.Dates(FechaInicio,DiasIntervalo,
#duration(1,0,0,0)),

Paso 6: La lista que acabamos de crear, debemos
transformarla en una tabla. Para ello podemos hacerlo de dos maneras distintas.

1.
Con la interfaz de usuario

2.
Con código M.

// Convertimos en tabla la lista

#"Convertido en tabla" =
Table.FromList(Fecha, Splitter.SplitByNothing(), null, null,
ExtraValues.Error),

Paso 7: Renombramos la columna a Fecha

// Cambiamos el nombre de la columna 1

#"Columnas con nombre
cambiado" = Table.RenameColumns(#"Convertido en tabla", {{"Column1", "Fecha"}}),

Paso 8: Transformamos el tipo de dato de la columna
ya que Power Query nos lo ha categorizado como Cualquiera y nosotros queremos
que sea tipo fecha.

// Cambiamos el tipo de la columna

#"Tipo de columna
cambiado" = Table.TransformColumnTypes(#"Columnas con nombre
cambiado", {{"Fecha", type date}}),

Paso 9: Extraemos el año

Aquí, vamos a modificar el código M que nos ha
generado Power Query, ya que nos ha categorizado la columna año como número
decimal, y el año, en mi pueblo es un número entero de toda la vida. Así además
ayudamos al motor para la compresión. Para ello:

// Extraemos el año

#"Año insertado" =
Table.AddColumn(#"Tipo de columna cambiado", "Año", each
Date.Year([Fecha]), Int64.Type),

Paso 10: Una vez ya tenemos el año, generamos la
columna del mes. Hacemos como con la columna de año y modificamos el tipo de
dato.

// Extraemos el mes

#"Mes insertado" =
Table.AddColumn(#"Año insertado", "Mes", each
Date.Month([Fecha]), Int64.Type),

Paso 11: Extraemos el nombre del mes

// Insertamos el nombre del mes

#"Nombre del mes
insertado" = Table.AddColumn(#"Mes insertado", "Nombre
del mes", each
Date.MonthName([Fecha]), type nullable text),

Paso 12: En el caso que queramos colocar el nombre
del mes en otra cultura, por ejemplo, inglés, basta con añadirle al código M lo
siguiente:

// Insertamos el nombre del mes en ingles

#"Nombre del mes insertado
1" = Table.AddColumn(#"Nombre del mes insertado", "Nombre
del mes ingles", each
Date.MonthName([Fecha],"en-US"), type
nullable text),

Paso 13: Extraemos el número del día.

// Insertamos el número del día

#"Día insertado" =
Table.AddColumn(#"Nombre del mes insertado 1", "Día", each
Date.Day([Fecha]), Int64.Type),

Paso 14: Extremos el nombre del día desde la columna
Fecha:

// Insertamos el nombre del día

#"Nombre del día
insertado" = Table.AddColumn(#"Día insertado", "Nombre
del día", each
Date.DayOfWeekName([Fecha]), type nullable text),

Paso 15: Extraemos el nombre del día en inglés

// Insertamos el nombre del día en inglés

#"Nombre del día insertado
1" = Table.AddColumn(#"Nombre del día insertado", "Nombre
del día ingles", each
Date.DayOfWeekName([Fecha],"en-US"), type
nullable text),

Paso 16: Ahora, una buena práctica, es convertir la
fecha en un ID único de número entero, de manera que la compresión sea mejor
que con columnas de tipo Fecha o Fecha y Hora. Para ello al año debemos
multiplicarlo por 10.000, el mes por 100 y sumar el año, mes y día.

// Convertimos la fecha en un número
entero multiplicando y sumando el año * 10000, el mes * 100 y el día * 1

#"Personalizado agregado"
= Table.TransformColumnTypes(Table.AddColumn(#"Nombre del día insertado
1", "IdFecha", each
[Año]*10000+[Mes]*100+[Día]),
{{"IdFecha", Int64.Type}}),

Paso 17: Ahora, vamos a obtener el número entero que
corresponde al campo fecha, de manera que nos sirva a futuro para calcular
desviaciones de manera sencilla, por ejemplo, saber si la fecha es una fecha
pasada o futura. Como sabemos, las columnas de fecha, para Power Query, son
números enteros y la hora son números decimales.

// Obtenemos el número entero de la fecha
y lo nombramos como IdFechaEntero

#"Personalizado agregado
1" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado", "IdFechaEntero", each
Number.From([Fecha])), {{"IdFechaEntero",
Int64.Type}}),

Paso 18: Ahora, calculamos el día actual, es decir,
el día de hoy para ello. ¡Atención! Depende de la franja horaria en la que nos
encontremos, hay un desvío con el número obtenido. Si estamos con la hora por
debajo de las 12:00 del mediodía, Power Query lo redondea hacia abajo pero si
hemos sobrepasado las 12:00 del mediodía Power Query lo redondea hacia arriba (
y esto no nos interesa ). ¿Cómo lo solucionamos? Basta con modificar el código
M para que redondee hacia debajo de manera que si pasamos las 12:00 del
mediodía no nos distorsione.

// Calculamos el día actual, redondeando
hacia abajo de manera que evitemos la desviación con la hora

#"Personalizado agregado
2" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado 1", "Hoy", each
Number.RoundDown(Number.From(DateTime.LocalNow()))), {{"Hoy",
Int64.Type}}),

Paso 19: Obtenemos el número del día de la semana.
Sumamos 1 ya que sino empieza en 0 el lunes y no en 1 como queremos.

// Insertamos el día de la semana y le
sumamos 1 para que empiece el lunes en 1

#"Día de la semana
insertado" = Table.AddColumn(#"Personalizado agregado 2", "Día
de la semana", each
Date.DayOfWeek([Fecha])+1, Int64.Type),

Paso 20: Calculamos si es laborable o no. Partiendo
de que para este modelo de Lunes a Viernes son Laborables mientras que sábado y
domingo son festivos. Para ello añadimos una columna condicional.

// Calculamos si es laborale o festivo
(fin de semana)

#"Columna condicional
insertada" = Table.AddColumn(#"Día de la semana insertado", "Laborable", each if
[Día de la semana] <= 5 then "Laborable" else "Festivo", type text),

Paso 21: Calculamos el trimestre del año. Power
Query nos lo crea como número, por lo que le cambiamos el formato a Int64.Type

// Insertamos el trimestre del año

#"Trimestre insertado" =
Table.AddColumn(#"Columna condicional insertada", "Trimestre", each
Date.QuarterOfYear([Fecha]), Int64.Type),

Paso 22: Si queremos el trimestre con el T1, T2,
etc.,… Debemos concatenar a la columna
de Trimestre la letra T. Para ello:

// Insertamos el trimestre del año
concatenando la T y convirtiendo en texto

#"Trimestre insertado 1"
= Table.AddColumn(#"Trimestre insertado", "Trimestre
TX", each "T"
& Text.From(Date.QuarterOfYear([Fecha])), type text),

Paso 23: Si queremos calcular el trimestre del año,
es tan sencillo como modificar el código M del paso anterior:

// Insertamos el trimestre del año
concatenando la Q y convirtiendo en texto

#"Trimestre insertado 2"
= Table.AddColumn(#"Trimestre insertado 1", "Trimestre
QX", each "Q"
& Text.From(Date.QuarterOfYear([Fecha])), type text),

Paso 24: Calculamos el año actual, de cara a
calcular los posibles desvíos y lo colocamos como Int64.

// Calculamos el año actual

#"Personalizado agregado
3" = Table.TransformColumnTypes(Table.AddColumn(#"Trimestre insertado
2", "AñoActual", each
Date.Year(DateTime.LocalNow())), {{"AñoActual",
Int64.Type}}),

Paso 25: Hacemos lo mismo para el mes actual.

// Calculamos el mes actual

#"Personalizado agregado
4" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado 3", "MesActual", each
Date.Month(DateTime.LocalNow())), {{"MesActual",
Int64.Type}}),

Paso 26: Realizamos el mismo procedimiento para el
cálculo del día actual.

// Calculamos el día actual

#"Personalizado agregado
5" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado 4", "DiaActual", each
Date.Day(DateTime.LocalNow())), {{"DiaActual",
Int64.Type}}),

Paso 27: Calculamos el desvío del año. Esto nos va a
aportar mucho dinamismo en los filtros. Para ello, a la columna año le restamos
la columna de año actual.

// Calculamos el desvío del año

#"Personalizado agregado
6" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado 5", "DesvioAño", each
[Año] - [AñoActual]), {{"DesvioAño",
Int64.Type}}),

Paso 28: Calculamos el desvío del mes. Esto nos va a
aportar mucho dinamismo en los filtros. Para ello, a la columna mes le restamos
la columna de año actual y SUMAMOS la columna DesvíoAño*12

// Calculamos el desvío del mes

#"Personalizado agregado
7" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado 6", "DesvioMes", each
[Mes]-[MesActual]+([DesvioAño]*12)), {{"DesvioMes",
Int64.Type}}),

Paso 29: Calculamos el desvío del día restando a la
columna IdFechaEntero la columna Hoy.

// Calculamos el desvío del día

#"Personalizado agregado
8" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado 7", "DesvioDia", each
[IdFechaEntero] - [Hoy]), {{"DesvioDia",
Int64.Type}}),

Paso 30: Calculamos trimestre actual que nos va a
servir para realizar los cálculos de desvíos

// Calculamos el trimestre actual

#"Personalizado agregado
9" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado 8", "TrimestreActual", each
Date.QuarterOfYear(DateTime.LocalNow())), {{"TrimestreActual",
Int64.Type}}),

Paso 31: Calculamos el desvío del trimestre. Esto
nos va a aportar mucho dinamismo en los filtros. Para ello, a la columna trimestre
le restamos la columna de trimestre actual y SUMAMOS la columna DesvíoAño*4

// Calculamos el desvío del trimestre

#"Personalizado agregado
10" = Table.TransformColumnTypes(Table.AddColumn(#"Personalizado
agregado 9", "DesvioTrimestre", each
[Trimestre]-[TrimestreActual]+([DesvioAño]*4)),
{{"DesvioTrimestre",
Int64.Type}}),

Paso 32: Insertamos mediante la interfaz de usuario
la semana

// Insertamos la semana actual mediante la
interfaz

#"Semana del año
insertada" = Table.AddColumn(#"Personalizado agregado 10", "Semana
del año", each
Date.WeekOfYear([Fecha]), Int64.Type),

Paso 33: Calculamos la semana ISO, mediante una
adaptación de la fórmula conocida como Ron de Bruin.

// Insertamos la semana ISO (Adaptación de
la fórmula de Ron de Bruin)

#"Personalizado agregado
11" = Table.TransformColumnTypes(Table.AddColumn(#"Semana del año
insertada", "SemanaISO", each if
Number.RoundDown((Date.DayOfYear([Fecha])-(Date.DayOfWeek([Fecha],
Day.Monday)+1)+10)/7)=0

then

Number.RoundDown((Date.DayOfYear(#date(Date.Year([Fecha])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Fecha])-1,12,31),
Day.Monday)+1)+10)/7)

else if

(Number.RoundDown((Date.DayOfYear([Fecha])-(Date.DayOfWeek([Fecha],
Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([Fecha]),12,31),
Day.Monday)+1<4))