如何创建Power BI可刷新合并查询以解决服务端数据合并报错

我在Power BI Desktop中开发支持定时刷新的查询，已掌握SQL Server和Web数据源的查询方法，Desktop端可正常刷新，但发布到Power BI Service后出现报错。

报错信息 #

Data source error[Unable to combine data] Section1/[Query Name]/[Applied Step Name] references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDbCommand interface. Table: [Query Name].

涉及的查询代码 #

ZdPaginatedTickets查询 #

let
    Source = Json.Document(Web.Contents("https://[COMPANY NAME].zendesk.com/api/v2/search.json", [
    Query = [
        query = "created_at>"&DateTime.ToText(Date.StartOfYear(DateTime.LocalNow()), [Format = "yyyy-MM-dd"])&" tags:returns",
        page = PageParam
    ]
])),
    count = {1..Number.RoundUp(Source[count]/100)},
    #"Converted to Table" = Table.FromList(count, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnZdTicketPagination", each fnZdTicketPagination([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column1"}),
    #"Expanded fnZdTicketPagination" = Table.ExpandTableColumn(#"Removed Columns", "fnZdTicketPagination", {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}, {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}),
    #"Expanded custom_fields" = Table.ExpandListColumn(#"Expanded fnZdTicketPagination", "custom_fields"),
    #"Expanded custom_fields1" = Table.ExpandRecordColumn(#"Expanded custom_fields", "custom_fields", {"id", "value"}, {"id.1", "value"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded custom_fields1", {"id.1"}, ZdTicketFields, {"id"}, "ZdTicketFields", JoinKind.LeftOuter),
    #"Expanded ZdTicketFields" = Table.ExpandTableColumn(#"Merged Queries", "ZdTicketFields", {"title"}, {"ZdTicketFields.title"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded ZdTicketFields",{"id.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[ZdTicketFields.title]), "ZdTicketFields.title", "value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Location", "Total time spent (sec)", "Issue Type", "Contact Reasons", "Type of Ticket (Tag)", "Time spent last update (sec)"})
in
    #"Removed Columns2"

ZdTicketFields查询 #

let
    Source = Json.Document(Web.Contents("https://[COMPANY NAME].zendesk.com/api/v2/ticket_fields")),
    ticket_fields = Source[ticket_fields],
    #"Converted to Table" = Table.FromList(ticket_fields, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}, {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"})
in
    #"Expanded Column1"

已尝试的解决方案及问题定位 #

• 使用临时查询、将两个数据源合并到单个查询，均未解决问题
• 最终确认报错源于Power BI的分区机制；Desktop端因开启「始终忽略隐私级别设置」屏蔽了问题，关闭该设置后Desktop端也会触发相同报错

内容的提问来源于stack exchange，提问作者Salvaria