Forum Discussion

Resolver II

5 years ago

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 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"

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"

5 Replies

pdbenbow

Resolver II

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"

v-kkf-msft

Community Support

5 years ago

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

Resolver II

5 years ago

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

Community Support

5 years ago

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.

Forum Discussion

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

5 Replies

Related content

SQL Server CDC on VM DB Source Fails with "Property 'username' value '' is invalid" in Fabric Events

Dataflow Gen2 cannot write to Fabric SQL Database, error says "Data source kind: FabricSql"

dataflow gen1

Fabric Dataflow Gen 2

"Working on it" Frustration