Устранение отсутствующих курсов валют с помощью Power Query в LuckyTemplates

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

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

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

Оглавление

• Определение недостающих данных
• Разделение разных валют
• Заполнение недостающих ставок
• Объединение всех валют
• Создание отношений в модели данных
• Сравнение методов DAX и Power Query
• Заключение

Определение недостающих данных

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

Если у вас нет ставки в какой-либо день и вы не учитываете ее, такие показатели, как «Общий объем продаж», не будут преобразованы.

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

Но обратите внимание, что 27-й и 28-й дни отсутствуют и не имеют коэффициента. Итак, если зайти в матрицу, то можно увидеть, что ставок на 27 и 28 нет.

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

Сумма 26,6983 в таблице не имеет смысла. Это просто сумма всех курсов валют выше нее, которую вам не нужно складывать, потому что они такие же, как и ниже текущего дня.

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

Итак, нажмите Transform Data , а затем откройте окно Power Query.

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

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

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

Разделение разных валют

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

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

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

В LuckyTemplates есть такая штука, как « Вызов пользовательской функции» . Это функция с FillMissingRates.

Если вы нажмете Евро, вы увидите полную таблицу, содержащую недостающие данные, которые вы видели в расширенной таблице. Он отсортирован от самых старых до самых новых ставок.

Выясните, что делают пользовательские функции. Если щелкнуть значок шестеренки рядом с «Вызвать пользовательскую функцию», вы увидите имя столбца и источник, который извлекается в столбце «Все».

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

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

Чтобы выяснить, какие дни отсутствуют, объедините его с таблицей дат.

Вот базовая отфильтрованная таблица валют и таблица DimDate:

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

Когда вы объедините его, он даст вам полную таблицу в качестве подтаблицы.

После этого отсортируйте строки, а затем разверните DimDate, чтобы включить только столбец Date.

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

Заполнение недостающих ставок

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

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

Затем удалите из таблицы ненужные столбцы, такие как столбец «Дата».

Столбец Date.1 уже содержит даты, поэтому столбец Date не нужен. Затем установите Crossrate на Decimal Number.

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

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

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

Объединение всех валют

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

Для начала возьмите существующую таблицу и создайте функцию, нажав « Таблица» и выбрав «Создать функцию» . Затем введите имя функции.

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

Затем назовите свой параметр Source, а затем введите Let. Поскольку отфильтрованные строки были удалены, измените значение в синтаксисе объединенных запросов на Источник. После этого удалите Source в конце синтаксиса и нажмите Done.

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

Следующее, что нужно сделать, это удалить все столбцы, кроме всех данных. Затем разверните этот столбец и снимите флажок «Использовать исходное имя столбца в качестве префикса». После этого нажмите 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 query. Но сначала превратите другой столбец синтаксиса в комментарий.

Вы можете видеть, что столбец работал всего 25 миллисекунд и имеет только 7 сканирований.

Вы можете четко видеть, какие из методов DAX и Power Query работают быстрее и лучше.

Заключение

Преимущество использования метода Power Query в LuckyTemplates заключается в том, что данные уже сохранены. Таким образом, мера может найти скорость, а затем вывести ее с помощью простых функций.

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

Однако это работает только в том случае, если данные, которые вы запрашиваете, не нужно преобразовывать на лету. Если это так, вы должны использовать DAX.

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