英文:
Why is query folding disabled in Power Query after adding a computed column using coalesce operator?
问题
在Power BI Desktop中，我从SQL Server表中提取了一些列，使用的是导入模式。在转换过程中，有2列，我想要基于它们创建第三个计算列，类似于SQL中的isnull(col1, col2) as 'col3'。但问题是在这一步失去了查询折叠。
我在我的Power Query中添加了一个自定义步骤，如下所示：
= Table.AddColumn(#"PreviousStep", "col3", each [col1] ?? [col2])
所有列都来自同一查询/表。我期望查询会折叠，但它并没有。请有人帮助我理解为什么？
请注意，我不能触摸源数据库，因此无法为自己创建任何视图。
英文:
In Power BI desktop, I pulled some columns off a SQL Server table in Import mode. During transformation, there are 2 columns using which I want to build a third computed column: something like isnull(col1,col2) as 'col3' if I had to write it in SQL. But problem is that query folding is lost at this step.
I added a custom step in my power query as follows:
= Table.AddColumn(#"PreviousStep", "col3", each [col1] ?? [col2])
All columns are from same query/table. I expected the query to fold, but it does not. Please could someone help me understand why?
Kindly note, that I can't touch the source database, hence can't create any views for myself.
答案1
得分: 1
为什么特定的 M 表达式会破坏折叠，我不知道。但您可以始终通过使用 原生查询折叠 和 Value.NativeQuery 来解决此问题。例如
let
Query = "select *, coalesce(OrderDate,ShipDate) ShipOrOrderDate from Sales.SalesOrderHeader",
Source = Sql.Database("office", "AdventureWorks2017"),
Data = Value.NativeQuery(Source, Query, null, [EnableFolding=true]),
#"Filtered Rows" = Table.SelectRows(Data, each [TotalDue] > 472.3108)
in
#"Filtered Rows"
Table.SelectRows 将被折叠成 SQL
select [_].[SalesOrderID],
[_].[RevisionNumber],
[_].[OrderDate],
[_].[DueDate],
[_].[ShipDate],
[_].[Status],
[_].[OnlineOrderFlag],
[_].[SalesOrderNumber],
[_].[PurchaseOrderNumber],
[_].[AccountNumber],
[_].[CustomerID],
[_].[SalesPersonID],
[_].[TerritoryID],
[_].[BillToAddressID],
[_].[ShipToAddressID],
[_].[ShipMethodID],
[_].[CreditCardID],
[_].[CreditCardApprovalCode],
[_].[CurrencyRateID],
[_].[SubTotal],
[_].[TaxAmt],
[_].[Freight],
[_].[TotalDue],
[_].[Comment],
[_].[rowguid],
[_].[ModifiedDate],
[_].[ShipOrOrderDate]
from
(
select *, coalesce(OrderDate,ShipDate) ShipOrOrderDate from Sales.SalesOrderHeader
) as [_]
where [_].[TotalDue] > 4.723108E+002
英文:
Why that particular M expression breaks folding, I don't know. But you can always work around this by using Native Query Folding with Value.NativeQuery. EG
let
Query = "select *, coalesce(OrderDate,ShipDate) ShipOrOrderDate from Sales.SalesOrderHeader",
Source = Sql.Database("office", "AdventureWorks2017"),
Data = Value.NativeQuery(Source,Query,null,[EnableFolding=true]),
#"Filtered Rows" = Table.SelectRows(Data, each [TotalDue] > 472.3108)
in
#"Filtered Rows"
The Table.SelectRows will be folded into SQL
select [_].[SalesOrderID],
[_].[RevisionNumber],
[_].[OrderDate],
[_].[DueDate],
[_].[ShipDate],
[_].[Status],
[_].[OnlineOrderFlag],
[_].[SalesOrderNumber],
[_].[PurchaseOrderNumber],
[_].[AccountNumber],
[_].[CustomerID],
[_].[SalesPersonID],
[_].[TerritoryID],
[_].[BillToAddressID],
[_].[ShipToAddressID],
[_].[ShipMethodID],
[_].[CreditCardID],
[_].[CreditCardApprovalCode],
[_].[CurrencyRateID],
[_].[SubTotal],
[_].[TaxAmt],
[_].[Freight],
[_].[TotalDue],
[_].[Comment],
[_].[rowguid],
[_].[ModifiedDate],
[_].[ShipOrOrderDate]
from
(
select *, coalesce(OrderDate,ShipDate) ShipOrOrderDate from Sales.SalesOrderHeader
) as [_]
where [_].[TotalDue] > 4.723108E+002
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论