Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Last active November 18, 2019 20:08
Show Gist options
  • Save ImkeF/873d4e62deaee07f2e79e38a52e90d2f to your computer and use it in GitHub Desktop.
Save ImkeF/873d4e62deaee07f2e79e38a52e90d2f to your computer and use it in GitHub Desktop.
Fetches all available time activity fields from the QuickBooks connector. Optional parameter allows filtering after a certain date.
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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment