Invoked function parameters error (Clockify API)

[Anonymous]

Hello everyone,

I'm trying to use an invoke function but i get an error I don't understand. 

I'm trying to get all the entries of everyone in my company from clockify using a main function and an invoked function. 

When I'm trying to pass a dynamic ID using each function, it doesn't work, but when I pass a static ID, it works. 

(I replaced my API key by XXXX)

GetTimeEntries (Invoked function) 

= (userID as text, PageNumber as number) =>
let
    Source = try Json.Document(Web.Contents("https://api.clockify.me/api/v1//workspaces/63be77bd5e398c3868773063/user/"& userID &"/time-entries?page-size=100&page=" & Number.ToText(PageNumber), [Headers=[#"X-Api-Key"="XXXX"]])) otherwise [description=null, tagIds=null, userId=null, billable=null, taskId=null, projectId=null, timeInterval=null, workspaceId=null, isLocked=null, customFieldValues=null, type=null, kioskId=null]
in
    Source

Request "Clockify - users" - static parameters ("63be77bd5e398c3868773062")
expected results : 215 

Results : 215 (with a pagination of 100 we have 3 lists)

OK

But I get 215 lines for each ID , as I pass a static parameter

let
    Source = Json.Document(Web.Contents("https://api.clockify.me/api/v1//workspaces/63be77bd5e398c3868773063/users", [Headers=[#"X-Api-Key"="XXXX"]])),

// Not revelant /////////////////
    #"Converti en table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"id", "email", "name", "memberships", "profilePicture", "activeWorkspace", "defaultWorkspace", "settings", "status", "customFields"}, {"id", "email", "name", "memberships", "profilePicture", "activeWorkspace", "defaultWorkspace", "settings", "status", "customFields"}),
    #"settings développé" = Table.ExpandRecordColumn(#"Column1 développé", "settings", {"weekStart", "timeZone", "timeFormat", "dateFormat", "sendNewsletter", "weeklyUpdates", "longRunning", "scheduledReports", "approval", "pto", "alerts", "reminders", "timeTrackingManual", "summaryReportSettings", "isCompactViewOn", "dashboardSelection", "dashboardViewType", "dashboardPinToTop", "projectListCollapse", "collapseAllProjectLists", "groupSimilarEntriesDisabled", "myStartOfDay", "projectPickerTaskFilter", "lang", "multiFactorEnabled", "theme", "scheduling", "onboarding", "showOnlyWorkingDays"}, {"settings.weekStart", "settings.timeZone", "settings.timeFormat", "settings.dateFormat", "settings.sendNewsletter", "settings.weeklyUpdates", "settings.longRunning", "settings.scheduledReports", "settings.approval", "settings.pto", "settings.alerts", "settings.reminders", "settings.timeTrackingManual", "settings.summaryReportSettings", "settings.isCompactViewOn", "settings.dashboardSelection", "settings.dashboardViewType", "settings.dashboardPinToTop", "settings.projectListCollapse", "settings.collapseAllProjectLists", "settings.groupSimilarEntriesDisabled", "settings.myStartOfDay", "settings.projectPickerTaskFilter", "settings.lang", "settings.multiFactorEnabled", "settings.theme", "settings.scheduling", "settings.onboarding", "settings.showOnlyWorkingDays"}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"settings développé",{"memberships", "profilePicture", "activeWorkspace", "defaultWorkspace", "settings.weekStart", "settings.timeZone", "settings.timeFormat", "settings.dateFormat", "settings.sendNewsletter", "settings.weeklyUpdates", "settings.longRunning", "settings.scheduledReports", "settings.approval", "settings.pto", "settings.alerts", "settings.reminders", "settings.timeTrackingManual", "settings.summaryReportSettings", "settings.isCompactViewOn", "settings.dashboardSelection", "settings.dashboardViewType", "settings.dashboardPinToTop", "settings.projectListCollapse", "settings.collapseAllProjectLists", "settings.groupSimilarEntriesDisabled", "settings.myStartOfDay", "settings.projectPickerTaskFilter", "settings.lang", "settings.multiFactorEnabled", "settings.theme", "settings.scheduling", "settings.onboarding", "settings.showOnlyWorkingDays", "status", "customFields"}),

///////////////
// look from here //////////////////
    #"Invoked Custom Function" = Table.AddColumn (#"Colonnes supprimées1", "NumberOfEntries", each if [id] <> null then GetNumberOfEntriesPerUser([id])  else null),
    #"ListofEntries" =  Table.AddColumn (#"Invoked Custom Function", "Entries", each if [id] <> null then  List.Generate(
        () => [Result = try GetTimeEntries("63be77bd5e398c3868773062",1) otherwise null, pagenumber=1],
        each List.IsEmpty([Result]) = false,
// Look the line below with the static parameter "63be77bd5e398c3868773062"////
        each [Result = try GetTimeEntries("64104151cd49c235581406fb",pagenumber) otherwise null, pagenumber = [pagenumber] + 1],
        each [Result]) 
        else null)
in
    ListofEntries

 Result OK : 

Request "Clockify - users" - dynamic parameters ([id])
expected results : 215 

Results : 215 (with a pagination of 100 we have 3 lists)

OK

But I get 215 lines for each ID , as I pass a static parameter

Request "Clockify - users" - static parameters ( [id] )
expected results : 215 for the first ID, 695 for the second id, 48 for the third id, 416 for forth id, 26 for the last ID

Results : error

KO

check the 5th code line before the end. 

let
    Source = Json.Document(Web.Contents("https://api.clockify.me/api/v1//workspaces/63be77bd5e398c3868773063/users", [Headers=[#"X-Api-Key"="XXXX"]])),

// Not revelant /////////////////
    #"Converti en table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"id", "email", "name", "memberships", "profilePicture", "activeWorkspace", "defaultWorkspace", "settings", "status", "customFields"}, {"id", "email", "name", "memberships", "profilePicture", "activeWorkspace", "defaultWorkspace", "settings", "status", "customFields"}),
    #"settings développé" = Table.ExpandRecordColumn(#"Column1 développé", "settings", {"weekStart", "timeZone", "timeFormat", "dateFormat", "sendNewsletter", "weeklyUpdates", "longRunning", "scheduledReports", "approval", "pto", "alerts", "reminders", "timeTrackingManual", "summaryReportSettings", "isCompactViewOn", "dashboardSelection", "dashboardViewType", "dashboardPinToTop", "projectListCollapse", "collapseAllProjectLists", "groupSimilarEntriesDisabled", "myStartOfDay", "projectPickerTaskFilter", "lang", "multiFactorEnabled", "theme", "scheduling", "onboarding", "showOnlyWorkingDays"}, {"settings.weekStart", "settings.timeZone", "settings.timeFormat", "settings.dateFormat", "settings.sendNewsletter", "settings.weeklyUpdates", "settings.longRunning", "settings.scheduledReports", "settings.approval", "settings.pto", "settings.alerts", "settings.reminders", "settings.timeTrackingManual", "settings.summaryReportSettings", "settings.isCompactViewOn", "settings.dashboardSelection", "settings.dashboardViewType", "settings.dashboardPinToTop", "settings.projectListCollapse", "settings.collapseAllProjectLists", "settings.groupSimilarEntriesDisabled", "settings.myStartOfDay", "settings.projectPickerTaskFilter", "settings.lang", "settings.multiFactorEnabled", "settings.theme", "settings.scheduling", "settings.onboarding", "settings.showOnlyWorkingDays"}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"settings développé",{"memberships", "profilePicture", "activeWorkspace", "defaultWorkspace", "settings.weekStart", "settings.timeZone", "settings.timeFormat", "settings.dateFormat", "settings.sendNewsletter", "settings.weeklyUpdates", "settings.longRunning", "settings.scheduledReports", "settings.approval", "settings.pto", "settings.alerts", "settings.reminders", "settings.timeTrackingManual", "settings.summaryReportSettings", "settings.isCompactViewOn", "settings.dashboardSelection", "settings.dashboardViewType", "settings.dashboardPinToTop", "settings.projectListCollapse", "settings.collapseAllProjectLists", "settings.groupSimilarEntriesDisabled", "settings.myStartOfDay", "settings.projectPickerTaskFilter", "settings.lang", "settings.multiFactorEnabled", "settings.theme", "settings.scheduling", "settings.onboarding", "settings.showOnlyWorkingDays", "status", "customFields"}),

///////////////
// look from here //////////////////
    #"Invoked Custom Function" = Table.AddColumn (#"Colonnes supprimées1", "NumberOfEntries", each if [id] <> null then GetNumberOfEntriesPerUser([id])  else null),
    #"ListofEntries" =  Table.AddColumn (#"Invoked Custom Function", "Entries", each if [id] <> null then  List.Generate(
        () => [Result = try GetTimeEntries([id],1) otherwise null, pagenumber=1],
        each List.IsEmpty([Result]) = false,
// Look the line below with the static parameter "63be77bd5e398c3868773062"////
        each [Result = try GetTimeEntries([id],pagenumber) otherwise null, pagenumber = [pagenumber] + 1],
        each [Result]) 
        else null)
in
    ListofEntries

-->My question : Why I get an error when I'm passing [id] as a parameter ? 

PS : thanks PBI Guy for the API loop code
https://pbi-guy.com/2022/07/05/how-to-loop-through-an-api-with-power-bi-without-knowing-last-page/

[Anonymous · Accepted solution]

Hello,

I did find a solution by my self. Invoke a function in another invoked function. I imagine that can affect performance but I don't have many records, so it's quite fine for me. 

Regards