Fetches all available time activity fields from the QuickBooks connector. Optional parameter allows filtering after a certain date.

Function.QuickBooksTimeActivity.pq

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))