Створення значень для кожної дати за допомогою формули 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.

Висновок

У цьому підручнику ви дізналися, як заповнити прогалини в наборі даних, створивши значення в кожному списку дат.

Ці значення дат допоможуть вам отримати цінну інформацію з вашої моделі. Якщо у ваших таблицях бракує інформації, ви можете скористатися цією технікою, щоб зробити їх більш повними.

У вашій таблиці можуть бути важливі дані, які ви пропускаєте, тому завжди розгортайте кожен рядок і стовпець.

Все найкраще,

Меліса