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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors