Ошибка ожидался токен comma - Исправление ошибок и поиск оптимальных решений проблем

let
    Источник = Folder.Files("W:Товарный запасАрхивыРасчетВставить данные за отчетную неделю"),
    #"Вызвать настраиваемую функцию1" = Table.AddColumn(Источник, "Преобразовать двоичный файл из 16 неделя", each #"Преобразовать двоичный файл из 16 неделя"([Content])),
    #"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
    #"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать двоичный файл из 16 неделя"}),
    #"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать двоичный файл из 16 неделя", Table.ColumnNames(#"Другие удаленные столбцы1"[#"Преобразовать двоичный файл из 16 неделя"]{0})),
    #"Измененный тип" = Table.TransformColumnTypes(#"Столбец расширенной таблицы1",{{"Source.Name", type text}, {"ORIGINALQTY, Int64.Type}, {"SKU", Int64.Type}, {"UHID", Int64.Type}, {"MATKL", type text}, {"FK", type text}, {"DESCR", type text}, {"CARRIERNAME", type text}, {"RECEIPTDATE", type date}, {"LASTCOST", type number}, {"SUSR1", type text}, {"CARTONGROUP", type text}, {"ORDINARY", Int64.Type}, {"RNR", Int64.Type}, {"BLOCK_ORD", Int64.Type}, {"BLOCK_RNR", Int64.Type}, {"QTY_DOST", Int64.Type}, {"QTY_SR", type number}, {"D", Int64.Type}, {"QTY_DOST_R", type number}, {"QTY_SR_R", type number}, {"ZP", Int64.Type}, {"ZD", Int64.Type}, {"QTY_ON_PALLET", Int64.Type}, {"PALLETS_ON_DC", Int64.Type}, {"PALLETS_SHIP_DAY", type number}, {"TZ_IN_PALLETS", type text}}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Измененный тип", each ([ZD] = 182)),
    #"Дублированный столбец" = Table.DuplicateColumn(#"Строки с примененным фильтром", "Source.Name", "Копия Source.Name"),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Дублированный столбец",{"Source.Name", "Копия Source.Name", "SKU", "UHID", "MATKL", "FK", "DESCR", "CARRIERNAME", "RECEIPTDATE", "LASTCOST", "SUSR1", "CARTONGROUP", "ORDINARY", "RNR", "BLOCK_ORD", "BLOCK_RNR", "QTY_DOST", "QTY_SR", "D", "QTY_DOST_R", "QTY_SR_R", "ZP", "ZD", "QTY_ON_PALLET", "PALLETS_ON_DC", "PALLETS_SHIP_DAY", "TZ_IN_PALLETS", "ORIGINALQTY"}),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Переупорядоченные столбцы","Копия Source.Name",Splitter.SplitTextByEachDelimiter({"#(tab)"}, QuoteStyle.Csv, false),{"Копия Source.Name.1", "Копия Source.Name.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Копия Source.Name.1", type text}, {"Копия Source.Name.2", Int64.Type}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип1",{"Копия Source.Name.2"}),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Удаленные столбцы","Копия Source.Name.1",Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv),{"Копия Source.Name.1.1", "Копия Source.Name.1.2", "Копия Source.Name.1.3", "Копия Source.Name.1.4", "Копия Source.Name.1.5", "Копия Source.Name.1.6", "Копия Source.Name.1.7"}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Разделить столбец по разделителю1",{{"Копия Source.Name.1.1", Int64.Type}, {"Копия Source.Name.1.2", Int64.Type}, {"Копия Source.Name.1.3", Int64.Type}, {"Копия Source.Name.1.4", Int64.Type}, {"Копия Source.Name.1.5", type text}, {"Копия Source.Name.1.6", type text}, {"Копия Source.Name.1.7", type text}}),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Измененный тип2",{"Копия Source.Name.1.2", "Копия Source.Name.1.3", "Копия Source.Name.1.4", "Копия Source.Name.1.5", "Копия Source.Name.1.6", "Копия Source.Name.1.7", "Source.Name"}),
    #"Переупорядоченные столбцы1" = Table.ReorderColumns(#"Удаленные столбцы1",{"SKU", "UHID", "MATKL", "FK", "DESCR", "CARRIERNAME", "RECEIPTDATE", "LASTCOST", "SUSR1", "CARTONGROUP", "ORDINARY", "RNR", "BLOCK_ORD", "BLOCK_RNR", "QTY_DOST", "QTY_SR", "D", "QTY_DOST_R", "QTY_SR_R", "ZP", "ZD", "QTY_ON_PALLET", "PALLETS_ON_DC", "PALLETS_SHIP_DAY", "TZ_IN_PALLETS","ORIGINALQTY", "Копия Source.Name.1.1"})
in
    #"Переупорядоченные столбцы1"

Источник

Remove From My Forums

Question
Hello Friends,

the other day Colin and Imke helped me with a power query.

The query is to import only selected text from each text file between
START and END into power query.

https://social.technet.microsoft.com/Forums/en-US/a9557aaf-0382-4d9f-848f-937b2fad88af/power-query-selected-import-from-text-files?forum=powerquery

I have now added to the code to try and get all the files from the folder
```
let
    Source = Lines.FromBinary(Folder.Files.Contents("C:UsersDanDesktopFiles"),
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    TrimmedText = Table.TransformColumns(ConvertedToTable,{},Text.Trim),
    PositionOfStart = List.PositionOf(TrimmedText[Column1], "START"),
    RemovedRowsFromTop = Table.RemoveFirstN(TrimmedText, PositionOfStart + 1),
    PositionOfEnd = List.PositionOf(RemovedRowsFromTop[Column1], "END"),
    RemovedRowsFromBottom = Table.RemoveRows(RemovedRowsFromTop, PositionOfEnd, Table.RowCount(RemovedRowsFromTop) - PositionOfEnd)
in
    RemovedRowsFromBottom
```
I am afraid something has gone wrong

I couldn’t find anything on google to help me solve this

thank you for any advice

Cheers Dan

Answers

Hi Dan,

there is no such function. You can find the M-function-library here:
https://msdn.microsoft.com/en-us/library/mt253322.aspx?f=255&MSPPError=-2147217396 and some other learning resources here:
http://www.thebiccountant.com/learning-resources/

Your example needs to be adjusted like this:

let 

// Turn Colins query into a function so that it can be applied to every row of your table / file in your folder

function = (Content) =>

let
    Source = Lines.FromBinary(Content)),
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    TrimmedText = Table.TransformColumns(ConvertedToTable,{},Text.Trim),
    PositionOfStart = List.PositionOf(TrimmedText[Column1], "START"),
    RemovedRowsFromTop = Table.RemoveFirstN(TrimmedText, PositionOfStart + 1),
    PositionOfEnd = List.PositionOf(RemovedRowsFromTop[Column1], "END"),
    RemovedRowsFromBottom = Table.RemoveRows(RemovedRowsFromTop, PositionOfEnd, Table.RowCount(RemovedRowsFromTop) - PositionOfEnd)
in
    RemovedRowsFromBottom,

// Access the content of your folder 

   Source0 = Folder.Files("C:UsersDanDesktopFiles"),

// Pass the content of column "Content" to the function above

   #"Added Custom" = Table.AddColumn(Source0, "Custom", each function([Content]))
in
    #"Added Custom"

Imke Feldmann TheBIccountant.com

Edited by

Sunday, July 17, 2016 9:59 AM
Marked as answer by
Dan_CS
Sunday, July 17, 2016 4:49 PM

Источник

[Format=»yyyyMMdd»])
то возвращает 20220131
А когда вставляю это выражение в работающий запрос
= Json.Document(Web.Contents(«https://bank.gov.ua/NBU_Exchange/exchange_site?start=20220115&end=Date.ToText(#date(2022, 01, 31), [Format=»yyyyMMdd»])&valcode=usd&sort=exchangedate&order=desc&json»))
то выдает ошибку
Expression.SyntaxError: Ожидался токен Comma.

russian

pbi

7

ответов

В данном случае comma это не запятая дословно, а разделитель. Вы в функцию Web.Contents передаёте ерунду, на что он и ругается

Ilya Shelegin

В данном случае comma это не запятая дословно, а р…

Эх, когда-нибудь дорасту до твоего уровня угадыванния)

Да че там угадывать, в функцию надо передать строку, а в неё запихивают другую функцию по среди текста не отделяя

Ilya Shelegin

Да че там угадывать, в функцию надо передать строк…

подскажите, а как ее отделить или где прочитать об этом.

Ну так посмотрите, что вы пишите «… end=Date.ToText(…»
у вас составной текст значит надо «склеить» несколько кусков «… end=» & Date.ToText()&»valcode…»

Источник

Хитрости »

23 Май 2017 16760 просмотров

Про получение курса валют функцией пользователя(UDF) при помощи VBA я уже писал в статье Получить курс валют от ЦБР. Но такой подход требует обязательного внедрения проекта VBA в файл, что не всегда удобно. Т.к. Power Query набирает популярность и доступна для всех последних версий Excel, то мне было интересно сделать с её помощью запрос получения курса валют как на одну отдельную заданную дату, так и за целый период.
Если еще не работали с этой надстройкой и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query — что такое и почему её необходимо использовать в работе?

Получение курса указанной валюты на заданную дату
Такое решение хорошо подойдет в случаях, если необходимо ежедневно обновлять курс для заданной валюты.
Самое простое — это обратиться к сервису ЦБР, который на указанную дату генерирует таблицу курсов всех валют в формате XML:
http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017
на выходе получается такая таблица:

<ValCurs Date="23.05.2017" name="Foreign Currency Market">
<Valute ID="R01010">
<NumCode>036</NumCode>
<CharCode>AUD</CharCode>
<Nominal>1</Nominal>
<Name>Австралийский доллар</Name>
<Value>42,0973</Value>
</Valute>
<Valute ID="R01020A">
<NumCode>944</NumCode>
<CharCode>AZN</CharCode>
<Nominal>1</Nominal>
<Name>Азербайджанский манат</Name>
<Value>33,1897</Value>
</Valute>
<Valute ID="R01035">
<NumCode>826</NumCode>
<CharCode>GBP</CharCode>
<Nominal>1</Nominal>
<Name>Фунт стерлингов Соединенного королевства</Name>
<Value>73,3467</Value>
</Valute>
<Valute ID="R01060">
<NumCode>051</NumCode>
<CharCode>AMD</CharCode>
<Nominal>100</Nominal>
<Name>Армянских драмов</Name>
<Value>11,7120</Value>
</Valute>
<Valute ID="R01090B">
<NumCode>933</NumCode>
<CharCode>BYN</CharCode>
<Nominal>1</Nominal>
<Name>Белорусский рубль</Name>
<Value>30,5069</Value>
</Valute>
<Valute ID="R01100">
<NumCode>975</NumCode>
<CharCode>BGN</CharCode>
<Nominal>1</Nominal>
<Name>Болгарский лев</Name>
<Value>32,2574</Value>
</Valute>
<Valute ID="R01115">
<NumCode>986</NumCode>
<CharCode>BRL</CharCode>
<Nominal>1</Nominal>
<Name>Бразильский реал</Name>
<Value>17,3666</Value>
</Valute>
<Valute ID="R01135">
<NumCode>348</NumCode>
<CharCode>HUF</CharCode>
<Nominal>100</Nominal>
<Name>Венгерских форинтов</Name>
<Value>20,4558</Value>
</Valute>
<Valute ID="R01200">
<NumCode>344</NumCode>
<CharCode>HKD</CharCode>
<Nominal>10</Nominal>
<Name>Гонконгских долларов</Name>
<Value>72,5693</Value>
</Valute>
<Valute ID="R01215">
<NumCode>208</NumCode>
<CharCode>DKK</CharCode>
<Nominal>10</Nominal>
<Name>Датских крон</Name>
<Value>84,7758</Value>
</Valute>
<Valute ID="R01235">
<NumCode>840</NumCode>
<CharCode>USD</CharCode>
<Nominal>1</Nominal>
<Name>Доллар США</Name>
<Value>56,4988</Value>
</Valute>
<Valute ID="R01239">
<NumCode>978</NumCode>
<CharCode>EUR</CharCode>
<Nominal>1</Nominal>
<Name>Евро</Name>
<Value>63,1713</Value>
</Valute>
<Valute ID="R01270">
<NumCode>356</NumCode>
<CharCode>INR</CharCode>
<Nominal>100</Nominal>
<Name>Индийских рупий</Name>
<Value>87,4933</Value>
</Valute>
<Valute ID="R01335">
<NumCode>398</NumCode>
<CharCode>KZT</CharCode>
<Nominal>100</Nominal>
<Name>Казахстанских тенге</Name>
<Value>18,1902</Value>
</Valute>
<Valute ID="R01350">
<NumCode>124</NumCode>
<CharCode>CAD</CharCode>
<Nominal>1</Nominal>
<Name>Канадский доллар</Name>
<Value>41,7891</Value>
</Valute>
<Valute ID="R01370">
<NumCode>417</NumCode>
<CharCode>KGS</CharCode>
<Nominal>100</Nominal>
<Name>Киргизских сомов</Name>
<Value>83,3685</Value>
</Valute>
<Valute ID="R01375">
<NumCode>156</NumCode>
<CharCode>CNY</CharCode>
<Nominal>10</Nominal>
<Name>Китайских юаней</Name>
<Value>81,9738</Value>
</Valute>
<Valute ID="R01500">
<NumCode>498</NumCode>
<CharCode>MDL</CharCode>
<Nominal>10</Nominal>
<Name>Молдавских леев</Name>
<Value>30,8147</Value>
</Valute>
<Valute ID="R01535">
<NumCode>578</NumCode>
<CharCode>NOK</CharCode>
<Nominal>10</Nominal>
<Name>Норвежских крон</Name>
<Value>67,3118</Value>
</Valute>
<Valute ID="R01565">
<NumCode>985</NumCode>
<CharCode>PLN</CharCode>
<Nominal>1</Nominal>
<Name>Польский злотый</Name>
<Value>15,0459</Value>
</Valute>
<Valute ID="R01585F">
<NumCode>946</NumCode>
<CharCode>RON</CharCode>
<Nominal>1</Nominal>
<Name>Румынский лей</Name>
<Value>13,8349</Value>
</Valute>
<Valute ID="R01589">
<NumCode>960</NumCode>
<CharCode>XDR</CharCode>
<Nominal>1</Nominal>
<Name>СДР (специальные права заимствования)</Name>
<Value>78,0022</Value>
</Valute>
<Valute ID="R01625">
<NumCode>702</NumCode>
<CharCode>SGD</CharCode>
<Nominal>1</Nominal>
<Name>Сингапурский доллар</Name>
<Value>40,6993</Value>
</Valute>
<Valute ID="R01670">
<NumCode>972</NumCode>
<CharCode>TJS</CharCode>
<Nominal>10</Nominal>
<Name>Таджикских сомони</Name>
<Value>64,0939</Value>
</Valute>
<Valute ID="R01700J">
<NumCode>949</NumCode>
<CharCode>TRY</CharCode>
<Nominal>1</Nominal>
<Name>Турецкая лира</Name>
<Value>15,8260</Value>
</Valute>
<Valute ID="R01710A">
<NumCode>934</NumCode>
<CharCode>TMT</CharCode>
<Nominal>1</Nominal>
<Name>Новый туркменский манат</Name>
<Value>16,1679</Value>
</Valute>
<Valute ID="R01717">
<NumCode>860</NumCode>
<CharCode>UZS</CharCode>
<Nominal>1000</Nominal>
<Name>Узбекских сумов</Name>
<Value>14,9073</Value>
</Valute>
<Valute ID="R01720">
<NumCode>980</NumCode>
<CharCode>UAH</CharCode>
<Nominal>10</Nominal>
<Name>Украинских гривен</Name>
<Value>21,4467</Value>
</Valute>
<Valute ID="R01760">
<NumCode>203</NumCode>
<CharCode>CZK</CharCode>
<Nominal>10</Nominal>
<Name>Чешских крон</Name>
<Value>23,8261</Value>
</Valute>
<Valute ID="R01770">
<NumCode>752</NumCode>
<CharCode>SEK</CharCode>
<Nominal>10</Nominal>
<Name>Шведских крон</Name>
<Value>64,7217</Value>
</Valute>
<Valute ID="R01775">
<NumCode>756</NumCode>
<CharCode>CHF</CharCode>
<Nominal>1</Nominal>
<Name>Швейцарский франк</Name>
<Value>57,9594</Value>
</Valute>
<Valute ID="R01810">
<NumCode>710</NumCode>
<CharCode>ZAR</CharCode>
<Nominal>10</Nominal>
<Name>Южноафриканских рэндов</Name>
<Value>42,7154</Value>
</Valute>
<Valute ID="R01815">
<NumCode>410</NumCode>
<CharCode>KRW</CharCode>
<Nominal>1000</Nominal>
<Name>Вон Республики Корея</Name>
<Value>50,5277</Value>
</Valute>
<Valute ID="R01820">
<NumCode>392</NumCode>
<CharCode>JPY</CharCode>
<Nominal>100</Nominal>
<Name>Японских иен</Name>
<Value>50,7193</Value>
</Valute>
</ValCurs>

Останется только правильно получить эту таблицу. Идем на вкладку Данные(Data) или Power Query —Получить данные(Get Data) —Из других источников() —Из интернета(From Web):

в появившемся окне в поле URL-адрес вписываем текст: http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017

нажимаем ОК. Появится окно предпросмотра, в котором пока ничего интересного нет. В этом окне жмем внизу кнопку Изменить(Edit). Появится окно редактора запроса, в котором будет три столбца: Valute, Attribute:Date, Attribute:name. В заголовке столбца Valute жмем кнопку с двумя развернутыми стрелками, в выпадающем списке снимаем галочку Использовать исходное имя столбца как префикс(Use original column name as prefix) и подтверждаем нажатием Ок:

в результате PowerQuery развернет таблицу всех курсов валют на указанную дату:

Тут два неприятных момента:

названия валют(столбец Name) у нас отображаются квадратиками(могут быть и ромбики и другие символы) вместо нормального «Евро», «Доллар США» и т.п. Все дело в кодировке. Power Query очень неохотно определяет кодировку запросов к файлам, в которых присутствуют русские символы. На своей практике ни разу не видел, чтобы определение было правильным
для изменения даты необходимо будет либо каждый раз создавать новый запрос либо изменять вручную данные текущего через расширенный редактор

Но оба этих нюанса мы сейчас исправим. Удобнее всего сделать это через расширенный редактор: вкладка Главная —Расширенный редактор:

Там будет следующий текст:
let
Источник = Xml.Tables(Web.Contents(«http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017»)),
#»Измененный тип» = Table.TransformColumnTypes(Источник,{{«Attribute:Date», type date}, {«Attribute:name», type text}}),
#»Развернутый элемент Valute» = Table.ExpandTableColumn(#»Измененный тип», «Valute», {«NumCode», «CharCode», «Nominal», «Name», «Value», «Attribute:ID»}, {«NumCode», «CharCode», «Nominal», «Name», «Value», «Attribute:ID»})
in
#»Развернутый элемент Valute»

Чтобы русский текст отображался корректно надо изменить кодировку на 1251.Это самая распространенная кодировка с поддержкой кириллицы для интернета и файлов XML, поэтому в большинстве случаев можно смело указывать её.
Для этого в тексте расширенного редактора ищем строку
Источник = Xml.Tables(Web.Contents(«http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017»)),
перед последней скобкой добавляем текст: , null, 1251
Источник = Xml.Tables(Web.Contents(«http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017»), null, 1251),
и нажимаем Готово. Сразу увидим, что русский текст теперь отображается правильно.
Теперь сделаем запрос динамическим, чтобы каждый день подставлялась текущая дата. Для этого в строке расширенного редактора:
Источник = Xml.Tables(Web.Contents(«http://www.cbr.ru/scripts/XML_daily.asp?date_req=23.05.2017»), null, 1251),
заменяем непосредственно дату на такой текст:
= Xml.Tables(Web.Contents(«http://www.cbr.ru/scripts/XML_daily.asp?date_req=»&DateTime.ToText(DateTime.LocalNow(), «dd.MM.yyyy»)), null, 1251),
DateTime.LocalNow() — функция языка М, которая получает текущую локальную дату в формате даты-времени
DateTime.ToText — функция языка М, которая преобразует дату в текстовое представление в указанном формате. В нашем случае нам нужен формат «dd.MM.yyyy». Его и указываем.

Все. Теперь список валют будет обновляться каждый раз на текущую дату. Остается дело за малым: установить фильтр на нужные типы валют(удобнее всего это делать через столбец CharCode) и удалить лишние столбцы.

Скачать файл с готовым запросом:

Курс валют на дату.xlsx (43,9 KiB, 1 821 скачиваний)

Курс валют за период с изменяемыми параметрами
Приведенный выше метод хорош, если курс надо получить на одну дату. А если надо получать курс валют за период дат? Например, чтобы потом использовать в таблице продаж для конвертации валюты в рубли. Плодить запросы не вариант. Однако можно использовать подключение к сайту ЦБР с параметрами. Для этого идем на вкладку Данные(Data) или Power Query —Получить данные(Get Data) —Из других источников() —Из интернета(From Web):

в появившемся окне в поле URL-адрес вписываем текст:
https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To=23.05.2017
раньше использовался этот запрос:
http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=R01235&date_req1=01.01.2017&date_req2=23.05.2017&rt=1&mode=1
но после того, как ЦБ поменял структуру сайтов и запросов — теперь эта строка подключения не актуальна — будет ошибка
где
R01235 — код валюты для получения курса. R01235 — Доллар США.
01.01.2017 — начальная дата периода для получения курса
23.05.2017 — конечная дата периода для получения курса
После нажатия Ок PowerQuery может задуматься на несколько секунд, а то и на минуту-другую, все зависит от загруженности сайта и текущего соединения. После этого появится окно навигатора следующего содержания:

Нам нужна последняя таблица — С 01.01.2017 по 23.05.2017 Динамика курса валюты Доллар США. Выделяем её в окне и нажимаем Загрузить(Load), если все устраивает и Правка(Edit), если хотим что-то изменить. Т.к. я хочу чуть больше автоматизировать процесс, то нажимаю Правка(Edit).
Сперва неплохо бы научить этот запрос выдавать таблицу курсов с указанной даты и до текущей. Для это мы можем использовать тот же подход, что и выше для одной даты. Переходим в расширенный редактор(вкладка Главная —Расширенный редактор) и в строке
Источник = Web.Page(Web.Contents(«https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To=23.05.2017»)),
вместо конечной даты записываем нужные функции:
https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To=»&DateTime.ToText(DateTime.LocalNow(), «dd.MM.yyyy»)
Однако такой подход не всегда удобен. Во-первых, не всегда нужен курс именно до текущей даты. Во-вторых, не всегда с однажды указанной. И в-третьих, очень хочется видеть курс не только для доллара США. Все это не очень сложно править руками прямо в запросе. Но ведь куда удобнее, когда можно задать даты и код валюты прямо на листе и оттуда же управлять таблицей курсов. Для этого применим способ, описанный мной в статье Относительный путь к данным PowerQuery — Вариант 2 — с применением умной таблицы на листе книги.

создаем таблицу с именем params, в которую и будем записывать начальную дату, конечную дату и код валюты (на основании этих данных будем получать таблицу курсов валют):
переходим в наш запрос и меняем строку
Источник = Web.Page(Web.Contents(«https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=01.01.2017&UniDbQuery.To=23.05.2017»)),

на такую:
Источник = Web.Page(Web.Contents(«https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=»&Excel.CurrentWorkbook(){[Name=»params»]}[Content]{0}[Код валюты]&»&UniDbQuery.From=»&Excel.CurrentWorkbook(){[Name=»params»]}[Content]{0}[Д1]&»&UniDbQuery.To=»&Excel.CurrentWorkbook(){[Name=»params»]}[Content]{0}[Д2])),

Это позволит передавать в запрос данные из созданной на листе таблицы(params):

Код валюты — указывается код валюты. Все коды можно получить при помощи запроса, который рассматривается в самом начале статьи: там на выходе получается таблица всех имеющихся валют
Д1 — начальная дата. Начиная с этой даты будут браться курсы валют
Д2 — конечная дата. Последняя дата, для которой будут браться курсы валют

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

Excel.CurrentWorkbook() — функция получения данных обо всех умных таблицах внутри книги Excel, в которой создан этот запрос(CurrentWorkbook — текущая книга)
{[Name=»params»]} — ссылка на таблицу с именем «params»
[Content] — все содержимое таблицы «params»
{0} — номер строки в указанной таблице(«params»), начиная с 0, без учета заголовков
[Код валюты] — имя столбца, из которого надо получить данные

Два главных момента:

даты в таблицу на листе необходимо записывать в текстовом формате, а не в формате даты. Иначе есть вероятность, что сайт не поймет значение как дату. Лучше всего использовать формат «ДД.ММ.ГГГГ». Можно перед записью даты просто поставить апостроф или назначить ячейкам формат текстовый. Для автоматической записи текущей даты в качестве Д2 можно использовать такую формулу:
=ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГГГ»)
=TEXT(TODAY(),»dd.MM.yyyy»)
таблица должна находиться в книге с запросом курсов валют
чтобы получить курс только на одну дату надо поставить одинаковые даты в столбцы Д1 и Д2

Теперь все, что останется сделать, это изменять параметры на свое усмотрение. После каждого изменения значения в таблице params запрос не обновится автоматом — его надо обновить принудительно. Для этого необходимо перейти на лист, с выгруженной результирующей таблицей, выделить любую ячейку в ней, перейти на вкладку Запрос(Query) и нажать Обновить(Refresh). Так же это можно сделать с вкладки Данные(Data) —Обновить все(Refresh all). Но в этом случае будут обновлены все запросы и сводные таблицы, что не всегда нужно, особенно если запросов много.

И один бонус: чтобы в этом варианте можно было получить курс за период не на одну валюту, а на несколько указанных и для каждой указать свой период, можно перейти в расширенный редактор и заменить весь текст в нем на такой:

let
    params = Excel.CurrentWorkbook(){[Name="params"]}[Content],
    rrows = Table.RowCount(params),
       GetWebContent = (lr as number) as table =>
        let
            surl = "https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ="&params{lr}[Код валюты]&"&UniDbQuery.From="&params{lr}[Д1]&"&UniDbQuery.To="&params{lr}[Д2]
        in
            Web.Page(Web.Contents(surl)){2}[Data],
    webContent = List.Transform({0..rrows-1}, each GetWebContent(_)),
    webContent2 = Table.Combine(webContent),
    PromHeaders1 = Table.PromoteHeaders(webContent2, [PromoteAllScalars=true]),
    PromHeaders2 = Table.PromoteHeaders(PromHeaders1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(PromHeaders2,{{"Дата▼", type date}, {"Единиц", Int64.Type}, {"Курс", type number}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Changed Type", "Курс на единицу", each [Курс]/[Единиц])
in
    #"Добавлен пользовательский объект"

Подтвердить изменения. Изначально изменений заметно не будет. Но главное отличие его от предыдущего в том, что первый берет данные исключительно из первой строки таблицы params, а второй запрос берет поочередно каждую строку, загружает курсы в соответствии с параметрами строки и данные по всем запросам выгружает в единую таблицу. Но и здесь не без недостатков — в таком виде запрос возвращает таблицу курсов без имени или кода валют. Поэтому запрос надо еще чуть доработать:

let
    params = Excel.CurrentWorkbook(){[Name="params"]}[Content],
    rrows = Table.RowCount(params),
       GetWebContent = (lr as number) as table =>
        let
            surl = "https://cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ="&params{lr}[Код валюты]&"&UniDbQuery.From="&params{lr}[Д1]&"&UniDbQuery.To="&params{lr}[Д2]
        in
            Table.AddColumn(Web.Page(Web.Contents(surl)){2}[Data], "Код валюты", each params{lr}[Код валюты]),
    webContent = List.Transform({0..rrows-1}, each GetWebContent(_)),
    webContent2 = Table.Combine(webContent),
    PromHeaders1 = Table.PromoteHeaders(webContent2, [PromoteAllScalars=true]),
    PromHeaders2 = Table.PromoteHeaders(PromHeaders1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(PromHeaders2,{{"Дата▼", type date}, {"Единиц", Int64.Type}, {"Курс", type number}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Changed Type", "Курс на единицу", each [Курс]/[Единиц])
in
    #"Добавлен пользовательский объект"

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

Скачать файл с готовым запросом:

Курс валют на дату.xlsx (43,9 KiB, 1 821 скачиваний)

Единственное, на что я еще хотел бы обратить внимание и что важно в обоих описанных случаях: ЦБР возвращает ставку не одним полем, а двумя связанными: Курс и Единиц. Это означает, что значение в столбце Курс не есть окончательный верный курс. Для некоторых валют этот курс выводится из расчета не на одну денежную единицу указанной валюты, а на 10 или даже 100. Например, для Армянских драмов или Датских крон. Чтобы не попасть в неприятную ситуацию я советую добавлять в запросы еще один столбец, в котором просто делить столбец Курс на Единиц. Идем на вкладку Добавить столбец(Add Column) —Пользовательский столбец(Custom Column)
в появившемся окне указываем имя столбца(я его назвал Курс на единицу), а в поле Пользовательская формула столбца(Custom column formula) записываем следующую формулу:
=[Курс]/[Единиц]

в случае с методом получением курса на одну дату, описанном в начале статьи, это будет формула:
=[Value]/[Nominal]

Так же см.:
Получить курс валют от ЦБР
Power Query — что такое и почему её необходимо использовать в работе?
Получить данные из файлов XML при помощи Power Query
План-фактный анализ в Excel при помощи Power Query
Относительный путь к данным PowerQuery

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

Question

Answers

7 ответов

Поиск по меткам

7

ответов