power query table.select优化求助(已上传需优化的报表)

cidaooo

各位大大们，我是刚学习power query的菜鸟。工作上有一张表需要用到lookup公式匹配另一个表里面的数据并返回另一个表中对应的一列，但是条件很多，跑一次很慢，而且每月都要重跑。我想写入power query里降低操作量，提高速度。excel代码如下：
=IF(AV2="","",
            LOOKUP(1,0/(
                                   ('Project'!$AA:$AA=AV2)                                
                                  *('Project'!$K:$K<=ROUNDDOWN(Q2,0))        
                                  *('Project'!$L:$L>=ROUNDDOWN(Q2,0))      
                                  *('Project'!$R:$R<=AA2)                                
                                  *('Project'!$S:$S>=AA2)                        
                                  *('Project'!$T:$T<=AB2)
                                  *('Project'!$U:$U>=AB2)
                                  *('Project'!$I:$I>=ROUND(AE2,4)
                                  )
                           ),
                 'Project'!$A:$A)
         )


然后我自己摸索写了一个query的代码如下，但是加载时候非常的慢。请问各位大神是不是思路有问题，或者该如何优化，谢谢！！
PS. project是匹配表， 返回[序号]列的数据到方案核对表中。

let
    源 = Excel.Workbook(File.Contents("方案核对.xlsx"), null, true),
    project=Excel.Workbook(File.Contents("project.xlsx"), null, true),
    方案核对_Table = 源{[Item="方案核对",Kind="Table"]}[Data],
    缓存project=Table.Buffer(project),
    已添加自定义 = Table.AddColumn( 方案核对_Table, "自定义", each
                                                    Table.SelectRows(缓存project,(x)=>
                                                    x[索引码]=[索引码]
                                                and x[开始日期] <=[申请提交日期]
                                                and x[结束日期] >=[申请提交日期]
                                                and x[首付下限] <=[首付比例]
                                                and x[首付上限] >=[首付比例]
                                                and x[尾款下限] <=[尾款比例]
                                                and x[尾款上限] >=[尾款比例]
                                                and x[SVW贴息比例金额]>=[整车厂贴息比例])[序号]),
    #"展开的“自定义”" = Table.ExpandListColumn(已添加自定义, "自定义")
in
    #"展开的“自定义”"

已上传模板，求大神帮助！感谢！

cidaooo

有没有大神可以帮忙提供个思路呀

deadzlq

没有数据，那只是一堆乱码

cidaooo

deadzlq 发表于 2020-6-1 12:52
没有数据，那只是一堆乱码

其实就是table.selectrows这个公式有没有优化的办法。。数据量大的时候太慢了。。像excel里lookup这种很慢可以借用数组+字典解决。query里有类似的办法吗

cidaooo

deadzlq 发表于 2020-6-1 12:52
没有数据，那只是一堆乱码

大神好，已上传了数据模板

cidaooo

在线等。。求思路

libo5563

先按索引码合并查询,相当于按索引码筛一遍,再对每个单元格里带过来的表用table.select筛选,计算量会少很多.

cidaooo

libo5563 发表于 2020-6-2 10:08
先按索引码合并查询,相当于按索引码筛一遍,再对每个单元格里带过来的表用table.select筛选,计算量会少很多.

索引码合并查询，然后把需要project表中对应的列展开，然后再添加条件列直接用if then引用表内数据。这个办法我试过，确实快很多，但是问题在于展开时候会产生很多重复列（因为索引码不是一对一是多对多），然后需要在最终返回结果后再清晰掉重复列。问题在于不能单纯的删除空/错误值，因为本身就可能有匹配不上的。我需要在保持初始表格完成且不重复的情况下按1、重复行有返回正确结果的则删除空值对应的重复行，2、重复行均返回空的则删除一个空的，3、不重复且返回空的不删除。这三个条件进行筛选我没想出100%不会出错的办法。。

deadzlq

PowerQuery参考优化写法

cidaooo

deadzlq 发表于 2020-6-2 11:23
PowerQuery参考优化写法

大佬，这个写法是有问题的。这个就是我楼上说的索引码合并查询然后在合并后的表内进行筛选，这样确实可以大幅提高速度。但是清单表左侧记录码是唯一且连续的。合并的时候会新增很多重复行，因为索引码不是一对一是多对多。之后匹配完后，就要按一下三个逻辑进行去重，但是去重逻辑的代码我没想出怎么写。您的这个结果上就出现了许多重复记录号的行。

去重逻辑：
1、记录号出现重复的，保留对应proj.序号不为错误/空的行。
2、记录号出现重复的，如果对应的proj.序号都为空/错误，则删除掉重复的，保留一行。
3、记录号没出现重复的，无论proj.序号是否为空/错误，均保留。

最终得到的结果应该是与清单一样连续且不重复的记录号，且保留了所有能匹配上proj.序号的行，删除了重复+序号空的行，没有匹配上的就留空或报错。