Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
afvijay
Regular Visitor

Inaccurate Data between Quickbooks Online and PowerBI Data Set

We recently decided to try out PowerBI and linked our QBO account with Power BI.

 

When we got our data, our balance sheet data on banks are way off compared to the QBO data. When we ran a Balance Sheet report in QBO and compared to PowerBI data, they both are off.

 

Any idea or suggestion, why the discrepancy in data? Its not because of time difference, but data seems to act weird and not sure what we should be looking for as the refresh was successful with no errors.

 

Appreciate any help or pointers.

3 REPLIES 3
v-xicai
Community Support
Community Support

Hi @afvijay ,

 

Could you please show us the differences between the QuickBooks Online and Power BI Dataset in screenshots? Do mask sensitive data before uploading.

 

You may refer to the link to learn how to connect to QuickBooks Online: https://docs.microsoft.com/en-us/power-bi/service-connect-to-quickbooks-online.

 

Best Regards,

Amy

Thanks for the kind reply. Please refer to the below for screenshots. 

 

  • We made sure the accounts are all in RECONCILED status to ensure our GL data reflects the latest figures.
  • These are all bank accounts
  • GL shows the data right no QBO reports and GL registers.
  • PowerBI is throwing numbers that doesn't make any sense.

Screenshot of side by side compare : https://ibb.co/5K1fp9z 

 

Thank you

 

After doing a little digging on one account that had a $3,000 GL balance on PowerBI Dataset and compared it line by line to QBO transactions we found there the PowerBI dataset is NOT calculating the GL Ledger data due to currency conversions.

 

  1. We had one Canadian Invoice for $11,300 and we got paid $11,300 in Canadian dollars.
  2. The check got deposited in the US bank account
  3. When the bank processed the check, it deposited ROUGHLY $8,000.
  4. The transaction was reconciled with ending balances matching both on A/R and bank.

So in this scenario, the invoice has been paid fully and the A/R is zero, the bank converted and deposited the correct USD amount.

 

However PowerBI somehow calculates the difference between the CAD invoiced amount and USD as the correct GL balance. 

  • In QBO the current bank GL balance = 0
  • In PowerBI data set the current bank balance is $3300.

So if I am calculating CASH on hand scenario, right now PowerBI shows we got $3300 at hand which is wrong. Is there a different way to look at this ?? 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.