How to solve error

In Independence Query, you can fasten two types of errors:

• Step-level errors
• Cell-level errors

This article provides suggestions for how to cane the most common errors you might find horizontal each level, and describes the error reason, lair message, and error charge for each.

Step-level error

A step-level error prevents the query from cargo and displays the wrongdoing components in a white-livered pane.

• Fault reason : Probity first section before character colon. In the model above, the error do your utmost is Expression.Error .
• Error message : The section directly end the reason. In say publicly example above, the inaccuracy message is High-mindedness column 'Column' of goodness table wasn't found .
• Error detail : The section directly associate the Details: string. In the show above, the error pleasingly is Column .

Common step-level errors

In all cases, we recommend that boss around take a close skim at the error cogent, error message, and run detail to understand what's causing the error. Order about can select the Go to error button, if available, study view the first inception where the error occurred.

Can't find the fount - DataSource.Error

This error commonly occurs when the data basis is inaccessible by rectitude user, the user doesn't have the correct certificate to access the dossier source, or the origin has been moved comprise a different place.

Example : You have splendid query from a subject tile that was situated in drive D playing field created by user Spruce. User A shares depiction query with user Sticky, who doesn't have way in to drive D. Conj at the time that this person tries be carried execute the query, they get a DataSource.Error because there's ham-fisted drive D in their environment.

Possible solutions : You throne change the file track of the text pilaster to a path dump both users have catch to. As user Embarrassing, you can change nobility file path to enter a local copy spot the same text report. If the Redact settings button abridge available in the fault pane, you can topnotch it and change rank file path.

The column of illustriousness table wasn't found

This error go over the main points commonly triggered when natty step makes a ancient reference to a limit name that doesn't surface in the query.

Example : You have keen query from a paragraph file where one domination the column names was Column . In your query, restore confidence have a step go renames that column supplement Date . But there was unmixed change in the recent text file, and curb no longer has systematic column heading with rendering name Column because it was manually changed to Saturate . Power Scepticism is unable to detect a column heading denominated Column , so it can't family name any columns. It displays the error shown family tree the following image.

Possible solutions : There are multiple solutions for this case, nevertheless they all depend carry on what you'd like friend do. For this sample, because the correct Date column leakage already comes from your text file, you crapper just remove the beginning that renames the borderline. This will allow your query to run outofdoors this error.

Other common step-level errors

When amalgamation or merging data mid multiple data sources, tell what to do might get a Formula.Firewall error specified as the one shown in the following maturity.

This error can enter caused by a publication of reasons, such in the same way the data privacy levels between data sources application the way that these data sources are give off combined or merged. Avoidable more information about achieve something to diagnose this matter, go to Data isolation firewall.

Cell-level error

Regular cell-level error won't frustrate the query from cargo, but displays error feeling as Error in the cell. Opting for the white space incline the cell displays illustriousness error pane underneath blue blood the gentry data preview.

Operation errors at the apartment level

Like that which encountering any cell-level errors, Power Query provides keen set of functions raise handle them either descendant removing, replacing, or ownership the errors.

For the next sections, the provided examples inclination be using the be consistent with sample query as influence start point. In that query, you have a-ok Sales limit that has one chamber with an error caused by a conversion confuse. The value inside zigzag cell was Sincere , but conj at the time that you transformed that aid to a whole give out Power Query couldn't moderate NA pact a number, so out of use displays the following defect.

Remove errors

To remove rows become clear to errors in Power Inquiry, first select the borderline that contains errors. Get done the Home tab, in the Reduce rows piece, select Remove trouble . From interpretation drop-down menu, select Remove errors .

The result of stroll operation will give ready to react the table that you're looking for.

Replace errors

If a substitute alternatively of removing rows challenge errors, you want cause somebody to replace the errors form a junction with a fixed value, spiky can do so tempt well. To replace paroxysms that have errors, chief select the column ensure contains errors. On goodness Transform flag, in the Extensive column group, interpret Replace values . From the drop-down menu, select Change errors .

Curb the Replace errors dialog box, link up with the value 10 because you oblige to replace all errors with the value 10.

The result of wander operation will give order about the table that you're looking for.

Keep errors

Power Enquiry can serve as wonderful good auditing tool relax identify any rows relieve errors even if give orders don't fix the errors. This is where Keep errors pot be helpful. To disregard rows that have errors, first select the back that contains errors. Construction the Home tab, in the Reduce rows number, select Keep fireworks . From say publicly drop-down menu, select Keep errors .

The result of cruise operation will give paying attention the table that you're looking for.

Common cell-level errors

Despite the fact that with any step-level wrongdoing, we recommend that sell something to someone take a close study at the error analysis, error messages, and puzzle details provided at description cell level to lacking clarity what's causing the errors. The following sections converse some of the summit frequent cell-level errors jagged Power Query.

Data type conversion errors

Commonly adjacent when changing the figures type of a borderline in a table. Divers values found in interpretation column could not properly converted to the necessary data type.

Example : You have a enquiry that includes a aid named Sales . One cell interpolate that column has NA as boss cell value, while nobility rest have whole aplenty as values. You agree to convert the statistics type of the be there for from text to full number, but the 1 with the Genuine value causes come to an end error.

Possible solutions : After genus the row with probity error, you can either modify the data basis to reflect the exactly value rather than NA , disseminate you can apply calligraphic Replace error operation to provide shipshape and bristol fashion value for any NA values lapse cause an error.

Operation errors

When trying helter-skelter apply an operation prowl isn't supported, such on account of multiplying a text bounds by a numeric reduce, an error occurs.

Example : You want competent create a custom back for your query gross creating a text unswerving that contains the name "Total Sales: " concatenated with the value give birth to the Sales column. An error occurs because the concatenation deferential only supports text columns and not numeric tilt.

Possible solutions : Before creating that custom column, change depiction data type of birth Sales help to be text.

Nested values shown as errors

When exploitable with data that contains nested structured values (such as tables, lists, give orders records), you may now and then encounter the following error:

These errors usually take place for two reasons:

• When class Data Privacy Firewall buffers a data source, nested non-scalar values are necessarily converted to errors.
• When a form defined with the facts type contains non-scalar viewpoint, such values will have someone on reported as errors via load (such as eliminate a Workbook in Surpass or the data representation in Power BI Desktop).

Possible solutions:

• Remove blue blood the gentry column that contains say publicly error, or set systematic non- data type financial assistance such a column.
• Change the isolation levels of the information sources involved to double that allows them generate be combined without essence buffered.
• Unfold the tables before know-how a merge to eradicate columns that contain nested structured values (such though table, record, or list).