Power Query LuckyTemplates | Создание новых записей на основе полей даты

В этом руководстве я хочу продемонстрировать несколько методов создания новых записей на основе полей даты с помощью Power Query LuckyTemplates. Вы можете посмотреть полное видео этого урока в нижней части этого блога.

Я собираюсь продемонстрировать два метода того, как этого добиться. Этот пример пришел от участника , который разместил вопрос с датой и количеством ночей пребывания. Участник хотел знать, как создавать новые записи для каждой ночи, проведенной человеком в мотеле, с помощью Power Query.

Оглавление

• Способ 1: использование функции Number.From в Power Query LuckyTemplates
• Способ 2: использование M Function List.Dates в Power Query LuckyTemplates
• Заключение

Способ 1: использование функции Number.From в Power Query LuckyTemplates

Если вы знакомы с вычислениями даты в Excel, вы, вероятно, знаете, что каждый день можно преобразовать в целое число, и вы можете сделать это и здесь, чтобы получить числовой эквивалент для каждой даты. Мы можем использовать функцию Number.From .

Я сошлюсь на свой исходный запрос, щелкнув его правой кнопкой мыши и выбрав Reference . Затем через эту мини-таблицу перейдите в Add Custom Column . Теперь давайте введем функцию Number.From и укажем на первый фактический столбец, содержащий наши даты. Не забудьте закрывающую скобку и нажмите OK .

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

Если мы проверим первую запись, этот человек остался всего на одну ночь, поэтому нам нужно вернуть только это первое фактическое значение даты. Затем для второй записи этот человек останавливался на две ночи подряд. Мы должны вернуть значение как для 13, так и для 14 декабря. Мы можем добиться этого, если добавим значение из столбца «Ночи» к значению, имеющемуся в нашем столбце «Пользовательский» , а затем вычтем единицу , потому что мы уже передали это начальное значение.

Итак, давайте перейдем к строке формул и расширим созданную нами логику. Мы должны использовать это числовое значение для даты дважды, поэтому я сохраню его в переменной. Я добавлю выражение let , затем добавлю имя для своей переменной. Я назову это FirstNight . И тогда мы можем добавить предложение in .

Списки можно расширить до новых строк с помощью набора инициализаторов списка, которые представляют собой фигурные скобки. Мы можем создать список с датами. Мы собираемся начать с нашей переменной FirstNight до переменной FirstNight, плюс все, что находится в нашем количестве ночей. С помощью набора квадратных скобок укажите на столбец «Ночи» и вычтите один . Я нажму OK , и теперь у нас есть список.

Когда я щелкаю сбоку в пустом пространстве, мы видим содержимое этого вложенного списка внизу во второй панели предварительного просмотра.

Теперь с помощью этих боковых стрелок мы можем Expand to New Rows . Затем я могу установить тип данных в дату. Итак, вы видите, что теперь у нас есть даты. Для первой записи мы возвращаем то же значение, что и в первом фактическом. Для этой второй записи у нас есть одна запись от 13 декабря и одна от 14 декабря.

Способ 2: использование M Function List.Dates в Power Query LuckyTemplates

Для второго метода мы можем использовать M - функцию List.Dates . Я сошлюсь на свой исходный запрос и снова, с этим значком мини-таблицы, добавлю настраиваемый столбец.

Я добавлю эту функцию List.Date, а затем помещу ее в открывающую скобку. Во-первых, ему требуется начальное значение в виде даты, и оно находится в нашем первом фактическом столбце, содержащем наши значения даты. Итак, выбираем то, что потом ставим запятую. Далее идет счет в виде числа, означающего, сколько дат вы хотите вернуть в этот список. И мы можем видеть это в колонке «Ночи» .

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

Опять же, у нас есть вложенный список. Для первой записи у нас есть только одно значение, а для второй записи у нас есть 13 и 14 декабря.

Затем мы можем снова развернуть это до новых строк и установить соответствующий тип данных на сегодняшний день.

Теперь я хочу указать, что для первого периметра в функции List.Dates требуется тип данных даты. Если я перейду к своему исходному шагу и изменю этот тип данных на дату/время, он вернет ошибку. Это потому, что это не свидание. Мы можем легко исправить это, зайдя в строку формул и добавив Date.From .

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

Значения записи — структурированное значение в Power Query.
Извлечение значений из записей и списков в Power Query.
Создание значений для каждой даты с использованием формулы Power Query.

Заключение

В этом блоге я продемонстрировал два способа создания новых записей на основе полей даты. Первый — путем создания списка дат с помощью инициализаторов списка, а второй — с помощью функции List.Dates.

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

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

Мелисса