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”:
Choose “Entity” and a window will pop up where you enter “select * from TimeActivity” into the first field (“query”):
Click invoke and you might be prompted with the sign-in-dialogue. Sign in and continue will return a table like this:
Click the expand-arrows (1) -> disable prefixes (2) -> Load more (3) -> Select All Columns -> OK -> dance the happy dance:
Employee data in a record that you can expand as you like:
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:
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()
| let func = | |
| (optional StartDate as date) => | |
| let | |
| Query = QuickBooks.Tables(){[Key="entity"]}[Data], | |
| DateString = Text.From(Date.Year(StartDate)) & "-" & Text.PadStart(Text.From(Date.Month(StartDate)),2,"0") & "-" & Text.PadStart(Text.From(Date.Day(StartDate)),2,"0"), | |
| Source = if StartDate = null then Query("select * from TimeActivity") else Query("select * from TimeActivity where TxnDate > '" & DateString & "'") , | |
| #"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"TxnDate", "NameOf", "EmployeeRef", "CustomerRef", "ItemRef", "BillableStatus", "Taxable", "HourlyRate", "BreakHours", "BreakMinutes", "StartTime", "EndTime", "domain", "sparse", "Id", "SyncToken", "MetaData", "Description", "Hours", "Minutes"}, {"TxnDate", "NameOf", "EmployeeRef", "CustomerRef", "ItemRef", "BillableStatus", "Taxable", "HourlyRate", "BreakHours", "BreakMinutes", "StartTime", "EndTime", "domain", "sparse", "Id", "SyncToken", "MetaData", "Description", "Hours", "Minutes"}), | |
| #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Column1",{{"StartTime", type datetimezone}, {"EndTime", type datetimezone}}), | |
| #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type1", "Hours0", each [EndTime] – [StartTime], type duration), | |
| #"Changed Type" = Table.TransformColumnTypes(#"Inserted Time Subtraction",{{"StartTime", type datetime}}), | |
| #"Added Custom" = Table.AddColumn(#"Changed Type", "TotalHours", each try #duration(0,[Hours],[Minutes],0) otherwise [Hours0]), | |
| #"Added Custom1" = Table.AddColumn(#"Added Custom", "HoursMinusBreaks", each if [Hours0] = null then [TotalHours] else [TotalHours] – #duration(0,[BreakHours],[BreakMinutes],0)), | |
| #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Hours0"}) | |
| in | |
| #"Removed Columns" , | |
| documentation = [ | |
| Documentation.Name = " Function.QuickBooksTimeActivity ", | |
| Documentation.Description = " Fetches all available time activity fields from the QuickBooks connector. Optional parameter allows filtering after a certain date. ", | |
| Documentation.LongDescription = " Fetches all available time activity fields from the QuickBooks connector. Optional parameter allows filtering after a certain date. ", | |
| Documentation.Category = " Function ", | |
| Documentation.Source = " Imke Feldmann: www.TheBIccountant.com. ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. For details see: https://www.thebiccountant.com/2019/01/10/get-full-time-activity-data-from-quickbooks-into-power-bi/ . ", | |
| Documentation.Examples = {[Description = " For details see: https://www.thebiccountant.com/2019/01/10/get-full-time-activity-data-from-quickbooks-into-power-bi/ . ", | |
| Code = " ", | |
| Result = " "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
This function also calculates the total duration from the 2 different capture methods.
Enjoy & stay queryious đ
Pingback: Power BI Secure Embed, DAX Variables, MS Flow and more... (January 14, 2018) | Guy in a Cube
is there any way to edit the tables that are given? I have multicurrency and the general ledger table does not have a column for home amount and when I query the report function to general ledger it is not in the nice, readable format like how the original table is. If you had any idea that would be great ive been spinning my wheels over this!!
Hi IO,
to my knowledge it’s not possible to edit the source data, I’m afraid
/Imke