Create Data Types in Power Query

[Manish4993]

Hi All,

Please go through with the screenshots. I was creating a custom Data Type by using a excel table data set. In final screenshot i got a #Field error, although non of the cell was blank in that selected entire data type row

 

[Alex Blakenburg]

In normal errors (I assume it works for custom data type errors) you should be able to click on where it says "8 errors" and it should open the query editor as a new query with just those rows in it.

 

[Manish4993]

In normal errors (I assume it works for custom data type errors) you should be able to click on where it says "8 errors" and it should open the query editor as a new query with just those rows in it.

No data found after clicking on errors.

 

[Alex Blakenburg]

No data found after clicking on errors.

View attachment 37089

In PQ can you go Add Column > Index Column > From 1
Then filter on that column so you get number 78555 - 78557 (expecting 78556 to be the problem row - its 78557 in your screenshot but that includes 1 heading row)
Then compare 78556 to the other 2 rows and perhaps drill into that row to be able to more clearly see what makes it up.

 

[Manish4993]

In PQ can you go Add Column > Index Column > From 1
Then filter on that column so you get number 78555 - 78557 (expecting 78556 to be the problem row - its 78557 in your screenshot but that includes 1 heading row)
Then compare 78556 to the other 2 rows and perhaps drill into that row to be able to more clearly see what makes it up.

I used the steps you said but still i am not able to find any error in that row range from 78555 to 78557. What to do now? Please connect me @ manish4993@gmail.com there i will share my laptop screen to u for better understanding.

 

[Alex Blakenburg]

Hello Manish,

I believe we have resolved your issue. The steps we followed are:-

Trouble Shooting Steps

1. Click on each of the steps starting from Source and look for columns headings that instead of a green bar have a little bit of red and a diagonal striped green.
   This signifies errors in the column.
   
   
   
   
2. When this appears you will have found the step that caused the errors and the column that has the error.
   For bar code it was the 1st Changed Type converting it to decimal. (There were alpha numeric bar codes)

Now that you know there are errors and in which step and column, if you want to work out what the problem is follow the below.

1. Added Index no after Source step
2. Go to step where errors first appear
3. Home > Keep Rows > Keep Errors
4. Get the Row no from the Index no column. At this point the column will simply show “Error” and not what the value was that caused the error.
5. Go to step before where the errors appear and scroll down to one of the rows identified in the above.
   You will now be able to see the value in that column which should give you a good idea of why the step caused an Error