Forum Discussion
Solved
Error when saving dataflow: "One or more entities reference a dynamic data source"
I'm attempting to port one of my PBI Desktop queries into a dataflow in the Power BI Service. The query hits the Zendesk Metrics API, which requires pagination. I wrote a custom query in M to accompl...
I was finally able to figure it out on my own after several more hours of experimentation. Part of it was needing to affix a "?" before the "page=" text. I also simplified the code substantially.
let Source = let BaseUrl = pMetricsUri, EntitiesPerPage = 100, Options = [Headers=[#"Authorization" = "Basic " & pToken ]], GetTotalCount = () => let Json = Json.Document(Web.Contents(BaseUrl, Options)), Entities = Json[count] in Entities, EntityCount = GetTotalCount(), PageCount = Number.RoundUp(EntityCount / EntitiesPerPage), PageIndex = { 1 .. PageCount}, GetJson = (PageIndex) => let Url = BaseUrl, Json = Json.Document(Web.Contents(Url, [ RelativePath = "?page=" & Text.From(PageIndex), Headers=[ #"Content-Type"="application/json", #"Authorization"="Basic " & pToken ] ])), Value = Json[ticket_metrics] in Value, Urls = List.Transform(PageIndex, each GetJson(_)), DataList = List.Union(Urls), TableFromList = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in TableFromList, #"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"ticket_id", "created_at", "updated_at", "group_stations", "assignee_stations", "reopens", "replies", "assignee_updated_at", "requester_updated_at", "status_updated_at", "initially_assigned_at", "assigned_at", "solved_at", "latest_comment_added_at", "reply_time_in_minutes", "first_resolution_time_in_minutes", "full_resolution_time_in_minutes", "agent_wait_time_in_minutes", "requester_wait_time_in_minutes", "on_hold_time_in_minutes"}, {"ticket_id", "created_at", "updated_at", "group_stations", "assignee_stations", "reopens", "replies", "assignee_updated_at", "requester_updated_at", "status_updated_at", "initially_assigned_at", "assigned_at", "solved_at", "latest_comment_added_at", "reply_time_in_minutes", "first_resolution_time_in_minutes", "full_resolution_time_in_minutes", "agent_wait_time_in_minutes", "requester_wait_time_in_minutes", "on_hold_time_in_minutes"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", {{"created_at", type datetime}, {"updated_at", type datetime}, {"assignee_updated_at", type datetime}, {"requester_updated_at", type datetime}, {"status_updated_at", type datetime}, {"initially_assigned_at", type datetime}, {"assigned_at", type datetime}, {"solved_at", type datetime}, {"latest_comment_added_at", type datetime}}), #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"latest_comment_added_at", DateTime.Date, type date}, {"solved_at", DateTime.Date, type date}, {"assigned_at", DateTime.Date, type date}, {"initially_assigned_at", DateTime.Date, type date}, {"status_updated_at", DateTime.Date, type date}, {"requester_updated_at", DateTime.Date, type date}, {"assignee_updated_at", DateTime.Date, type date}, {"updated_at", DateTime.Date, type date}, {"created_at", DateTime.Date, type date}}), #"Expanded reply_time_in_minutes" = Table.ExpandRecordColumn(#"Extracted Date", "reply_time_in_minutes", {"business"}, {"reply_time_in_minutes"}), #"Expanded first_resolution_time_in_minutes" = Table.ExpandRecordColumn(#"Expanded reply_time_in_minutes", "first_resolution_time_in_minutes", {"business"}, {"first_resolution_time_in_minutes"}), #"Expanded full_resolution_time_in_minutes" = Table.ExpandRecordColumn(#"Expanded first_resolution_time_in_minutes", "full_resolution_time_in_minutes", {"business"}, {"full_resolution_time_in_minutes"}), #"Expanded agent_wait_time_in_minutes" = Table.ExpandRecordColumn(#"Expanded full_resolution_time_in_minutes", "agent_wait_time_in_minutes", {"business"}, {"agent_wait_time_in_minutes"}), #"Expanded requester_wait_time_in_minutes" = Table.ExpandRecordColumn(#"Expanded agent_wait_time_in_minutes", "requester_wait_time_in_minutes", {"business"}, {"requester_wait_time_in_minutes"}), #"Expanded on_hold_time_in_minutes" = Table.ExpandRecordColumn(#"Expanded requester_wait_time_in_minutes", "on_hold_time_in_minutes", {"business"}, {"on_hold_time_in_minutes"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded on_hold_time_in_minutes", {"ticket_id", "created_at", "updated_at", "assignee_updated_at", "requester_updated_at", "status_updated_at", "initially_assigned_at", "assigned_at", "solved_at", "latest_comment_added_at", "reply_time_in_minutes", "first_resolution_time_in_minutes", "full_resolution_time_in_minutes", "agent_wait_time_in_minutes", "requester_wait_time_in_minutes", "on_hold_time_in_minutes", "group_stations", "assignee_stations", "reopens", "replies"}), #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns", {{"reply_time_in_minutes", Int64.Type}, {"first_resolution_time_in_minutes", Int64.Type}, {"full_resolution_time_in_minutes", Int64.Type}, {"agent_wait_time_in_minutes", Int64.Type}, {"requester_wait_time_in_minutes", Int64.Type}, {"on_hold_time_in_minutes", Int64.Type}, {"group_stations", Int64.Type}, {"assignee_stations", Int64.Type}, {"reopens", Int64.Type}, {"replies", Int64.Type}}), #"Marked key columns" = Table.AddKey(#"Changed Type1", {"ticket_id"}, false) in #"Marked key columns"
Hi pdbenbow ,
I modified the query and you could try the following query to see if it is successful.
let
Source = let
BaseUrl = pMetricsUri,
Token = pToken,
EntitiesPerPage = 100,
Options = [Headers=[ #"Authorization" = "Basic " & Token ]],
Url = BaseUrl,
GetTotalCount = () =>
let
Json = Json.Document(Web.Contents(Url,Options)),
Entities = Json[count]
in
Entities,
EntityCount = GetTotalCount(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndex = { 1 .. PageCount},
GetPage = (PageIndex) =>
let
PageUrl = BaseUrl,
Json = Json.Document(Web.Contents(BaseUrl,[RelativePath="page=" & Text.From(PageIndex)],Options)),
Value = Json[ticket_metrics]
in
Value,
GetUrl = (PageIndex) =>
let
PageNum = [RelativePath="page=" & Text.From(PageIndex)],
PageUrl = BaseUrl & PageNum
in
PageUrl,
Urls = List.Transform(PageIndex, each GetUrl(_)),
Pages = List.Transform(PageIndex, each GetPage(_)),
DataList = List.Union(Pages),
TableFromList = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
TableFromList,
#"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"ticket_id", "created_at", "updated_at", "group_stations", "assignee_stations", "reopens", "replies", "assignee_updated_at", "requester_updated_at", "status_updated_at", "initially_assigned_at", "assigned_at", "solved_at", "latest_comment_added_at"}, {"ticket_id", "created_at", "updated_at", "group_stations", "assignee_stations", "reopens", "replies", "assignee_updated_at", "requester_updated_at", "status_updated_at", "initially_assigned_at", "assigned_at", "solved_at", "latest_comment_added_at"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", {{"created_at", type datetime}, {"updated_at", type datetime}, {"assignee_updated_at", type datetime}, {"requester_updated_at", type datetime}, {"status_updated_at", type datetime}, {"initially_assigned_at", type datetime}, {"assigned_at", type datetime}, {"solved_at", type datetime}, {"latest_comment_added_at", type datetime}}),
#"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"latest_comment_added_at", DateTime.Date, type date}, {"solved_at", DateTime.Date, type date}, {"assigned_at", DateTime.Date, type date}, {"initially_assigned_at", DateTime.Date, type date}, {"status_updated_at", DateTime.Date, type date}, {"requester_updated_at", DateTime.Date, type date}, {"assignee_updated_at", DateTime.Date, type date}, {"updated_at", DateTime.Date, type date}, {"created_at", DateTime.Date, type date}}),
#"Transform columns" = Table.TransformColumnTypes(#"Extracted Date", {{"ticket_id", type text}, {"group_stations", type text}, {"assignee_stations", type text}, {"reopens", type text}, {"replies", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"ticket_id", null}, {"group_stations", null}, {"assignee_stations", null}, {"reopens", null}, {"replies", null}})
in
#"Replace errors"
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-kkf-msft , thanks again for your suggestion, but this doesn't work because you're attempting to pass 3 arguments to the Web.Contents() function. Error message is below:
Details
Arguments = {"https://myOrganization.zendesk.com/api/v2/ticket_metrics.json?", [...], [...]}