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

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

Создание значений для дат — важный метод, который следует изучить в LuckyTemplates. Это может дать вам данные, которые вы хотите видеть в течение нескольких дней в вашей модели. Этот метод также закрывает промежутки между столбцами.

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

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

Оглавление

• Установите дополнительные значения
• Создайте условие фильтра для идентификаторов
• Добавьте условие фильтра для дат
• Проверка и сортировка столбца даты
• Используйте функцию Table.FirstN
• Получите разницу в днях между двумя значениями даты
• Определите шаг веса
• Заполни пропуски
• Получите весовую проекцию дат
• Заключение

Установите дополнительные значения

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

Для этого перейдите в Power Query, чтобы увидеть свою таблицу.

Вам нужно загрузить копию ваших данных в память. Для этого необходимо использовать меру Table.Buffer .

Если вы нажмете TableBuffer, вы увидите, что делает мера.

Затем вернитесь к своему набору данных и нажмите fx в строке формул. Вам нужно добавить Table.Buffer в меру.

Затем переименуйте этот шаг в BufferedTable .

Затем добавьте пользовательский столбец.

Создайте условие фильтра для идентификаторов

В этой формуле Power Query измените указанный шаг с BufferedTable на RenameColumns , поскольку сначала следует проверить BufferedTable.

Чтобы проверить BufferedTable, используйте Table.SelectRows , который возвращает таблицу, соответствующую выбранному условию.

Итак, для каждого выражения в формуле используйте Table.SelectRows, а затем используйте BufferedTable.

В качестве условия фильтра используйте пользовательскую функцию завершения и введите BT для буферизованной таблицы . Проверьте столбец идентификатора, если он равен идентификатору из текущей записи. Затем получите доступ к текущей записи, используя символ подчеркивания в качестве оператора доступа, а затем сошлитесь на тот же столбец ID.

Это даст вам отфильтрованную таблицу, содержащую только те идентификаторы, которые соответствуют текущей записи. 

Добавьте условие фильтра для дат

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

Используйте эти функции для проверки столбца даты BT:

Этот столбец должен быть больше или равен дате текущей записи.

При этом таблица будет содержать две строки вместо трех:

Проверка и сортировка столбца даты

Вам нужно проверить правильность сортировки столбца даты с помощью Table.Sort . Функция Table.Sort сортирует таблицу по одному или нескольким столбцам в зависимости от требуемых критериев.

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

Вам нужно щелкнуть правой кнопкой мыши по таблице и добавить ее как новый запрос.

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

Затем щелкните столбец даты и выберите «Сортировка по возрастанию».

Скопируйте всю формулу:

Вернитесь к основному запросу и вставьте его в строку формул:

Вы должны удалить ссылку Table.Sort, потому что таблица, которая должна передать параметр, должна быть результатом Table.SelectRows.

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

Используйте функцию Table.FirstN

Для этого шага вам нужно всего 2 записи вместо 3.

Чтобы исправить это, вернитесь к пользовательскому запросу. Щелкните Сохранить ряды, а затем Сохранить верхние ряды.

Введите 2 в поле для количества строк.

Вы заметите, что функция Table.FirstN появляется в строке формул. Table.FirstN возвращает первые строки таблицы в зависимости от значения countOrCondition .

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

Каждый объект таблицы в наборе теперь будет иметь 1 или 2 строки.

Получите разницу в днях между двумя значениями даты

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

Начните с добавления пользовательского столбца. Назовите это Дни . Затем следуйте этой формуле Power Query:

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

Затем добавьте номер типа в формулу следующим образом.

Как видите, вы получаете разницу в 385 дней с 14 сентября 2017 года по 4 октября 2018 года.

Определите шаг веса

Далее необходимо рассчитать, каким должен быть вес на каждый день.

Вам нужно снова добавить настраиваемый столбец, назвать его WeightStep и скопировать эту формулу Power Query:

Синтаксис проверяет, содержит ли таблица более 1 строки, вычитает вес из текущей записи и делит эту разницу на количество дней.

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

Заполни пропуски

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

Сначала добавьте еще один настраиваемый столбец и назовите его DateKey . Затем следуйте этой формуле Power Query:

Формула проверяет, есть ли разница в дате. Если есть, он создает список дат между начальной датой из текущей записи и расширяет его на количество дней. Но если нет никаких различий, он возвращает дату.

Вот результат:

Субъектам таблицы, не имевшим различий, была возвращена дата; таблицы, которые сделали, получили список дат.

Если вы развернете DateKey на новые строки, вы сможете увидеть записи всех дней.

Получите весовую проекцию дат

После создания списка дат вам необходимо получить прогноз веса для дат.

Сначала добавьте пользовательский столбец и назовите его WeightProjection . Затем введите эту формулу Power Query:

Синтаксис получает значение веса, добавляет его к значению DateKey, вычитает его из значения даты и умножает на WeightStep.

Снова добавьте номер типа в конец формулы.

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

Удалите вспомогательные столбцы: Custom, Days и WeightStep, используя приведенную ниже формулу, и измените формат столбца DateKey на Date.

Заключение

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

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

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

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

Мелисса