Ожидался токен comma что значит
Здравствуйте. Подскажите, пожалуйста. Почему когда ввожу в Query Date.ToText(#date(2022, 01, 31),
[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.
7 ответов
В данном случае comma это не запятая дословно, а разделитель. Вы в функцию Web.Contents передаёте ерунду, на что он и ругается
Эх, когда-нибудь дорасту до твоего уровня угадыванния)
Да че там угадывать, в функцию надо передать строку, а в неё запихивают другую функцию по среди текста не отделяя
Common Power Query errors & how to fix them
I’m sure you’ve not got this far without encountering your fair share of Power Query errors. Just like Excel and other applications, Power Query has its own unique error messages. You’ve probably forgotten the first time you encountered the #NAME? or #VALUE! errors in Excel, but over time you hopefully worked out what to do when they arose. Now you are seeing Power Query errors, which probably appear strange and unfamiliar. It can be daunting at first, but over time you will understand what the errors are and what causes them.
While we can’t cover every error, the purpose of this post is to help demystify some of the more common errors you are likely to encounter.
Types of Power Query errors
Error messages can appear in various places, such as in the Queries & Connections pane, within the Power Query Editor, or maybe just as a value in a field.
I have grouped the common errors into three types:
- Process creation errors
- Data processing errors
- Software bugs
We look at each of these and find out how to fix the most common issues.
Process creation errors
Process creation errors occur as we build a query. These are driven by either errors in the M code or our lack of understanding of how Power Query works.
M code errors
M code errors can be challenging to find, especially if we are new to the language. A comma, a mistyped word, or even a capital letter is enough to cause the process to fail. The three main places where we can edit M code are:
- Custom Columns
- Advanced Editor
- Formula Bar
Let’s start by looking at Custom Columns, then move on to look at the Advanced Editor and Formula Bar.
Custom Columns
Of the M coding options, the Custom Column feature is the most accessible and the one we are most likely to use
Custom Columns contain a syntax check at the bottom of the screen to help guide us with formulas. Unfortunately, unless we’ve been working with Power Query for a while, we won’t understand what many of these messages mean.
The screenshot above shows the Token RightParen expected error message (we can also see a red squiggly underline below the comma). This is just one of many potential messages. As we type into the formula box, the message will change. Therefore, it is not worth looking at this message until we think the formula is finished. If the Show error link is visible, we can click it to take us to where the problem is.
Once you know what the messages mean, they are not as confusing as might initially seem. The most common warnings you’ll come across are:
- Token Literal expected means the next thing in the formula is expected to be a value, column name, or function.
- Token Then expected, or Token Else expected means the words then or else are expected to be entered. These will appear when writing an if statement.
- Token RightParen expected means that a closing bracket (or parentheses depending on your local vernacular), is expected to close a formula.
- A Comma cannot precede a RightParen means what it says; a comma cannot be directly in front of a closing bracket. There are no circumstances in M where this should be necessary.
- Invalid literal indicates an issue with the value entered as an argument (this often occurs when a text string has not been closed using the double quotation character).
- Token EoF expected usually occurs when an invalid function name is used, or it uses the wrong case (for example, if is a valid command, while If with an upper case I is not).
- Token internal expected means the logical test, true value, or false value of an if statement is missing, or a formula contained within these arguments is incomplete.
- The formula is incomplete usually indications no formula has been entered (only the equals symbol in the formula box).
Once we get the message that No syntax errors have been detected, we can click the OK button to close the window. Of course, this doesn’t mean the formula or data types are correct, but the syntax has been entered correctly.
Advanced Editor & Formula Bar
The Advanced Editor and Formula bar accept changes even if it causes an error. Unfortunately, this means the variety of error messages increases when using these features:
- The Advanced Editor has the same warning message at the bottom as a Custom Column but allows us to click Done even if there is an error in the code.
- The Formula Bar has no error checks. We can make any changes to the code and press the Enter key to accept those changes without any checks.
Given the multitude of possible errors we could create, we can’t go through all of them. However, it is much easier to troubleshoot once you know how to read the error message.
Advanced Editor syntax errors
Where there are syntax errors in the Advanced Editor, it highlights them with a red squiggly underline and describes the error at the bottom.
In the example above, the comma is missing at the end of the Source step. Therefore, this creates an error at the start of the #”Changed Type” step.
The underline may not show us exactly where the issue is; however it highlights at what point Power Query identifies the error. So we know it should be in the code prior to the error.
Expression syntax errors
As noted above, nothing stops us from entering errors into the Advanced Editor or Formula Bar.
The screenshot below shows an Expression.SyntaxError… hmmm… what does that mean?
If we look below the error message, Power Query has kindly shown us where the error is. If you notice, there is an arrow —->; this indicates the line that contains the error. By looking along that line, we find a group of ^^^; these pinpoint where the error resides.
In our example above, the error is that we have used a data type of tet, which is invalid.
Where there are multiple errors in the code, we may need to go through several rounds of error fixing as the error message will only show one error at a time.
Formula.Firewall error
There is a very frustrating error, which will rear its head from time to time – the dreaded Formula.Firewall error.
This error can take two forms:
Error message #1
Formula.Firewall: Query ‘____’ (step ‘____’) is accessing the data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
Error message #2
Formula.Firewall: Query ‘____’ (step ‘____’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
What do these mean? And how can we fix it?
Power Query does not like to use two data sources with different privacy settings. This usually occurs when there are:
- External and internal data sources combined in a single query
- Dynamic data sources used to define the source of another query
The following steps should fix the Formula.Firewall error.
Apply correct privacy settings
Let’s start by applying the privacy settings. We can do this by ignoring privacy or using the correct setting for each data source.
Ignore privacy
This first option is not ideal, as it ignores the data privacy settings entirely. However, it’s a useful little fix if you are the only person accessing the data.
Click File > Option Settings > Query Options.
The Query Options window dialog box. Select Privacy > Always ignore Privacy Level settings, then click OK.
Apply privacy for each data source
Alternatively, rather than ignoring the privacy settings, we could set them correctly.
To set the data source for inputs, click File > Options > Data source settings.
In the data source settings dialog box, select the source and click edit permissions. This allows us to set the privacy setting for each source.
There are four privacy settings:
- None: There are no privacy settings applied. Microsoft recommends only using this in a controlled environment.
- Private: The data is confidential or sensitive and should not be shared. This data cannot be shared with another data source.
- Organizational: The data can be shared within the organization. This data can only be shared with other organization data sources.
- Public: The data can be shared with any other data source, including public or organizational sources.
We should set the correct privacy level for our data sources.
Flattening queries
If there is still a Formula.Firewall error, we can combine the queries into a single query. The most straightforward approach to achieve this is shown in this post: https://exceloffthegrid.com/power-query-source-cell-value/
Data processing errors
Data processing errors occur when the data is fed through the transformation process. There may be nothing specifically wrong with the data or the process, yet the two don’t work well together. It could be something as simple as the transformation steps expecting to find a column called “Product”, but a “Product” column does not exist in the data set. Neither the data nor the process is incorrect, but they just don’t fit together.
The most common errors in this area are:
- Wrong source location
- Column name changes
- Incorrect data types
Let’s look at each of them in a bit more detail
Wrong source location
The wrong source location error occurs when a file or database changes location, or a server has crashed, and therefore the source cannot be accessed. Either way, Power Query can’t find the source data.
After refreshing, an error message like the following will appear, detailing the file location it cannot find.
We also see an error in the Queries & Connections window. If we double-click the query, we find out more detail about the error.
The Power Query editor opens and shows the following message. Click Go To Error to go to the exact step.
Finally, we can click Edit Settings to change the source location in the window.
There are other, and maybe better, options for changing the source data location; I have written about this in a previous post, so check out that for more details.
Missing column names
Generally, Column header names are hardcoded somewhere within the M code. Therefore, any changes in source data structure can trigger the following error.
The Queries & Connections pane will show the same Download did not complete error we saw earlier. Opening the Query reveals further details about the error.
Ideally, we should aim to build queries that can be flexible when column names change, though that isn’t always possible.
As a quick fix, we can either:
- Change the header name in the source data
- Correct the hard-coded value in the M code through the Advanced Editor or Formula Bar
- Delete the old step and insert a new one that correctly picks up the new column name.
But you must be careful; poorly implemented changes can cause other problems further down in the query.
Incorrect data types
Data type errors will not prevent the data from loading into the query; instead, those cells are loaded as blank. Queries and Connections pane shows the error and indicates the number of lines with errors.
The screenshot above shows 50 errors, but it could easily be just 1 or 2, depending on the structure of the data.
Data type errors occur when:
- Data is converted from one type to another – for example, trying to change a text string into a decimal data type
- Incorrect data types used within functions – for example, trying to use a number function on a text data type, or trying to multiply text values
Excel is very forgiving and will happily switch between data types where it can. However, power Query is not as forgiving; therefore, getting the correct data type is essential.
After opening the query, Power Query shows the errors. The pink color below the column header displays the % of errors found in the first 1000 records.
If the error is not found within the first 1000 records:
- Change the setting in the status bar to column profiling based on the entire data set.
- Filter to include only errors by clicking Home > Keep Rows > Keep Errors
After clicking the word “Error” within the Preview Window, it provides details about the specific issue.
In the screenshot above, we can see that Power Query was trying to convert a text value into a date, which caused the error.
While there may be multiple lines with errors, it does not mean you must fix each row individually. Changing one step may be enough to fix all the errors at the same time.
Software bugs
Finally, there is another unfortunate type of error that is outside of our control; software bugs.
When I started using Power Query, I came across two issues (though I didn’t know they were bugs at the time). In both cases, I concluded it was my fault for not understanding the tool correctly. However, it wasn’t me, but the software which was not working correctly.
As Power Query is continually updated, bugs can come and go quickly as newer versions are released. However, I would say that over the past few years, Power Querty has become robust and rarely suffers from issues.
Hopefully, you will not encounter any of the problems I had; they have already been resolved. Therefore, if you meet an issue where the software is not behaving as documented, then updating to the newest version should resolve the issue. Also, ensure you report any issues to Microsoft; they can only fix issues if they know they exist.
Conclusion
Power Query error messages can seem confusing as they use terms that we are unfamiliar with. However, I hope this post has helped you to identify your error and provides suggestions on how to fix it.
Read more posts in this Introduction to Power Query series
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn’t until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I’m guessing the examples in this post don’t exactly match your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Token Comma Expected in Power Query Advanced Editor Error Message
Would anyone please review the following code to see why I keep getting a "Token Comma Expected" error message in Power BI Advanced Editor M Code:
1 Answer 1
Make sure let is lowercase (let and not Let).
-
The Overflow Blog
Related
Hot Network Questions
Subscribe to RSS
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.2.10.43235
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.
Power Query — How to fix the "Expression.SyntaxError: Token Comma expected".
Title is pretty self-explanatory. New to PQ and I am trying to create a conditional column by using the following formula:
What I am trying to write in the formula is: If the cell/record on Amount column is null AND the cell/record on column Balance is not null, then return value from Balance column, if not, return value from Amount column.
Using the IF(AND formula from excel as a reference, I am adding a coma after one of the nulls and one after the balance, but I am getting the error reference on the post title.
Expression.syntaxerror: token comma expected.
Are you wondering why the expression.syntaxerror: token comma expected. error message occur?
In this article, we are going to show you how to fix the expression.syntaxerror token comma expected .
Also, you will understand why this error appears in your code, so you just have to continue reading until the end of this discussion.
What is “expression.syntaxerror: token comma expected”?
Please enable JavaScript
The expression.syntaxerror: token comma expected. error message occurs when there’s a problem with the syntax of an expression or with the positioning of the comma in your code.
In simple words, this error message is raised when a comma is either missing or in the wrong spot in your code.
The comma serves as a separator between different elements or arguments within the expression, enabling the programming language to distinguish between them.
Without the comma, the interpreter or compiler gets confused and throws this error message.
Why does the expression.syntaxerror: token comma expected. occur?
This error message occurs due to several factors, such as:
�� When you forget to put a comma between the things you give to a function.
�� If you put a comma in the wrong spot when making a list or a group of things.
�� when you make a dictionary using curly braces, you need to put a comma between each thing in the dictionary. If you don’t put the comma in the right place or forget it, you’ll see an error message indicates expression.syntaxerror token comma expected.
How to “expression.syntaxerror: token comma expected.”?
To fix the expression.syntaxerror: token comma expected. error, you need to find where in your code a comma , is missing and add it.
This error can occur in various programming languages when the syntax rules for using commas are not followed correctly. For instance, in Python, this error can occur when defining a tuple with only one element and forgetting to add a trailing comma.
Let’s take a look at this example:
Incorrect code:
As you can see, we’re trying to define a dictionary with three key-value pairs. In Python, when defining a dictionary, we need to separate each key-value pair with a comma (,).
If we forget to add a comma between two key-value pairs, Python will interpret the second key as part of the value of the first key-value pair, and we’ll get this error message.
Corrected code:
When we put the missing comma in the fixed code, we are telling Python that we have three separate key-value pairs. This simple correction solves the error.
Conclusion
In conclusion, the expression.syntaxerror: token comma expected. error message occurs when there’s a problem with the syntax of an expression or with the positioning of the comma in your code.
To fix this error, you need to find where in your code a comma, is missing and add it.
This error can occur in various programming languages when the syntax rules for using commas are not followed correctly.
By executing the solutions above, you can master this SyntaxError with the help of this guide.
You could also check out other SyntaxError articles that may help you in the future if you encounter them.
We are hoping that this article helps you fix the error. Thank you for reading itsourcecoders ��
Здравствуйте. Подскажите, пожалуйста. Почему когда ввожу в Query Date.ToText(#date(2022, 01, 31),
[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.
7 ответов
В данном случае comma это не запятая дословно, а разделитель. Вы в функцию Web.Contents передаёте ерунду, на что он и ругается
Эх, когда-нибудь дорасту до твоего уровня угадыванния)
Да че там угадывать, в функцию надо передать строку, а в неё запихивают другую функцию по среди текста не отделяя
Курс валют при помощи Power Query
Про получение курса валют функцией пользователя(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 доллар</Name> <Value>42,0973</Value> </Valute> <Valute манат</Name> <Value>33,1897</Value> </Valute> <Valute стерлингов Соединенного королевства</Name> <Value>73,3467</Value> </Valute> <Valute драмов</Name> <Value>11,7120</Value> </Valute> <Valute рубль</Name> <Value>30,5069</Value> </Valute> <Valute лев</Name> <Value>32,2574</Value> </Valute> <Valute реал</Name> <Value>17,3666</Value> </Valute> <Valute форинтов</Name> <Value>20,4558</Value> </Valute> <Valute долларов</Name> <Value>72,5693</Value> </Valute> <Valute крон</Name> <Value>84,7758</Value> </Valute> <Valute США</Name> <Value>56,4988</Value> </Valute> <Valute рупий</Name> <Value>87,4933</Value> </Valute> <Valute тенге</Name> <Value>18,1902</Value> </Valute> <Valute доллар</Name> <Value>41,7891</Value> </Valute> <Valute сомов</Name> <Value>83,3685</Value> </Valute> <Valute юаней</Name> <Value>81,9738</Value> </Valute> <Valute леев</Name> <Value>30,8147</Value> </Valute> <Valute крон</Name> <Value>67,3118</Value> </Valute> <Valute злотый</Name> <Value>15,0459</Value> </Valute> <Valute лей</Name> <Value>13,8349</Value> </Valute> <Valute (специальные права заимствования)</Name> <Value>78,0022</Value> </Valute> <Valute доллар</Name> <Value>40,6993</Value> </Valute> <Valute сомони</Name> <Value>64,0939</Value> </Valute> <Valute лира</Name> <Value>15,8260</Value> </Valute> <Valute туркменский манат</Name> <Value>16,1679</Value> </Valute> <Valute сумов</Name> <Value>14,9073</Value> </Valute> <Valute гривен</Name> <Value>21,4467</Value> </Valute> <Valute крон</Name> <Value>23,8261</Value> </Valute> <Valute крон</Name> <Value>64,7217</Value> </Valute> <Valute франк</Name> <Value>57,9594</Value> </Valute> <Valute рэндов</Name> <Value>42,7154</Value> </Valute> <Valute Республики Корея</Name> <Value>50,5277</Value> </Valute> <Valute иен</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 943 скачиваний)
Курс валют за период с изменяемыми параметрами
Приведенный выше метод хорош, если курс надо получить на одну дату. А если надо получать курс валют за период дат? Например, чтобы потом использовать в таблице продаж для конвертации валюты в рубли. Плодить запросы не вариант. Однако можно использовать подключение к сайту ЦБР с параметрами. Для этого идем на вкладку Данные (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 — текущая книга)
- — ссылка на таблицу с именем «params»
- [Content] — все содержимое таблицы «params»
- — номер строки в указанной таблице(«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="¶ms
Подтвердить изменения. Изначально изменений заметно не будет. Но главное отличие его от предыдущего в том, что первый берет данные исключительно из первой строки таблицы 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="¶ms
Теперь в таблице будет еще один столбец — код валюты, чтобы можно было удобно просматривать любую из загруженных валют. В файле приложен именно такой вариант, как более сложный и удобный.
Скачать файл с готовым запросом:
Курс валют на дату.xlsx (43,9 KiB, 1 943 скачиваний)
Единственное, на что я еще хотел бы обратить внимание и что важно в обоих описанных случаях: ЦБР возвращает ставку не одним полем, а двумя связанными: Курс и Единиц. Это означает, что значение в столбце Курс не есть окончательный верный курс. Для некоторых валют этот курс выводится из расчета не на одну денежную единицу указанной валюты, а на 10 или даже 100. Например, для Армянских драмов или Датских крон . Чтобы не попасть в неприятную ситуацию я советую добавлять в запросы еще один столбец, в котором просто делить столбец Курс на Единиц. Идем на вкладку Добавить столбец (Add Column) —Пользовательский столбец (Custom Column)
в появившемся окне указываем имя столбца(я его назвал Курс на единицу ), а в поле Пользовательская формула столбца (Custom column formula) записываем следующую формулу:
=[Курс]/[Единиц]
в случае с методом получением курса на одну дату, описанном в начале статьи, это будет формула:
=[Value]/[Nominal]