Get full Time Activity data from QuickBooks into Power BI

Problem

As per the time of writing, the native QuickBooks connector in Power BI has some shortcomings for the Time Activity-data: It will not return employee details (so you will not know who did the hours) and it will not return hours (if they haven’t been entered by start- and end-date).

Solution

But fortunately the connector has 2 functions, who can return the full data that the QBO-API has to offer. At the end of the list in the navigation pane there are the functions “Entity” and “Report”:

QuickBooks Time Activity Navigation Pane

Choose “Entity” and a window will pop up where you enter “select * from TimeActivity” into the first field (“query”):

QuickBooks Function Dialogue

Click invoke and you might be prompted with the sign-in-dialogue. Sign in and continue will return a table like this:

QuickBooks Time Activity Result Table with Records

Click the expand-arrows (1) -> disable prefixes (2) -> Load more (3) -> Select All Columns -> OK -> dance the happy dance:

QuickBooks Results Time Activity

Employee data in a record that you can expand as you like:

QuickBooks Time Activity Employee data

And at the end of the table you’ll find the hours and minutes for the entries who didn’t use start- and end date:

QuickBooks Hours

Bonus

According to the manual, you can also pass parameters to this function call. I’ve created a handy function with an optional parameter that allows you to pass in a date after which the time entries shall be returned. But you can use the function without this parameter as well and return all time entries by just adding open and close parenthesis like so: NameOfTheFunction()

This function also calculates the total duration from the 2 different capture methods.

Enjoy & stay queryious 😉

Comment (1) Write a comment

  1. Pingback: Power BI Secure Embed, DAX Variables, MS Flow and more... (January 14, 2018) | Guy in a Cube

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz