Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 accomplish this, and it works beautifully both in Power Query and in the Dataflow Editor. However, when attempting to save the dataflow, I receive the error "One or more entities reference a dynamic data source."
I've read multiple articles that reference these issues, and have attempted to fix it myself using RelativePath, but I still can't seem to get it to work. Maybe I'm using RelativePath in the wrong place? I'm hoping another set of eyes on this can help me resolve it.
Reference:
Here's my query:
let
Source =
let
BaseUrl = pMetricsUri,
Token = pToken,
EntitiesPerPage = 100,
Options = [Headers=[ #"Authorization" = "Basic " & Token ]],
Url = BaseUrl,
GetJson = (Url) =>
let
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in
Json,
GetTotalCount = () =>
let
Json = GetJson(Url),
Entities = Json[count]
in
Entities,
EntityCount = GetTotalCount(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndex = { 1 .. PageCount},
GetPage = (PageIndex) =>
let
PageUrl = BaseUrl & "page=" & Text.From(PageIndex),
Json = GetJson(PageUrl),
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"
Solved! Go to Solution.
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"
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 ,
Try the following query:
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)])),
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 for your suggestion! Unfortunately, the proposed solution causes the following error:
DataFormat.Error: We found extra characters at the end of JSON input.
Details
Value =
Position = 4
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:
Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
39 | |
21 | |
21 | |
19 | |
10 |
User | Count |
---|---|
35 | |
35 | |
34 | |
20 | |
14 |