Выборка данных по курсу валют за период. Часть 1.
Начало работы.
Перед вами первая часть урока по использованию информации выбранных курсов валют за заданный период для перевода значения продаж в тенге в валюту. Для работы мы будем использовать данные с сайта национального банка Республики Казахстан. В результате работы наш отчет станет выглядеть как на рисунке ниже. Естественно, что цвета и прочее форматирование визуальных элементов изменять в своем стиле не запрещено.
Рисунок 1
Используемые действия
Приступим к работе и определяемся с шагами.
В данном уроке мы:
- Подключим таблицу Excel с данными о продажах.
- Создадим параметры для ограничения диапазона дат.
- Создадим запрос для вывода информации о периоде выборки и выборе валюты по коду.
- Получим значение курса валют за выбранный с помощью параметров период,
- Напишем меры для вычисления базовых показателей.
Подключение данных.
Открываем программу Power BI Desktop и выбираем вариант «Пустой отчет». На вкладке «Главная» нажимаем кнопку «Преобразование данных» и попадаем в окно редактора Power Query.
Рисунок 2
В редакторе на вкладке «Главная» даем команду «Создать источник» и выбираем вариант «Книга Excel”. Указываем файл «Реализация 2021 Павлодар.xlsx», ссылку на который можно найти в конце статьи. В окне навигаторы отмечаем лист «Реализация» и нажимаем «ОК».
Рисунок 3
Рисунок 4
Создание параметров
В редакторе Power Query открываем управление параметрами:
Рисунок 5
Создаем два параметра StartDate и EndDate. В них будем хранить начальную и конечную дату выбранного периода. Учитываем следующее:
- И в номере месяца, и в числе должны быть двухзначные значения. То есть не 3.9, а 03.09;
- Год представлен четырехзначным числом;
- StartDate не может быть ранее End Date;
- Обе даты не могут выходить за 2021 год;
- Обе даты представлены текстовыми значениями;
Пример добавления параметра StartDate на рисунке ниже.
Рисунок 6
Значения созданных параметров:
StartDate – 01.02.2021;
EndDate – 16.02.2021;
Создаем запросы с данными о периоде и кодами валют
Подготовим запросы для вывода информации о периоде выборки и для выбора кода валюты.
На вкладке «Главная» нажимаем кнопку «Создать источник». Выбираем вариант «пустой запрос».
Рисунок 7
В строке формулы вводим
= StartDate&” – “&EndDate
Рисунок 8
Переименовываем запрос. Задаем для него название «Диапазон выборки». На этом настройка запроса завершена.
Рисунок 9
Перейдем к созданию запроса с кодами валют. Мы будем использовать варианты KZT, USD, EUR для вывода значений в казахстанских тенге, в долларах США и в евро соответственно.
На вкладке «Главная» нажмем кнопку «Введите данные». Открывается окно создания таблицы, в котором вносим значения как на изображении ниже.
Рисунок 10
Получаем значение курса валют за выбранный период и настраиваем запросы.
Извлекаем с сайта Национального банка информацию о курсе валют и преобразовываем его.
Приступаем к получению значения о курсе валют за последний период Набираем в окне браузера адрес https://nationalbank.kz/ru и переходим в раздел «Статистика».
Рисунок 11
Здесь нас интересуют ссылка «Курсы валют и стоимость золота» → «Ежедневные официальные (рыночные) курсы валют.
Рисунок 12
Переходим по последней указанной ссылке, отмечаем в таблице внизу доллар и 1 евро и нажимаем кнопку «показать отчет»
Рисунок 13
Откроется страница с отчетом по выбранным валютам за последнюю неделю. Копируем из адресной строки адрес этой страницы, закрываем браузер и возвращаемся в окно редактора Power Query, где создадим и настроим запрос получения курса за период.
На вкладке «Главная» выбираем команду: «Создать источник» → «Интернет». Вставляем в поле URL адреса скопированную из браузера строку и нажимаем ОК.
Рисунок 14
В окне навигатора слева отмечаем пункт «Таблица 1», нажимаем ОК и запрос готов.
Рисунок 15
Теперь необходимо изменить источник запроса, указав вместо явных начальной и конечной даты периода выборки параметры, созданные нами ранее. Щелкаем правой кнопкой в списке запросов слева запрос «Таблица 1» и выбираем внизу пункт «Расширенный редактор».
Вносим изменения в сроке в скобках за Web.BrowserContents:
Рисунок 16
Например, было так:
“https://nationalbank.kz/ru/exchangerates/ezhednevnye-oficialnye-rynochnye-kursy-valyut/report?beginDate=30.10.2025&endDate=06.11.2025&search-exchanges=&rates%5B%5D=5&rates%5B%5D=6″
А стало так:
https://nationalbank.kz/ru/exchangerates/ezhednevnye-oficialnye-rynochnye-kursy-valyut/report?beginDate=”&StartDate&”&endDate=”&EndDate&”&search-exchanges=&rates%5B%5D=5&rates%5B%5D=6″. Другими словами, заменяем первую дату в сроке на комбинацию”&StartDate&”, а вторую на ”&EndDate&” ( в обоих случаях кавычки и регистр важны!).
Рисунок 17
Нажимаем «Готово», запрос обновится и покажет информацию за период, заданный нашими параметрами.
Настроим текущий запрос «таблица 1». Переименуем его в «Курсы валют» и укажем для первого столбца с датами название «период». Удерживая CTRL выделяем поля «ДОЛЛАР США» и «ЕВРО». Используя щелчок правой кнопкой мыши по заголовку любого из них, вначале запускаем пункт «Замена значений».
Значение для поиска – точка.
Заменить на – запятая.
После замены снова вызываем контекстное меню на заголовке одного из выделенных столбцов и выбираем команду «Изменить тип» → «Десятичное число». Далее нажмем на запросе «Курсы валют» в списке запросов и выберем пункт «Включить в загрузку» для отключения.
Переходим в запрос «реализация».
На вкладке главная нажимаем кнопку «Объединить запросы»
Рисунок 18
В верхней части окна «Слияние» выбираем запрос «Реализация» отмечаем щелчком по заголовку поле «дата продаж». Снизу указываем запрос «Курсы валют» и указываем поле «период». Нажимаем ОК. В запросе реализация на поле «курсы валют» нажимаем кнопку «развернуть».
Рисунок 19
Оставляем отмеченными только флажки у полей «ДОЛЛАР США» и «ЕВРО». Все остальные флажки, в том числе галочку «использовать исходное имя столбца как префикс», снимаем.
Рисунок 20
Переходим в поле «ЕВРО» или «ДОЛЛАР США» и с помощью фильтра отключаем значение NULL.
На этом настройка запросов завершена. Нажимаем на вкладке главная кнопку «закрыть и применить». После этого произойдет переход в окно Power BI Desktop, а окно редактора Power Query закроется. После применения изменений наша запросы отобразятся на панели «Данные» справа
Создадим меры для вычисления базовых показателей по валютам и проверим работу.
Для хранения мер создадим отдельную таблицу. На вкладке «моделирование» нажимаем кнопку «создать таблицу». Вводим формулу:
_Расчеты=
Обратите внимание – после знака равенства ничего нет. Нажмем Enter.
Создадим в этой таблице две меры:
USD = AVERAGE(‘реализация'[ДОЛЛАР США])
EUR=AVERAGE(‘реализация'[ЕВРО])
Проверим работу отчета.
Перенесем в верхнюю часть холста элемент «Карточка» и добавим в него поле «диапазон выборки» из одноименной таблицы.
В нижней части расположим гистограмму с группировкой. Из таблицы реализация в ось Х перенесем поле «Даты продаж», в ось Y – меры USD и EUR из таблицы _Расчеты. На первый взгляд, значения каждой меры одинаковы, но это не так. Чтобы убедиться в этом, достаточно просто поменять тип на график или в окне форматирования включить метки данных, указав для значений два знака после запятой.
Рисунок 21
А теперь проверим работу отчета. На вкладке «Главная» дадим команду «Преобразование данных» → «Изменить параметры».
Рисунок 22
Для параметра StartDate укажем значение 05.07.2021, а для параметра EndDate – 10.07.2021.
Рисунок 23
После нажатия ОК нажимаем «Применить изменения».
Рисунок 24
После загрузки информация отчета обновилась.
Рисунок 25
На этом наш урок подошел к концу. В следующем занятии мы рассмотрим пересчет сумм продаж и затрат по курсу выбранных валют за указанный период, оформим наш отчет и экспортируем его в PDF.
Я же прощаюсь с вами и желаю успеха в работе!
