1.3 Power Query組件

在Power Query組件中，進行數(shù)據(jù)清洗和集成主要有四大組件作用，四大組件可以分工協(xié)作，幫助我們高效完成數(shù)據(jù)的集成和清洗。

（1）數(shù)據(jù)獲取組件

數(shù)據(jù)獲取組件是對接各種不同的數(shù)據(jù)來源，使用Power Query支持各個不同的業(yè)務數(shù)據(jù)或各類不同的在線API數(shù)據(jù)的獲取，目前數(shù)據(jù)非常多，Excel和Power BI提供的數(shù)據(jù)種類大概有100多種，包含文件夾、文本數(shù)據(jù)、XML數(shù)據(jù)和JSON數(shù)據(jù)，都可以通過數(shù)據(jù)獲取進行數(shù)據(jù)的導入。

（2）數(shù)據(jù)清洗組件

數(shù)據(jù)清洗組件主要負責獲取各類數(shù)據(jù)后，通過數(shù)據(jù)清洗組件進行數(shù)據(jù)的清洗和重構，保證RAW數(shù)據(jù)經過清洗和重構后變成相對比較可靠和有用的數(shù)據(jù)，這個過程是使用數(shù)據(jù)清洗組件完成的。

（3）數(shù)據(jù)合并組件

數(shù)據(jù)合并組件是為了完成多個不同數(shù)據(jù)來源數(shù)據(jù)內容的合并，通常來說，數(shù)據(jù)合并組件用來查詢和合并多個不同文件中的數(shù)據(jù)，通常來說，合并分為橫向合并和縱向合并兩種，橫向合并類似于數(shù)據(jù)庫中的外鍵合并，而縱向合并是基于相同的數(shù)據(jù)字段進行的合并。合并的最終目的是將所有的相關數(shù)據(jù)整合到一起。

（4）數(shù)據(jù)分享組件

數(shù)據(jù)分享組件是在Excel中進行數(shù)據(jù)訪問連接的分享，在Power BI中沒有相應的數(shù)據(jù)分享組件。在Excel中如果希望將目前的Power Query連接分享到其他同事Excel中進行數(shù)據(jù)訪問集成并且重復使用，這時Power Query數(shù)據(jù)分享組件將幫助我們完成數(shù)據(jù)連接分享功能。

在實際的應用過程中，數(shù)據(jù)集成組件是Power Query的必選組件。而其他的組件在實際應用過程中有不同的場景，具體有哪些組合場景呢？

（1）數(shù)據(jù)集成

我們需要實現(xiàn)多個不同數(shù)據(jù)來源的數(shù)據(jù)集成，為后期數(shù)據(jù)的使用提供標準數(shù)據(jù)。

（2）數(shù)據(jù)集成+數(shù)據(jù)清洗

我們需要對復雜數(shù)據(jù)進行集成和清洗的時候，除了通過數(shù)據(jù)集成方式將數(shù)據(jù)導入，根據(jù)其他的需求，還要將數(shù)據(jù)進行重構和重新定義，例如，我們導入了一些會員身份證號碼，需要基于身份證號碼進行出生年月日的提取，這時就需要進行數(shù)據(jù)清洗和集成。

（3）數(shù)據(jù)集成+數(shù)據(jù)清洗+數(shù)據(jù)合并

當然，也可能會有一些特殊的應用場景，例如，數(shù)據(jù)的內容包含在一個文件夾中，文件夾中包含了需要合并的1—12月的銷售業(yè)績。當我們進行數(shù)據(jù)集成后要完成必要的數(shù)據(jù)清洗，再進行多個數(shù)據(jù)表合并。

在Excel中開啟Power Query組件和在Power BI中開啟相應的組件方法略有不同，這里我們基于Excel和Power BI分享如何開啟相應的Power Query功能組件。

1. Excel的Power Query組件注冊與開啟

Excel從2010版本開始提供了Power Query組件進行數(shù)據(jù)的集成，不同的Excel版本使用Power Query的方法略有不同，以下版本的Excel 均可主動或被動支持Power Query。

■ Office 2010和Office 2013：以加載項方式實現(xiàn)Power Query組件。

■ Office 2016版本：默認集成Power Query組件。

■ Office 365版本：默認集成Power Query組件。

目前Office 2010和Office 2013版本的Power Query插件提供的功能和函數(shù)不再更新，如果需要使用最新的函數(shù)，需要將Excel更新到Office 2016或更新的版本。

Office 2010和Office 2013需要通過COM加載項的方式啟用Power Query加載項，如在圖1.4所示的對話框中選中Power Queryfor Excel復選框。

在Excel 2016 版本中Power Query不再以加載項的方式提供服務，而是集成在Excel的數(shù)據(jù)標簽中。在Excel 2016 版本中，Excel數(shù)據(jù)導入和Power Query功能都存在，但這兩個功能是完全不同的，如圖1.5所示，左邊框中內容為Excel的數(shù)據(jù)導入功能，即將外部數(shù)據(jù)導入Excel中；右邊框中為Power Query獲取外部數(shù)據(jù)功能，即通過Power Query建立外部查詢功能。

Excel數(shù)據(jù)導入與Power Query看起來功能相同，實際上差別很大，兩者的功能差別在哪里呢？

（1）支持數(shù)據(jù)來源類型

Excel獲取外部數(shù)據(jù)支持的數(shù)據(jù)源類型比較少，目前僅支持文本文件、SQL Server 及Web等類型數(shù)據(jù)。而建立查詢方式支持非常廣泛的數(shù)據(jù)源類型，包含文本文件、Excel文件、SQL Server、MySQL和網頁的API連接，都是標準支持的數(shù)據(jù)類型。

（2）導入數(shù)據(jù)處理

獲取外部數(shù)據(jù)方式僅僅支持將所有的數(shù)據(jù)導入Excel后再進行數(shù)據(jù)的處理，通過導入操作支持的數(shù)據(jù)量是有限的，目前僅僅支持1048576行數(shù)據(jù)。而建立查詢方式支持數(shù)據(jù)導入的方式相當靈活，我們可以將數(shù)據(jù)導入Excel中，也可以建立到數(shù)據(jù)源連接，選擇建立連接后的所有數(shù)據(jù)處理將沒有上限，幾千萬、上億行的數(shù)據(jù)處理都可以在Excel處理過程中得到支持。

（3）數(shù)據(jù)導入后操作與處理

通過獲取外部數(shù)據(jù)方式導入的數(shù)據(jù)只能在Excel中進行再處理，所有的數(shù)據(jù)將保存在Excel中。而通過查詢方式導入后的數(shù)據(jù)支持Power Query的M函數(shù)處理，處理完成后可以保存在Excel文件中，也可以為千萬級的數(shù)據(jù)進行建模操作。

2. Office 365 版本的Excel 支持

接下來我們看看Office 365 版本的Excel對Power Query的支持。在Office 365版本中已經完全替代了獲取外部數(shù)據(jù)，獲取外部數(shù)據(jù)的功能選項已經從數(shù)據(jù)標簽消失了，只有“獲取和轉換數(shù)據(jù)”組，如圖1.6所示。

Office各個版本的Power Query有什么不同呢？

（1）函數(shù)數(shù)量不同

目前各個版本中Excel 提供的Power Query函數(shù)都有所不同，函數(shù)功能和數(shù)量會隨著Office的更新而更新。

（2）智能提示功能

在Office 365版本中的Power Query提供了智能提示功能，而在非Office 365版本的Power Query中將不包含智能提示功能，圖1.7所示的界面為Office 365版本的Power Query啟用智能提示功能的效果，即依據(jù)輸入的字符提供智能提示。

1.3.1 Excel的Power Query組件

在Excel早期版本中，我們必須在COM組件加載后才能載入Power Query，而在Office 2016版本之后，默認就存在Power Query組件，從而幫助我們進行數(shù)據(jù)的導入和處理。通過快速導入命令可以實現(xiàn)常規(guī)類型的快速導入，包含但不僅限于：CSV 數(shù)據(jù)格式，Excel數(shù)據(jù)格式，WEB 數(shù)據(jù)格式。

圖1.8就是Excel中的快速導入命令，快速導入命令位于“數(shù)據(jù)”選項卡中，利用快速導入命令能夠實現(xiàn)常用的數(shù)據(jù)導入。

在實際的操作過程中，快速導入命令不能完成所有數(shù)據(jù)的導入操作，當需要對文件夾、MySQL數(shù)據(jù)庫等進行導入操作，這些在快速導入命令中是無法完成的，這時就需要通過標準的導入界面來實現(xiàn)數(shù)據(jù)的導入操作，而在Excel和Power BI的標準界面中進行的操作略有不同。

在Excel中的Power Query導入數(shù)據(jù)操作，導入數(shù)據(jù)窗口支持在獲取數(shù)據(jù)和Power Query編輯器窗口這兩個不同的入口進行數(shù)據(jù)導入，目前Excel支持如下數(shù)據(jù)內容和格式的導入。

1. 來自文件

■ 工作簿：支持Excel 工作簿數(shù)據(jù)導入。

■ CSV/TXT文件：支持CSV/TXT文本數(shù)據(jù)導入。

■ XML：支持XML格式數(shù)據(jù)導入。

■ JSON：支持JSON格式數(shù)據(jù)導入。

■ PDF：支持PDF格式數(shù)據(jù)導入。

■ 文件夾：支持文件夾內多個不同文件數(shù)據(jù)導入。

■ SharePoint：支持SharePoint服務器列表數(shù)據(jù)和內容數(shù)據(jù)導入。

2. 來自數(shù)據(jù)庫

■ SQL Server：支持來自SQL Server數(shù)據(jù)庫的數(shù)據(jù)導入。

■ MySQL：支持來自MySQL數(shù)據(jù)庫的數(shù)據(jù)導入。

■ Mongo DB：支持來自Mongo DB數(shù)據(jù)庫的數(shù)據(jù)導入。

■ Oracle：支持來自Oracle數(shù)據(jù)庫的數(shù)據(jù)導入。

3. 來自Azure

■ Azure Database：支持來自Azure數(shù)據(jù)庫的數(shù)據(jù)導入。

■ Azure HDinsight：支持來自HDinsight的數(shù)據(jù)導入。

4. 來自在線源

■ SharePoint在線服務：支持在線的SharePoint數(shù)據(jù)和列表獲取。

■ Dynamic 365：支持Dynamic 365數(shù)據(jù)獲取。

■ Sales Force：支持Sales Force數(shù)據(jù)獲取。

5. 來自其他來源

■ 空查詢：支持自定義的數(shù)據(jù)內容獲取。

■ Web：支持來自網站的數(shù)據(jù)內容提取。

■ OleDB：支持以OleDB 驅動連接的數(shù)據(jù)內容。

■ ODBC：支持以ODBC驅動方式連接的數(shù)據(jù)源。

Excel的Power Query支持導入的數(shù)據(jù)類型會隨著Office補丁的更新逐漸增加，圖1.9是當前Power Query導入數(shù)據(jù)能夠支持的數(shù)據(jù)類型。

1.3.2 Power BI的Power Query數(shù)據(jù)導入界面

Power BI中的各個Power組件已經被深度集成到Power BI中，不再作為像Excel一樣的獨立組件而存在。在Power BI的界面中為了快速導入這些常用的數(shù)據(jù)，也有相應的快速導入命令。Power BI的快速導入命令位于主頁面中，圖1.10就是Power BI的快速導入命令。

在Power BI的數(shù)據(jù)導入界面中，提供了更多類型的數(shù)據(jù)導入，支持常用的數(shù)據(jù)導入類型如下。

1. 文件類型

■ CSV/TXT文件：支持CSV/TXT 文本數(shù)據(jù)導入。

■ Excel文件：支持Excel 工作簿數(shù)據(jù)導入。

■ XML文件：支持XML格式數(shù)據(jù)導入。

■ JSON文件：支持JSON格式數(shù)據(jù)導入。

■ PDF文件：支持PDF格式數(shù)據(jù)導入。

■ 文件夾數(shù)據(jù)：支持文件夾內多個不同文件數(shù)據(jù)導入。

■ SharePoint：支持SharePoint數(shù)據(jù)內容獲取。

2. 數(shù)據(jù)庫

■ SQL Server：支持SQL Server 數(shù)據(jù)庫訪問。

■ MySQL：支持MySQL數(shù)據(jù)庫訪問。

■ Oracle：支持Oracle 數(shù)據(jù)庫訪問。

■ PostSQL：支持PostSQL 數(shù)據(jù)庫訪問。

3. Power Platform

■ Power BI數(shù)據(jù)集：支持Power BI數(shù)據(jù)集訪問。

■ Power BI數(shù)據(jù)流：支持Power BI數(shù)據(jù)流訪問。

■ Common Data Service：支持CDS數(shù)據(jù)訪問。

4. Azure 服務

■ Azure SQL數(shù)據(jù)庫服務：支持Azure SQL數(shù)據(jù)庫訪問。

■ Azure Blog存儲：支持Azure Blog數(shù)據(jù)存儲訪問。

■ Azure表存儲：支持Azure 表存儲的數(shù)據(jù)庫訪問。

■ Azure HDinsight：支持Azure HDinsight的數(shù)據(jù)庫訪問。

5. 聯(lián)機服務

■ SharePoint Online列表：支持SharePoint Online的列表訪問。

■ Exchange Online 服務：支持Exchange Online的數(shù)據(jù)內容訪問。

■ Dynamics 365：支持Dynamics 365數(shù)據(jù)訪問。

■ SalesForce：支持SalesForce數(shù)據(jù)訪問。

■ Zendesk：支持Zendesk數(shù)據(jù)訪問。

■ Zoho CRM：支持Zoho CRM數(shù)據(jù)訪問。

6. 其他系列服務

■ Web：支持網頁及網頁API訪問和獲取。

■ 空語句：支持自定義數(shù)據(jù)的訪問。

■ Python腳本：支持Python腳本的數(shù)據(jù)內容。

■ R語言腳本：支持R語言腳本的數(shù)據(jù)內容。