希望你能帮到忙。我的电源查询中有以下代码,它与连接的电源数据透视表配合使用效果很好。一旦源行返回"此表为空",就会出错:
- 电源查询返回错误消息,指出无法识别源行后面的 2 行。
- Power Pivot 中的表显示上次工作查询的最后结果。
如果没有结果,我需要表格为空。
我该怎么做?
let
UrlSource = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(UrlSource,{{"Url", type text}}),
Url = #"Changed Type"{0}[Url],
UserInput = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Sagsnummer Type" = Table.TransformColumnTypes(UserInput,{{"Sagsnummer", type text}}),
Sagsnummer = #"Changed Sagsnummer Type"{0}[Sagsnummer],
Source = OData.Feed(Url & "/FileContacts?$select=CustomLabel_Summary,Name/Name1&$expand=Name&$filter=File/FileNo eq '" & Sagsnummer & "'"),
#"Expanded Name" = Table.ExpandRecordColumn(Source, "Name", {"Name1"}, {"Name.Name1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Name",{{"CustomLabel_Summary", "Rolle"}, {"Name.Name1", "Kontakt"}})
in
#"Renamed Columns"
添加最后一个步骤,检查 UrlSource 和 UserInput 是否有一行输入。如果没有,则默认为某个空表:
= if Table.RowCount(UrlSource) > 0 and Table.RowCount(UserInput) > 0 then #"Renamed Columns" else #table({"Rolle", "Kontakt"}, {})
总之,您的代码看起来像
let
UrlSource = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(UrlSource,{{"Url", type text}}),
Url = #"Changed Type"{0}[Url],
UserInput = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Sagsnummer Type" = Table.TransformColumnTypes(UserInput,{{"Sagsnummer", type text}}),
Sagsnummer = #"Changed Sagsnummer Type"{0}[Sagsnummer],
Source = OData.Feed(Url & "/FileContacts?$select=CustomLabel_Summary,Name/Name1&$expand=Name&$filter=File/FileNo eq '" & Sagsnummer & "'"),
#"Expanded Name" = Table.ExpandRecordColumn(Source, "Name", {"Name1"}, {"Name.Name1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Name",{{"CustomLabel_Summary", "Rolle"}, {"Name.Name1", "Kontakt"}}),
Custom1 = if Table.RowCount(UrlSource) > 0 and Table.RowCount(UserInput) > 0 then #"Renamed Columns" else #table({"Rolle", "Kontakt"}, {})
in
Custom1