PowerQuery案例合并同一個(gè)文件夾下多個(gè)Excel工作簿文件及SQL轉(zhuǎn)置動(dòng)態(tài)查詢(xún)自動(dòng)刷新

1、去年一分一檔，今年一分一檔。換算快速統(tǒng)計(jì)兩家指標(biāo)。財(cái)務(wù)付款統(tǒng)計(jì)表與成本合同臺(tái)賬匹配合同編號(hào)let fx=(s)= let源=Web.BrowserContents( HYPERLINK /search/%22&s&%22.html /search/&s&.html),信息=List.Transform(List.Select(Text.Split(源,href=),eachText.Contains(_,torrent),(x)=&Text.Split(x, target)0),a=List.Transform( 信息,Web.BrowserContents), b=List.Transfor

2、m(a,(r)=Text.Split(List.Select(Text.Split(r,href=),eachText.Contains(_,thunder)0,)0) in b in fx文件夾源 =Table.AddColumn( Folder.Files(x),添加列,each Excel.Workbook(Content)直接兩個(gè)文件源。=Table.Combine(Excel.Workbook(File.Contents(C:UsersAdministratorDesktopa1.xls),null, true)Name= 第 2 頁(yè) Data,Excel.Workbook(File

3、.Contents(C:UsersAdministratorDesktopa2.xls), null, true)Name=第 2 頁(yè)Data)清單去空白行。=Table.TransformColumns( 源,表2.項(xiàng)目特征描述,each Text.Combine(List.Select(Text.Split(_,#(lf),(x)=Text.Clean(x),#(lf)”)A1-1 自定義序列。=Table.TransformColumns(Table.AddIndexColumn( 源，序號(hào)，1, 1),序號(hào),eachNumber.ToText(_,A1-0)連接極簡(jiǎn)方法。=Table.

4、NestedJoin( 源,品名，表 2,品名,表 2, JoinKind.LeftOuter)，再展開(kāi)=Table.AddColumn(源，自定義,each 表 2List.PositionOf( 表 2品名品名)數(shù)量)=Table.AddColumn(更改的類(lèi)型，規(guī)范名稱(chēng),each Table.SelectRows(清單規(guī)范，(a)=Text.Contains( 項(xiàng)目編碼,a項(xiàng)目編碼)Table 合并列。=Table.FromRecords(Table.TransformRows( 源表，each 年份=年份,合同號(hào)=合同 號(hào),合同名稱(chēng)=合同名稱(chēng),合同產(chǎn)值=#合同產(chǎn)值”,簽證匯總金額=簽證

5、匯總金額,變更金 額匯總=變更金額匯總,簽證占比=簽證占比,變更占比=變更占比,簽證分級(jí) =Text.From(簽證 10 萬(wàn)上)&-&Text.From( 簽證 5 至 10 萬(wàn))&-&Text.From(簽證 5 萬(wàn)下),簽證總份數(shù)=簽證總份數(shù),變更分級(jí)=Text.From(變更10萬(wàn)上)&-&Text.From( 變更5至10 萬(wàn))&-&Text.From(變更5萬(wàn)下),變更總份數(shù)=變更總份數(shù))提取 “OA 字符后 8 位。Text.Middle(#流程#(lf)狀態(tài),Text.PositionOf(# 流程#(lf)狀態(tài),a)+1,8)/區(qū)分大小寫(xiě)提取分隔符之間的文本。Text.Bet

6、weenDelimiters(合同號(hào), )判斷公司名中是否包含關(guān)鍵詞。Text.Contains(公司名，“銀行)Text.Contains(公司名/關(guān)鍵詞)保留包含“清單”或“結(jié)”的行。 Table.SelectRows(刪除的列，each Text.Contains( 合同名稱(chēng)清單)orText.Contains(合同名稱(chēng),結(jié))提取數(shù)字。添加自定義列”對(duì)話框，首先將默認(rèn)的列名“Custom,修改為“純文本”，接下來(lái)在下面的“自定義列公式”窗格輸入“ =Text.Trim(Text.Remove(DATA,0.9),): 檢查無(wú)誤之后單擊“確定”按鈕，很快就可以在新添加的自定義列顯示從“ D

7、ATA列提取出來(lái)的純文本內(nèi)容，按照同樣的方法繼續(xù)添加“純數(shù)值”、“不重復(fù)數(shù)值”兩個(gè)自定義列，公式分別為 “ =Text.Remove(Text.Trim(Text.Remove(DATA,A.z),),)和=Text.Combine(List.Distinct(Text.ToList(純數(shù)值)”,請(qǐng)注意后一個(gè)公式的“純數(shù)值必須與上一個(gè)自定義列的名稱(chēng)保持一致，否則會(huì)提示“ Error ”Text.Remove中使用的第二個(gè)參數(shù)A.z),移除所有大小寫(xiě)英文字母。Text.Remove(原始信息, 一.畬K),移除中文。Text.Remove(原始信息,0.9),移除數(shù)字。Text.Remove(原

8、始信息, .),移除英文字符以上：Text.Remove(自定義, .,0.9,A.z)Text.Remove(自定義,Text.ToList(Text.Remove( 自定義, 一.畬|),兩層 Remove保留純粹漢字Text.Select( 自定義, 一.畬T)。Text.Select 是Power BI Desktop12月版本更新中新增的函數(shù)，目前Excel中還沒(méi)有，不過(guò)大概應(yīng)該在一個(gè)月之內(nèi)會(huì)更新出來(lái)。用法與 Text.Remove類(lèi)似，但功能相反，區(qū)別就在 于一一你不要的我都要。remove是刪除第二參數(shù)里包含的字符，而 select是僅保留第二參數(shù)里包含的字符。2014.04.0

9、6 轉(zhuǎn)為日期。=Table.TransformColumnTypes( 篩選白行,指令單日期, type date, 自定義行，type date)(未成功)去掉動(dòng)態(tài)成本3級(jí)合同分解PQ開(kāi)始分組依據(jù)按二級(jí)科目分組、操作/所有行，即：=Table.Group(源,二級(jí)科目, 計(jì)數(shù), each _, type table),然后再次按二級(jí)科目分組。核對(duì)兩列數(shù)據(jù)差集信息(對(duì)比差異)。 (朱仕平教程2季33課，因少信息為空，左-右、右-左) let源=Excel.CurrentWorkbook()Name= 表 1Content,拆分 1 = Table.AddColumn( 源，第一列,each T

10、ext.Split( 數(shù)據(jù) 1,),拆分 2 = Table.AddColumn( 拆分 1,第二列,each Text.Split( 數(shù)據(jù) 2,),差異=Table.AddColumn( 拆分 2,差異列,each Text.Combine(List.Difference(第二列,第一列),)in差異利用js里面的正則表達(dá)式進(jìn)行字符處理。=TaMe.Add8UinCI!L事取片K，型acH 打(數(shù)據(jù))1比地?fù)?jù)-1般報(bào)映廿雷-1 1 1贈(zèng)12虻卻好2小洞2人工“中國(guó)1A你篁老就|附5盤(pán)口口是好一招?盍幾4幅廣；腓是怦科子EU后病鈍let；：J5 = txc&l, cur ra：nfworkiM

11、k f m) (cor tent j tf x*( x) - Web * Fa k t * a ver itr-s,* & x -Yk pott 1 */ A zfi 9 *7 13 docuuenit writ c f st r .fc re pia c c J parti /*);/1 cri* Mt* 藥信RC hi Idm 1 Uh i 1 dmX ”口上江f 那一=N.ble . ArldCalum C旅J搓H簫果Z 一亡H (匚*巖 inres&合并。查詢(xún) 1&S詢(xún) 2; list&list2 ,代替 Table.Combine ; List.Combine深化。=Excel.Cu

12、rrentWorkbook()Name= 表 1Content 負(fù)責(zé)人=呂靜況 周一 幫助。=#shared源。透視表、PQ均可用“連接”為源； SQL可以用PQ為源？Power Query/從其他源/從ODBC, PQ中可以在源編輯處修改SQL語(yǔ)句。拆分列并展開(kāi)。=Table.ExpandListColumn(Table.AddColumn( 源，自定義，each Text.ToList(內(nèi)容), 自定義)=Table.ExpandListColumn(Table.AddColumn( 源，拆分,each Text.ToList(簽證內(nèi)容簡(jiǎn)述), 拆分)篩選合同號(hào)不為空。=Table.Sel

13、ectRows(提升的標(biāo)題，each ( 合同號(hào) null)生成兩表(列)數(shù)據(jù)全部組合的極簡(jiǎn)方法。方法一：分別給每個(gè)表添加一列值相同字段，然后再用合并查詢(xún)的方法來(lái)完成方法二：直接在其中一個(gè)表(如“項(xiàng)目”)里添加自定義列，引用另一個(gè)表(如本例中的“部門(mén)”)，再把自定義列 的表展開(kāi)即可另=大王,小王 & List.TransformMany( 黑桃,紅心,梅花,方塊,(a)=A,2.9,10,J,Q,K,(x,y)=x&y)試一下：if 表2.單位工程=表1.單位工程then 添加標(biāo)識(shí)字段 else nullPower Query從文件夾，關(guān)鍵字模糊檢索、合并同一個(gè)文件夾下多個(gè)Excel工作簿文件

14、2017.01.30 ,成本部，王明金1、新建一個(gè)基于文件夾的查詢(xún)Power Query/從文件/從文件夾，瀏覽、找到文件夾;點(diǎn)編輯按鈕(不要直接點(diǎn)加載)2、編輯查詢(xún)刪除不需要的多余列，但不要?jiǎng)h除Content列否則2.2報(bào)錯(cuò)！；/添加列，【自定義列】，輸入“ Excel.Workbook(Content)洛歸自定義列添加列后如下圖所示:百度云同步用九匚呢一,舍并測(cè)試0*EWir rrwMFotrtfflMm1 iKh和1朋EU7打與制 gmRifiprdpinsry3/珥皿即期】k器FG細(xì)砌3cM邙國(guó)1肛/9 J ,第HeerE|ia值If一愉-flvry.Ei并卡Id.國(guó) Conumt 曙

15、 DM modiried|iS自較世1Binary2017/1/26 11:01756Table2Binary也胃注11:0223Table3BimjryTable4Binary20W 113 上3Table自定義列右側(cè)擴(kuò)展按鈕展開(kāi)，只選擇 data;它，圖 Content / 蝎 Date nwiftfied |f *23 自定義 叵、鵬目馱蝴列2, 擴(kuò)展 黑臺(tái) ItemI_ KindI Name/筑免靠運(yùn)庠I心前舞!列表可能不完駕力湊更冬踞謂再次點(diǎn)擊定義列右側(cè)擴(kuò)展按鈕展開(kāi)，選擇需要的列曲.臼1 Bir2 Bir3 Bir4 Bir5 Bir6 Sir7 Bir g Rir9 Bir10 B

16、irContent i Date racdifkd 1 嘮自定義 Qatm ，爐、搜索要持屣的更|*擴(kuò)展 聚合回選擇斫有劃I Coluntiil| 畫(huà) Column2|I J ColumnI s-m-.mi-mi-.i|，;匚 uiiTjColumns_| ColumnbI Coluimn? Column8 1 Columnz便用原始列名作為前夔加裁更多冢可3、關(guān)閉并上載二、又一轉(zhuǎn)置神器查詢(xún)（Power Query）2017.02.09 ,成本部，王明金1、源表（不用直接導(dǎo)入工作薄了，這樣無(wú)法使用SQL語(yǔ)句，用：從其他源/從ODBC）兩bi EWke從 CD3L機(jī)DD3L尋人皓近1再也前出HT

17、flJ司,方步:口匕，*此.也，r 詢(xún)-山匕 iU-Mi * !W:coq 3Pvk 1Mt? tT：, * ；*至 EfTttl（.: t - frwi丁潛”這里我們給出了通用版的連接字符串的規(guī)律：2007以上版連接字符串：“百度云同步盤(pán) 動(dòng)態(tài)成本成本部大數(shù)據(jù)-宏（實(shí)時(shí)更新）.xlsmmypath;dsn=dBASE Files 2003版連接字符串：Driver=Microsoft Excel Driver （*.xls）;DriverId=790;Dbq=C:MyExcel.xls;DefaultDir=c:mypath;我們直接拷貝百度方同步盤(pán)動(dòng)態(tài) 成本成本部大數(shù)據(jù)-宏（實(shí)時(shí)更新）.x

18、lsm;defaultdir=c:mypathSQL語(yǔ)句：SELECT * FROM （select咨詢(xún)合同號(hào),清單編制咨詢(xún)單位AS咨詢(xún)單位，清單as 分類(lèi),V as 核查， 合同編號(hào)，合同名稱(chēng)，供方名稱(chēng)from MC 合同大臺(tái)賬Where 清單編制咨詢(xún)單位Null AND 清單編制咨詢(xún)單 位- AND 清單編制咨詢(xún)單位/ AND 清單編制_咨詢(xún)單位NOT LIKE % 自 AND項(xiàng)目=二期一 UNIONALL select 咨詢(xún)合同,咨詢(xún)一單位,結(jié)算as 分類(lèi),V as核查，合同編號(hào)，合同名稱(chēng)，供方名稱(chēng)from MC合同大臺(tái)賬Where 咨詢(xún)_單位Null AND咨詢(xún)_單位-AND咨詢(xún)_單位

19、/ AND咨詢(xún)_單位NOTLIKE %自% AND項(xiàng)目=二期一 ）order by 合同名稱(chēng)，咨詢(xún)單位，咨詢(xún)合同號(hào)合同號(hào)供方合同類(lèi)別核查201701總包施工合同清單V201701總包施工合同結(jié)算V201702電梯安裝合同清單V201702電梯安裝合同巳口舁V201703消防專(zhuān)業(yè)分包合同2土管 巳口舁V201704景觀施工合同結(jié)算V201704精裝修專(zhuān)業(yè)分包合同清單V |201704精裝修專(zhuān)業(yè)分包合同幺士首 巳口舁V2、透視列FiA用第一行Hmei tj!守i7RCE反轉(zhuǎn)行證百罰二丈豐-亂m幸向*備帝隸唳列- 一囹幽e類(lèi)型膽皂生二y移熱.!.而星 L為福 ：蛔4力出庠小作匕位”森三三列fx ut

20、ile.anfdi,CCAHft-*tEL供王昌可二 1印片 iO#類(lèi)別3、上載點(diǎn)6月居一點(diǎn)供方含-三眼顆 一在府雷1口 0mg總目題:畬同河平220171M口包旃工營(yíng)口第耳*3【工。”。金電璃量*N同利4412&1702電崎窈其當(dāng)回隼茸4號(hào)2017103消防專(zhuān)業(yè)若爸_亞置WS201703景觀缸合同的72017M靖石厝寺土石清單TB(21794希蒙岷業(yè)一鋁茸寸透視列使用則*7中伯咨棕創(chuàng)建斬列,合同號(hào)供方合同清單結(jié)算201701總包施工合同V201702電梯安裝合同VV201703消防專(zhuān)業(yè)分包合同V|201704景觀施工合同V201704精裝修專(zhuān)業(yè)分包合同VV|MQ態(tài)參數(shù)查詢(xún)一例1、數(shù)據(jù)/自其他

21、來(lái)源/來(lái)自Micrsoft Query雙擊*,視圖/條件（圖一）：（圖一）將數(shù)據(jù)返回，參數(shù)/選單元格，簡(jiǎn)單修改 SQL語(yǔ)句（圖二、圖三）（圖二）戮瑞.0從下列單元格中獲取甄值：|=Sheetl!tB$L厘|回 元格值更理時(shí)自動(dòng)刷新期冒使用下列值國(guó)）屣總參教值的方式.使用下列字籽串提示輸入?yún)?shù)值0 -碇 |匚矍消|（圖三）可按同樣的步驟設(shè)置參數(shù)2注：1、模糊查詢(xún)，SQL語(yǔ)句中“二”改為“LIKE”，提供參數(shù)單元格填寫(xiě)“咐口,其他“與”參數(shù)條 件單元格可先填寫(xiě)為“ %。2、【計(jì)算選項(xiàng)】要改為“自動(dòng)”才會(huì)在單元格值更改時(shí)自動(dòng)刷新。書(shū)耳方央旦fitcF FiLcj3、口處也可這樣寫(xiě)（見(jiàn)下圖）：Exce

22、l加載項(xiàng)管理Excel造吸常握二附明詰言前張官定義功能區(qū)俁速訪問(wèn)工具欄Jbofe項(xiàng)信任中心加載1頁(yè)查看和售理Microsoft Office加費(fèi)甌名稱(chēng)江曲而用程應(yīng)門(mén)口就國(guó)Chinese Conversion AddinMirroioft Power Query for Excel非活動(dòng)應(yīng)用程序加戲項(xiàng)AlPConverter For OfficeKingsaft Ecel Macro Scanner AddinMicrosoft Actions Pane 31、中文簡(jiǎn)繁轉(zhuǎn)換工具，在“審閱”選項(xiàng)卡的“中文簡(jiǎn)繁轉(zhuǎn)換”組中選擇相應(yīng)的按鈕2、Microsoft Power Query 工具3、數(shù)據(jù)挖掘加

23、載:PowerQuery快速合并網(wǎng)頁(yè)Table多頁(yè)數(shù)據(jù)Excel powerqueryweb 網(wǎng)頁(yè)數(shù)據(jù)之含PowerQuerTUiJ 合并網(wǎng)頁(yè)除bk?多頁(yè)；【查詢(xún)】/編輯查詢(xún)/高級(jí)編輯器原有：Table 2let源=Wet. Pfese (Web.Contents http: fit luto. octi.lKne .net/form-1 -1. httfLtData2 -DE mOut. HYPERLINK /forum-1-1.html /forum-1-1.html修改：Table 2let 困亂() Ueb .Page (Web - Contents (http: / / club,

24、ercelhome. netorun-1- I page S titul )2) Data Jj 0ata2 = 11.5)inData2)let函數(shù)=(page) = Web.Page(Web.Contents( HYPERLINK /forum-1- /forum-1- & page &.html)2Data,Data2 = 1.5inData2A = （選輝所有列）網(wǎng)頁(yè)器用Web.Page(Web.Contents(Text.From(https:/soudaxue/querySpecialtyScore.html?&keyWord1=%E5%9C%9F%E6%9C%A8%E5%B7%A

25、5%E7%A8%8B&studentprovince=%E5%AE%89%E5%BE%BD&fstype=%E7%90%86%E7%A7%91&pa=List.Transform(1.5,(x)=Web.Page(Web.Contents( HYPERLINK /soudaxue/querySpecialtyScore.html?&keyWord1=%E5%9C%9F%E6%9C%A8%E5%B7%A5%E7%25 /soudaxue/querySpecialtyScore.html?&keyWord1=%E5%9C%9F%E6%9C%A8%E5%B7%A5%E7% A8%8B&student

26、province=%E5%AE%89%E5%BE%BD&fstype=%E7%90%86%E7%A7%91&page=&Text.From(x)=List.Transform(1.1469,(x)=Web.Page(Web.Contents( HYPERLINK /soudaxue/querySpecialtyScore.html?&studentprovince= /soudaxue/querySpecialtyScore.html?&studentprovince=安徽 &fstype=理科 &page=& Text.From(x) & &zytype=&argschtype= 普通本科

27、&schoolyear=&schoolpc= 本科批)自定義 1 = Table.Combine(源),篩選的行 =Table.SelectRows( 自定義 1, each (Source = Table),# 展開(kāi)的“ Data = Table.ExpandTableColumn(篩選的行 ，Data, 學(xué)校名稱(chēng),專(zhuān)業(yè)名稱(chēng),招生地區(qū),文理科, 年份，錄取批次，錄取平均分，Data. 學(xué)校名稱(chēng),Data.專(zhuān)業(yè)名稱(chēng),Data.招生地區(qū)，Data.文理科,Data.年份,Data. 錄取批次, Data. 錄取平均分),刪除的列=Table.RemoveColumns(# 展開(kāi)的Data ,Ca

28、ption, Sourc e, ClassName, Id)in刪除的列Power Query結(jié)合SQL語(yǔ)句使用Power Query 數(shù)據(jù)源來(lái)自SQL語(yǔ)句用：Power Query/從其他源/從ODBCX從。DBC數(shù)據(jù)溟名制DSN） heel Files,揖高級(jí)選溟 字符徘憑據(jù)屋性）何選）rlerivsr=fMicreieft Excel Driver f 4句.創(chuàng)出），:d fNw ri 1 口 dE：dbq -百度三一味母 前惠成型落立本部土雙層 5QL朝何4 二隔號(hào)，山震在大白銖.合同呂，K合向大日咻.合同名稱(chēng)，記陳月或證內(nèi)寄茴廷，。曲娛糧價(jià)以足岫a）as棱油價(jià) 1丈自賬簽研臺(tái)賬 證大

29、臺(tái)賬.合同號(hào)-附合同大自岷.合同編他AH） （200000） or核價(jià)”哈同五值電J） 200000） or （核價(jià)/合同產(chǎn)值0.3） order by 記賬月 大“為1叵血s EXJIX .Qu&ry （lrtMfrr=：-MlcraiCfft Fscel Prtvir （* 試腦成本國(guó)本部大翻據(jù)一專(zhuān)實(shí)時(shí)更新：-HK”代 君都胃危目.英證仁古司建.d夠也1蚪。日 as棣 手一心地 I隹由第or工匚el Filesfif 呻1.:電印=1卬;dED：t/度三用步整 A 1 Q三一二了 新瞞號(hào)JI喳證共原一合（刁號(hào)JI涪同式占寄 坪 二LTE*二岫用工汩 軍照火/三死 廉磐371 自力丸也，*d，機(jī)圮出于二d芻詢(xún)?cè)O(shè)司佳等，巴.我國(guó)囪號(hào)1 0012 0063 0Z-Z9V堞含陰 舊展含河莒膝卜虺 lean . 班萼art酣逑E新區(qū)sti - JJlijL i聚江牛舊三雕一劇立場(chǎng)一工UM.憑補(bǔ)的三劃