来自 REST API 调用的 PowerBI XML 数据源有时会返回“表".数据类型 [英] PowerBI XML Data Source from REST API Call sometimes returns "table" data type

问题描述

使用 PowerBI，我正在调用一个简单的 REST API，该 API 从 SAAS 应用程序返回 XML 文档.

在某些情况下，PowerBI 正在为 XML 可能具有空值的某些列创建 Table 类型.一个非常简单的 XML 示例是:

<记录><位置><id>123</id><code>abc</code></位置><位置><id>123</id><代码></代码><========= 1/2 - 从 <code/></位置></记录>

由于第二个 Location 记录中的 code 属性为空，因此当 PowerBI 读取此数据源时，它会将生成的 code 列视为表"值.

此处示例

由于我有许多客户端从同一个 SAAS 应用程序中提取数据，并且它们都可能/可能不使用特定的公共字段(例如代码)，因此很难创建一个可以与多个客户端共享的公共模型.

我不是 Power Query 专家，但我已投入大量时间寻找在 Power Query 中动态扩展"表类型列的方法.我已经好几次接近找到可行但无法解决此问题的解决方案.

展开后的表格中总会有两个值之一.它将是文本值或空值.永远不会超过这两个值.

非常感谢您的想法，第一个帮助我解决此问题的人可以获得一张星巴克礼品卡.

您可以调用 2 个 API 来验证此行为.https://pbitest.proxy.beeceptor.com/ 创建所描述的问题.https://pbitest2.proxy.beeceptor.com/ 像我期望 pbitest 一样工作.

PBI 查询非常简单:

让 Source = Xml.Tables(Web.Contents("https://pbitest2.proxy.beeceptor.com/")),表 = 源{0}[表]在桌子

解决方案

奇怪的是，Power Query 认为像 <code></code> 这样表示的空元素是一个表.p>

这是一个针对该行为的丑陋查询.

让xml = "<记录><位置><id>123</id><code>abc</code></位置><位置><id>124</id><代码></代码></位置></记录>",源 = Xml.Document(xml),#删除其他列"= Table.SelectColumns(Source,{"Value"}),#扩展价值"= Table.ExpandTableColumn(#"Removed Other Columns", "Value", {"Name", "Value"}, {"Name", "Value.1"}),#删除其他列1"= Table.SelectColumns(#"扩展值",{"Value.1"}),#扩展值.1"= Table.ExpandTableColumn(#"Removed Other Columns1", "Value.1", {"Name", "Value"}, {"Name", "Value"}),Table1 = Table.AddColumn(#"Expanded Value.1", "Value2",each if Value.Is([Value], type table) then Table.FirstValue([Value]) else [Value]),#添加自定义"= Table.AddColumn(#"Table1", "UID", each if [Name]="id" then [Value] else null),#删除的列"= Table.RemoveColumns(#"Added Custom",{"Value"}),#填满"= Table.FillDown(#"Removed Columns",{"UID"}),#重命名的列"= Table.RenameColumns(#"Filled Down",{{"Value2", "Value"}}),#透视列"= Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Name]), "Name", "Value"),#已删除的列 1"= Table.RemoveColumns(#"Pivoted Column",{"UID"})在#已删除的列 1"

Using PowerBI, I am calling a simple REST API that returns and XML document from a SAAS app.

In some cases, PowerBI is creating a Table type for certain columns where the XML may have empty values. A very simple XML example is:

<Record>
  <Location>
     <id>123</id>
     <code>abc</code>
  </Location>
  <Location>
     <id>123</id>
     <code></code>      <=========  1/2 - changed this from <code/>
  </Location>
</Record>

Since the code attribute is empty in the second Location record, when PowerBI reads this data source it will treat the resulting code column as a 'Table' value.

example here

Since I have many clients that pull data from the same SAAS app and they all may/may not use a particular common field (e.g., code) it makes it difficult to create a common model that I can share with multiple clients.

I am not a Power Query Expert but I've invested a significant amount of time looking for a method to dynamically 'expand' the Table type columns in Power Query. I've come close a few times to finding a solution that will work but have not been able to solve this problem.

There will always be one of two values in the expanded table. It will either be text value or null. There will never be more than those two values.

Your thoughts are greatly appreciated and a StarBucks Gift Card is available to the first person who helps me solve this issue.

There are 2 APIs that you can call to validate this behavior. https://pbitest.proxy.beeceptor.com/ Creates the issue described. https://pbitest2.proxy.beeceptor.com/ Works as I would expect pbitest to work.

The PBI Query is very simple:

let Source = Xml.Tables(Web.Contents("https://pbitest2.proxy.beeceptor.com/")),
Table = Source{0}[Table]
in
Table

解决方案

That's odd that Power Query thinks an empty element expressed like <code></code> is a Table.

Here's an ugly query that works around that behavior.

let
    xml = "<Record>
  <Location>
     <id>123</id>
     <code>abc</code>
  </Location>
  <Location>
     <id>124</id>
     <code></code>     
  </Location>
</Record>",
    Source = Xml.Document(xml),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Value"}),
    #"Expanded Value" = Table.ExpandTableColumn(#"Removed Other Columns", "Value", {"Name", "Value"}, {"Name", "Value.1"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Value",{"Value.1"}),
    #"Expanded Value.1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Value.1", {"Name", "Value"}, {"Name", "Value"}),
    Table1 = Table.AddColumn(#"Expanded Value.1", "Value2",each if Value.Is([Value], type table) then Table.FirstValue([Value]) else [Value]),
    #"Added Custom" = Table.AddColumn(#"Table1", "UID", each if [Name]="id" then [Value] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"UID"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Value2", "Value"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Name]), "Name", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"UID"})
in
    #"Removed Columns1"

这篇关于来自 REST API 调用的 PowerBI XML 数据源有时会返回“表".数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！