基于Excel2013的PowerQuery入門

2018年6月19日筆記

所有要進行操作的文件下載鏈接:https://pan.baidu.com/s/10VtUZw8G-Ly-r4VypntjiA 密碼: y5qu

下載成功后,整個文件夾如下圖所示。

文件夾圖示.png

0.Power Query與其他PowerBI系列組件的關系

獲取數據——>分析數據——>呈現數據

PowerQuery獲取和整理——>PowerPivot建模和分析——>PowerView交互式報表||PowerMap地圖可視化——>PowerBI在線版可視化儀表板發布和分享

關系圖.png

簡而言之:獲取——>分析——>呈現——>發布

1.數據導入Power Query并進行追加查詢

新建一個空的excel文件,在導航欄的POWER QUERY中選擇從文件——>從Excel

導入按鈕圖示.png

導入一店數據1.png

導入一店數據2.png

點擊加載,一店.xlsx這個文件的數據會被導入到工作薄查詢中。

工作簿查詢.png

用相同的方法加載下載文件中的二店.xlsx文件,結果圖示如下。

image.png

雙擊上圖中的一店,出現下圖所示界面。選擇刪除最前面幾行。

刪除前三行1.png

刪除前三行2.png

打開左邊查詢欄1.png

點擊上圖所示的按鈕,出現下圖所示界面。

打開左邊查詢欄2.png

追加查詢1.png

追加查詢2.png

關閉并上載1.png

點擊上面的按鈕,回到Excel界面, 出現下圖所示的工作簿查詢。

關閉并上載2.png

從上圖可以看出一店從6377行增加到10739行。2.數據的行列管理及篩選

刪除行

打開下載文件中的02-數據的行列管理及篩選.xlsx,出現如下圖所示。

刪除空行1.png

如下圖所示,選定要加載的區域,即A3:C25區域

刪除空行2.png

刪除空行3.png

點擊上圖箭頭所示處,出現下圖所示。

刪除空行4.png

刪除空行5.png

成功刪除空行.png

刪除間隔行

刪除間隔行1.png

設置數值如下圖所示,然后點擊確定。

刪除間隔行2.png

成功刪除間隔行.png

關閉并上載

關閉并上載1.png

設置數值如下圖所示,點擊下圖中的加載。

關閉并上載2.png

成功關閉并上載.png

填充行

如下圖所示,打開第二個表

加載到查詢編輯器1.png

加載到PowerQuery中如下圖所示

加載到查詢編輯器2.png

將第一行作為標題.png

成功將第一行作為標題.png

刪除間隔行1.png

刪除間隔行2.png

成功刪除最后一行.png

填充按鈕位置.png

成功填充.png

選擇導航欄中的開始中的關閉并加載至,出現下圖所示,填入現有工作表的你想填入的位置。

加載設置.png

成功填充并將成果加載至原有表.png

3.數據格式的轉換

打開下載文件中的03-數據格式的轉換.xlsx,如下圖所示。

打開文件圖示.png

加載數據至PowerQuery中.png

修整1.png

成功修整.png

拆分列1.png

拆分列2.png

成功拆分列.png

修整1.png

成功修整.png

合并的期間選擇多列的時候,要先選擇姓名.1那一列,再選擇姓名.2

合并列1.png

合并列2.png

成功合并.png

拆分列1.png

拆分列2.png

成功拆分.png

修整.png

成功修整.png

合并列1.png

合并列2.png

成功合并.png

首字母大寫按鈕位置.png

成功設置首字母大寫.png

轉成文本1.png

替換1.png

替換2.png

成功替換.png

可以將下圖與結果表進行對照

成功加載.png

4.數據的拆分合并提取

打開下載文件中的04-數據的拆分合并提取.xlsx,如下圖所示。

打開文件圖示.png

加載至查詢編輯器.png

修改數據類型為文本.png

成功修改數據類型.png

添加重復列.png

按字符數拆分列1.png

按字符數拆分列2.png

列重命名.png

成功列重命名.png

提取1.png

提取2.png

選定新產生的一列轉換數據類型為整數

轉化1.png

轉化2.png

轉化3.png

轉換4.png

轉換5.png

轉換6.png

轉換7.png

成功轉換.png

其他的各項步驟原理相同,省略。5.刪除重復項

在下載文件中打開05-刪除重復項.xlsx,如下圖所示。

刪除重復項1.png

加載數據到PowerQuery中.png

客戶首次購買分析

選定下單日期這一列,進行升序排序。

下單日期升序排序.png

選定客戶名稱這一列,進行刪除重復項

對客戶名稱刪除重復項.png

首次購買分析結果.png

客戶最大訂單分析

選定金額這一列,進行降序排序

金額降序排序.png

選定客戶名稱這一列,進行刪除重復項

image.png

客戶最大訂單分析結果.png

多次購買客戶分析

選定客戶名稱這一列,進行保留重復項

保留重復項按鈕位置.png

只有1次購買記錄的客戶會被刪除,多次購買記錄的客戶會被保留。

例如一個客戶有3次購買記錄,保留重復項后該客戶被保留3次購買記錄。

保留結果.png

6.刪除錯誤

打開下載文件中的06-刪除錯誤.xlsx,如下圖所示。

打開文件圖示.png

加載數據至查詢編輯器中.png

選定日期這一列,將數據類型改為整數。

image.png

刪除錯誤行.png

成功刪除.png

選擇導航欄開始中的關閉并上載至,并按照下圖所示設置。

關閉并上載至原有表.png

成功刪除錯誤行.png

7.轉置和反轉

打開下載文件中的07-轉置和反轉.xlsx,如下圖所示。

打開文件圖示.png

加載數據到PowerQuery中.png

成功加載結果.png

將標題作為第一行.png

轉置按鈕位置.png

轉置后結果.png

將第一行作為標題.png

取消自動更改類型.png

關閉并上載至原有表格.png

上載設置.png

轉置結果.png

如果上載位置有偏差,自己可以移動表格位置調整至上圖所示效果8.透視和逆透視

打開下載文件中的08-透視和逆透視.xlsx,如下圖所示

打開文件圖示.png

不要選中第一列,選中后面的列,然后點擊下圖所示的逆透視列。

逆透視1.png

成功逆透視結果.png

選擇關閉并上載至,在窗口中設置值如下圖所示。

上載1.png

上載2.png

出現上圖所示2月,3月的錯誤,要設置___單元格格式。

上載3.png

成功上載結果.png

透視——不要聚合

選擇不要聚合這個表,加載至PowerQuery中第一步操作如下。

透視1.png

值列為是否完成銷售額,點開高級選項,聚合值函數選擇不要聚合,最后點擊確定。

透視2.png

成功透視結果.png

加載至原有表.png

成功加載至原有表結果.png

9.分組依據

打開下載文件中的09-分組依據.xlsx,如下圖所示。

打開文件圖示.png

分組依據1.png

分組依據2.png

成功分組結果.png

10.添加列

打開下載文件中的10-添加列.xlsx,如下圖所示。

打開文件圖示.png

進行分組操作.png

逆序排序.png

添加索引列.png

自定義列按鈕位置.png

自定義列設置.png

成功添加自定義列.png

免責聲明:本文僅代表文章作者的個人觀點,與本站無關。其原創性、真實性以及文中陳述文字和內容未經本站證實,對本文以及其中全部或者部分內容文字的真實性、完整性和原創性本站不作任何保證或承諾,請讀者僅作參考,并自行核實相關內容。

http://www.uswqb.club/style/images/nopic.gif
分享
評論
首頁
高速公路之王电子游艺
兆新股份股票 青海11选5的台子 快乐赛车玩法 攒劲甘肃麻将论坛 股市权重股有哪些 上海天天彩选4今开奖 星悦浙江麻将官网下载安装 甘肃十一选五的走势 篮球比分直播网 微信股票交流群 云南星悦麻将app 内蒙古十一选五任选图 产业基金配资 微乐龙江棋牌 广西11选5玩法 湖北麻将怎么玩讲解