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

去年一分一档，今年一分一档。换算快速统计两家指标。财务付款统计表与成本合同台账匹配合同编号letfx=(s)=let源=Web.BrowserContents(),信息=List.Transform(List.Select(Text.Split(源,href=),eachText.Contains(_,torrent)),(x)=(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),添加列,eachExcel.Workbook([Content]))直接两个文件源。=Table.Combine({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.项目特征描述,eachText.Combine(List.Select(Text.Split(_,#(lf)),(x)=Text.Clean(x)),#(lf))})A1-1…自定义序列。=Table.TransformColumns(Table.AddIndexColumn(源,序号,1,1),{序号,eachNumber.ToText(_,A1-0)})连接极简方法。=Table.NestedJoin(源,{品名},表2,{品名},表2,JoinKind.LeftOuter)，再展开=Table.AddColumn(源,自定义,each表2{List.PositionOf(表2[品名],[品名])}[数量])=Table.AddColumn(更改的类型,规范名称,eachTable.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(删除的列,eachText.Contains([合同名称],清单)orText.Contains([合同名称],结))提取数字。添加自定义列”对话框，首先将默认的列名“Custom”修改为“纯文本”，接下来在下面的“自定义列公式”窗格输入“=Text.Trim(Text.Remove([DATA],{0..9}),)”，检查无误之后单击“确定”按钮，很快就可以在新添加的自定义列显示从“DATA”列提取出来的纯文本内容，按照同样的方法继续添加“纯数值”、“不重复数值”两个自定义列，公式分别为“=Text.Remove(Text.Trim(Text.Remove([DATA],{A..z}),),)”和“=Text.Combine(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是PowerBIDesktop12月版本更新中新增的函数，目前Excel中还没有，不过大概应该在一个月之内会更新出来。用法与Text.Remove类似，但功能相反，区别就在于——你不要的我都要。remove是删除第二参数里包含的字符，而select是仅保留第二参数里包含的字符。2014.04.06转为日期。=Table.TransformColumnTypes(筛选的行,{{指令单日期,typedate},{自定义行,typedate}})（未成功）去掉动态成本3级合同分解。PQ//开始//分组依据//按二级科目分组、操作/所有行，即：=Table.Group(源,{二级科目},{{计数,each_,typetable}}),然后再次按二级科目分组。核对两列数据差集信息（对比差异）。（朱仕平教程2季33课，因少信息为空，左-右、右-左）let源=Excel.CurrentWorkbook(){[Name=表1]}[Content],拆分1=Table.AddColumn(源,第一列,eachText.Split([数据1],,)),拆分2=Table.AddColumn(拆分1,第二列,eachText.Split([数据2],,)),差异=Table.AddColumn(拆分2,差异列,eachText.Combine(List.Difference([第二列],[第一列]),,))in差异利用js里面的正则表达式进行字符处理。&合并。查询1&查询2；list&list2，代替Table.Combine；List.Combine深化。==Excel.CurrentWorkbook(){[Name=表1]}[Content]{[负责人=吕静]}[周一]帮助。=#shared源。透视表、PQ均可用“连接”为源；SQL可以用PQ为源？PowerQuery/从其他源/从ODBC，PQ中可以在源编辑处修改SQL语句。拆分列并展开。=Table.ExpandListColumn(Table.AddColumn(源,自定义,eachText.ToList([内容])),自定义)=Table.ExpandListColumn(Table.AddColumn(源,拆分,eachText.ToList([签证内容简述])),拆分)筛选合同号不为空。=Table.SelectRows(提升的标题,each([合同号]null))生成两表（列）数据全部组合的极简方法。方法一：分别给每个表添加一列值相同字段，然后再用合并查询的方法来完成方法二：直接在其中一个表（如“项目”）里添加自定义列，引用另一个表（如本例中的“部门”），再把自定义列的表展开即可另={大王,小王}&List.TransformMany({黑桃,红心,梅花,方块},(a)={A,2..9,10,J,Q,K},(x,y)=x&y)试一下：if[表2.单位工程]=[表1.单位工程]then添加标识字段elsenullPowerQuery从文件夹，关键字模糊检索一、合并同一个文件夹下多个Excel工作簿文件2017.01.30，成本部，王明金1、新建一个基于文件夹的查询1.1、PowerQuery/从文件/从文件夹，浏览、找到文件夹；1.2、点编辑按钮（不要直接点加载）；2、编辑查询2.1、删除不需要的多余列,但不要删除[Content]列否则2.2报错！；2.2、/添加列，【自定义列】，输入“Excel.Workbook([Content])”；添加列后如下图所示：2.3、自定义列右侧扩展按钮展开，只选择data；2.4、再次点击定义列右侧扩展按钮展开，选择需要的列3、关闭并上载二、又一转置神器——查询（PowerQuery）2017.02.09，成本部，王明金1、源表（不用直接导入工作薄了，这样无法使用SQL语句，用：从其他源/从ODBC。）这里我们给出了通用版的连接字符串的规律：2007以上版连接字符串：“driver={MicrosoftExcelDriver(*.xls,*.xlsx,*.xlsm,*.xlsb)};driverid=1046;dbq=C:\D:\百度云同步盘\动态成本\@成本部大数据--宏（实时更新）.xlsm;defaultdir=c:\mypath;dsn=dBASEFiles”2003版连接字符串：Driver={MicrosoftExcelDriver(*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;我们直接拷贝driver={MicrosoftExcelDriver(*.xls,*.xlsx,*.xlsm,*.xlsb)};driverid=1046;dbq=D:\百度云同步盘\动态成本\@成本部大数据--宏（实时更新）.xlsm;defaultdir=c:\mypathSQL语句：SELECT*FROM(select咨询合同号,[清单编制_咨询单位]AS咨询单位,'清单'as分类,'√'as核查,合同编号,合同名称,供方名称from[MC合同大台账]Where[清单编制_咨询单位]NullAND[清单编制_咨询单位]'-'AND[清单编制_咨询单位]'/'AND[清单编制_咨询单位]NOTLIKE'%自%'AND项目='二期一'UNIONALLselect[咨询_合同],[咨询_单位],'结算'as分类,'√'as核查,合同编号,合同名称,供方名称from[MC合同大台账]Where[咨询_单位]NullAND[咨询_单位]'-'AND[咨询_单位]'/'AND[咨询_单位]NOTLIKE'%自%'AND项目='二期一')orderby合同名称,咨询单位,咨询合同号合同号供方合同类别核查[2017]01总包施工合同清单√[2017]01总包施工合同结算√[2017]02电梯安装合同清单√[2017]02电梯安装合同结算√[2017]03消防专业分包合同结算√[2017]04景观施工合同结算√[2017]04精装修专业分包合同清单√[2017]04精装修专业分包合同结算√2、透视列3、上载合同号供方合同清单结算[2017]01总包施工合同√√[2017]02电梯安装合同√√[2017]03