PowerBI商業(yè)數(shù)據(jù)分析-第二章

PowerBI商業(yè)數(shù)據(jù)分析第二章xxxPowerQuery數(shù)據(jù)準(zhǔn)備2.1認(rèn)識PowerQuery目錄CONTEXT2.2數(shù)據(jù)導(dǎo)入2.3數(shù)據(jù)清洗2.4PQ應(yīng)用-二維表轉(zhuǎn)一維表2.5認(rèn)識M語言2.6M語言應(yīng)用-文本移除和提取2.7分列技巧2.8批量匯總技巧PowerQuery是微軟的數(shù)據(jù)連接和數(shù)據(jù)準(zhǔn)備技術(shù)，使業(yè)務(wù)用戶能夠無縫訪問存儲在數(shù)百個數(shù)據(jù)源中的數(shù)據(jù)，并通過易于使用、引人入勝且無代碼的用戶體驗來對其進(jìn)行調(diào)整以適應(yīng)他們的需求。2.1認(rèn)識PowerQuery如果還沒有任何數(shù)據(jù)，在PowerBIDesktop中，點擊獲取數(shù)據(jù)，選擇相應(yīng)的數(shù)據(jù)格式導(dǎo)入后，就可以進(jìn)入PowerQuery編輯器；如果已經(jīng)導(dǎo)入數(shù)據(jù)，想再次進(jìn)入查看或者編輯，可以點擊上圖中的“轉(zhuǎn)換數(shù)據(jù)”進(jìn)入。2.1認(rèn)識PowerQueryPowerQuery入口2.1認(rèn)識PowerQueryPowerQuery界面2.2數(shù)據(jù)導(dǎo)入PowerQuery的強(qiáng)大數(shù)據(jù)處理功能，幾乎可以從任何來源、任何結(jié)構(gòu)、任何形式上獲取數(shù)據(jù)，具體可以連接的數(shù)據(jù)類型;不僅支持微軟自己的數(shù)據(jù)格式，比如Excel、SQLServer、Access等;還支持SAP、Oracle、MySQL、DB2等幾乎能見到的所有類型的數(shù)據(jù)格式。2.2數(shù)據(jù)導(dǎo)入從網(wǎng)頁抓取數(shù)據(jù)示例放映幻燈片查看操作步驟2.3數(shù)據(jù)清洗PowerQuery最常用的16個界面功能1.提升標(biāo)題2.更改數(shù)據(jù)類型方式一方式二3.刪除錯誤/空值右鍵刪除錯誤篩選-去掉null勾選4.刪除重復(fù)項右鍵刪除重復(fù)項5.填充Excel合并單元格，導(dǎo)入后變?yōu)閚ull5.填充6.合并列7.拆分列8.分組9.提取提取前兩個字符10.轉(zhuǎn)置10.轉(zhuǎn)置-將標(biāo)題作為第一行10.轉(zhuǎn)置11.行列操作12.逆透視列13.透視列13.透視列透視“屬性”列14.添加列14.1添加重復(fù)列14.2添加索引列14.3添加條件列14.3添加條件列14.4添加自定義列14.4添加自定義列14.5添加示例列14.5添加示例列15.追加查詢15.追加查詢16.合并查詢16.合并查詢16.合并查詢2.4PowerQuery應(yīng)用示例二維表轉(zhuǎn)一維表二維表什么是一維表、二維表？一維表一維表的每一列就是一個維度，列名就是該列值的共同屬性一維表的每一行就是一條獨立的記錄為什么要轉(zhuǎn)換為一維表？二維表更符合我們?nèi)粘５拈喿x習(xí)慣，信息更濃縮，適合展示結(jié)果，但作為源數(shù)據(jù)進(jìn)行數(shù)據(jù)分析時，一維表更適合。一維表的每一列是一個獨立的維度，列是數(shù)據(jù)分析的基礎(chǔ)，比如制作圖表時直接把字段拖入到某個屬性框中，以及后面將會學(xué)習(xí)的利用列與其他表建立關(guān)系；編寫DAX時直接使用列名等。如何轉(zhuǎn)換為一維表？主要是利用PowerQuery的逆透視功能根據(jù)不同的二維表結(jié)構(gòu)，靈活使用填充、轉(zhuǎn)置、合并列、分列等操作四種常見的二維表結(jié)構(gòu)：行列標(biāo)題均為單層的二維表行標(biāo)題有多層的二維表列標(biāo)題有多層的二維表行列標(biāo)題均有多層的二維表二維表轉(zhuǎn)一維表|1.行列標(biāo)題均為單層的二維表思路：1、選中標(biāo)題列；2、逆透視其他列。二維表轉(zhuǎn)一維表|1.行列標(biāo)題均為單層的二維表放映幻燈片查看操作步驟二維表轉(zhuǎn)一維表|2.行標(biāo)題有多層的二維表思路：1、向下填充行標(biāo)題列；2、選中多個行標(biāo)題列；3、逆透視其他列。二維表轉(zhuǎn)一維表|2.行標(biāo)題有多層的二維表放映幻燈片查看操作步驟二維表轉(zhuǎn)一維表|3.列標(biāo)題有多層的二維表思路：1、轉(zhuǎn)置表；2、向下填充；3，將第一行用作標(biāo)題；

4，選中標(biāo)題列；5、逆透視其他列。二維表轉(zhuǎn)一維表|3.列標(biāo)題有多層的二維表放映幻燈片查看操作步驟二維表轉(zhuǎn)一維表|4.行、列標(biāo)題均有多層的二維表思路：1、向下填充；2，合并列；3、轉(zhuǎn)置表；4、向下填充；5、將第一行用作標(biāo)題；

6，選中標(biāo)題列；7、逆透視其他列；8、拆分列。實際上是第2種和第3種二維表的結(jié)合二維表轉(zhuǎn)一維表|4.行、列標(biāo)題均有多層的二維表放映幻燈片查看操作步驟2.5認(rèn)識M語言PowerQuery的秘密都在這里2.5認(rèn)識M語言2.5認(rèn)識M語言為什么要學(xué)習(xí)M語言？

界面操作配合M函數(shù)，更加靈活、簡潔高效部分復(fù)雜操作必須借助M函數(shù)2.5認(rèn)識M語言M函數(shù)基本規(guī)范

M函數(shù)嚴(yán)格區(qū)分大小寫，每一個字母必須按函數(shù)規(guī)范書寫，第一個字母都是大寫表被稱為Table，每行的內(nèi)容是一個Record，每列的內(nèi)容是一個List行標(biāo)用大括號{}列標(biāo)用中括號[]示例：取表中第一行的內(nèi)容：=表{0}提取表中自定義列的內(nèi)容：=表[自定義]取表種第一行自定義列的內(nèi)容：=表{0}[自定義]2.5認(rèn)識M語言常用的M函數(shù)

聚合函數(shù)：求和：List.Sum()求最小值：List.Min()求最大值：List.Max()求平均值：List.Average()文本函數(shù)：求文本長度：Text.Length()去文本空格：Text.Trim()取前n個字符：Text.Start()取后n個字符：Text.End()移除文本：Text.Remove()提取文本：Text.Select()提取數(shù)據(jù)函數(shù)：從Excel表中提取數(shù)據(jù)：Excel.Workbook()從Csv/Txt中提取數(shù)據(jù)：Csv.Document()條件函數(shù)：Ifthenelse（相當(dāng)于Excel中的IF函數(shù))2.5認(rèn)識M語言從哪里查找和學(xué)習(xí)M語言官網(wǎng)學(xué)習(xí)：/zh-cn/powerquery-m/power-query-m-reference

2.6M語言應(yīng)用示例文本處理技巧Text.Remove和Text.Select2.6M語言應(yīng)用示例Text.Remove原始數(shù)據(jù)添加自定義列：Text.Remove([客戶],{"A".."Z"})移除英文字符、分離出中文語法：

Text.Remove(被處理的文本，移除的字符）2.6M語言應(yīng)用示例Text.Remove原始數(shù)據(jù)語法：

Text.Remove(被處理的文本，移除的字符）Text.Remove([客戶],{“一”..“龜”})，移除中文字符2.6M語言應(yīng)用示例Text.Remove原始數(shù)據(jù)語法：

Text.Remove(被處理的文本，移除的字符）Text.Remove([客戶{“A”..“z”,“一”..“龜”}），移除英文和中文字符2.6M語言應(yīng)用示例Text.Select原始數(shù)據(jù)語法：

Text.Select(被處理的文本，選擇的字符）Text.Select([客戶],{“0”..“9”})）提取數(shù)字，與Text.Remove效果一樣2.6M語言應(yīng)用示例Text.Remove和Text.Select總結(jié)根據(jù)實際情況選擇使用：Text.Remove/Text.Select代碼更加簡潔移除字符類型M語言移除數(shù)字Text.Remove([文本數(shù)據(jù)],{"0".."9"})移除大寫英文字符Text.Remove([文本數(shù)據(jù)],{"A".."Z"})移除小寫英文字符Text.Remove([文本數(shù)據(jù)],{"a".."z"})移除全部英文字符Text.Remove([文本數(shù)據(jù)],{"A".."z"})移除全部中文字符Text.Remove([文本數(shù)據(jù)],{"一".."龜"})提取字符類型M語言提取數(shù)字Text.Select([文本數(shù)據(jù)],{"0".."9"})提取大寫英文字符Text.Select([文本數(shù)據(jù)],{"A".."Z"})提取小寫英文字符Text.Select([文本數(shù)據(jù)],{"a".."z"})提取全部英文字符Text.Select([文本數(shù)據(jù)],{"A".."z"})提取全部中文字符Text.Select([文本數(shù)據(jù)],{"一".."龜"})2.7PowerQuery應(yīng)用數(shù)據(jù)分列技巧分列|按分割符分列|從數(shù)字到非數(shù)字分列|從小寫到大寫分列|拆分到行分列|多種分割符分列|多種分割符-利用M語言靈活分列直接添加步驟，輸入下述代碼即可。主要是使用Splitter.SplitTextByAnyDelimiter函數(shù)，第一個參數(shù)寫入所有分隔符的列表{"，","；","-","+","。"}2.8PowerQuery應(yīng)用批量匯總匯總多個sheet2.8PowerQuery應(yīng)用：批量匯總一個Excel表的多個sheet思路：

任意導(dǎo)入一個sheet；

刪除“源”之后的步驟；展開數(shù)據(jù)。匯總多個sheet2.8PowerQuery應(yīng)用：批量匯總一個Excel表的多個sheet思路：

任意導(dǎo)入一個sheet；

刪除“源”之后的步驟；展開數(shù)據(jù)。放映幻燈片查看操作步驟匯總多個工作簿2.8PowerQuery應(yīng)用：批量匯總多個工作簿思路：

選擇從文件夾匯總匯總多個sheet思路：

任意導(dǎo)入一個sheet；

刪除“源”之后的步驟；展開數(shù)據(jù)。放映幻燈片查看操作步驟2.8PowerQuery應(yīng)用：批量匯總多個工作簿匯總多個sheet思路：

任意導(dǎo)入一個sheet；

刪除“源”之后的步驟；展開數(shù)據(jù)。2.8PowerQuery應(yīng)用：批量匯總關(guān)于批量匯總的技巧：1、無論是匯總多個sheet還是多個工作簿，都可以使用文件夾的方式；2、匯總以后，文件夾中數(shù)據(jù)發(fā)生變動，或者放入新的文件，PowerBI點擊刷新即可；3、雖然可以點擊合并按鈕自動合并Excel，但建議使用Excel.Workbook函數(shù)來解析；4、不僅可以批量匯總