Ошибка ожидался токен 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:

1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
2. Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
3. 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.
4. 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.