我继承了一个相当复杂的excel工作簿,下面的查询向用户抛出了一个错误,因为在#“Filtered Rows”步骤中没有空值,它只返回了一个空表。因此,我正在尝试找出是否有一种方法可以让查询在表为空时停止,但如果它不继续。感谢您关于如何进行此操作的任何建议。
我正在尝试添加#“检查空表”步骤。
Source = Excel.CurrentWorkbook(){[Name="SAPCrosstab5"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Planning Reference", type text}, {"Column2", type text}, {"Project Definition", type text}, {"Column4", type text}, {"Distribution Channel", type text}, {"Distribution Channel_1", type text}, {"Contract Fee Type", type text}, {"Comp VS Sole Source", type text}, {"Outside/Assist (Rplan)", type text}, {"Booking Disposition (Rplan)", type text}, {"", type text}, {"Sold To Party", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Planning Reference"},#"AOP losses filter",{"Planning Reference"},"AOP losses filter",JoinKind.LeftOuter),
#"Expanded AOP losses filter" = Table.ExpandTableColumn(#"Merged Queries", "AOP losses filter", {"Planning Reference"}, {"Planning Reference.1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded AOP losses filter", each ([Planning Reference.1] = null) and ([#"Booking Disposition (Rplan)"] = "Non-firm / Opportunity")),
#"Check Empty Table" = if Table.IsEmpty(#"Filtered Rows") then Stop Query else #"Filtered Rows",
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Project Definition", "Column4", "Distribution Channel", "Planning Reference.1"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#""]), "", "_2", List.Sum),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"BBN", "Total Sales", "Total Profit", "Total Bookings"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","Non-firm / Opportunity","Inactive/Delete",Replacer.ReplaceText,{"Booking Disposition (Rplan)"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Distribution Channel_1", "Distribution Channel"}})
in
#"Renamed Columns"
1条答案
按热度按时间vyswwuz21#
当您了解
let ... in ...
可以嵌套时,这实际上是微不足道的。如果我没有犯语法错误,应该是这样的:
微软在他们的页面here上暗示了这种处理方式(“跳过”一些计算而不是停止整个评估):
if
如果条件then
真表达式else
假表达式*仅当if条件的计算结果为值
true
时,才会计算true表达式。*仅当if条件的计算结果为值
false
时,才计算false表达式。