Unable to refresh PeopleHR API connection in PB Service when combining tables

[AliPoa]

Hello, Please can anyone offer advice on the following?

I have used Power Query in a PBI report to access the PeopleHR API. I am drawing down both the Employee Table and Timesheets table, merging them in a single query. This works in Power BI Desktop.

However, when I publish to the Service, it fails to refresh stating: Information is needed in order to combine data. Please specify a privacy level for each data source.;Information about a data source is required.. The exception was raised by the IDbCommand interface.

When I call the Employee table only, everything works. The problem is combining the two tables together.

I have narrowed it down to the Credentials in the PBI Service when it calls "https://api.peoplehr.net/"

(If it shows"https://api.peoplehr.net/Employee", used when testing with only the Employee table, it works with the same settings)

I have tried variations of settings but the above is the only way it will accept, but then fails to refresh.

I am unable to call both the Employee and Timesheet tables seperately in Power Query as the Timesheets table does not include the EmployeeID.

My code in Power Query is as follows:

let
// api url
base_url = "https://api.peoplehr.net",
api_key = "xxxxx",
JsonHeader = [#"Content-Type" = "application/json"],
PostJson = (path as text, payload as record) =>
Json.Document(
Web.Contents(
base_url,
[
RelativePath = path,
Headers = JsonHeader,
Content = Text.ToBinary(Text.FromBinary(Json.FromValue(payload), TextEncoding.Utf8))
]
)
),

EmployeeJson =
PostJson(
"Employee",
[
APIKey = api_key,
Action = "GetAllEmployeeDetail",
IncludeLeavers = "true"
]
),
Employees = EmployeeJson[Result],
// Turn JSON result into table
EmployeeTable = Table.FromList(Employees, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Expand all of the inner records
ExpandEmployeeRecords = Table.ExpandRecordColumn(EmployeeTable, "Column1", {
"EmployeeId","Department"
}),
ExpandInnerFields = (tbl as table, field as text) =>
Table.ExpandRecordColumn(
tbl, field, {"DisplayValue"}, {field}
),
FieldsToExpand = {
"EmployeeId", "Department"
},
FullyExpandedEmployees = List.Accumulate(
FieldsToExpand,
ExpandEmployeeRecords,
(state, current) => ExpandInnerFields(state, current)
),
GetTimesheetInfo = (employeeId as text, department as text) =>
let
TimesheetJson = PostJson(
"Timesheet",
[
APIKey = api_key,
Action = "GetTimesheetDetail",
EmployeeId = employeeId
]
),
Result = TimesheetJson[Result],
ResultAsList =
if Result = null then
{}
else if Value.Is(Result, type list) then // Return as-is
Result
else if Value.Is(Result, type record) then // Cast as list
{ Result }
else
{},
Timesheet1 = Table.FromList(ResultAsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Timesheet2 = Table.ExpandRecordColumn(Timesheet1, "Column1", {"TimesheetDate", "TotalTimeWorkedTodayInMins"}),
Timesheet3 = Table.AddColumn(Timesheet2, "EmployeeId", each employeeId),
Timesheet4 = Table.AddColumn(Timesheet3, "Department", each department),
// Purge blank or incorrect entries
Timesheet5 =
if Result = null or Result = "" then
#table({}, {})
else if not Value.Is(Timesheet1, type table) then
#table({}, {})
else if Table.RowCount(Timesheet1) = 0 then
#table({}, {})
else
Timesheet4
in
Timesheet5,
Employees1 = Table.ToRecords(FullyExpandedEmployees),
TimesheetTables = List.Transform(Employees1, each GetTimesheetInfo(_[EmployeeId], _[Department])),
Timesheets = Table.Combine(TimesheetTables)
in
Timesheets

Has anyone got any suggestions or have been able to get this working?

Many thanks.

[v-hjannapu]

Hi @AliPoa,

I would also take a moment to thank @AjiteshChurewal  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[AjiteshChurewal · Accepted solution]

Hi,

Since the Employee API runs successfully on its own, the issue appears to be related to the Timesheet API, which is currently being executed separately for each Employee ID.

I would recommend retrieving the timesheet data in bulk instead of making individual API calls per employee. Fetching the data in a single request should help avoid refresh issues and improve overall performance and reliability.