Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to determine which clomun and what reason cause the "error" rows in data input?

Hi Power BI Gurus,

 

I guess this could be an esay question... I import an excel file into Power BI, and received an notice like below:  

error 2.JPG

 

Then I click on the view errors...    In the editor view.. how could I know what cuase this import error? which column has data quality issue? maybe it's the data type?  I am still very new to Power BI... Anyone please help~~

 

 

Thank you!!

 

Flora

 

error1.JPG

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @Anonymous,

 

Go through your table and you will have some cell with the word ERROR if you click on the right side of the cell (don't click the ERROR) on the bottom you will see cause of the error:

 

errors.gif

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Aron_Moore
Solution Specialist
Solution Specialist

When you click "View errors" it generates a copy of the query with just the error rows:

Capture.PNG

 

Look at the rows to see which column is giving you the errors.

Capture1.PNG

 

I'm guessing you're importing excel. In that case any #DIV/0, #NAME, and #VALUE with give you errors/that message. The query with errors will load, it'll just replace the error value with blank.

View solution in original post

8 REPLIES 8
Aron_Moore
Solution Specialist
Solution Specialist

When you click "View errors" it generates a copy of the query with just the error rows:

Capture.PNG

 

Look at the rows to see which column is giving you the errors.

Capture1.PNG

 

I'm guessing you're importing excel. In that case any #DIV/0, #NAME, and #VALUE with give you errors/that message. The query with errors will load, it'll just replace the error value with blank.

Anonymous
Not applicable

@Aron_Moore Thank you!  And I am able to locate the error column..  So how could I fixe the error in this particular case?  I tried to just change the trx number type from Decimal number to text, but look like it Power Bi still doesn't like it...  Any further suggestions?

 

Thank you~~

 

error 3.JPG

You should be able to convert it to text, or not convert it to decimal.

 

In your original query are you using "Use first row as headers"?

 

It throws in a Change Type step when you do this, which I almost always remove.

Capture2.PNG

Anonymous
Not applicable

Hi @Aron_Moore

 

The data just pick the first row as the header from the excel. 

 

By default, the data type is Decimal Number,  and the values display the values as "Error". I manaully chage the data type to "TEXT", but the value still shows as Error.  I click on apply query, and received below message... so confused.. what deos this column 74 refer to?   please further advise..

 

Thank you!

 

  query error.JPG

What is likely happening is your query refers to a specific named column "Column74" and not it's not there, either removed or renamed.

 

For example my query here removes all columns but "Column1", if I change the name of that column it'll break the query.

 

Working

Capture.PNGCapture1.PNG

 

Oops!

Capture3.PNGCapture2.PNG

 

If you walk backwards through the query you'll likely find which step is causing the issue.

 

No data after "Renamed Columns" so that is likely causing the issue (but we knew that).

Capture5.PNGCapture4.PNG

Hi @Anonymous,

 

Check on your query all the steps that have Change Type I believe that in one of them you are converting the specific column to number instead of text just overwrite that step by formatting the column to text.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

Hi @Anonymous,

 

Go through your table and you will have some cell with the word ERROR if you click on the right side of the cell (don't click the ERROR) on the bottom you will see cause of the error:

 

errors.gif

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix  Thank you !  This helps.. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.