Как создать таблицу дат в Microsoft Power BI

2 сентября 2023 г.

В статье Как узнать, подходит ли таблица автоматической даты при использовании Power BI, рассматривается внутренняя работа того, как Microsoft Power BI обрабатывает даты и время. Это самый быстрый и простой вариант, потому что вы вообще ничего не делаете. Хотя Power BI создаст для вас внутреннюю таблицу дат, она не всегда будет соответствовать необходимым вам требованиям к фильтрации и группировке. В этом случае вы можете создать таблицу дат самостоятельно. Умение создавать свои собственные является преимуществом, когда вы работаете со сложным набором данных.

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

Я использую Microsoft Power BI Desktop в 64-разрядной системе Windows 11. На протяжении всей статьи я буду использовать термины «таблица дат» и «таблица фактов» для описания таблицы дат и таблиц данных соответственно, поскольку термины «дата» и «данные» слишком похожи и их можно спутать.

Для этого руководства вы можете скачать демонстрационный файл Microsoft Power BI.

Что такое таблица дат в Power BI?

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

Лучше заранее узнать, планируете ли вы создать таблицу дат, поскольку Power BI допускает только одну таблицу дат. Если вы создадите визуальные элементы на основе внутренней таблицы автоматической даты, а затем создадите собственную таблицу дат и пометите ее как таковую, Power BI уничтожит таблицу автоматической даты и все основанные на ней визуальные элементы.

СМОТРИТЕ: изучите этот параллельный анализ Google Workspace и Microsoft 365.

Таблица дат выглядит как любая другая таблица со строкой для каждой даты. Первый столбец — это столбец типа данных даты и времени с именем Date. В остальных столбцах хранятся метаданные о каждой дате, например год, квартал, месяц и т. д. (рис. A).

Рисунок А

Используя пользовательскую таблицу дат, вы управляете иерархиями дат, используемыми вашей моделью. Это сводится к быстрым мерам и другим оценкам. Таблице фактов сложно соответствовать требованиям, предъявляемым к таблице дат:

В таблице дат должен быть столбец с именем Date, который является типом данных даты и времени. Столбец «Дата» должен содержать уникальные значения. Столбец «Дата» не может содержать пустые или нулевые значения. В столбце «Дата» не может быть отсутствующих дат — даты должны быть последовательными.

Если вы решите использовать таблицу фактов, вы можете определить ее как таблицу дат следующим образом:

Выберите таблицу на панели данных. Щелкните таблицу правой кнопкой мыши и выберите «Пометить как таблицу дат». Затем выберите «Пометить таблицу как дату» в появившемся меню.

Когда вы помечаете таблицу фактов как таблицу дат, Power BI построит связи и иерархии на основе этой таблицы. В противном случае вам придется создать необходимые связи между таблицей дат (фактов) и другими таблицами, чтобы получить те же результаты.

СМОТРИТЕ: Вот как создать панель мониторинга в Power BI.

Как использовать DAX для создания таблицы дат в Power BI

Если вам нужно создать пользовательскую таблицу дат, вы можете использовать DAX для создания вычисляемой таблицы. DAX — это язык выражений, используемый в службах Analysis Services, Power BI и Power Pivot, который включает функции и операторы.

Вы можете использовать функцию DAX CALENDAR или CALENDARAUTO для создания таблицы дат. Оба возвращают таблицу дат в один столбец.

При рассмотрении того, какую функцию использовать, CALENDAR требует первую и последнюю даты, чтобы он мог создать полный список дат. CALENDARAUTO использует существующие даты в таблице фактов для создания списка дат. Мы будем использовать CALENDAR для создания таблицы дат.

Как использовать CALENDAR для создания таблицы дат

Функция КАЛЕНДАРЬ использует следующий синтаксис:

КАЛЕНДАРЬ(<начальная_дата>, <конечная_дата>)

Где start_date — это первая дата в результирующей таблице дат, а end_date — последняя. Функция вернет таблицу из одного столбца, заполненную списком дат от start_date до end_date и каждого промежуточного дня.

Теперь давайте воспользуемся КАЛЕНДАРЕМ, чтобы создать таблицу дат в Power BI, используя 1 января 2000 г. в качестве начальной_даты и 31 декабря 2021 г. в качестве конечной_даты:

Запустите Power BI. При необходимости выберите «Создать» в меню «Файл», чтобы работать с новым файлом .pbix. Вы не хотите работать с существующим файлом .pbix. Нажмите «Просмотр таблицы» на левой панели. При необходимости щелкните Главное меню. В крайнем правом углу меню выберите пункт «Написать выражение DAX для создания новой таблицы». Power BI предоставляет первый компонент — Table =. Заполните выражение Таблица = КАЛЕНДАРЬ (ДАТА (2020, 1, 1), ДАТА (2022, 12, 31)) (рис. Б). Нажмите «Зафиксировать», галочка слева.

Рисунок Б

Создайте таблицу со списком дат.

Функция создает новую таблицу, называет один столбец «Дата» и соответствующим образом заполняет этот столбец. Стоит отметить, что существует 1096 различных значений или строк. Однако 365*3 равно 1095. Power BI знает, что 2020 год был високосным.

Как использовать CALENDAR для создания таблицы дат и заполнения ее полей

Следующим шагом будет добавление столбцов для каждого компонента даты, который вам понадобится для сортировки и фильтрации: номер недели, номер месяца, квартал, год и т. д. На этом этапе вы можете использовать опцию «Добавить столбец», чтобы добавить больше столбцов. Однако вам это не обязательно. Более эффективно добавить все эти столбцы при создании таблицы:

Дата =

ДОБАВИТЬ КОЛОННЫ (

КАЛЕНДАРЬ (ДАТА (2020, 1, 1), ДАТА (2022, 12, 31)),

«Год», ГОД([Дата]),

«Номермесяца», МЕСЯЦ([Дата]),

«Квартал», КВАРТАЛ([Дата]),

«ДеньНедели», ДЕНЬНЕД([Дата])

На рисунке C показаны результаты. Количество добавляемых столбцов зависит от потребностей фильтрации и группировки ваших визуализаций и отчетов.

Рисунок С

Вы можете использовать DAX для создания таблицы дат.

СМ.: Узнайте, как использовать Power BI в Outlook и Office для повествования о данных.

В этом примере функция создает всего несколько столбцов, но вам может понадобиться гораздо больше. Полный список можно найти на странице Функции даты и времени (DAX) – DAX | Microsoft Learn.

Это выражение объединяет ADDCOLUMNS и CALENDAR:

Дата — это имя результирующей таблицы. ADDCOLUMNS позволяет указать столбцы для даты. CALENDAR создает таблицу дат и заполняет первый столбец. Следующие четыре строки определяют столбцы метаданных.

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

СМОТРИТЕ: следуйте инструкциям в этом руководстве, чтобы добавить вычисляемый столбец в Microsoft Power BI.

С другой стороны, если вы хотите, чтобы Power BI сделал это за вас, не помечайте таблицу как таблицу дат. Вместо этого вы можете создать отношения и использовать их для специализированной группировки и фильтрации. Это одна из тех областей, где нет правильного или неправильного, но знание ваших данных является ключом к принятию наиболее эффективного выбора.

Помните: когда вы помечаете таблицу фактов как таблицу дат, Power BI удаляет встроенную таблицу автоматической даты и все визуальные элементы, которые вы ранее создали на ее основе. Если вы отмените выбор таблицы дат вручную, Power BI автоматически создаст новую таблицу автоматических дат.

---

Оригинал