Imported excel values showing as zero

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.

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!!