I have a Power Query that is accessing an api data source which works fine in Desktop but not in Power BI Service.
Whenever I try to refresh it says cant refresh dynamic content.
Not sure what needs to be done,
This is the query,
let
Source = List.Generate( () =>
[ URL = "https://xx.keka.com/api/v1/hris/employees" ,
options = [Headers=[#"authorization"="Bearer "&Query2()]], Result = Json.Document(Web.Contents(URL, options)) ],
each [URL] <> null,
each [ URL = [Result][nextPage] ,
options = [Headers=[#"authorization"="Bearer "&Query2()]], Result = Json.Document(Web.Contents(URL, options)) ]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"URL", "token", "options", "Result"}, {"Column1.URL", "Column1.token", "Column1.options", "Column1.Result"}),
#"Expanded Column1.Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Result", {"data", "pageNumber", "pageSize", "firstPage", "lastPage", "totalPages", "totalRecords", "nextPage", "previousPage", "succeeded", "message", "errors"}, {"Column1.Result.data", "Column1.Result.pageNumber", "Column1.Result.pageSize", "Column1.Result.firstPage", "Column1.Result.lastPage", "Column1.Result.totalPages", "Column1.Result.totalRecords", "Column1.Result.nextPage", "Column1.Result.previousPage", "Column1.Result.succeeded", "Column1.Result.message", "Column1.Result.errors"}),
#"Expanded Column1.Result.data" = Table.ExpandListColumn(#"Expanded Column1.Result", "Column1.Result.data"),
#"Expanded Column1.Result.data1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data", "Column1.Result.data", {"employeeNumber", "firstName", "middleName", "lastName", "displayName", "email", "city", "countryCode", "image", "jobTitle", "secondaryJobTitle", "reportsTo", "dottedLineManager", "contingentType", "timeType", "workerType", "isPrivate", "isProfileComplete", "maritalStatus", "marriageDate", "gender", "joiningDate", "professionalSummary", "dateOfBirth", "resignationSubmittedDate", "exitDate", "employmentStatus", "accountStatus", "invitationStatus", "exitStatus", "exitType", "exitReason", "personalEmail", "workPhone", "homePhone", "mobilePhone", "bloodGroup", "attendanceNumber", "probationEndDate", "currentAddress", "permanentAddress", "relations", "educationDetails", "experienceDetails", "customFields", "groups", "leavePlanInfo", "bandInfo", "payGradeInfo", "shiftPolicyInfo", "weeklyOffPolicyInfo", "captureSchemeInfo", "trackingPolicyInfo", "expensePolicyInfo", "overtimePolicyInfo", "id"}, {"Column1.Result.data.employeeNumber", "Column1.Result.data.firstName", "Column1.Result.data.middleName", "Column1.Result.data.lastName", "Column1.Result.data.displayName", "Column1.Result.data.email", "Column1.Result.data.city", "Column1.Result.data.countryCode", "Column1.Result.data.image", "Column1.Result.data.jobTitle", "Column1.Result.data.secondaryJobTitle", "Column1.Result.data.reportsTo", "Column1.Result.data.dottedLineManager", "Column1.Result.data.contingentType", "Column1.Result.data.timeType", "Column1.Result.data.workerType", "Column1.Result.data.isPrivate", "Column1.Result.data.isProfileComplete", "Column1.Result.data.maritalStatus", "Column1.Result.data.marriageDate", "Column1.Result.data.gender", "Column1.Result.data.joiningDate", "Column1.Result.data.professionalSummary", "Column1.Result.data.dateOfBirth", "Column1.Result.data.resignationSubmittedDate", "Column1.Result.data.exitDate", "Column1.Result.data.employmentStatus", "Column1.Result.data.accountStatus", "Column1.Result.data.invitationStatus", "Column1.Result.data.exitStatus", "Column1.Result.data.exitType", "Column1.Result.data.exitReason", "Column1.Result.data.personalEmail", "Column1.Result.data.workPhone", "Column1.Result.data.homePhone", "Column1.Result.data.mobilePhone", "Column1.Result.data.bloodGroup", "Column1.Result.data.attendanceNumber", "Column1.Result.data.probationEndDate", "Column1.Result.data.currentAddress", "Column1.Result.data.permanentAddress", "Column1.Result.data.relations", "Column1.Result.data.educationDetails", "Column1.Result.data.experienceDetails", "Column1.Result.data.customFields", "Column1.Result.data.groups", "Column1.Result.data.leavePlanInfo", "Column1.Result.data.bandInfo", "Column1.Result.data.payGradeInfo", "Column1.Result.data.shiftPolicyInfo", "Column1.Result.data.weeklyOffPolicyInfo", "Column1.Result.data.captureSchemeInfo", "Column1.Result.data.trackingPolicyInfo", "Column1.Result.data.expensePolicyInfo", "Column1.Result.data.overtimePolicyInfo", "Column1.Result.data.id"}),
#"Expanded Column1.Result.data.jobTitle" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data1", "Column1.Result.data.jobTitle", {"identifier", "title"}, {"Column1.Result.data.jobTitle.identifier", "Column1.Result.data.jobTitle.title"}),
#"Expanded Column1.Result.data.reportsTo" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.jobTitle", "Column1.Result.data.reportsTo", {"id", "firstName", "lastName", "email"}, {"Column1.Result.data.reportsTo.id", "Column1.Result.data.reportsTo.firstName", "Column1.Result.data.reportsTo.lastName", "Column1.Result.data.reportsTo.email"}),
#"Expanded Column1.Result.data.dottedLineManager" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.reportsTo", "Column1.Result.data.dottedLineManager", {"id", "firstName", "lastName", "email"}, {"Column1.Result.data.dottedLineManager.id", "Column1.Result.data.dottedLineManager.firstName", "Column1.Result.data.dottedLineManager.lastName", "Column1.Result.data.dottedLineManager.email"}),
#"Expanded Column1.Result.data.contingentType" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.dottedLineManager", "Column1.Result.data.contingentType", {"id", "name"}, {"Column1.Result.data.contingentType.id", "Column1.Result.data.contingentType.name"}),
#"Expanded Column1.Result.data.currentAddress" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.contingentType", "Column1.Result.data.currentAddress", {"addressLine1", "addressLine2", "countryCode", "city", "state", "zip"}, {"Column1.Result.data.currentAddress.addressLine1", "Column1.Result.data.currentAddress.addressLine2", "Column1.Result.data.currentAddress.countryCode", "Column1.Result.data.currentAddress.city", "Column1.Result.data.currentAddress.state", "Column1.Result.data.currentAddress.zip"}),
#"Expanded Column1.Result.data.permanentAddress" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.currentAddress", "Column1.Result.data.permanentAddress", {"addressLine1", "addressLine2", "countryCode", "city", "state", "zip"}, {"Column1.Result.data.permanentAddress.addressLine1", "Column1.Result.data.permanentAddress.addressLine2", "Column1.Result.data.permanentAddress.countryCode", "Column1.Result.data.permanentAddress.city", "Column1.Result.data.permanentAddress.state", "Column1.Result.data.permanentAddress.zip"}),
#"Expanded Column1.Result.data.relations" = Table.ExpandListColumn(#"Expanded Column1.Result.data.permanentAddress", "Column1.Result.data.relations"),
#"Expanded Column1.Result.data.relations1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.relations", "Column1.Result.data.relations", {"id", "relationType", "gender", "firstName", "lastName", "displayName", "email", "dateOfBirth", "profession", "mobile"}, {"Column1.Result.data.relations.id", "Column1.Result.data.relations.relationType", "Column1.Result.data.relations.gender", "Column1.Result.data.relations.firstName", "Column1.Result.data.relations.lastName", "Column1.Result.data.relations.displayName", "Column1.Result.data.relations.email", "Column1.Result.data.relations.dateOfBirth", "Column1.Result.data.relations.profession", "Column1.Result.data.relations.mobile"}),
#"Expanded Column1.Result.data.educationDetails" = Table.ExpandListColumn(#"Expanded Column1.Result.data.relations1", "Column1.Result.data.educationDetails"),
#"Expanded Column1.Result.data.educationDetails1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.educationDetails", "Column1.Result.data.educationDetails", {"degree", "branch", "yearOfJoining", "yearOfCompletion", "cgpa", "university", "id", "customFields"}, {"Column1.Result.data.educationDetails.degree", "Column1.Result.data.educationDetails.branch", "Column1.Result.data.educationDetails.yearOfJoining", "Column1.Result.data.educationDetails.yearOfCompletion", "Column1.Result.data.educationDetails.cgpa", "Column1.Result.data.educationDetails.university", "Column1.Result.data.educationDetails.id", "Column1.Result.data.educationDetails.customFields"}),
#"Expanded Column1.Result.data.experienceDetails" = Table.ExpandListColumn(#"Expanded Column1.Result.data.educationDetails1", "Column1.Result.data.experienceDetails"),
#"Expanded Column1.Result.data.experienceDetails1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.experienceDetails", "Column1.Result.data.experienceDetails", {"companyName", "jobTitle", "dateOfJoining", "dateOfRelieving", "location", "description", "id", "customFields"}, {"Column1.Result.data.experienceDetails.companyName", "Column1.Result.data.experienceDetails.jobTitle", "Column1.Result.data.experienceDetails.dateOfJoining", "Column1.Result.data.experienceDetails.dateOfRelieving", "Column1.Result.data.experienceDetails.location", "Column1.Result.data.experienceDetails.description", "Column1.Result.data.experienceDetails.id", "Column1.Result.data.experienceDetails.customFields"}),
#"Expanded Column1.Result.data.customFields" = Table.ExpandListColumn(#"Expanded Column1.Result.data.experienceDetails1", "Column1.Result.data.customFields"),
#"Expanded Column1.Result.data.customFields1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.customFields", "Column1.Result.data.customFields", {"id", "title", "type", "value"}, {"Column1.Result.data.customFields.id", "Column1.Result.data.customFields.title", "Column1.Result.data.customFields.type", "Column1.Result.data.customFields.value"}),
#"Expanded Column1.Result.data.groups" = Table.ExpandListColumn(#"Expanded Column1.Result.data.customFields1", "Column1.Result.data.groups"),
#"Expanded Column1.Result.data.groups1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.groups", "Column1.Result.data.groups", {"id", "title", "groupType"}, {"Column1.Result.data.groups.id", "Column1.Result.data.groups.title", "Column1.Result.data.groups.groupType"}),
#"Expanded Column1.Result.data.leavePlanInfo" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.groups1", "Column1.Result.data.leavePlanInfo", {"identifier", "title"}, {"Column1.Result.data.leavePlanInfo.identifier", "Column1.Result.data.leavePlanInfo.title"}),
#"Expanded Column1.Result.data.shiftPolicyInfo" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.leavePlanInfo", "Column1.Result.data.shiftPolicyInfo", {"identifier", "title"}, {"Column1.Result.data.shiftPolicyInfo.identifier", "Column1.Result.data.shiftPolicyInfo.title"}),
#"Expanded Column1.Result.data.weeklyOffPolicyInfo" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.shiftPolicyInfo", "Column1.Result.data.weeklyOffPolicyInfo", {"identifier", "title"}, {"Column1.Result.data.weeklyOffPolicyInfo.identifier", "Column1.Result.data.weeklyOffPolicyInfo.title"}),
#"Expanded Column1.Result.data.captureSchemeInfo" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.weeklyOffPolicyInfo", "Column1.Result.data.captureSchemeInfo", {"identifier", "title"}, {"Column1.Result.data.captureSchemeInfo.identifier", "Column1.Result.data.captureSchemeInfo.title"}),
#"Expanded Column1.Result.data.trackingPolicyInfo" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.captureSchemeInfo", "Column1.Result.data.trackingPolicyInfo", {"identifier", "title"}, {"Column1.Result.data.trackingPolicyInfo.identifier", "Column1.Result.data.trackingPolicyInfo.title"}),
#"Expanded Column1.Result.data.expensePolicyInfo" = Table.ExpandRecordColumn(#"Expanded Column1.Result.data.trackingPolicyInfo", "Column1.Result.data.expensePolicyInfo", {"identifier", "title"}, {"Column1.Result.data.expensePolicyInfo.identifier", "Column1.Result.data.expensePolicyInfo.title"}),
#"Expanded Column1.Result.data.educationDetails.customFields" = Table.ExpandListColumn(#"Expanded Column1.Result.data.expensePolicyInfo", "Column1.Result.data.educationDetails.customFields"),
#"Removed Duplicates" = Table.Distinct(#"Expanded Column1.Result.data.educationDetails.customFields", {"Column1.Result.data.employeeNumber"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Column1.Result.data.marriageDate", type datetimezone}, {"Column1.Result.data.joiningDate", type datetimezone}, {"Column1.Result.data.dateOfBirth", type datetimezone}, {"Column1.Result.data.exitDate", type datetimezone}, {"Column1.Result.data.resignationSubmittedDate", type datetimezone}, {"Column1.Result.data.probationEndDate", type datetimezone}, {"Column1.Result.data.educationDetails.yearOfJoining", type datetime}, {"Column1.Result.data.educationDetails.yearOfCompletion", type datetime}, {"Column1.Result.data.experienceDetails.dateOfJoining", type datetime}, {"Column1.Result.data.experienceDetails.dateOfRelieving", type datetime}})
in
#"Changed Type"
