拖链计算公式Power Query案例合并同一个文件夹下多个Excel工作簿文件及SQL转置动态查询自动刷新

2020年10月3日发(作者：鱼又玄)
去年一分一档，今年一分一档。换算
快速统计两家指标。
财务付款统计表与成本合同台账匹配合同编号


let fx=(s)=>
let
源 = rContents(
信息= orm(((源,
ns(_,
a=orm(信息,rContents),
b=orm(a,(r)=>(((r,
ns(_,
in
b
in
fx

文件夹源 = umn( (添加列
直接两个文件源。= e({ok(ts(
null, true){[Name=第2页

true){[Name=第2页
清单去空白行。= ormColumns(源,{表2.项目特征描述
e(((_,
A1-1…自定义序列。= ormColumns(exColumn(源, 序号序号
(_,
连接极简方法。
= Join(源,{品名表2,{品名表ter)，再展开

= umn(源, 自定义表2{onOf(表2[品名],[品名])}[数量])
= umn(更改的类型, 规范名称清单规范,(a)=>
ns([项目编码],a[项目编码])))
Table合并列。= cords(ormRows(源表, each [年份=[年份],合同号=[合同
号],合同名称 =[合同名称],合同产值=[#签证汇总金额=[签证汇总金额],变更金
额汇总=[变更金额汇总] ,签证占比=[签证占比],变更占比=[变更占比],签证分级
=([签证10万上])&签证5至1 0万])&签证5万下]),
签证总份数=[签证总份数],变更分级=([变更10万上])&变更5 至10
万])&变更5万下]),变更总份数=[变更总份数]] ))

提取“OA”字符后8位。([#流程#(lf)状态流程#(lf)状态
区分大小写

提取分隔符之间的文本。nDelimiters([合同号],

判断公司名中是否包含关键词。ns([公司名],银行
ns([公司名],[关键词])


保留包含“清单”或“结”的行。
Rows(删除的列, each ns([合同名称], 清单or
ns([合同名称], 结

提取数字。添加自定 义列”对话框，首先将默认的列名“Custom”修改为“纯文本”，接下来在下面的“自定义列公
式 ”窗格输入“= (([DATA],{
快就可以在新添加的自定义列显示从“DATA”列提取出来的 纯文本内容，按照同样的方法继续添加“纯数值”、
“不重复数值”两个自定义列，公式分别为“=(( ([DATA],{
=e(ct(([纯数值])))”，请注意后一个公式的“纯数值”
必须 与上一个自定义列的名称保持一致，否则会提示“Error”
中使用的第二个参数{，移除所有大小写英文字母。
([原始信息],{一龥，移除中文。
([原始信息],{，移除数字。
([原始信息],{，移除英文字符
以上：([ 自定义],{
([自定义],(([自定义],{一龥两层Remove保留纯粹汉字
([自定义],{一龥。是Power BI Desktop12月版本更新中新增的函数，目前Excel中还没有，不过大概应该在一个月之内会更新出来。用法与类似，但功能相反，区别就在
于——你不要的我都要。remove是删除第二参数里包含的字符，而select是仅保留第二参数里包含的 字符。
2014.04.06转为日期。= ormColumnTypes(筛选的行,{{指令单日期type date}, {自定义行type
date}})

（未成功）去掉动态成本3级合同分解。PQ开始分组依据按二级科目分组、操作所有行，即： = (源,
{二级科目计数然后再次按二级科目分组。

核对两列数据差集信息（对比差异）。（朱仕平教程2季33课，因少信息为空，左-右、右-左）
let
源 = tWorkbook(){[Name=表1
拆分1 = umn(源, 第一列数据1],
拆分2 = umn(拆分1, 第二列数据2],
差异 = umn(拆分2, 差异列第二列],[第一列]),
in
差异


利用js里面的正则表达式进行字符处理。


&合并。查询1&查询2；list&list2，代替e；e
深化。= = tWorkbook(){[Name=表1{[负责人=吕静周一]
帮助。=#shared
源。透视表、PQ均可用“连接”为源；SQL可以用PQ为源？
Power Query从其他源从ODBC ，PQ中可以在源编辑处修改SQL语句。
拆分列并展开。
= ListColumn(umn(源, 自定义内容])),自定义
= ListColumn(umn(源, 拆分签证内容简述])), 拆分
筛选合同号不为空。
= Rows(提升的标题, each ([合同号] <> null))

生成两表（列）数据全部组合的极简方法。
方法一：分别给每个表添加一列值相同字段，然后再用合并查询的方法来完成
方法二：直接在 其中一个表（如“项目”）里添加自定义列，引用另一个表（如本例中的“部门”），再把自定义列
的表 展开即可
另= {大王小王& ormMany({黑桃红心梅花方块
试一下：if [表2.单位工程]=[表1.单位工程] then 添加标识字段 else null

Power Query 从文件夹，关键字模糊检索










一、合并同一个文件夹下多个Excel工作簿文件
2017.01.30，成本部，王明金

1、新建一个基于文件夹的查询
1.1、Power Query从文件从文件夹，浏览、找到文件夹；
1.2、点编辑按钮（不要直接点加载）；

2、编辑查询
2.1、删除不需要的多余列,但不要删除[Content]列否则2.2报错！；
2.2、添加列，【自定义列】，输入“ok([Content])”；

添加列后如下图所示：

2.3、自定义列右侧扩展按钮展开，只选择data；


2.4、再次点击定义列右侧扩展按钮展开，选择需要的列

3、关闭并上载
二、又一转置神器——查询（Power Query）
2017.02.09，成本部，王明金

1、源表（不用直接导入工作薄了，这样无法使用SQL语句，用：从其他源从ODBC。）


这里我们给出了通用版的连接字符串的规律：
2007以上版连接字符串：
“driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};driverid=1046;dbq=C: D:百度云同步
盘动态成本@成本部大数据-- 宏（实时更新）.xlsm;defaultdir=c:mypath;dsn=dBASE Files”
2003版连接字符串：
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:;DefaultDir=c:mypath;

我们直接拷贝
driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};driverid=1046;dbq=D:百度云同步盘动态
成本@成本部大数据 --宏（实时更新）.xlsm;defaultdir=c:mypath
SQL语句：SELECT * FROM (select 咨询合同号,[清单编制_咨询单位] AS 咨询单位,'清单' as 分类,'√' as 核查,
合同编号,合同名称,供方名称 from [MC合同大台账] Where [清单编制_咨询单位]<>Null AND [清单编制_咨询单
位]<>'-' AND [清单编制_咨询单位]<>'' AND [清单编制_咨询单位] NOT LIKE '%自%' AND 项目='二期一' UNION
ALL select [咨询_合同],[咨询_单位],'结算' as 分类,'√' as 核查,合同编号,合同名称,供方名称 from [MC合
同大台账] Where [咨询_单位]<>Null AND [咨询_单位]<>'-' AND [咨询_单位]<>'' AND [咨询_单位] NOT LIKE '%
自%' AND 项目='二期一') order by 合同名称,咨询单位,咨询合同号
合同号
[2017]01
[2017]01
[2017]02
[2017]02
[2017]03
[2017]04
供方合同
总包施工合同
总包施工合同
电梯安装合同
电梯安装合同
消防专业分包合同
景观施工合同
类别
清单
结算
清单
结算
结算
结算
清单
核查
√
√
√
√
√
√
√ [2017]04 精装修专业分包合同
2、透视列
3、上载

[2017]04 精装修专业分包合同 结算 √



合同号 供方合同 清单 结算
[2017]01 总包施工合同 √ √
[2017]02 电梯安装合同 √ √
[2017]03 消防专业分包合同 √
[2017]04 景观施工合同 √
[2017]04 精装修专业分包合同 √ √



MQ动态参数查询一例


1、数据自其他来源来自Micrsoft Query
双击*，视图条件（图一）：

（图一）

将数据返回，
参数选单元格，简单修改SQL语句（图二、图三）,

（图二）




（图三）

可按同样的步骤设置参数2
注：
1、模糊查询，SQL语句中“=”改为“LIKE”,提供参数单元格填写“%扣%” ，其他“与”参数条
件单元格可先填写为“%”。

2、【计算选项】要改为“自动”才会在单元格值更改时自动刷新。
3、[]处也可这样写（见下图）：
Excel 加载项管理


1、中文简繁转换工具，在“审阅”选项卡的“中文简繁转换”组中选择相应的按钮
2、Microsoft Power Query 工具

3、数据挖掘加载：



PowerQuery快速合并网页Table多页数据
；
Excel power
queryweb网页数据之

【查询】[编辑查询][高级编辑器]

原有：

http:

修改：

let
函数 =(page) => (ts(

Data2 = {1..5}
in
Data2







“列表”列重命名为“pege”；
“pege”列改为“文本”类型；


umn(重命名的列, 网页数据函数([pege]))

，选“忽略”。







删除不需要的列。
关闭并上载。

----------------- -------------------------------------------------- ------------------
阳光高考网爬专业分数数据：
=(ts(
%E6%9C%A8%E5%B7%A5%E7%A8%8B&studentprovince=%E5%AE %89%E5%BE%BD&fstype=%E7%90%86%E7%A7%91&page=1
改 为：
let
源 =
orm({1..5},(x)=>(ts((Word1=%E5%9C%9F%E6%9C%A8%E5%B7%A5%E7%A8%8B&stude ntprovince=%E5%AE%89%E5%BE%BD&fstype=%E7%90%86%E7% A7%91&page=x
= orm({1..5},(x)=>
(ts(
A8% 8B&studentprovince=%E5%AE%89%E5%BE%BD&fstype=%E7%9 0%86%E7%A7%91&page=
= orm({1..1469},(x)=>
(ts(安徽&fstype=理科&page=
& (x) & 普通本科&schoolyear=&schoolpc=本科一批
自定义1 = e(源),
筛选的行 = Rows(自定义1, each ([Source] =
#展开的“Data”, 学校名称专业名称招生地区文理科
年份录取批次录取平均分学校名称专业名称 招生地区文理科年份
录取批次录取平均分
删除的列 = Columns(#展开的“Data”e
in
删除的列
-------- -------------------------------------------------- ----------------------------------

Power Query 结合SQL语句使用
Power Query 数据源来自SQL语句用：Power Query从其他源从ODBC

driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};driverid=1046;dbq=D:百度云同步盘
动态成本@成本部大数据 --宏（实时更新）.xlsm
SELECT 签证编号,MC签证大台账.合同号, MC合同大台账.合同名称,记账月,签证内容简述,round(核价10000,2)
as 核价价
FROM MC合同大台账,MC签证大台账
WHERE MC签证大台账.合同号 = MC合同大台账.合同编号 AND ((核价>200000) or (核价[①合同产值]>0.3))
order by 记账月
PQ中可以在源编辑处修改SQL语句(上图红色下划线)。

连接属性查看如下：

----------------------------------------- -------------------------------------------------- -----
以下未通过测试：
SELECT a.项目,合同号,b.合同名称,签证单编 号,签证内容简述,签证份数,签证审核金额,变更单编号,变更内容
简述,变更份数,变更审核金额, 记账月,b.①合同产值 FROM ((select [项目_(实际)] as 项目,合同号,合同名称,签
证编号 as 签证单编号,签证内容简述,null as 签证份数,VAL(核价& as 签证审核金额,'' as 变更单编号,'' as
变更内容简述,'' as 变更份数,0 as 变更审核金额,记账月 from [MC签证大台账]a union all select 计项
目,合同号&'-A签证小计','>>>>>>>>>>>' as 合同名称,null as 签证单编号,null as 签证内容简述,VAL(count(核
价)) as 签证份数,sum(VAL(核价&as 签证审核金额,'' as 变更单编号,'' as 变更内容简述,'' as 变更份数,0
as 变更审核金额,null as 记账月 from [MC签证大台账] group by 合同号 UNION ALL select 项目,合同号,[涉及
_合同] as 合同名称,签证单编号, as 签证内容简述, as 签证份数,0 as 签证审核金额,签发单编号 as
变更单编号,变更事项 as 变更内容简述,null as 变更份数,val(估价& as 变更审核金额,记账月 from [MC设计
变更台帐] union all select 计 as 项目,合同号&'-B变更小计','<<<<<<<<<<<' as 合同名称,签证单编
号,签证内容简述,签证份数,0 as 签证审核金额,null as 变更单编号,null as 变更内容简
述,VAL(count(估价)) as 份数,sum(估价) as 变更审核金额,null as 记账月 from [MC设计变更台帐] group by 合
同号)a left join MC合同大台账 b on a.合同号=b.合同编号) where 合同号 like 合同号,记账
月,a.项目
以上已知：null as通过；
VAL(核价&) as 签证审核金额不通过；需要去除&或改为VAL(核价&'')；
val(估价& as 变更审核金额不通过;去除val、&；〔VAL(估价&'')也通过了！〕
as 签证单编号不通过;修改为'' as；
计修改为：'计'

========= ================================================== =====================================


如何在目标文本中查找是否包含多个文本
Power Query ? shenzq ? 于 2天前 ? 76 阅读
ns( text as nullable text, substring as text, optional comparer as nullable function) as nullable
logical
ns只能查找一个文本,如果想查找多个文本需使用如下自定义函数.
let
fx=(x,y)=>e(orm(y,each ns(x,_)))
in
fx(对象,{多个包含对象})

例如: 查找职位名称中是否包含数据运营 数据分析 分析师
let
fx=(x,y)=>e(orm(y,each ns(x,_)))
in
fx([职位名称],{数据运营数据分析分析师