Hi Alicia,

I just listed to 5.04 and I don't mention avoiding using ODBC connections...are you sure it was this lesson?

Mynda

I appreciate your quick response!! You are correct I was off by one, it is in 5.04 about 40n seconds in.

I have been working on finding a way to import my transactions from QuickBooks and have the class and jobs that are entered in QuickBooks when we enter a transaction. That way I can use the variable to do my % Allocations, and right now I am thinking a little transforming to then get them back in the correct format with one line per transaction.

I think I have it, finally have the first step. 

My plan right now is to (PLEASE LET ME KNOW IF I AM THINK AT ALL THE WRONG WAY AS I AM STILL VERY NEW TO ALL THIS), 

1. Input each of the 64 different QuickBooks database "transaction" tables, using ODBC (Unless you can advise me to go in a different direction), into the query.

2. Removing row except for TxnDate, TxnID, TxnLineID, className, class Numbers, JobName, and Job Numbers.  Then append the 64 tables with the transactions together.

3. Input the Transaction table from the QuickBooks Database, using ODBC into the query.

4. Then merge the Appended (64 tables) to the Transaction table, using TXnDate, TxnId, TanLineID. (This will get all the transactions that QuickBooks would not allow us to assign to a class, like a millage when we have more than one job on a paycheck, we use journal entries to get those over to a class right now.)

Next Adjustment:

1. Take the newly merged query and create multiple new ones, filtered for each Allocation class (indirect cost), 1 newly merged query that has the Allocations Filtered out (for the direct cost)

         Then I plan to use variables for % to other classes. (ex. Overhead to Iceberg, Romaine, Geen Leaf, Wheat) I need to do this on monthly bases so This will need a little more work.

2. Create a new Append that has all the filtered Allocation classes and the newly created Allocated to classes (for my final transaction Data to use in power pivot).

I still have a little more to go than just the above, as we need to be able to look at things not only by field but also by acre. 

I am enjoying learning, and your format has been very helpful!! Thank you for all the work you have put into this!

Alicia Boelts

Hi Alicia,

Thanks for confirming it wasn't 5.04. I presume you meant 5.05 even though you wrote 5.04 again in your reply 😉

What I say in that video is this:

"Power Query has a ton of built-in connectors, and they should always be your first option when connecting to your dataset. If you use an ODBC or OLEDB connector you have a much higher likelihood of breaking query folding, than you do if you use the existing connector available in Power Query. So, be sure to check the connectors available before jumping to use ODBC or OLEDB."

There is no connector for QuickBooks, so you don't have any choice but to use the ODBC connector. Query folding pushes the work to the database, rather than making Power Query to all the work. If you can't query fold, it may result in slower queries.

I'll address your other points:

1. 64 transaction tables seems like a lot...maybe these are dimension tables in database speak, as it would be odd to have transactions split across multiple tables in a database. Although that's a lot of dimension tables too. I wonder if you're referring to reports, as opposed to actual tables.

2. I think you mean 'removing columns' not rows. If so, ok.

3. 'the transaction table'...not sure how this transaction table is different to the other 64 transaction tables.

4. Make sure you compare the totals for your final table match the totals in QuickBooks.

Next Adjustment:

1. I don't think you need separate queries to generate the % allocations for each class. You can probably use this technique modified for percentages.

2. ok

Mynda

Thank you for your quick response. You are correct in your assumptions of my typos.  5.05 and columns.  I agree with you I felt like the 64 was a lot too, but I could not find a better way to get the classes and jobs added to the transactions. Below is a link from online, with people talking about the issue. 

https://www.experts-exchange.com/questions/27318995/How-do-I-LINK-the-CLASS-table-to-the-TRANSACTION-table-in-Quiickbooks.html

I will look at the link you sent me today, Thank you for all your help with this. I am very excited to see the end product, the drastic change it will make in my workflow, and our company's ability to see the information we need quickly. 🙂 Thank you for all your work here in setting up all the classes, it truly is amazing!

Alicia Boelts

Hi Alicia,

Great to hear you're enjoying the course!

Thanks for sharing the link. You didn't say which tables you were planning on using from QuickBooks (it's ok, 64 is a lot to list), but assuming you want to get the sales data, then the SalesOrder table is linked to the Class table and the Customers table. And the Customers table is linked to the Jobs table, so in theory you should only need to get those 4 tables in order to aggregate Sales by Class or Sales by Jobs. Likewise for the expenses (not sure what tables you'll need, maybe Invoices and Journals). 

Note: I can see this information on the ODBC information for QuickBooks that's linked to in the post you linked to.

Quickbooks is a multi-relational database. All you should need to do is use those same relationships to extract the data you need, then you can recreate the aggregations/reports as required. From what you've described above, it sounds like you're thinking of QuickBooks in terms of the reports you currently extract from it but connecting via ODBC enables you to skip the report limitations and go straight to the data. 

Mynda