Error when saving dataflow: "One or more entities reference a dynamic data source"

[pdbenbow]

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:

• Dynamic Web.Contents() and Power BI Refresh Errors – Data Inspirations
• Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

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"

[pdbenbow · Accepted 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"

[v-kkf-msft]

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.

[pdbenbow]

@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

[v-kkf-msft]

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.

[pdbenbow]

@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.

Details

Arguments = {"https://myOrganization.zendesk.com/api/v2/ticket_metrics.json?", [...], [...]}