Imported excel values showing as zero

[Drue]

I have a system where Quickbooks Online is emailing me several reports in .xls format. I use PowerAutomate to remove the attachments and save them to a folder. These reports are ran and saved every night, providing updated numbers on a daily basis. I have linked these reports into PowerBI desktop. When I first did this, everything worked great. Now, however, one just 1 of my 6 excel documents I am importing, the data is coming into PowerBI as 0. I have looked in my excel document and I can see the numbers present. I checked the cell format for the numbers, and it is set to custom. This is the case in all 6 excel files, and is set when the files are sent out of QuickBooks online. I then went into PowerQuery and from the very start (after power query's automatic Source, Navitage and Promoted headers steps) the values show 0 and the data type is text. Does anyone know how I can get my values from excel to show properly in PowerBI while keeping my system automated?

[edhans · Accepted solution]

If the Replace Values from @mahoneypat doesn't work, then I think the problem is the XLS file. Power Query does not work reliabily with XLS files. They must be XLSX (or XLSM, XLSB) files.  I wrote a blog article about one issue here and there was no way to fix it. The custom format in the XLS was wonky - created from an outside system (ADP vs Quicken) and even though it opened up correctly in Excel and I could see the -5 value, Power Query only saw 5, so obviously the values were wrong.

The fix is, save as XLSX and it worked again. I reported it to MS and they essentially responded "Yeah, you are right. Well, we replaces XLS in 2007, so we aren't fixing it." Which I understand.

Your only solution to maintain the automation is to:

1. Get the file in XLSX format from Quicken and pray the adhere to the XLSX format spec
2. Get the XLS format, and use Power Automate to do the conversion for you before it saves it. I know it is possible, but I am not a Power Automate flow expert on doing that conversion on the fly.

[Syndicate_Admin]

I'm having this same exact problem with *some* of the Quickbooks Online reports, but not all.  Upon export from QBO, they're xlsx format.  If I open the downloaded file I can see the values.  But if I try to import the data with Power Query, at the "Get Data" step the values are zero.   I am able to get around this by opening the downloaded file and doing a save and replace.  Can someone explain what's going on here?  Quickbooks Online only gives us two opens to download, Excel or PDF.  And the result with Excel is xlsx, but it seems it's not a good file?

[edhans]

@Drue I'm glad my suggestion to convert the XLS to XLSX using Power Automate helped. Glad your project is moving forward.

[Drue · Accepted solution]

For anyone that might stumble accross this and wants the answer, I figured it out. QuickBooks Online will email reports on a daily basis. I use Power Automate to save the excel files to a folder on my OneDrive for Business. The lame part is QuickBooks only emails .xls files. I then used Power Automate and the Cloudmersive Document Conversion connection. Cloudmersive is free if you are under a certain number of documents a month, which I am. So it was a great solution. I did have to save the .xlsx files to a different folder after converting them (all in Power Automate). I then have my Power BI reports point to those files!

Quickbooks emails the documents once per day. I have the conversion Power Automate flow run once per day and I have PowerBI auto refresh once a day. So now my financial reporting is fully automated and up to date for the power that be! Woohoo!! 

[edhans · Accepted solution]

If the Replace Values from @mahoneypat doesn't work, then I think the problem is the XLS file. Power Query does not work reliabily with XLS files. They must be XLSX (or XLSM, XLSB) files.  I wrote a blog article about one issue here and there was no way to fix it. The custom format in the XLS was wonky - created from an outside system (ADP vs Quicken) and even though it opened up correctly in Excel and I could see the -5 value, Power Query only saw 5, so obviously the values were wrong.

The fix is, save as XLSX and it worked again. I reported it to MS and they essentially responded "Yeah, you are right. Well, we replaces XLS in 2007, so we aren't fixing it." Which I understand.

Your only solution to maintain the automation is to:

1. Get the file in XLSX format from Quicken and pray the adhere to the XLSX format spec
2. Get the XLS format, and use Power Automate to do the conversion for you before it saves it. I know it is possible, but I am not a Power Automate flow expert on doing that conversion on the fly.

[mahoneypat]

This is definitely doable, and it sounds like Quickbooks changed the report outputs slightly?  So you have 0s showing where you used to have nulls? or vice versa?  Either way, you should be able to get it back to the way it was with a Replace Values step.

Regards,

Pat

[Drue]

No, I didn't do this. But I did go back to the Applied Steps 'source' and I re-expanded the table. When I did that, the numbers appeared, but I had to re-format everything again. I don't understand why it isn't automatically doing it when the file refreshes though.

[Anonymous]

Hi @Drue ,

Did you try to copy the data/values only into another (new) Excel file and source it from this new file? Just to see if there may be a problem with the way data is stored in the Excel file?

Kind regards,

JB.