¡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)
in
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))
then 1
else
Number.RoundDown((Date.DayOfYear([Fecha])-(Date.DayOfWeek([Fecha], Day.Monday)+1)+10)/7)), {{"SemanaISO", Int64.Type}})
in
#"Personalizado agregado 11"
Y con esto llegamos al final de la sesión que presenté en el GPPB 2023. Aquí abajo os dejo el video por si queréis verlo.
