基于 Excel 列列表筛选 SQL 视图的 Power Query [英] Power Query to Filter a SQL view based on an Excel column list

问题描述

有没有办法使用 Power Query 根据 Excel 表列中的值列表过滤 SQL 视图?

Is there a way to filter a SQL view based on a list of values in an excel table column using Power Query?

我有一个返回大量数据(数百万条记录或属性)的 SQL 视图.用户希望根据属性 ID 的 excel 表列进行过滤.我知道我可以根据视图和电源查询中的 excel 列之间的属性 ID 进行合并连接.但看起来合并首先引入了数百万条记录，然后在连接中对其进行了过滤.这需要很长时间.用户希望每天动态更改 propertyID 列表并运行查询.

I have a SQL view that returns a large set of data (millions of records or properties). Users want to filter that based on an excel table column of property IDs. I know I can just do a merge join based on the property ID between the view and the excel column in power query. But it looks like the merge brings in the millions of records first then filtered it in the join. Which takes a long time. Users want to change the list of propertyIDs on the fly on a daily basis and run the query.

本质上，我想在 Excel 中创建电源查询SQL 中有什么

Essentially, I wanted to create in Excel power query what is in SQL

SELECT * FROM SQLViewName 
WHERE PropertyID IN (Select Column from ExcelTable) 

推荐答案

你应该可以使用 List.Contains 函数来做到这一点.

You should be able to do this with a List.Contains function.

如果我的 ExcelTable 是

ID
---
436
437
438
439

然后添加这样的过滤器应该可以解决问题:

then adding a filter like this should do the trick:

Table.SelectRows(SQLViewName, each List.Contains(ExcelTable[ID], [PropertyID]))

<小时>

当我尝试这个并在最后一个应用步骤中执行 View Native Query 时，它将 Excel 表折叠到 WHERE 子句中，其中 ExcelTable 值作为文字如下:

---

When I tried this and did View Native Query on the last applied step, it folded the Excel table into a WHERE clause with the ExcelTable values as literals like this:

select [_].[PropertyID],
    [_].[OtherColumns]
from [dbo].[SQLViewName] as [_]
where [_].[PropertyID] in (436, 437, 438, 439)

这让我可以在几秒钟内加载数百万行的表.

This allowed me to load a multi-million-row table in just a couple seconds.

这篇关于基于 Excel 列列表筛选 SQL 视图的 Power Query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！