Forum Discussion
pdbenbow
5 years agoResolver II
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...
- 5 years ago
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"
pdbenbow
5 years agoResolver II
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"