Power BI Desktop does not refresh from Google Sheets, shows Error

[Cesylyaa]

I started analysis/visualization on Power BI Desktop, with my Data Source as Google Sheets.
All was working fine, the data was being pulled successfully.

While working with the file content i started with, I can refresh my dashboards just fine.

When i now add new rows to the Google Sheets, and refresh in Power BI Desktop, i get an error.

Kindly Note that when i remove the added rows and refresh, it refreshes with no error.
The moment i add a new row, it shows that row as an error.

Please help.

[ray_aramburo · Accepted solution]

Did you add a new row with a different data type from their column? Usually those errors are due data type mismatches.

[Cesylyaa]

I went through the data again.
Indeed, it was a data type issue.

Although the numeric data looked same, they were formatted differently.
One was currency and the other was Number format.

I've resolved it. 
Thank you!

[Cesyl]

I went through the data again.
Indeed, it was a data type problem. 

Although it looked the same, the underlying formatting  was different.

Thank you so much.

[Cesylyaa]

Rows 270 and 271 are parrt of the initial data. 
I added the data contained in row 272.

I don't see any differences in the Data types.

What else could it be please?

[ray_aramburo]

@Cesylyaa check what @Anonymous indicates. That's likely the issue as that column probably is typed as a numerical column. Click on the Changed Type step in Power Query and there change the Attribute Account to type Text. After that, you'll need to click on Insert/Replace step instead of adding a new one. 

[Cesylyaa]

I had started off with the column having its contents in number format, and the other values i was adding were in currency format, hence the reason for the error.

Although they looked same, i had to check each value in the Data source to find out which ones were formatted wrongly.

Thank you!

[Anonymous]

Thanks for the reply from ray_aramburo and audreygerred , please allow me to provide another insight:

Hi, @Cesylyaa 
 

Could you please inform me about the data types of the Attributed Account and Remark columns in the Power Query process?

If the Attributed Account column has been converted to a numeric type after data processing, then given that your new data is "Unknown," which is a text type, it may not be possible to convert this text containing characters into a numeric type.

For further details, please refer to:

Solved: DataFormat.Error: We couldn't convert to Number - ... - Microsoft Fabric Community

Data types in Power BI Desktop - Power BI | Microsoft Learn

Type conversion - PowerQuery M | Microsoft Learn

Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[audreygerred]

Hi! Have you clicked the view errors to see what is going on? Is there something different with the 1 new row compared to the rest?

[Cesylyaa]

For the error, when i click on it, this is what ai see