Создаем автообновляемую таблицу с датами

Таблица с датами — абсолютный мастхэв в любом отчете PowerBI. В этой статье я подробно разберу процесс создания таблицы-календаря в Power Query Editor, а для ленивых в конце статьи будет инструкция по созданию такой таблицы буквально в два клика.

Создаем столбец дат

Для начала нам потребуется столбец с датами, для этого обратимся к функции List.Dates. Эта функция позволит нам создать список дат начиная с текущей и заканчивая заданной нами.

Вместо стартовой даты мы будем использовать конструкцию DateTime.Date(DateTime.FixedLocalNow()), которая возвращает текущую дату.

Полный код запроса выглядит следующим образом:

let    
    Source = List.Dates(DateTime.Date(DateTime.FixedLocalNow()), 365, #duration(-1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in    #"Converted to Table"

Первый шаг этого кода создает список из дат, а второй шаг конвертирует этот список в таблицу. Цифра 365 в коде — количество дней, начиная с текущего, которые вы хотите отобразить, в данном случае, один год. Если вам нужно больше, введите необходимое значение. Рекомендую взять большой интервал, а потом просто отфильтровать значения в столбце начиная с нужной даты.

 Чтобы создать такую же таблицу:

1. Перейдите в Power Query Editor (Edit Queries);
2. Cоздайте новый пустой запрос (Blank Query);
3. Откройте Advanced Editor;
4. Вставьте представленный выше код и нажмите Done.

Дополняем таблицу необходимыми полями

Далее нам просто необходимо дополнить таблицу столбцами, которые нам будут необходимы для реализации нашего отчета. Дополнительные столбцы индивидуальны для каждого проекта, я расскажу только про те, которые я использую всегда.

В каждом проекте я использую столбцы: День, месяц, год, дата начала недели, дата начала месяца. Последние два нужны для того, чтобы строить графики с переключением детализации по дням, неделям и месяцам. Остальные нужны для создания фильтрации данных в отчетах.

Создаются столбцы очень просто:

1. Выделите созданный нами столбец с датами;
2. Перейдите на вкладку Add Column;
3. Выберите Date — Month — Name of Month.

По такому же принципу вы можете создать остальные столбцы: 

• год: Year — Year;
• дата начала недели: Week — Start of Week;
• дата начала месяца: Month — Start of Month.

Версия для ленивых

Для тех, кому лень вникать в то, как создается такая таблица:

1. Перейдите в Power Query Editor (Edit Queries);
2. Cоздайте новый пустой запрос (Blank Query);
3. Откройте Advanced Editor;
4. Вставьте представленный ниже код и нажмите Done.

let
    Source = List.Dates(DateTime.Date(DateTime.FixedLocalNow()), 365, #duration(-1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),    
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),    
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),    
    #"Inserted Day" = Table.AddColumn(#"Changed Type", "Day", each Date.Day([Date]), Int64.Type),    
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day", "Month Name", each Date.MonthName([Date]), type text),    
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type),    
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Year", "Start of Month", each Date.StartOfMonth([Date]), type date),    
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Start of Month", "Start of Week", each Date.StartOfWeek([Date]), type date),    
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Start of Week", "Start of Year", each Date.StartOfYear([Date]), type date)
in    #"Inserted Start of Year"

После этого необходимо будет связать ваши таблицы, содержащие даты, с этой таблицей и можно будет использовать ее для создания всех фитров и визуализаций.