Power Query | QuickBooks Online | Number Formatting .xls files

[Tekell]

I currently have several reports ( .xls files ) being emailed from QuickBooks online to a Sharepoint location. When I open these reports, the number format is "custom". When I connect Power Query to these sources it cannot "recognize" that number format and therefore brings in "0". As I manually change the number format from "custom" to "general, currency, etc..." Power Query then brings in the actual data. 

Does anyone have a solution for this ?

[v-stephen-msft]

Hi @Tekell ,

You can also try replacing errors after data is imported into Power Query.

Select the column and find the data type option, change the type to "Decimal Number" or "Currency", depending on what is appropriate for your data.

If changing the data type results in errors, you can replace the errors with null or a default value.

Right-click on the column header and select "Replace Errors".

Enter the value you want to replace the errors with (e.g., null or 0).

Best Regards,

Stephen Tao

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

[Tekell]

Here's the number formatting in the .xls files:

[mussaenda]

Hi @Tekell , 

you can solve your issue by going to the power query, remove the auto change type step for that field and change type using local 

then select data type as decimal number

and selectt he language of your currency

Just take note that in Europe, "," means "." and vice versa.

Hope this helps