Power Query: un calendario perpetuo

Por esta fechas todos esperamos tener a nuestra disposición un calendario del año siguiente.
Y este será nuestro objetivo de hoy: Construir con Power Query un calendario perpetuo.

Lo único que necesitamos en nuestra hoja de cálculo es una tabla (de un solo campo 'año' y un solo registro) para informar el año del que queremos obtener nuestro calendario.

Así en A1:A2 tenemos una 'Tabla1' con el año deseado.

El primer paso consiste en llevar la tabla al editor de Power Query (ficha Datos > grupo Obtener y transformar > Desde una tabla).

A continuación pasaremos ese valor del año a parámetro, para esto desde el editor de PQ hacemos clic derecho sobre el dato y elegimos la opción de Rastrear desagrupando datos

Este paso es importante ya que convertimos en parámetro o variable el año sobre el que trabajaremos...
Aprovechamos y cambiamos el nombre de la consulta por 'año'.

En el paso siguiente generaremos con una función M un listado de fechas para el año elegido... usaremos la función List.Dates

Dentro de editor generaremos una nueva consulta en blanco, desde la ficha Inicio > grupo Nueva consulta > Nuevo Origen > Otros orígenes > Consulta en blanco

En la consulta añadiremos la fórmula:
= List.Dates(#date(año, 1, 1), 366, #duration(1, 0, 0, 0))
la cual generará un listado de 366 días consecutivos del año 2019

Con el listado de fechas generada el siguiente paso consiste en convertirlo en una Tabla.

Hacemos clic derecho sobre el listado y elegimos la opción de: A la tabla

En las siguiente ventana aceptamos sin mas...

Otro paso importante es aplicar un filtro para asegurarse que solo se muestran fechas del año deseado...
Sobre el campo en cuestión aplicamos el filtro personalizado:
posterior o igual a 01/01/2019
y
anterior o igual a 31/12/2019

Y antes de continuar editamos el paso para sustituir el año 2019 informado por nuestro parámetro 'año'

hasta ahora nuestro código generado sería:
let
Source = List.Dates(#date(año, 1, 1), 366, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each [Column1] >= #date(año, 1, 1) and [Column1] <= #date(año, 12, 31)) in #"Filtered Rows"

Las siguientes etapas consisten en duplicar hasta en cinco ocasiones nuestro listado de fechas y modificar el dato que nos devuelve con criterios de fechas diferentes.. también es posible agregar columnas personalizadas, empleadno en todo caso las funciones M de texto que ya vimos aquí.
En nuestro caso serían algunas de estas funciones:
Date.WeekOfYear - para obtener el número de la semana dentro del año (un valor entre 1 y 53)
Date.DayOfWeek - para obtener, en número, el día de la semana (un valor entre 0-lunes y 6-domingo)
Date.DayOfWeekName- para obtener, en texto, el día de la semana (lunes a domingo)
Date.Day - veremos el día del mes (valor entre 1 y 31)
Date.Month - veremos el mes del año (valor entre 1 y 12)
Date.MonthName - veremos el mes del año en texto (enero, febrero, marzo, ..., diciembre)

Podemos usar el botón derecho del ratón para ir duplicando la columna y modificando el dato mostrado.
Este es el aspecto de nuestra consulta en este momento:
let

Source = List.Dates(#date(año, 1, 1), 366, #duration(1, 0, 0, 0)),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each [Column1] >= #date(año, 1, 1) and [Column1] <= #date(año, 12, 31)),


#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Column1", "Num Semana"),

#"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Num Semana", Date.WeekOfYear, Int64.Type}}),

#"Duplicated Column1" = Table.DuplicateColumn(#"Calculated Week of Year", "Column1", "Dia Semana"),

#"Calculated Day of Week" = Table.TransformColumns(#"Duplicated Column1",{{"Dia Semana", Date.DayOfWeek, Int64.Type}}),

#"Duplicated Column2" = Table.DuplicateColumn(#"Calculated Day of Week", "Column1", "Día Mes"),

#"Extracted Day" = Table.TransformColumns(#"Duplicated Column2",{{"Día Mes", Date.Day, Int64.Type}}),

#"Duplicated Column3" = Table.DuplicateColumn(#"Extracted Day", "Column1", "Mes"),

#"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column3", {{"Mes", each Date.MonthName(_), type text}}),

#"Duplicated Column4" = Table.DuplicateColumn(#"Extracted Month Name", "Column1", "Mes Num"),

#"Extracted Month" = Table.TransformColumns(#"Duplicated Column4",{{"Mes Num", Date.Month, Int64.Type}})

in

#"Extracted Month"

Por completar nuestro trabajo (y de paso aprender alguna cosa mas) vamos a generar una tabla de equivalencias número - día de la semana, desde una consulta nueva en blanco (Ya hemos visto antes cómo acceder a ella), y escribiremos el siguiente código:
let
Source = Table.FromRecords({
[dia = 0, Name = "Lunes"],
[dia = 1, Name = "Martes"] ,
[dia = 2, Name = "Miércoles"] ,
[dia = 3, Name = "Jueves"],
[dia = 4, Name = "Viernes"],
[dia = 5, Name = "Sábado"],
[dia = 6, Name = "Domingo"]
})
in
Source

Siendo este el resultado:

Con nuestra consulta creada 'Dia_Semana' la combinaremos con la consulta previa 'Calendario' de trabajo.

Entramos en nuestra consulta 'Calendario' y la combinamos con el campo común [Dia Semana].
Ficha Inicio > Combinar > Combinar consultas > Combinar consultas (consulta esta consulta con otra del libro)

Después podemos expandir el campo y dejar solo visible el día de la semana (campo 'Name').
Importante también forzar la ordenación en ascendente por nuestro campo 'Column1' inicial.
Y en este momento ya podemos quitar esta 'Column1' y la del 'Dia semana' numerado que ya no necesitaremos en adelante.
Nuestro de código de consulta será con estos últimos pasos el siguiente:
let

Source = List.Dates(#date(año, 1, 1), 366, #duration(1, 0, 0, 0)),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each [Column1] >= #date(año, 1, 1) and [Column1] <= #date(año, 12, 31)),


#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Column1", "Num Semana"),

#"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Num Semana", Date.WeekOfYear, Int64.Type}}),

#"Duplicated Column1" = Table.DuplicateColumn(#"Calculated Week of Year", "Column1", "Dia Semana"),

#"Calculated Day of Week" = Table.TransformColumns(#"Duplicated Column1",{{"Dia Semana", Date.DayOfWeek, Int64.Type}}),

#"Duplicated Column2" = Table.DuplicateColumn(#"Calculated Day of Week", "Column1", "Día Mes"),

#"Extracted Day" = Table.TransformColumns(#"Duplicated Column2",{{"Día Mes", Date.Day, Int64.Type}}),

#"Duplicated Column3" = Table.DuplicateColumn(#"Extracted Day", "Column1", "Mes"),

#"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column3", {{"Mes", each Date.MonthName(_), type text}}),

#"Duplicated Column4" = Table.DuplicateColumn(#"Extracted Month Name", "Column1", "Mes Num"),

#"Extracted Month" = Table.TransformColumns(#"Duplicated Column4",{{"Mes Num", Date.Month, Int64.Type}}),


#"Merged Queries" = Table.NestedJoin(#"Extracted Month",{"Dia Semana"},Dia_Semana,{"dia"},"Dia_Semana",JoinKind.LeftOuter),

#"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Dia_Semana", {"Name"}, {"Dia_Semana.Name"}),

#"Sorted Rows" = Table.Sort(#"Expanded {0}",{{"Column1", Order.Ascending}}),

#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Column1","Dia Semana"})

in

#"Removed Columns"

Y el aspecto de la consulta:

Procederemos a realizar una dinamización de columnas para tener en distintas columnas los siete días de la semana...
Por tanto seleccionamos los campos 'Dia_Semana.Name' y 'Día mes' y desde la ficha Transformar > grupo Cualquier columna > botón Columna dinámica

Previo al paso final volvemos a ordenar por asegurarnos por un criterio múltiple de dos campos:
'Mes Num" es ascendente y 'Num Semana' en ascendente
Y una vez ordenados terminamos eliminando la columna 'Mes Num'.

El paso final es reordenar las columnas para visualizar los días de la semana ordenados como: Lunes,Martes, ..., Sábado, Domingo.

El código completo sería finalmente:
let

Source = List.Dates(#date(año, 1, 1), 366, #duration(1, 0, 0, 0)),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each [Column1] >= #date(año, 1, 1) and [Column1] <= #date(año, 12, 31)),


#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Column1", "Num Semana"),

#"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Num Semana", Date.WeekOfYear, Int64.Type}}),

#"Duplicated Column1" = Table.DuplicateColumn(#"Calculated Week of Year", "Column1", "Dia Semana"),

#"Calculated Day of Week" = Table.TransformColumns(#"Duplicated Column1",{{"Dia Semana", Date.DayOfWeek, Int64.Type}}),

#"Duplicated Column2" = Table.DuplicateColumn(#"Calculated Day of Week", "Column1", "Día Mes"),

#"Extracted Day" = Table.TransformColumns(#"Duplicated Column2",{{"Día Mes", Date.Day, Int64.Type}}),

#"Duplicated Column3" = Table.DuplicateColumn(#"Extracted Day", "Column1", "Mes"),

#"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column3", {{"Mes", each Date.MonthName(_), type text}}),

#"Duplicated Column4" = Table.DuplicateColumn(#"Extracted Month Name", "Column1", "Mes Num"),

#"Extracted Month" = Table.TransformColumns(#"Duplicated Column4",{{"Mes Num", Date.Month, Int64.Type}}),


#"Merged Queries" = Table.NestedJoin(#"Extracted Month",{"Dia Semana"},Dia_Semana,{"dia"},"Dia_Semana",JoinKind.LeftOuter),

#"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Dia_Semana", {"Name"}, {"Dia_Semana.Name"}),

#"Sorted Rows" = Table.Sort(#"Expanded {0}",{{"Column1", Order.Ascending}}),

#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Column1","Dia Semana"}),

#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Dia_Semana.Name]), "Dia_Semana.Name", "Día Mes", List.Sum),


#"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"Mes Num", Order.Ascending}, {"Num Semana", Order.Ascending}}),

#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Mes Num"}),

#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Num Semana", "Mes", "Lunes", "Martes", "Miércoles", "Jueves", "Viernes", "Sábado", "Domingo"})

in

#"Reordered Columns"

El aspecto antes de Cerrar y Cargar..y llevar los datos a la hoja de cálculo:

Listo.
Como veíamos en la primera imagen tenemos nuestro calendario del año asociado al informado en la celda A2...