Excel POWER查询(使用SQL)将行值作为参数传递 [英] Excel power query (using SQL) to pass row value as parameter

问题描述

我正在尝试使用MS Excel Power Query根据每行中的项目从SQL DB中获取值。

我的Excel表格在A1、B1中有以下内容：

日期：=今天()

A2、B2具有以下标头，而A3具有水果列表。C2-F2包含其他信息。因此，val需要在col中填充。B

Fruit, Val
Apple
Orange
Banana

SQL查询如下：

select val
from MY_TABLE
WHERE fruit = ?
AND date = ?

？是参数，它链接到单元格$B$1(日期)，$A3(水果列表中的第一项)

我使用的是ODBC数据连接，我在其中输入查询并将最终参数插入为？

然后通过编辑Connections&>属性，我更改了‘Definition’选项卡下的参数，选择了适当的单元格。

但当我将其拖到下一个单元格时，它不会更新。我尝试将$A3更改为$A4，但再次仅在单元格B3中返回值。

您知道如何为每一行更新此内容吗？

我知道我可以使用MS SQL数据连接，在那里我可以使用类似

的查询

SELECT val
FROM MY_TABLE
WHERE fruit IN (
'Apple',
'Orange',
'Banana'
)

但许多人都在使用EXCEL表格，因此，水果列表会定期更新。所以使用静态查询并不理想。

我想要实现的是，每当水果列表更新时，用户可以选择快速填充到下一个单元格，这将通过引用等效的单元格A来更新列B。

推荐答案

我无法以这种方式重现您在创建动态SQL查询参数时遇到的确切问题(由于某种原因，定义选项卡下的参数按钮显示为灰色，我正在使用Windows 10上的Microsoft 365的Excel)。无论如何，如果您成功地做到了这一点，您不是会为每个单元格得到一个唯一的查询吗？我想这会影响单击数据&>全部刷新时的性能。

在任何情况下，我相信使用Power Query的原因之一是让它为您编写SQL查询：Power Query Editor&>查询设置窗格右侧的right-click on the last one应用步骤，然后单击View Native Query以查看发送到服务器的SQL查询。当您进一步处理数据时，这个底层的SQL查询将根据query folding支持的语句进行自动编辑。当然，连接器需要支持这一点，因此我建议使用MS SQL Server连接器。请注意，有时View Native Query选项显示为灰色，但查询折叠仍在进行，确定的唯一方法是在数据库上使用性能分析工具。

这里有一种使用Power Query的方法，以便在一次数据刷新中更新整个Val列。

单击单元格B1并将其命名为cellDate，方法是在公式栏左侧的名称框中使用，然后右键单击单元格B1&>从表/区域获取数据...若要打开Power Query编辑器，请执行以下操作。

将Power Query编辑器公式栏的内容替换为：

= Date.From(Excel.CurrentWorkbook(){[Name="cellDate"]}[Content][Column1]{0})

您现在有了一个从单元格B1返回日期的查询。现在，单击包含要从数据库导入的表的查询(本例中名为Fruits)。使用下拉列表筛选日期列并选择任意随机日期。

在编辑栏中，将#date(2021, 9, 10)替换为cellDate。现在，每当您更改单元格B1中的日期并刷新数据时，此筛选器都会更新。如果您正在忽略Privacy Level settings或对您的工作簿使用公共隐私级别，则此筛选步骤应折叠到数据源。

关闭并仅将这些查询作为连接加载。

选择包含水果名称的单元格范围，创建一个表，将其命名为listFruits，然后右键单击；从表/范围中获取数据...若要打开Power Query编辑器，请执行以下操作。

在左侧的查询列表中，右键单击listFruits&>复制。将其重命名为listFruitsValues。在主页选项卡上&>合并查询。选择Fruits作为第二个表，然后单击每个表中的Fruit列。选择作为联接类型：左侧外部(全部从第一个开始，匹配从第二个开始)，然后单击确定。请注意，从这一步开始，查询不会折叠回数据源。

单击Fruits列的展开按钮，仅选择Val列，取消选中使用原始列名作为前缀，然后选择确定。删除Fruit列。

这是Power Query Editor窗口在此阶段的外观。

现在，您可以在Fruit表旁边的工作表中加载listFruitsValues查询。以下是默认表格格式的外观。

现在，如果对日期和/或水果列表进行任何编辑，则单击数据&>全部刷新将相应地更新Val列。

最后，如果为日期过滤的源表(即本例中的Fruits)不太大，我建议考虑一种不同的方法。上述方法的问题在于，用户在每次编辑水果列表后都需要点击刷新全部按钮。这可以通过在单独的工作表中加载Fruits查询并使用以下公式填充Val列来避免：

=XLOOKUP(A4,Fruits[Fruit],Fruits[Val])

通过创建具有Fruit和Val列的单个表，这些值在更改水果列表时立即更新，而Fruits查询只需要在更改日期时刷新。

这篇关于Excel POWER查询(使用SQL)将行值作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！