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

去年一分一档，今年一分一档。

换算快速统计两家指标。

财务付款统计表与成本合同台账匹配合同编号let fx=(s)=>let源 = Web.BrowserContents("https:///search/"&s&".html"),信息= List.Transform(List.Select(Text.Split(源,"href="""),eachText.Contains(_,"torrent")),(x)=>"https://"&Text.Split(x,""" target"){0}),a=List.Transform(信息,Web.BrowserContents),b=List.Transform(a,(r)=>Text.Split(List.Select(Text.Split(r,"href="""),eachText.Contains(_,"thunder")){0},""">"){0})inbinfx文件夹源= Table.AddColumn( Folder.Files("x"), "添加列", each Excel.Workbook([Content]))直接两个文件源。

= bine({Excel.Workbook(File.Contents("C:\Users\Administrator\Desktop\a1.xls"), null, true){[Name="第2页"]}[Data],Excel.Workbook(File.Contents("C:\Users\Administrator\Desktop\a2.xls"), null,true){[Name="第2页"]}[Data]})清单去空白行。

= Table.TransformColumns(源,{"表2.项目特征描述",eachbine(List.Select(Text.Split(_,"#(lf)"),(x)=>Text.Clean(x)<>""),"#(lf)")})A1-1…自定义序列。

= Table.TransformColumns(Table.AddIndexColumn(源, "序号", 1, 1),{"序号",each Number.ToText(_,"A1-0")})连接极简方法。

= Table.NestedJoin(源,{"品名"},表2,{"品名"},"表2",JoinKind.LeftOuter)，再展开= Table.AddColumn(源, "自定义", each 表2{List.PositionOf(表2[品名],[品名])}[数量])= Table.AddColumn(更改的类型, "规范名称",each Table.SelectRows(清单规范,(a)=> Text.Contains([项目编码],a[项目编码])))Table合并列。

= Table.FromRecords(Table.TransformRows(源表, each [年份=[年份],合同号=[合同号],合同名称=[合同名称],合同产值=[#"①合同产值"],签证汇总金额=[签证汇总金额],变更金额汇总=[变更金额汇总],签证占比=[签证占比],变更占比=[变更占比],签证分级=Text.From([签证10万上])&"-"&Text.From([签证5至10万])&"-"&Text.From([签证5万下]),签证总份数=[签证总份数],变更分级=Text.From([变更10万上])&"-"&Text.From([变更5至10万])&"-"&Text.From([变更5万下]),变更总份数=[变更总份数]] ))提取“OA”字符后8位。

Text.Middle([#"流程#(lf)状态"],Text.PositionOf([#"流程#(lf)状态"],"a")+1,8) //区分大小写提取分隔符之间的文本。

Text.BetweenDelimiters([合同号], "[", "]")判断公司名中是否包含关键词。

Text.Contains([公司名],"银行")Text.Contains([公司名],[关键词])保留包含“清单”或“结”的行。

Table.SelectRows(删除的列, each Text.Contains([合同名称], "清单") or Text.Contains([合同名称], "结"))提取数字。

添加自定义列”对话框，首先将默认的列名“Custom”修改为“纯文本”，接下来在下面的“自定义列公式”窗格输入“= Text.Trim(Text.Remove([DATA],{"0".."9"})," ")”，检查无误之后单击“确定”按钮，很快就可以在新添加的自定义列显示从“DATA”列提取出来的纯文本内容，按照同样的方法继续添加“纯数值”、“不重复数值”两个自定义列，公式分别为“=Text.Remove(Text.Trim(Text.Remove([DATA],{"A".."z"}),"")," ")”和“=bine(List.Distinct(Text.ToList([纯数值])))”，请注意后一个公式的“纯数值”必须与上一个自定义列的名称保持一致，否则会提示“Error”Text.Remove中使用的第二个参数{"A".."z"}) ，移除所有大小写英文字母。

Text.Remove([原始信息],{"一".."龥"})，移除中文。

Text.Remove([原始信息],{"0".."9"})，移除数字。

Text.Remove([原始信息],{" ".."~"})，移除英文字符以上：Text.Remove([自定义],{" ".."~","0".."9","A".."z"})Text.Remove([自定义],Text.ToList(Text.Remove([自定义],{"一".."龥"}))),两层Remove保留纯粹汉字Text.Select([自定义],{"一".."龥"})。

Text.Select是Power BI Desktop12月版本更新中新增的函数，目前Excel中还没有，不过大概应该在一个月之内会更新出来。

用法与Text.Remove类似，但功能相反，区别就在于——你不要的我都要。

remove是删除第二参数里包含的字符，而select是仅保留第二参数里包含的字符。

2014.04.06转为日期。

= Table.TransformColumnTypes(筛选的行,{{"指令单日期", type date}, {"自定义行", type date}})（未成功）去掉动态成本3级合同分解。

PQ//开始//分组依据//按二级科目分组、操作/所有行，即： = Table.Group(源, {"二级科目"}, {{"计数", each _, type table}}),然后再次按二级科目分组。

核对两列数据差集信息（对比差异）。

（朱仕平教程2季33课，因少信息为空，左-右、右-左）let源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],拆分1 = Table.AddColumn(源, "第一列", each Text.Split([数据1],",")),拆分2 = Table.AddColumn(拆分1, "第二列", each Text.Split([数据2],",")),差异 = Table.AddColumn(拆分2, "差异列", each bine(List.Difference([第二列],[第一列]),", ")) in差异利用js里面的正则表达式进行字符处理。

&合并。

查询1&查询2；list&list2，代替bine；bine深化。

= = Excel.CurrentWorkbook(){[Name="表1"]}[Content]{[负责人="吕静"]}[周一]帮助。

=#shared源。

透视表、PQ均可用“连接”为源；SQL可以用PQ为源？Power Query/从其他源/从ODBC ，PQ中可以在源编辑处修改SQL语句。

拆分列并展开。

= Table.ExpandListColumn(Table.AddColumn(源, "自定义", each Text.ToList([内容])),"自定义")= Table.ExpandListColumn(Table.AddColumn(源, "拆分", each Text.ToList([签证内容简述])), "拆分")筛选合同号不为空。