Power BI动态数据源：一次解决问题的思考历程

前两天在使用powerbi从trello获取数据发布到云端进行刷新时，出现一个从没遇到过的错误，这个错误导致的结果是数据源那一项直接没了，连给你纠正错误的机会都不给：

点开“发现数据源”：

此数据集包含一个动态数据源。由于 Power BI 服务中不刷新动态数据源，因此不会刷新此数据集。了解详细信息: https://aka.ms/dynamic-data-sources

自然是要点击详细信息查看错误原因：

点开上方链接，官方解释：

动态数据源是这样一种数据源，其中的部分或所有信息在 Power Query 运行查询之后才能确定是否需要连接，因为数据是在代码中生成的或从其他数据源返回的。示例包括：SQL Server 数据库的实例名称和数据库；CSV 文件的路径；或 Web 服务的 URL。

In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

在大多数情况下，无法在 Power BI 服务中刷新使用动态数据源的 Power BI 数据集。有几种例外情况，可以在 Power BI 服务中刷新动态数据源，例如，将 RelativePath 和查询选项与 Web.Contents M 函数结合使用时。也可以刷新引用 Power Query 参数的查询。

若要确定是否可以刷新动态数据源，请在 Power Query 编辑器中打开“数据源设置”对话框，然后选择“当前文件中的数据源” 。在出现的窗口中，查找以下警告消息，如下图所示：

某些数据源可能未列出，因为它们包含手动编写的查询。

如果该警告显示在出现的“数据源设置”对话框中，则会显示无法在 Power BI 服务中刷新的动态数据源。

如果该警告显示在出现的“数据源设置”对话框中，则会显示无法在 Power BI 服务中刷新的动态数据源。

这是我的查询模型：

【这个是动态数据源的】源代码：

let
Source = Json.Document(Web.Contents("https://api.trello.com/1/boards/"&TrelloBoardKey&"/cards?key="&TrelloAPIKey&"&token="&TrelloAPIToken&"")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展开的“Column1”" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"id", "name"}),
    已调用自定义函数 = Table.AddColumn(#"展开的“Column1”", "ge_checklists", each ge_checklists("3f01edb8xxxxxxxxef411f1339ef64fd", "e7666a662dxxxxxxxxe3d25d1a75b33f2af2cbxxxxxxxxecf2029f0e8c", [id])),
    #"展开的“ge_checklists”" = Table.ExpandTableColumn(已调用自定义函数, "ge_checklists", {"Column1"}, {"ge_checklists.Column1"}),
    #"展开的“ge_checklists.Column1”" = Table.ExpandRecordColumn(#"展开的“ge_checklists”", "ge_checklists.Column1", {"id", "name"}, {"ge_checklists.Column1.id", "ge_checklists.Column1.name"})
in
    #"展开的“ge_checklists.Column1”"

这个是的ge_checklists的源代码：

let
    源 = (TrelloAPIKey as text, TrelloAPIToken as text, TrelloCardID as text) => let
Source = Json.Document(Web.Contents("https://api.trello.com/1/cards/"&TrelloCardID&"/checklists?key="&TrelloAPIKey&"&token="&TrelloAPIToken)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"
in
    源

在这里面我是先用Web.Contents获取了一堆数据，再调用自定义函数，而自定义函数ge_checklists还是Web.Contents来获取。

根据官方的解释【其中的部分或所有信息在 Power Query 运行查询之后才能确定是否需要连接，因为数据是在代码中生成的或从其他数据源返回的。】，第二个Web.Contents是否需要连接，取决于第一个Web.Contents是否会返回数据，因为只有第一个Web.Contents返回数据时，自定义函数ge_checklists里的Web.Contents才会有存在的意义，代码才会被允许执行。

而在Power Query 运行查询之前，Power Query如何才能知道第一个Web.Contents是否真的会返回数据呢？

它是直接先检查第一个Web.Contents的url，如果这个url是个准确的不含参数的url，那么它的返回值就认为是固定的不会变的，在桌面端Power Query中是什么结果，上了云端也是什么结果。

很自然的，在桌面端Power Query中，调用自定义函数之前，一定是要有数据的，正常人不可能对一个空表进行自定义函数查询。以下展示非正常人类：

【筛选的行】，本来就是空表，这是用自定义函数查询了个寂寞吗？

在本地Power Query中，步骤是一步一步进行的，每一步都得进行计算，对于这种空表进行运算是毫无意义的。但是没办法，本地Power Query程序就是这样，无意义的运算也得运算。

而在云端，由于Power BI 背后的引擎，智能化运算过程，对于无意义的运算自然是要尽最大可能的规避，因此如果第一个Web.Contents返回的是空表，那么第二步的自定义函数还需要计算吗？完全不需要！（此处，举个极端的例子，一个数据量非常大的表导入Power Query，进行了上百个操作，各种自定义函数，筛选去重，修改，计算，然后最后一步是筛选为空表，就跟上图一样，请问进行刷新操作时，Power Query会花费较长时间计算还是直接不进行计算直接当成个空表处理？答案是后者，这就是Power Query的智能引擎。）

也就是说，在云端，对于第二步的自定义函数查询，如果第一步确切的返回数据，那么自定义函数查询就运行，如果第一步确切的不返回数据，那么自定义函数查询就不运行。一切都是确定的。

现在我的第一个Web.Contents里的url是拼接起来的，在运行查询之前，引擎并不能知道我这个随便拼接起来的url是否能够返回数据，也就是不确定。

在面对不确定问题时，Power BI 的引擎无法确定是否要进行第二步的自定义查询，因此就陷入了迷茫。

好在Power Query给我们提供了一种解决办法，例外的情况：

在大多数情况下，无法在 Power BI 服务中刷新使用动态数据源的 Power BI 数据集。有几种例外情况，可以在 Power BI 服务中刷新动态数据源，例如，将 RelativePath 和查询选项与 Web.Contents M 函数结合使用时。也可以刷新引用 Power Query 参数的查询。

查阅Web.Contents的说明：

Web.Contents - PowerQuery M | Microsoft Docs

https://docs.microsoft.com/zh-cn/powerquery-m/web-contents

我们对照着示例将源代码进行修改：

let
in   
Web.Contents(       
  "https://api.trello.com/1/boards/",       
    [           
        RelativePath=TrelloBoardKey&"/cards",           
        Query=[key=TrelloAPIKey,token=TrelloAPIToken]       
    ]   
)

结果发布的时候出现这个：

到云端看看，按照anonymous验证不了：

按照basic方式填入用户名和密码也不行：

还是有问题的。没有办法，耐着性子又看了一遍Web.Contents的示例，发现它的url是网站根目录"https://www.bing.com"，

我这个是添加了“/1/boards”这个后缀的，而这部分应该放在relativepath的，疏忽了。

赶紧修改过来：

let
in
   Web.Contents(
          "https://api.trello.com",
          [
          RelativePath="1/boards/"&TrelloBoardKey&"/cards",
          Query=[key=TrelloAPIKey,token=TrelloAPIToken]       
          ]   
)

再次发布到云端，设置数据源，没问题：

再进行一次刷新，成功了：

在工作、学习、生活中总是会遇到各种各样的问题，可是又有什么可怕的呢，直面它，战胜它，这就是该有的人生。