Вирішення відсутніх курсів валют за допомогою Power Query у LuckyTemplates

У цьому посібнику ви дізнаєтесь, як використовувати Power Query для конвертації валют у LuckyTemplates. Ви можете переглянути повне відео цього підручника внизу цього блогу.

Метод, описаний у цьому блозі, допоможе вам вирішити проблеми в таблиці курсів валют, використовуючи останній доступний курс.

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

Зміст

• Ідентифікація відсутніх даних
• Розділення різних валют
• Заповнення відсутніх ставок
• Об'єднання всіх валют
• Створення зв’язків у моделі даних
• Порівняння методів DAX і Power Query
• Висновок

Ідентифікація відсутніх даних

Ця проблема може виникнути, якщо в таблиці "Дата" немає даних для святкових чи вихідних днів.

Якщо у вас немає ставки в будь-який день і ви не враховуєте її, такі показники, як Загальні продажі, не будуть конвертовані.

Якщо ви подивитеся на цю таблицю курсів валют, у ній є курси в євро на січень 2016 року.

Але зауважте, що 27-й і 28-й дні відсутні і не мають норми. Отже, якщо зайти в матрицю, то можна побачити, що для 27 і 28 ставок немає.

Курс валюти з відсутнім показником – це просто сума таблиці Фактичних курсів валют.

Загальна сума 26,6983 у таблиці не має сенсу. Це лише сума всіх курсів валют вище, яку вам не потрібно складати, оскільки вони є такими, якими вони є на поточний день.

Тепер це можна виправититому що це більше проблема моделювання даних, яка включає запити та агрегування. Це також можна зробити за допомогою DAX, але простіше та швидше за допомогою Power Query у LuckyTemplates.

Отже, натисніть «Перетворити дані» , а потім відкрийте вікно Power Query.

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

І тоді це підсумкова таблиця, яка має значення 27 і 28 січня. Це таблиця або результат, який ви повинні мати.

Імпортуйте свої дані з будь-якого джерела, з якого ви їх отримуєте, і просто змініть тип даних.

Розділення різних валют

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

Коли ви групуєте їх, обов’язково згрупуйте їх у всіх рядках, щоб, наприклад, якщо ви натиснете «Євро», відображатимуться лише курси валют у євро.

Ви також можете побачити, що тут відсутні дні, а також валюта фунтів.

У LuckyTemplates є така штука під назвою Invoke Custom Function . Це функція з FillMissingRates.

Якщо ви натиснете «Євро», ви побачите повну таблицю, яка містить відсутні дані, які ви бачили в розгорнутій таблиці. Він відсортований від найстаріших до найновіших ставок.

Дізнайтеся, що роблять спеціальні функції. Якщо ви клацнете значок шестірні біля Invoke Custom Function, ви побачите назву стовпця та джерело, яке витягує, у стовпці All.

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

Наприклад, якщо ви хочете зосередитися на євро, відфільтруйте його до тікера.

Щоб визначити, яких днів бракує, об’єднайте його з таблицею дат.

Ось базова відфільтрована таблиця валют і таблиця DimDate:

Для Join Kind ви можете використовувати Full Outer, щоб додати всі рядки з обох таблиць. Це показує, чого не вистачає, і відповідні ставки.

Коли ви об’єднаєте його, ви отримаєте повну таблицю як допоміжну таблицю.

Після цього відсортуйте рядки, а потім розгорніть DimDate, щоб включити лише стовпець Date.

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

Заповнення відсутніх ставок

Важливо, щоб вони були в такому порядку, оскільки ви збираєтеся заповнити всі нулі у всіх стовпцях, крім Date.1.

Після того, як ви заповните його, ви побачите, що нульові значення змінилися на євро та останній доступний курс.

Потім видаліть із таблиці непотрібні стовпці, як-от стовпець «Дата».

Стовпець Date.1 уже містить дати, тому стовпець Date не потрібен. Потім встановіть перехресний курс на десяткове число.

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

Тепер у вас є повна таблиця курсів валют.

Однак це працює лише для цієї таблиці. Ви повинні знайти спосіб застосувати ту саму логіку до таблиці, де згруповано валюти.

Об'єднання всіх валют

Якщо у вас різні валюти, важко підтримувати різні таблиці курсів. Отже, вам потрібно об’єднати їх, щоб створити одну таблицю валют.

Для початку візьміть наявну таблицю та створіть функцію, натиснувши « Таблиця» та вибравши «Створити функцію» . Далі введіть назву функції.

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

Далі назвіть свій параметр Source, а потім введіть Let. Оскільки відфільтровані рядки було видалено, змініть значення в синтаксисі об’єднаних запитів на Джерело. Після цього видаліть джерело в кінці синтаксису, а потім натисніть «Готово».

Тепер перейдіть до пункту «Додати стовпець», натисніть «Викликати спеціальну функцію». Введіть назву стовпця, наприклад «Усі дані». Для запиту функції виберіть функцію, яка була створена раніше. Виберіть «Усі» як «Джерело», а потім натисніть «ОК».

Наступне, що потрібно зробити, це видалити всі стовпці, крім усіх даних. Потім розгорніть цей стовпець і зніміть прапорець «Використовувати оригінальну назву стовпця як префікс». Після цього натисніть OK і змініть типи стовпців.

Встановіть тип стовпців «Дата», «Тікер» і «Кросрейт» відповідно на «Дата», «Текст» і «Десятковий». Після завершення натисніть «Закрити та застосувати».

Тепер ви отримаєте комбіновану таблицю курсів валют без пропущених дат і курсів.

Створення зв’язків у моделі даних

У вашій моделі даних ви можете побачити таблицю FactCurrencyRates . Тепер створіть зв’язок Ticker to Ticker і Date to Date із цією таблицею та таблицею DimCurrencyRates і DimDate.

У своїй матриці введіть курс валюти без пропусків , щоб побачити, що тепер у ній є курси для 27-го та 28-го числа, зроблені в Power Query.

Стовпець «Останній звітний курс валюти» також показує курси за відсутні дні. Він показує однакові ставки та значення. Але різниця між ними в тому, що цей стовпець створюється в DAX.

Це міра та синтаксис цього стовпця. Це не так просто, як використовувати лише функцію в Power Query.

Подивившись на таблицю, ви побачите стовпець Конвертовані продажі без пропущених. Він показує продажі, конвертовані за допомогою стовпця «Курс валюти». Інший стовпець із перетвореними продажами використовує стовпець, створений у DAX.

Функція, яка використовується для отримання перетворених продажів. Якщо ви подивіться на вимірювання для цього стовпця, ви побачите, що SUMX переходить у таблицю DimDate.

Порівняння методів DAX і Power Query

Насправді ви можете порівняти обидва перетворені стовпці продажів, які використовували DAX і Power Query, у LuckyTemplates.

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

Далі відкрийте DAX Studio. Це програма, яка показує, як і чому все працює у вашій моделі. Після цього скопіюйте запит вашої матриці та вставте його всередину студії.

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

А потім завантажте таймінг сервера та план запитів. Оскільки ви порівнюєте продуктивність, спочатку очистіть кеш, перш ніж запускати порівняння.

Після запуску тесту можна побачити, що стовпець, який використовував DAX, отримав багато сканувань і має загальний час 71 мілісекунду.

Тепер запустіть інший стовпець, який використовував запит Power. Але спочатку перетворіть інший стовпець у синтаксисі на коментар.

Ви бачите, що стовпець працював лише 25 мілісекунд і має лише 7 сканувань.

Ви можете чітко побачити, який із методів DAX і Power Query працює швидше та краще.

Висновок

Перевагою використання методу Power Query у LuckyTemplates є те, що дані вже збережені. Отже, міра може знайти швидкість, а потім вивести її за допомогою простих функцій.

Інша особливість цього методу полягає в тому, що він не сповільнюється, якщо обчислення стає складним.

Однак це працює лише в тому випадку, якщо дані, які ви запитуєте, не потрібно перетворювати на льоту. Якщо це так, ви повинні використовувати DAX.

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