Преобразование кода YYWWD в значение даты в LuckyTemplates

В этом блоге мы обсудим, как преобразовать код YYWWD в значение даты . Это руководство было основано на ответе на вопрос участника  . Вы можете посмотреть полное видео этого урока в нижней части этого блога.

Участник попросил способ преобразовать его поле «Подтвержденная дата доставки» в формате года, недели и дня недели и преобразовать его в поле даты.

В своем примере участник объясняет, что код 20145 следует преобразовать в 2020-04-03. Отсюда можно сделать вывод, что первые две цифры соответствуют году, следующие две цифры соответствуют номеру недели, а последняя цифра соответствует дню недели.

Теперь, когда у нас уже есть представление о том, как справиться с запросом, давайте начнем.

Оглавление

• Разделение кода на части для правильного значения даты
• Столбец года
• Недельная колонка
• Колонка дня
• 3-сторонний поиск для получения значения даты
• Определение пар
• Получение правильного значения даты
• Вычитание из столбца дня
• Заключение

Разделение кода на части для правильного значения даты

Чтобы упростить нашу работу, давайте загрузим наши данные в редакторе Power Query .

Первое, на что я обратил внимание, загрузив данные в Power Query, это то, что он немедленно сделал шаг изменения типа, как показано ниже.

Поскольку нам это не нужно, мы можем удалить этот шаг, нажав кнопку X рядом с ним.

Теперь давайте начнем разбивать код на отдельные столбцы для года, недели и дня недели.

Столбец года

Как я упоминал ранее, первые две цифры кода соответствуют году значения даты.

Чтобы добавить отдельный столбец для года, нам нужно убедиться, что выбран столбец Подтвержденная дата доставки . Для этого перейдите на вкладку «Добавить столбец» , нажмите «Извлечь» , а затем выберите «Первые символы» .

Поскольку нам нужны только первые две цифры для столбца года, введите «2» на вкладке «Количество» и нажмите «ОК».

Теперь давайте изменим наш новый столбец, отредактировав панель формул. Итак, чтобы изменить имя столбца, измените Первые символы в строке формул на Год. Чтобы превратить его в правильное значение года, давайте добавим 2000 к каждому из наших значений.

Обратите внимание, что после того, как мы изменим нашу формулу, мы получим ошибку из-за несоответствия типов.

В сообщении об ошибке говорится, что мы не можем применить оператор + к значениям числового и текстового типов.

Чтобы исправить это, нам нужно отредактировать нашу формулу, чтобы текстовое значение было преобразовано в число. Для этого мы будем использовать функцию Number.from и заключать последующие части в круглые скобки. Затем измените тип возвращаемого значения с текста на число.

Недельная колонка

Давайте теперь извлечем номер недели из кода.

Снова убедитесь, что выбран столбец « Подтвержденная дата доставки» , перейдите на вкладку «Добавить столбец», нажмите «Извлечь», затем выберите «Диапазон».

Чтобы получить третью и четвертую цифру нашего кода, нам нужно начать отсчет с 0, так как Power Query основан на нуле. Это означает, что для получения третьей цифры нашего кода наш диапазон должен начинаться с 2.

А так как нам нужно всего 2 цифры из кода номера недели, мы введем 2 в поле Количество символов.

Опять же, здесь применима та же логика. Чтобы преобразовать метку столбца в ее правильное имя, измените текстовый диапазон на Неделя в строке формул.

Кроме того, добавьте функцию Number.from в нашу формулу, заключите последующие части в круглые скобки и измените тип возвращаемого значения на число.

Колонка дня

Для столбца дня нам нужна только последняя цифра кода. Чтобы извлечь его, выберите столбец «Подтвержденная дата доставки» , перейдите на вкладку «Добавить столбец», выберите «Извлечь» и нажмите « Последние символы» .

Введите 1, потому что от кода, который нам нужно извлечь, осталась только одна цифра.

Подобно тому, что мы делали ранее, в строке формул измените «Последние символы» на «День», чтобы у нашего нового столбца было правильное имя.

Снова добавьте функцию Number.from , заключите следующие части формулы в круглые скобки и измените тип возвращаемого значения на число.

3-сторонний поиск для получения значения даты

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

Для этого перейдите на вкладку «Главная» и выберите «Объединить запросы».

Поскольку мы собираемся объединиться с нашей таблицей «Даты», щелкните раскрывающуюся вкладку и выберите «Даты».

Определение пар

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

В верхней таблице выберите столбец года, а затем соедините его со столбцом года в таблице ниже.

Чтобы выбрать другую пару, нажмите кнопку Ctrl на клавиатуре, выберите столбец недели в верхней таблице и соедините его со столбцом номера недели в таблице ниже.

Наконец, все еще удерживая нажатой кнопку Ctrl, выберите столбец дня из верхней таблицы и соедините его со столбцом DayInWeek в таблице ниже.

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

Как показано ниже, столбцы «Год» должны иметь индекс 1, столбец «Недели» должен иметь индекс 2, а столбец «Дни» должен иметь индекс 3.

Получение правильного значения даты

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

Как видите, первое значение в нашем новом столбце дат — 4-4-2020. Однако помните, что на форуме код 20145 следует преобразовать в 3-4-2020.

Это потому, что если мы изучим нашу таблицу дат, мы увидим, что в столбце DayInWeek счет начинается с 0 до 6, а не с 1 до 7.

Вычитание из столбца дня

Чтобы исправить это, давайте вычтем 1 из всех значений в нашем столбце «День».

В разделе «Примененные шаги» щелкните шаг, в который мы добавили столбец дня. Это был шаг «Вставленные последние символы» .

В строке формул вычтите 1 сразу после скобок.

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

Наконец, последнее, что нам нужно сделать, это очистить и удалить столбцы, которые нам больше не нужны.

Для этого выберите «Выбрать столбцы», отмените выбор всего остального и оставьте отмеченными столбец «Подтвержденная дата доставки» и столбец даты.

И мы закончили! Результат нашей работы должен выглядеть так.

Отображение результатов до текущей даты или определенной даты в LuckyTemplates
Предотвращение прогнозирования результатов за год до даты
Фильтрация данных по настраиваемым финансовым годам и кварталам с использованием вычисляемых столбцов в LuckyTemplates

Заключение

В этом блоге мы преобразовали код в формате года, недели и дня недели в правильное значение даты. Мы разбили код на части и создали из него 3 столбца. Используя трехсторонний поиск, мы объединили эти столбцы вместе, чтобы получить одно значение даты.

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

Всего наилучшего,

Мелисса