power query прибавить к дате дни
Глава 24. Динамический календарь в Power Query
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
С помощью Power Query довольно легко настроить полностью динамический календарь в Excel. Благодаря идеям главы 23 вы сможете управлять им на основе значений в двух ячейках Excel. Вам предстоит пройти четыре шага:
Рис. 24.1. Таблица параметров
Скачать заметку в формате Word или pdf, примеры в формате архива
Добавление таблицы параметров
Функция fnGetParameter
Откройте файл fnGetParameter.txt. Скопируйте его содержимое в буфер. Вернитесь в книгу Excel, пройдите по меню Данные –> Получить данные –> Из других источников –> Пустой запрос. В редакторе Power Query перейдите на вкладку Главная –> Расширенный редактор. Выделите весь код в окне и нажмите Ctrl+V –> Готово. Переименуйте запрос fnGetParameter.
Рис. 24.2. Функция fnGetParameter готова к работе
Построение структуры календаря
Продолжим. Создайте новый запрос не выходя из редактора Power Query. Главная –> Создать источник –> Другие источники –> Пустой запрос. Переименуйте запрос Calendar. Самый простой способ начать строить календарь – создать простой список. Введите в строке формул: =<1..10>. Нажмите Enter. Вы создали список от одного до десяти:
Рис. 24.3. Теперь у вас есть простой список
Преобразуйте этот список в таблицу и посмотрите, что произойдет, если изменить тип данных –> Дата. Перейдите на вкладку Средства для списков –> Преобразование –> В таблицу. Оставьте параметры по умолчанию и нажмите Ok. Щелкните правой кнопкой мыши столбец Column1 –> Тип изменения –> Дата. Щелкните правой кнопкой мыши столбец Column1 –> Переименовать –> Date. Хотя это не тот диапазон дат, который вам нужен, вы получили фрагмент календаря:
Рис. 24.4. Календарь; правда, немного устаревший))
Теперь нужно вставить пару шагов в начале запроса, чтобы извлечь даты начала и окончания, а затем передать эти даты в список вместо «от 1 до 10». Перейдите на вкладку Главная –> Расширенный редактор. После строки let добавьте две строки кода M:
startdate = fnGetParameter( » Start Date » ),
enddate = fnGetParameter( » End Date » ),
Не забудьте про запятые в конце каждой строки. Нажмите Готово. Убедитесь, что оба шага возвращают «правильные» даты:
Рис. 24.5. Start Date возвращает 01.01.2014
Замените числа 1 и 10 переменными. Выберите шаг Источник. Измените формулу:
Нажать Enter. К сожалению, возвращается ошибка:
Рис. 24.6. Что не так?
startdate = Number.From(fnGetParameter( » Start Date » )),
enddate = Number.From(fnGetParameter( » End Date » )),
Нажмите Готово. Перейдите на шаг startdate. Дата отражается в виде целого числа 41640. Перейдите к последнему шагу запроса, вы увидите таблицу, начинающуюся с 1 января 2014 года:
Рис. 24.7. Календарь готов к использованию
Добавление столбцов календаря
Выберите столбец Date –> Добавление столбца –> Дата –> Год –> Год. Выберите столбец Date –> Добавление столбца –> Дата –> Квартал –> Квартал года. Выберите столбец Date –> Добавление столбца –> Дата –> Месяц –> Месяц. Выберите столбец Date –> Добавление столбца –> Дата –> День –> День недели.
Рис. 24.8. В календарь добавлены часто используемые столбцы
Функции даты в Excel и Power Query
Рис. 24.9. Примеры функций Excel
Рис. 24.10. Сравнение функций в Excel и Power Query
Сложение дат
Вы будете разочарованы, если попытаетесь взять дату и добавить к ней число. Даже несмотря на то, что даты – это числа, Power Query не может неявно преобразовать тип данных, как это умеет делать Excel. Поэтому в Power Query существует коллекция функций для добавления дат:
Рис. 24.11. Функции сложения дат в Excel и Power Query
Даты как текст
Для возврата дат в текстовом формате в Excel используют функцию ТЕКСТ(). В Power Query аналогичным целям служит функция Date.ToText(). С последней нужно держать ухо востро: не только имя функции чувствительно к регистру, но и ее параметры. Поскольку Power Query не русифицирован, то и текст он возвращает английский:
Рис. 24.12. Даты как текст; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Создание таблиц дат в Power BI Desktop
В этой статье описаны средства моделирования данных, работающие с Power BI Desktop. В ней описываются оптимальные методики разработки для создания таблиц дат в моделях данных.
Для работы с функциями логики операций со временем выражений анализа данных (DAX) необходимо соблюдать предварительное требование к модели: в модели должна иметься хотя бы одна таблица дат. Таблица дат — это таблица, которая соответствует следующим требованиям:
Для добавления таблицы дат в модель можно использовать любую из нескольких возможностей:
Таблица дат, возможно, является наиболее согласованной функцией, которая будет добавлена в любую из ваших моделей. Более того, в организации таблица дат должна быть определена согласованно. Поэтому мы рекомендуем создать шаблон Power BI Desktop, включающий в себя полностью настроенную таблицу дат. Поделитесь шаблоном со всеми разработчиками моделей в вашей организации. Таким образом, каждый раз, когда кто-то разрабатывает новую модель, он может начать с согласованно определенной таблицы дат.
Использование функции «Автоматические дата и время»
Функция Автоматические дата и время обеспечивает удобную, быструю и простую в использовании логику операций со временем. Авторы отчетов могут работать с логикой операций со временем при фильтрации, группировании и детализации в определенные календарные периоды.
При работе с календарными периодами, а также при наличии упрощенных требований к модели в отношении времени рекомендуется всегда включать параметр «Автоматические дата и время». Кроме того, использовать этот параметр удобно при создании специализированных моделей, а также при просмотре или профилировании данных. Однако при таком подходе не поддерживается разработка одной таблицы дат, которая может распространять фильтры на несколько таблиц. Дополнительные сведения см. на странице Автоматические дата и время в Power BI Desktop.
Подключение с помощью Power Query
Если источник данных уже содержит таблицу дат, рекомендуется использовать ее в качестве источника для таблицы дат модели. Обычно это необходимо при подключении к хранилищу данных, так как оно будет содержать таблицу измерения даты. Таким образом, ваша модель использует единый источник достоверной информации для времени в вашей организации.
Если вы разрабатываете модель DirectQuery, а источник данных не содержит таблицу дат, настоятельно рекомендуем добавить таблицу дат в источник данных. Она должна соответствовать всем требованиям к моделировании таблицы дат. Затем можно использовать Power Query для подключения к таблице дат. Таким образом, в вычислениях модели могут использоваться возможности логики операций со временем DAX.
Создание таблицы дат с помощью Power Query
Таблицу дат можно создать с помощью Power Query. Дополнительные сведения см. в записи блога Криса Уэбба (Chris Webb) Generating A Date Dimension Table In Power Query (Создание размерной таблицы дат в Power Query).
Если у вас нет определения хранилища данных или другого согласованного определения времени в вашей организации, рекомендуем использовать Power Query для публикации потока данных. Затем подключите всех разработчиков моделей данных к потоку данных, чтобы добавить таблицы дат в модели. Поток данных станет единым источником достоверной информации для времени в вашей организации.
Если необходимо создать таблицу дат, рекомендуется сделать это с помощью DAX. Это может быть проще для вас. Более того, это будет удобней, так как DAX включает некоторые встроенные средства аналитики для упрощения создания таблиц дат и управления ими.
Создание таблицы дат с помощью DAX
Таблицу дат в модели можно сгенерировать, создав вычисляемую таблицу с помощью функций DAX CALENDAR или CALENDARAUTO. Каждая функция возвращает таблицу дат с одним столбцом. Затем вычисляемую таблицу можно расширить с помощью вычисляемых столбцов, чтобы обеспечить поддержку требований к фильтрации и группированию по интервалу дат.
Клонирование с помощью DAX
Если в модели уже есть таблица дат и требуется дополнительная таблица, ее можно легко клонировать. Это необходимо в том случае, когда дата представляет собой ролевое измерение. Вы можете клонировать таблицу, создав вычисляемую таблицу. Выражение вычисляемой таблицы — это просто имя имеющейся таблицы дат.
Дальнейшие действия
Дополнительные сведения, связанные с темой этой статьи, см. в следующих ресурсах.
Добавление столбца, основанного на типе данных (Power Query)
Существует множество распространенных способов изменения и форматирование различных типов данных при добавлении столбца. Например, может потребоваться определить, является ли число нечетным или четным, в нижнем регистре является текстовая строка или отображается название месяца даты/времени. Power Query предлагает множество полезных команд, которые можно использовать, не прибегая к формулам.
В каждом из следующих разделов приводится несколько примеров того, что можно сделать, и подводятся итоги всех команд, доступных для каждого типа данных.
Важно! В следующих примерах используется вкладка «Добавить столбец», чтобы исходный столбец не изменился. Обычно такой способ помогает избежать ошибок, непреднамеренных обновлений. Каждая из этих команд также доступна на вкладке «Преобразование», в которой изменяется исходный столбец. Будьте осторожны при изменении исходного столбца, что может привести к ошибкам случайного обновления.
Совет. Чтобы быстро создать образец данных и попробовать процедуру, создайте таблицу Excel с несколькими столбцами и строками, а затем импортировать ее. Дополнительные сведения см. в теме «Создание таблицы и импорт из таблицы Excel».
В этом разделе приводится несколько примеров текста, а затем показан список всех доступных текстовых команд. Команда «Парс» обсуждается в другой теме. Дополнительные сведения см. в parse text as JSON или XML.
Чтобы сделать каждое слово в строке заветие:
Выберем столбец «Громкоеmemo».
Выберите «Добавить столбец> Извлекать> «С завеизировать каждое слово». Будет добавлен новый столбец с результатом.
Чтобы найти длину строки:
Вы выберите столбец «Полное имя».
Выберите «Добавить столбец> формат> длину. Будет добавлен новый столбец с результатом.
Общие сведения о командах для работы с текстовыми типами данных