Error when refreshing

[Johan-MTM]

Hi,

When I do a resfresh in desktop I get an error in 78 rows and I don't really understand why. First I thought its because source_published are empty/missing data for these books but w have many more rows where source_published is empty and they don't generate any errors. Does the screenshot below give you guys any idea to what could be wrong?

[HotChilli]

Click in the Error 'cell' (but not on the Error) and the message below will show you what the error is.

[Johan-MTM]

Thank you for the reply.

This is the error message:

I change the column from whole number to date (yyyy) and the rest of the 200000 rows works fine, even around 3000 rows where source_publiched are empty genereates no error. Its just these 77 rows that do. Any idea what I can do to fix it?

[bhelou]

Dear , check the nature of the dates maybe its not a date for these rows , if you can share some sample to figure it out . 

Regards , 

[Johan-MTM]

I would love to share some example but not sure how 🙂 Before I do the refresh, these cells are simply empty.

[bhelou]

hi , 

so you know the error from where , you can do as follow for it : 

Option 1 - in power queries try to remove the blanks ( in this case the rows will be deleted , data loss may occur )
Option 2 : replace the blank values with a date ( this will solve the issue and the rows will be kept as is ) 

Please accept as a solution if this works with you . 
Regards ,

[Johan-MTM]

I like the sound of option # 2 🙂 Care to help me how a power query like that would look like?

[bhelou]

Hi , 

check the source file where it is coming from and what it is defined as a text , decimal or a date, then change the type of it from power queries 

[Johan-MTM]

Hi!

The column contains years (dates when book are published) and it was whole numbers from the beginning. I changed it to date (yyyy) and it works fine for the rest of the 200000 rows, its just these 77 rows that cause the error.
Can I somehow change the type of just these 77 rows in order to get aroudn the errors?