Error on initial refresh, bad Power Query | CLEARIFY - Page 1

Trying to implement QQube for multi-company. Got the sync working OK, now trying Power BI. I have been using Power BI for longer than it has been Generally Available, started helping MS during the development stages, so I know Power BI inside and out. 

When I go to refresh a Power BI model for the first time (Accounts Payable) I get a Refresh error: $ TRIAL BALANCE ACTIVITY BY CLASS; "The column 'Current Ending TB Amount' of the table wasn't found'.

When I go to inspect the Power Query, the Navigation step bombs out with this error: "The table has no visible columns and cannot be queried. Details: vf_FINANCCIALTBCLASS"

What is going on? Does anyone quality check this stuff?

I'm new to QQube. How do I report this as an error?

Complete newbie here. I am weak on QQube and Power Bi, but I've been using and teaching QuickBooks Desktop from day one. I don't find this an error or bug, but maybe lack of communication on available data. Though QQube may have an article on this. I imagine that this is a limitation of QuickBooks, not QQube.

The error is probably based on the fact that, while QBDT-Premier (not Pro) advertises that it provides a Balance Sheet by class, it only does so in very specific scenarios.  

Think of this. An A/P bill is $1000, allocated between 2 classes. Subsequently, there is an A/P Payment of $500, on account. The assumption is that the bill credit to A/P is allocated too. Nope. It can only contain one class name. Then we assume that the $500 payment is allocated to A/P how? By %?  It just does not work. The only way to get class names in balance sheet transactions is to never have a split allocation.

And check this out.

 https://quickbooks.intuit.com/learn-support/en-us/help-article/business-reports/transactions-supported-balance-sheet-class-desktop/L7I9cjyfa_US_en_US

Hi Todd,

  I was out of the office for a bit so just seeing this. Like you,  I have also been using Power BI for longer than most people care about:-).  I'm a solution provider for QQUBE and perhaps I can help.  While my friend  Sara Laidlaw is 100% right on the what the actual data will show internally ( Balance sheet by Class is a very ugly data set) , the problem you are experiencing has nothing to do with the quality of the software or the availability of QuickBooks data; it is simply a matter of understanding the way this publisher has implemented the QQUBE data warehouse.  Let me help:

The only authorized interfaces for the database are via the documented views.  During the refresh operation, the publisher disables user access to the system by way of deleting all (?) or at least most of those views. The warehouse is built in a long string of transformations after the data extraction / refresh so this is is done to ensure whatever information you are getting is complete. 

 The table you are going after holds the trial balance by class information. QQUBE has multiple tables with trial balance information and that particular one is only available when the Financial Summary by Class is enabled prior to a sync operation.  One thing that I personally find useful when I am coding a solution for customers is to use the Excel plugin to confirm a dataset on a new/changed sync configuration. Even with my decades of experience writing SQL I find that using that helps me get the right tables that match my needs. 

If you're not getting what you need I'm happy to do a remote session to help. You can schedule a meeting at:  https://www.betterideagroup.com/collaborate.html

Ernest

p.s. I've see the next version of QQUBE and it's support for Power BI in particular has been stepped up, ALOT !  It truly  is a beautiful data model with some excellent DAX patterns provided as a part of the package, you won't want to miss that !

Scott, you properly nailed the problem with the lack of allocation to the A/P and A/R accounts when there are multiple classes on different lines on a transaction.

QuickBooks does not store that information underneath the hood - primarily as it would have taken up a ton of space within the database to do so; as such Intuit used a different strategy:   Calculate the Balance Sheet by Class in QuickBooks on the fly e.g. as you run the report.

So in our case, we can replicate the actual Balance Sheet by Class report using the Intuit SDK to extract that exact report, and know that is exactly as it shows in QB.  We also use that information along with the Profit and Loss by Class  to create the Trial Balance Activity by Class reports.

What we can't do - and what nobody can do because Intuit doesn't store the splits in the database - is to reverse engineer a balance sheet by class from the general ledger detail.

Todd this was a good intention on our part, but with poor implementation.

We have configured QQube such that an example is not available unless the data has been synched successfully.  This is the ONE case where we actually have several data models within the same .pbix file.   

So if you loaded Accounts Payable it would show as being available - even though the Financial Summary by Class was not loaded.

So to fix your issue, you need to also load the Financial Summary by Class - OR remove those elements from the Power BI .pbix file.

This will be corrected in our next version, and apologize for any confusion.