Una de las múltiples ventajas de PowerBI reside en la reutilización de código y en este post vamos a aprovecharnos del fantástico lenguaje M para crear las famosas y recurrentes dimensiones fecha y tiempo sin necesidad de T-SQL, precargarlas desde ningun origen ni nada parecido. Lo primero que tenemos que hacer obviamente es crear un nuevo documento PowerBI y clicar en “Edit Queries”

Una cosa que hago yo siempre (y te recomiendo) es empezar creando los parámetros de ServerName y DatabaseName. De esta forma podrás cambiar fácilmente el origen de tus datos para testing o para lo que quieras. Para ello pulsa sobre “Manage Parameters” y create estos dos parámetros:

*Estos dos parámetros los vamos a crear porque al final crearemos un template reutilizable,
pero en ningún momento conectaremos a ningún sitio para crear nuestras dimensiones Fecha y Tiempo

Una vez creados estos parámetros (que como comento no vamos a utilizar ahora), vamos a prepararnos para generar nuestras dimensiones con M. Lo primero que debemos hacer es crearnos dos parámetros que identificarán las fechas de inicio y fin que usaremos para la dimensión Fecha. Haremos esto puesto que lo que queremos es dar la flexibilidad durante la creación o refresco de cambiar el rango temporal de nuestra dimensión.

De nuevo volveremos a hacer lo mismo, pero en este caso para crearnos nuestros parámetros “StartDate” y “EndDate”. Si lo prefieres, puedes utilizar M también para hacerlo:

#date(2016, 10, 21) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]

#date(2016, 11, 11) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]

Si prefieres hacerlo con la interfaz, deberías hacer algo como esto:

*Es importante marcar el check “Required” puesto que se consideran parámetros necesarios,
no pueden venir a null ni ser otro tipo de datos que no sea de tipo fecha

Ya tenemos preparadas los 4 parámetros que utilizaremos (aunque recuerda que no vamos a conectarnos a SQL Server para nada, los parámetros ServerName y DatabaseName los hemos creado para después).

Crear dimension Fecha

Lo primero es añadir un origen como query en blanco, que contendrá el código M de la función que generará nuestros datos:

Entramos ahora en el editor avanzado

Y escribimos el siguiente código M:

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertCalendarMonthDay = Table.AddColumn(InsertCalendarQtr, "CalendarMonthDay", each Number.ToText([MonthOfYear]) & "-" & Number.ToText([DayOfMonth]) ),
InsertDayWeek = Table.AddColumn(InsertCalendarMonthDay, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
in
CreateDateTable

Renombramos el nombre de la “Query” para que sea mas reconocible para despues por su nombre fnDimDate. Quedará por tanto de esta forma:

Ya tenemos lista la función que nos va a generar los valores en función de los 3 parámetros de entrada básicos:

• StartDate
  • Fecha de inicio desde la que se generarán las filas de nuestra dimensión fecha
• EndDate
  • Fecha de fin desde la que se generarán las filas de nuestra dimensión fecha
• Culture
  • Opcionalmente puedes hacer que las representaciones de texto de los valores sean en el idioma seleccionado
    • “en” -> ingles
    • “es” -> castellano
    • …

Pero con esto no tenemos nada todavía, para poder tener nuestra dimensión con datos necesitamos invocar dicha función, lo cual haremos creando otro origen de query en blanco y poniendo el siguiente código M de invocación de la función:

let
Source = fnDimDate(StartDate,EndDate, null),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayInWeek", Int64.Type}, {"CalendarMonthDay", type text}})
in
#"Changed Type"

*NOTA: El #”Changed Type” es porque quiero cambiar los tipos de datos a numérico

Lo cual inmediatamente nos generará los datos (nótese que debido al locale de mi máquina los nombres de meses los tengo en castellano, si los quisiera en ingles, en la función el tercer parámetro deberia ser “en”)

Dimension Tiempo

Podemos hacer exactamente lo mismo para la dimensión tiempo, la cual vamos a hacer en este caso que contenga todas las horas del día con sus respectivos minutos y segundos. Vamos a construir por tanto una dimensión de tiempo con nivel de detalle de segundos. En este caso vamos a hacer exactamente lo mismo que antes, empezando por una función programada en lenguaje M que nos devolverá con precisión de segundos los valores que deseamos. A diferencia del caso anterior, ahora realmente no necesitamos parámetros de entrada a dicha función puesto que todos los días tienen las mismas horas, minutos y segundos :). Además, en este caso no partimos de un dia concreto, sino que queremos generar tantas filas como segundos tiene un día.

let CreateTimeTable = () as table =>
let
// 86400 segundos tiene un dia
SecondCount = 86400,
// Crearemos un tipo Time que se irá incrementando en 1, para los 86400 segundos que tiene un día
// Este será por tanto nuestro iterador y generará las 86400 entradas (una por segundo) de nuestro dia
Source = List.Times(#time(0, 0, 0),SecondCount , #duration(0,0,0,1)),
// Transformamos dicha lista en una tabla sobre la que empezar a trabajar, creandole el resto de columnas útiles para nuestra dimensión tiempo
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
// Change that table's one column to type Time
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type time}}),
// Rename column to Time
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Time"}}),
// Start inserting columns for each unit of time to represent in the dimension
InsertHour = Table.AddColumn(RenamedColumns, "Hour", each Time.StartOfHour([Time])),
InsertMinute = Table.AddColumn(InsertHour, "Minute", each Time.Minute([Time])),
InsertSecond = Table.AddColumn(InsertMinute, "Second", each Time.Second([Time])),
ChangedTypeHour = Table.TransformColumnTypes(InsertSecond,{{"Hour", type time}}),
// Creating levels in the hierarchy that might be useful for reporting. Omit if not useful to yours
InsertQuarterHour = Table.AddColumn(ChangedTypeHour, "Quarter Hour", each if [Minute]<15 then [Hour] else if [Minute] < 30 then Value.Add([Hour],#duration(0,0,15, 0)) else if [Minute] < 45 then Value.Add([Hour],#duration(0,0,30, 0)) else Value.Add([Hour],#duration(0,0,45, 0))),
ChangedTypeQtrHr = Table.TransformColumnTypes(InsertQuarterHour,{{"Quarter Hour", type time}}),
ReorderedColumns = Table.ReorderColumns(ChangedTypeQtrHr,{"Time", "Hour", "Quarter Hour", "Minute"}),
InsertHourNumber = Table.AddColumn(ReorderedColumns, "Hour Number", each Time.Hour([Time])),
NextHour = Table.AddColumn(InsertHourNumber, "Next Hour", each Value.Add([Hour],#duration(0,1,0, 0))),
NextQuarterHour = Table.AddColumn(NextHour, "Next Quarter Hour", each Value.Add([Quarter Hour],#duration(0,0,15, 0))),
InsertPeriod = Table.AddColumn(NextQuarterHour, "Period of Day",
each if [Hour Number] >= 0 and [Hour Number] < 4 then "After Midnight" else
if [Hour Number] >= 4 and [Hour Number] < 8 then "Early Morning" else
if [Hour Number] >= 8 and [Hour Number] < 12 then "Late Morning" else
if [Hour Number] >= 12 and [Hour Number] < 16 then "Afternoon" else
if [Hour Number] >= 16 and [Hour Number] < 20 then "Evening" else "Late Night"),
InsertPeriodSort = Table.AddColumn(InsertPeriod, "PeriodSort", each
if [Hour Number] >= 0 and [Hour Number] < 4 then 0 else
if [Hour Number] >= 4 and [Hour Number] < 8 then 1 else
if [Hour Number] >= 8 and [Hour Number] < 12 then 2 else
if [Hour Number] >= 12 and [Hour Number] < 16 then 3 else
if [Hour Number] >= 16 and [Hour Number] < 20 then 4 else 5),
InsertTimeKey = Table.AddColumn(InsertPeriodSort, "TimeKey", each Time.ToText([Time], "HHmmss"), type text)
in
InsertTimeKey
in
CreateTimeTable

Al igual que antes, deberemos crear una nueva query en blanco para invocar nuestra función de tiempo. Nuestro código M de invocación ahora es así de sencillo:

Y listo, ya tenemos nuestras dimensiones Fecha y Tiempo para conseguir los análisis temporales que necesitemos. De hecho, una vez aplicamos cambios y salimos:

Aquí las tenemos listas para comenzar nuestros análisis

Exporta como plantilla

Como consejo, te recomiendo exportar el proyecto como Template de PowerBI, para que todo esto lo podamos seguir reutilizando en el futuro y no lo tengamos que repetir cada vez:

Inconvenientes de esta técnica

Como es obvio, el uso de M para crear la dimensión de tiempo tiene el inconveniente que si queremos modificar el rango temporal deberemos editar las fechas inicio-fin desde el propio PowerBI (o mediante Powershell, pero eso será en otro post). Si usáramos el método tradicional de crearnos nuestras tablas de dimensión tiempo en nuestro origen de datos favorito (motor relacional, ñongad,…) pues sería tan sencillo como entrar a ellos y modificarlo, pero son dos enfoques diferentes al expuesto en este post.

Espero que te sea útil!