Data source error power query

Работа с ошибками в Power Query

В Power Query можно столкнуться с двумя типами ошибок:

• Ошибки на уровне шага
• Ошибки на уровне ячеек

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

Ошибка на уровне шага

Пошаговая ошибка предотвращает загрузку запроса и отображает компоненты ошибок на желтой панели.

• Причина ошибки: первый раздел перед двоеточием. В приведенном выше примере причина ошибки — Expression.Error.
• Сообщение об ошибке: раздел непосредственно после причины. В приведенном выше примере сообщение об ошибке — столбец «Столбец» таблицы не найден.
• Сведения об ошибке: раздел непосредственно после строки Details: В приведенном выше примере сведения об ошибке — «Столбец«.

Распространенные ошибки на уровне шага

Во всех случаях рекомендуется внимательно ознакомиться с причиной ошибки, сообщением об ошибке и подробными сведениями об ошибке, чтобы понять, что вызывает ошибку. Вы можете нажать кнопку «Перейти к ошибке» , если она доступна, чтобы просмотреть первый шаг, в котором произошла ошибка.

Не удается найти источник — DataSource.Error

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

Пример. У вас есть запрос из текстовой плитки, которая была расположена на диске D и создана пользователем A. Пользователь A предоставляет общий доступ к запросу пользователю B, у которого нет доступа к диску D. Когда этот пользователь пытается выполнить запрос, он получает dataSource.Error , так как в своей среде нет диска D.

Возможные решения. Вы можете изменить путь к файлу текстового файла на путь, к которому у обоих пользователей есть доступ. Как пользователь Б, вы можете изменить путь к файлу, чтобы он был локальной копией того же текстового файла. Если кнопка «Изменить параметры» доступна в области ошибок, ее можно выбрать и изменить путь к файлу.

Столбец таблицы не найден

Эта ошибка обычно активируется, когда шаг создает прямую ссылку на имя столбца, которое не существует в запросе.

Пример. У вас есть запрос из текстового файла, в котором одно из имен столбцов — Column. В запросе есть шаг, который переименовывает этот столбец в date. Но в исходном текстовом файле произошло изменение, и у него больше нет заголовка столбца с именем Column , так как он был изменен вручную на Date. Power Query не удается найти заголовок столбца с именем Column, поэтому он не может переименовать столбцы. Отображается ошибка, показанная на следующем рисунке.

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

Другие распространенные ошибки на уровне шага

При объединении или объединении данных между несколькими источниками данных может возникнуть ошибка Formula.Firewall , например, показанная на следующем рисунке.

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

Ошибка уровня ячейки

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

Средства профилирования данных помогают более легко выявлять ошибки на уровне ячеек с помощью функции качества столбца. Дополнительные сведения: средства профилирования данных

Обработка ошибок на уровне ячейки

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

В следующих разделах указанные примеры будут использовать тот же пример запроса, что и начальная точка. В этом запросе есть столбец Sales с одной ячейкой с ошибкой, вызванной ошибкой преобразования. Значение внутри этой ячейки было NA, но при преобразовании этого столбца в целое число Power Query не удалось преобразовать НС в число, поэтому отображается следующая ошибка.

Удаление ошибок

Чтобы удалить строки с ошибками в Power Query, сначала выберите столбец, содержащий ошибки. На вкладке «Главная» в группе «Уменьшить строки» выберите «Удалить строки«. В раскрывающемся меню выберите «Удалить ошибки«.

Результат этой операции даст вам таблицу, которую вы ищете.

Замена ошибок

Если вместо удаления строк с ошибками необходимо заменить ошибки фиксированным значением, это также можно сделать. Чтобы заменить строки с ошибками, сначала выберите столбец, содержащий ошибки. На вкладке «Преобразование» в группе «Любой столбец » выберите «Заменить значения«. В раскрывающемся меню выберите «Заменить ошибки«.

В диалоговом окне «Замена ошибок » введите значение 10 , так как вы хотите заменить все ошибки значением 10.

Результат этой операции даст вам таблицу, которую вы ищете.

Сохранение ошибок

Power Query может служить хорошим средством аудита для выявления строк с ошибками, даже если вы не исправите ошибки. Здесь могут быть полезны ошибки keep . Чтобы сохранить строки с ошибками, сначала выберите столбец, содержащий ошибки. На вкладке «Главная» в группе «Уменьшить строки» выберите «Сохранить строки«. В раскрывающемся меню выберите «Сохранить ошибки«.

Результат этой операции даст вам таблицу, которую вы ищете.

Распространенные ошибки на уровне ячеек

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

Ошибки преобразования типов данных

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

Пример. У вас есть запрос, содержащий столбец с именем Sales. Одна ячейка в этом столбце содержит значение NA в качестве значения ячейки, а остальные имеют целые числа в качестве значений. Вы решили преобразовать тип данных столбца из текста в целое число, но ячейка со значением NA приводит к ошибке.

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

Ошибки операций

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

Пример. Вы хотите создать настраиваемый столбец для запроса, создав текстовую строку, содержащую фразу «Total Sales: » сцеплено со значением из столбца Sales . Ошибка возникает из-за того, что операция объединения поддерживает только текстовые столбцы, а не числовые.

Возможные решения. Перед созданием этого настраиваемого столбца измените тип данных столбца Sales на текст.

Источник

Handling data source errors (Power Query)

It sure feels great when you finally set up your data sources and shape data just the way you want. Hopefully, when you refresh data from an external data source, the operation goes smoothly. But that’s not always the case. Changes to the flow of data all along the way can cause problems that end up as errors when you attempt to refresh data. Some errors may be easy to fix, some may be transient, and some may be hard to diagnose. What follows is a set of strategies you can take to handle errors that come your way.

There are two types of errors that might occur when you refresh data.

Local If an error occurs in your Excel workbook, then at least your troubleshooting efforts are limited and more manageable. Perhaps refreshed data caused an error with a function, or the data created an invalid condition in a drop-down list. These errors are bothersome, but fairly easy to track down, identify, and fix. Excel also has improved error handling with clearer messages and context-sensitive links to targeted help topics to help you figure out and fix the problem.

Remote However, an error that comes from a remote external data source is another matter entirely. Something has happened in a system that might be across the street, half-way around the world, or in the cloud. These types of errors require a different approach. Common remote errors include:

Couldn’t connect to a service or resource. Check your connection.

The file you’re trying to access could not be found.

The server is not responding and may be undergoing maintenance.

This content is not available. It may have been removed or is temporarily unavailable.

Please wait. the data is being loaded.

What follows are a few suggestions to help you deal with errors you may encounter.

Find and save the specific error First examine the Queries & Connections pane (Select Data > Queries & Connections, select the connection, and then display the flyout). See what data access errors occurred and make note of any additional details provided. Next, open the query to see any specific errors with each query step. All errors are displayed with a yellow background for easy identification. Write down or screen capture the error message information, even if you don’t fully understand it. A colleague, administrator, or a support service in your organization may be able to help you understand what happened and propose a solution. For more information, see Dealing with errors in Power Query.

Get help information Search the Office Help and Training site. Not only does this contain extensive help content, but also troubleshooting information. For more information see Fixes or workarounds for recent issues in Excel for Windows.

Leverage the technical community Use Microsoft Community sites to search for discussions pertaining specifically to your problem. It is highly likely you are not the first person to experience the problem, others are dealing with it, and may have even found a solution. For more information, see The Microsoft Excel Community and Office Answers Community.

Search the web Use your preferred search engine to look for additional sites on the web that may provide pertinent discussions or clues. This can be time-consuming, but it’s a way to cast a wider net to look for answers to particularly thorny questions.

Contact Office Support At this point, you probably understand the issue much better. This can help you focus your conversation and minimize time spent with Microsoft Support. For more information, see Microsoft 365 and Office Customer Support.

Although you may not be able to fix the problem, you can find out precisely what is the problem to help others understand the situation and solve it for you.

Issues with services and servers Intermittent network and communication errors are a likely culprit. The best you can do is wait and try again. Sometimes, the problem just goes away.

Changes to location or availability A database or file has been moved, corrupted, taken offline for maintenance, or the database has crashed. Disk devices can become corrupt, and files become lost. For more information, see Recover lost files on Windows 10.

Changes to authentication and privacy It can suddenly happen that a permission no longer works, or a change was made to a privacy setting. Both events can prevent access to an external data source. Check with your administrator or the administrator of the external data source to see what has changed. For more information, see Manage data source settings and permissions and Set privacy levels.

Opened or locked files If a text, CSV, or workbook is open, any changes to the file are not included in the refresh until the file has been saved. Also, if the file is open it may be locked, and can’t be accessed until it is closed. This can happen when the other person is using a non-subscription version of Excel. Ask them to close the file or check it in. For more information, see Unlock a file that has been locked for editing.

Changes to schemas at the backend Somebody changes a table name, column name, or data type. This is almost never wise, can have a huge impact, and is especially dangerous with databases. One hopes that the database management team has put the proper controls to prevent this from happening, but slipups do occur.

Blocking errors from query folding Power Query tries to improve performance whenever it can. It’s often better to run a database query on a server to take advantage of greater performance and capacity. This process is called query folding. However, Power Query blocks a query if there is a potential for data to be compromised. For example, a merge is defined between a workbook table and a SQL Server table. The workbook data privacy is set to Privacy, but the SQL Server data is set to Organizational. Because Privacy is more restrictive than Organizational, Power Query blocks the information exchange between the data sources. Query folding occurs behind the scenes, so it may surprise you when a blocking error occurs. For more information, see Query folding basics, Query folding, and Folding with Query Diagnostics.

Often with Power Query, you can find out precisely what is the problem and fix it yourself.

Renamed tables and columns Changes to original table and column names or column headers almost certainly will cause problems when you refresh data. Queries rely on table and column names to shape data in almost every step. Avoid changing or removing original table and column names, unless your purpose is to make them match with the data source.

Changes to data types Data type changes can sometimes cause errors or unintended results, especially in functions which might require a specific data type in the arguments. Examples include, replacing a text data type in a number function or attempting to do a calculation on a non-numeric data type. For more information see Add or change data types.

Cell-level errors These types of errors won’t prevent a query from loading, but they display Error in the cell. To see the message, select whitespace in a table cell containing Error. You can remove, replace, or just keep the errors. Examples of cell errors include:

Conversion You attempt to convert a cell containing NA to a whole number.

Mathematical You try to multiply a text value by a numeric value.

Concatenation You attempt to combine strings but one of them is numeric.

Safely experiment and iterate If you are not sure that a transformation could have a negative impact, copy a query, test out your changes, and iterate through variations of a Power Query command. If the command doesn’t work, just delete the step you created and try again. To quickly create sample data with the same schema and structure , create an Excel table of several columns and rows, and then import it (Select Data > From Table/Range). For more information, see Create a table and Import from an Excel Table.

You may feel like a kid in a candy store when you first grasp what you can do with data in the Power Query Editor. But resist the temptation to eat all the candy. You want to avoid making transformations that might inadvertently cause refresh errors. Some operations are straightforward, such as moving columns to a different position in the table, and should not lead to refresh errors down the road, because Power Query tracks columns by their column name.

Other operations might lead to refresh errors. One general rule of thumb can be your guiding light. Avoid making significant changes to the original columns. To play it safe, copy the original column with a command ( Add a Column, Custom Column, Duplicate Column, and so on), and then make your changes to the copied version of the original column. What follows are the operations that can sometimes lead to refresh errors and some best practices to help things go more smoothly.

Improve efficiency by filtering data as early as possible in the query and remove unneeded data to reduce unnecessary processing. Also, use AutoFilter to search for or select specific values and take advantage of type-specific filters available in date, datetime, and date timezone columns (such as Month, Week, Day).

Data types and column headers

Power Query automatically adds two steps to your query immediately after the first Source step: Promoted Headers, which promotes the first row of the table to be the column header, and Changed Type, which converts the values from the Any data type to a data type based on the inspection of the values from each column. This is a useful convenience, but there may be times when you want to explicitly control this behavior to prevent inadvertent refresh errors.

Rename a column

Avoid renaming the original columns. Use the Rename command for columns that are added by other commands or actions.

For more information, see Rename a column.

Split copies of the original column, not the original column.

For more information, see Split a column of text.

Merge copies of the original columns, not the original columns.

For more information, see Merge columns.

Remove a column

If you have a small number of columns to keep, use Choose Column to keep the ones you want.

Consider the difference between removing a column and removing other columns. When you choose to remove other columns, and you refresh your data, new columns added to the data source since your last refresh might remain undetected because they would be considered other columns when the Remove Column step is again executed in the query. This situation won’t occur if you explicitly remove a column.

Tip There is no command to hide a column (as there is in Excel). However, if you have a lot of columns and you want to hide many of them to help focus your work, you can do the following: remove the columns, remember the Step that was created, and then remove that step before you load the query back to the worksheet.

For more information, see Remove columns.

Replace a value

When you replace a value, you are not editing the data source. Rather, you are making a change to the values in the query. The next time you refresh your data, the value you searched for may have slightly changed, or no longer be there, and so the Replace command may not work as originally intended.

For more information, see Replace values.

Pivot and Unpivot

When you use the Pivot Column command, an error can occur when you pivot a column, don’t aggregate values, but more than a single value is returned. This situation can arise after a refresh operation that changes the data in an unanticipated way.

Use the Unpivot Other Columns command when not all columns are known, and you want new columns added during a refresh operation to also be unpivoted.

Use the Unpivot Only Selected Columns command when you don’t know the number of columns in the data source, and you want make sure that selected columns remain unpivoted after a refresh operation.

Prevent errors from occurring If an external data source is managed by another group in your organization, they need to be aware of your dependency on them and to avoid changes to their systems that can cause problems downstream. Keep a record of impacts on data, reports, charts, and other artifacts that depend on the data. Set up lines of communication to ensure that they understand the impact and take the necessary steps to keep things running smoothly. Find ways to create controls that minimize unnecessary changes and anticipate consequences of necessary changes. Admittedly, this is easy to say and sometimes hard to do.

Future-proof with query parameters Use query parameters to mitigate changes to, for example, a data location. You can design a query parameter to substitute a new location, such as a folder path, file name, or URL. There are additional ways to use query parameters to mitigate issues. For more information, see Create a parameter query.

Источник

Hi @basil0713 

Download sample Excel file

If the source file isn’t found then accuracy_final_8 is null, but your code is trying to carry out operations on accuracy_final_8 even if it is null — hence the error message.

Your code is also trying to use a function that doesn’t exist Text.RowCount.  It should be Table.RowCount.

In this code I’ve reorganised it so that any steps that you want to perform after testing if the source file exists are in a small function called DoSteps.  It is easier then to call that function if the file exists, or do nothing if it doesn’t

let

    DoSteps = (the_table) =>
    let 
        #"Renamed Columns" = Table.RenameColumns(the_table,{{"Column1", "accuracy_final_8"}}),
        #"Removed Errors" = Table.RemoveRowsWithErrors(#"Renamed Columns", {"accuracy_final_8"})
    in
        #"Removed Errors",

    accuracy_final_8 = 
    let
        Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Filepath],

        Source = Table.FromColumns({Lines.FromBinary(File.Contents(Filepath & "accuracy_final_8.txt"), null, null, 1252)}),
        //Result = try Text.RowCount(Source)
        Result = try Table.RowCount(Source)
    in
        if Result[HasError] then null else Source,
    
    Final = if accuracy_final_8 = null then null else DoSteps(accuracy_final_8)
in
    Final

I’m also loading the Filepath from a table in Excel which is slightly different to the way you are doing it — but please refer to the sample Excel file linked to above.

I’ve tested this code with the sample data you screenshotted and it works fine too

Regards

Phil

Recently I was asked an interesting question by Ondra Plánička in the comments of a blog post: how can you handle errors caused by unavailable or missing data sources in Power Query?

Let’s imagine you are loading a csv file like this one into Excel using Power Query:

The M query generated by Power Query will be as follows:

let

    Source = Csv.Document(File.Contents("C:UsersChrisDocumentsSampleData.csv")

                ,null,",",null,1252),

    #"First Row as Header" = Table.PromoteHeaders(Source),

    #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header"

                ,{{"Month", type text}, {"Product", type text}, {"Sales", Int64.Type}})

in

    #"Changed Type"

If you load into the Excel Data Model you’ll see the following in the Power Pivot window:

So far so good. But what happens if you try to refresh the query and the csv file is not there any more? The query refreshes but you will see the following in the Power Pivot window:

The structure of the table that has been loaded has changed: instead of three columns you get just one, containing the error message. This wipes any selections in Excel PivotTables that are based on this table; they will need to be recreated when the source file is available once again. Similarly, any relationships between this table and other tables in the Excel Data Model get deleted and have to be added again manually when the source file is there again. Not good.

Here’s how to alter the query so that it handles the error more gracefully:

let

    //This is the original code generated by Power Query

    Source = 

      Csv.Document(File.Contents("C:UsersChrisDocumentsSampleData.csv"),null,",",null,1252),

    #"First Row as Header" = Table.PromoteHeaders(Source),

    #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",

      {{"Month", type text}, {"Product", type text}, {"Sales", Int64.Type}}),

    //End of original code

    //Define the alternative table to return in case of error    

    AlternativeOutput=#table(type table [Month=text,Product=text,Sales=Int64.Type],

      {{"Error", "Error", 0}}),

    //Does the Source step return an error?

    TestForError= try Source,

    //If Source returns an error then return the alternative table output

    //else return the value of the #"Changed Type" step

    Output = if TestForError[HasError] then AlternativeOutput else #"Changed Type"

in

    Output

While the code from the original query remains intact, the following extra steps have been added:

• The AlternativeOutput step returns a table (defined using #table) that has exactly the same columns as the csv file. This table has one row containing the text “Error” in the two text columns and 0 in the Sales column.
• The TestForError step uses a try to see whether the Source step returns an error (for example because the file is missing)
• The Output step checks to see whether TestForError found an error – if it does, it returns the table defined in the AlternativeOutput step, otherwise it returns the contents of the csv file as returned by the #”Changed Type” step.

Now when you run the query and the csv file is missing, then you see the following in the Power Pivot window:

Because this table has the same structure as the one the query returns when the csv file is present, any PivotTables connected to this table will still retain their selections and the relationships in the Excel Data Model are left intact. This means that when the csv file is back in its proper place everything works again with no extra work required.

You can download the example workbook and csv file here.

My name is Chris Webb, and I work on the Power BI CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
View all posts by Chris Webb

As any other programming language, Power Query handles errors in its own unique way and the goal of this blog post is to give you a few hints on how to audit the errors or warnings that Power Query might throw your way.

Types of Errors

Power Query primarily handles 2 types or categories of errors:

• Step level Errors – it’s main symptom is that you query simply will not load giving you a message similar to the one below

• Value Level Errors – query will load, but will have a warning saying that it loaded with errors and give you a hyperlink to see what errors it had when loading. For example, you could click on the “1 error” hyperlink shown in the image below and Power Query will create a new query with only the rows that have errors so you can audit those rows specifically.

Ways to Audit Errors

The best way to audit the errors is to go into the Power Query editor window (fka Query Editor window) and go through the steps and values to read the error messages.

For both categories or types of errors, it is extremely important to understand the Error Message which provides a path (exactly where it happened, usually the first sentence in the error message) and checking the Error Reason (exactly why it happened, usually in the details section of the error message).

An example of a Step level error with its Error message is shown in the next image:

In some cases Power Query even gives us this “Go To Error” button so we can go to the first step where we hit the error so we can fix the issue. If Power Query doesn’t give you that button, or it doesn’t get you to the first step where you error was raised then it is helpful to navigate through the steps to find out where the error first appeared.

For Value Level errors the hyperlink to the errors is an invaluable help. Clicking that hyperlink will make Power Query automatically create another query, usually with the Format “Errors in [Name of Original Query]”, with only the rows that had errors in your original query and it’ll also add an Index Column (called Row Number) to tell you exactly in what row the error happened. You can click in the whitespace next to the value errors to see the Error Message in the Cell Preview pane as shown below.

One thing to mention is that you could’ve manually gone through your original query, select the fields that you want to audit and select the option called “Keep Errors” so you can only see the errors found in those specific fields/columns:

The Most Common Errors in Power Query

From my experience over the years, there are only a handful of errors that you’ll encounter in Power Query. There are so few that I created the next list of the most common errors that might come across when working with Power Query.

Most Common Step Level Errors

1. A Data Source function Error – usually caused by Power Query not having the right credentials or unable to connect to the data source (wrong file path or server name in most cases). Example: A query is being pointed to a filepath that no longer exists and, since Power Query can’t find it or connect to it, it displays a DataSource Error as shown below

1. Missing Columns Errors – usually caused when a step is referencing a column that no longer exists. Example: The report was doing a fill down operation over a column that was named “Employee”, but suddenly the files had a change so that column would have the name “Full Name” instead. This discrepancy gives us the errors below

Most Common Value Level Errors

1. Conversion Errors – converting a text that isn’t a date to a date data type can bring an error. When a value can not be converted to the desired data type, its output will be an error as shown below (Power Query can’t convert the text string ‘——‘ to a date)

1. Operation Errors – when a operation or a function requires a specific data type for a value, but we pass a completely different data type, then its output will be an error value. Example:  in the next image you’ll see that I try multiplying a column that has a text value “1” against a column that has a numeric value 1. Since the Column1 is set to text, that is not the number 1, but just a text “1”, so that operation yields an error and the Details tells us that the operator (*) can’t be applied to it. Similar to this situation, you can find others with functions that only accept certain data types and we try to pass a completely different data type that causes errors.

To learn more cool techniques and more advanced scenarios with the M language, go ahead and enroll to the FREE trial of the Power Query Academy where I talk more in detail about these type of techniques.

Доброго всем времени суток. При подключении к google — таблицам выскакивает сообщение «DataSource.Error: Базовое соединение закрыто: Непредвиденная ошибка при приеме. Сведения: далее ссылка на сам гугл». префикс export прописан. Ранее эти запросы работали без проблем. Ситуация появилась после обновления Win10->Win11. Комп рабочий, винда лицензия. Если есть мысли как исправить — подскажите пожалуйста.

	Билял говорил, что гугл иногда меняет ссылки. Может перепроверить ? Компьютер никогда не заменит человека (©️ Hannibal Lecter)

	перепроверил. ссылка та же. скинул книгу на машину с десяткой и всё работает. проблема именно в оси, но так как срок отката (10 суток) закончился, копать надо в сторону проблем с ОСью

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI	Даниил Чусов, другие выгрузки из инета работают? Не гуглодоки? И уточните чем тащите? Что за эксель? Изменено: PooHkrd — 01.11.2021 16:09:57 Вот горшок пустой, он предмет простой…

	Других запросов в инэт нету. Офис 2021 LTSC ProPlus. В гугле корп.отчёты сотрудников по проектам. Локальные запросы работают без проблем

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI	Даниил Чусов, попробуйте, любые простейшие, хоть бы любое из вложений xlsx на данном форуме считать. Вот горшок пустой, он предмет простой…

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

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI	Даниил Чусов, короче в 11 винде отсутствует IE 10, с помощью которого PQ по умолчанию шарится в инете. В сентябре-октябре читал статью на тему как прописать в реестре, что бы PQ использовал то ли IE11, то ли Edge. Статья была от самого MS. Гуглите в ту сторону. Вот горшок пустой, он предмет простой…

	Спасибо. Это уже направление! Если найду курс и маршрут обязательно здесь отпишусь.

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI	Даниил Чусов, короче читал вот такое Но там именно про авторизацию, возможно поковырявшись там с ключами и вам чем-то поможет. Вот горшок пустой, он предмет простой…

DOCKER Пользователь Сообщений: 9 Регистрация: 20.07.2016	Ребят так и не понял есть решение этой фигни под win 11? Бесит по всем сайтам такая фигня Можно сделать всё, главное было бы желание найти решение.

	Странно…У меня уже давно Win 11, практически с её выхода — и все работает без проблем. Перешел с 10-ки совершенно безболезненно в плане PQ. Это я к тому, что возможно влияет не только сама ОС, но и связка ОС-версия Excel-версия PQ. Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

surkenny Пользователь Сообщений: 2141 Регистрация: 13.06.2014	#13 16.01.2023 16:27:09 Дмитрий(The_Prist) Щербаков, без примера сложно вообще понять. Может, там в коде ошибка P.S. У меня аналогично. Танцев с бубнами не производил, на win11 так же работает

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

1. Introduction to Power Query
2. Get data into Power Query – 5 common data sources
3. Data Refresh Power Query in Excel: 4 ways & advanced options
4. Use the Power Query editor to update queries
5. Get to know Power Query Close & Load options
6. Power Query Parameters: 3 methods
7. Common Power Query transformations (50+ powerful transformations explained)
8. Power Query Append: Quickly combine many queries into 1
9. Get data from folder in Power Query: combine files quickly
10. List files in a folder & subfolders with Power Query
11. How to get data from the Current Workbook with Power Query
12. How to unpivot in Excel using Power Query (3 ways)
13. Power Query: Lookup value in another table with merge
14. How to change source data location in Power Query (7 ways)
15. Power Query formulas (how to use them and pitfalls to avoid)
16. Power Query If statement: nested ifs & multiple conditions
17. How to use Power Query Group By to summarize data
18. How to use Power Query Custom Functions
19. Power Query – Common Errors & How to Fix Them
20. Power Query – Tips and Tricks