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 the Query editor can you find the row that is returning the error (they should be in the same order as in Excel so look for the value in the previous row 35).
Click on the Data Type column on that record, it will explode the values. Is there anything obvious that shows up ?
If it helps you can click on the Into Table Convert button if that makes it easier to read (top left corner).

 

[Manish4993]

In the Query editor can you find the row that is returning the error (they should be in the same order as in Excel so look for the value in the previous row 35).
Click on the Data Type column on that record, it will explode the values. Is there anything obvious that shows up ?
If it helps you can click on the Into Table Convert button if that makes it easier to read (top left corner).

Hi Alex,

I checked it but nothing found that could help to get the error reason!

 

[Manish4993]

Hi Alex,

I checked it but nothing found that could help to get the error reason!

In the Query editor can you find the row that is returning the error (they should be in the same order as in Excel so look for the value in the previous row 35).
Click on the Data Type column on that record, it will explode the values. Is there anything obvious that shows up ?
If it helps you can click on the Into Table Convert button if that makes it easier to read (top left corner).

Thanks Alex. Got your point.. ?

 

[Alex Blakenburg]

Glad you managed to resolve it. ?

 

[Manish4993]

Glad you managed to resolve it. ?

Hwy Alex,

After removing all the errors, at the time of load (data refresh) it is still reflecting few errors.

 

[Alex Blakenburg]

What did you change to get rid of the ones you were able to get rid of ? I tried to force both null and Error but neither gave me the #Field! error you were getting.
Also are you able to show me the Excel error you are getting now and the Query fields for that line ?

 

[Manish4993]

What did you change to get rid of the ones you were able to get rid of ? I tried to force both null and Error but neither gave me the #Field! error you were getting.
Also are you able to show me the Excel error you are getting now and the Query fields for that line ?

i used the replace error function of power query. i replaced them with blanks. Also we can connect on Any Desk or Team Viewer if possible. please let me know if u r comfortable in this.

 

[Alex Blakenburg]

I am pretty new on this forum myself so I don't know what the protocol is on that. Can we start with giving me a screenshot of the error you are seeing in Excel and also of what that row looks like in Power Query when you expand the data type.
And if the data source is excel an XL2BB of the problem row ideally with the row above it an below it as well. If its a text file a copy of those 3 rows.

 

[Manish4993]

I am pretty new on this forum myself so I don't know what the protocol is on that. Can we start with giving me a screenshot of the error you are seeing in Excel and also of what that row looks like in Power Query when you expand the data type.
And if the data source is excel an XL2BB of the problem row ideally with the row above it an below it as well. If its a text file a copy of those 3 rows.

Now only few errors are left but i don't know the way to directly jump to error prone row in power query editor.
In this case row number is 78557.