[學習筆記] [Office] Power BI: Power Query (M)、Power Pivot (DAX函數)、Power Map | 三大財報動態圖表實作－Cata Viva 拉丁樂

什麼是 Power BI?

全名【Business Intelligence】，是微軟推出的資料視覺化應用，快速建立互動式的視覺圖表，應用在行銷、商業、市調、地理圖都很適合

通常整理數據時，會先用 EXCEL 的進階功能 、(COM 增益集) 將數據整理好，再進 Power BI Desktop 做圖

先把數據資料整理好、格式化為表格，再作分析圖，只要表格內容有更新，圖片上資料也會自動更新

因此下面的筆記會包含上面提到的進階功能

裡面有許多功能，主要為 Power Query、 Power Query M、Power Pivot (DAX函數)、Power Map

PS 你可能也聽過 Power View，Power View 是 Power BI Desktop 的前身，2021年已經移除

為什麼我要學?

看看這篇【Power BI 財務分析教學】Power BI 只要五步驟就可以做財務分析! 以Tesla特斯拉為例，作者只動了幾個手指，甚至不需要寫程式碼，好看實用的報表就出來了 (好心動)

如何入門?

我買過幾個線上課程，也可以看微軟官方的頻道【Microsoft Power BI】或是一些中文教學影片

ㄏ

市面上只有這款軟體嗎?

其實有很多，因為這是微軟出的，有excel基礎好上手，才選擇從這入門，這篇 PowerBI Vs Tableau Vs FineReport, 14大關鍵點，2022年最詳細比較!「選型必看」 滿清楚的，學完可以再學 Tableau、FineReport

PAPAYA 是神吧~ 什麼都會

與tableau不同的是，因為是微軟所出，資料匯入power bi後，可以先用【Power Query (M)】整理資料、【Power Pivot (DAX函數) 】新增新的圖表欄位建立關聯性、【Power Map】製作地圖表

詳細說明:

Power Query
整理&整併資料庫，將數個格式不同的資料庫快速整併成同一個，方便用來分析

Power Pivot
管理資料庫，重點功能"關聯式資料庫"讓我們能夠輕易的連結 "維度表" 及 "明細表" (Dimension sheet & fact sheet)，大幅增加excel能夠容納的資料筆數

Power map
如果資料庫中有地理資料，可以呈現在地圖上，而且可以自動產生很酷的解說動畫。(第一次看到是真的覺得滿酷的，但實用性老實說還好 :D

先來學學 EXCEL 進階功能【Power Query】

什麼是 Power Query?

Power Query 是能夠在EXCEL中，整合和轉換 EXCEL 多份表格資料的工具，例如每月報表，標頭需相同

PS 只能用欄操作，如果是列，使用轉換 - 表格 - 轉置，整理完表格再轉置回去

怎麼進入 Power Query?

(匯入檔案後，一定要先把所有資料建立成表格)

1. 資料 - 取得及轉換資料 - 從表格/範圍 (直接建立表格) (如果有合併儲存格先取消+跨欄置中)

之後會自動跳出 Power Query編輯器

左上角關閉並載入

2. 資料 - 取得資料 - 從檔案 - 從 EXCEL 活頁簿

之後會自動跳出 Power Query編輯器

右下角可選轉換資料 or 載入/載入至

3. 資料 - 取得資料 - 從檔案 - 從資料夾(多份檔案)

選有檔案的資料夾

右下角可選合併與轉換資料 or 合併並載入

左上角關閉並載入至(樞紐分析)

4. 資料 - 取得資料 - 從檔案 - 從文字/CSV、TXT、PDF

公開資訊觀測站、銀行報表都可以下載匯入

PDF 先用【Power Query編輯器】整理，會比較好使用

5. 資料 - 取得資料 - 從檔案 - 從Web、XML、Jason

從Web 不是網頁網址! 是【文字/CSV檔案】按右鍵複製網址

6. 資料 - 取得資料 - 從資料庫 - 從Access、SQL、Oracle、Azure

怎麼整理 Power Query 資料?

(Excel 資料 - 查詢與連線 or 資料 -取得與轉換資料 - 現有連線可以看所有匯入的報表，可以重新整理、載入)

(Excel 右邊的查詢與連線，按 右鍵 - 內容 可調多久更新一次)

管理資料行: 選擇資料行移除資料行　　

縮減資料列: 保留移除資料列　（不能水平篩選，可以上方選 轉換－轉置，整理完再轉置回來)

排序

轉換：使用第一個資料作為表頭

合併

參數

資料來源

新增查詢

先來學學 EXCEL 進階功能【Power Pivot】

什麼是 Power Pivot?

Power Pivot 是增強版的樞紐分析表，可以做不同標頭的資料分析

多個檔案標頭如果一樣，也可以先在 Power Query 把資料整理好，左上角關閉並載入至 ( 只建立連線 + 新增此資料至資料模型)，再來做資料模型的關聯與計算

PS 以前可能是用下拉式選單 or VLOOKUP 來處理各種收支報表，但資料量大不好使用，就可以用 Power Pivot

怎麼開啟 Power Pivot?

到 選項 - 增益集 - COM 增益集 - 執行 - 勾選 Power Pivot for Excel

怎麼匯入檔案到建立 Power Pivot?

(已在表格頁面) Power Pivot - 資料表加入至資料表模型

or Power Pivot - 管理點兩下進入 - 取得外部資料 - 從其他來源 - EXCEL檔案 (可以從其他來源，匯入多次標頭不同檔案)

or 多個檔案標頭如果一樣，Power Query 匯入時勾選 只建立連線 + 新增此資料至資料模型，再回到 Power Pivot - 管理 - 點兩下進入

怎麼在 Power Pivot 表格間建立關聯?

(匯入檔案後，一定要先把所有資料建立成表格)

在Power Pivot 中圖表檢視 - 拉關聯性 - 再回到 Power Pivot 主資料夾 插入樞紐分析表

PS Power Pivot 設計管理關聯性可以看建立過的關聯性

也可以回到插入來源的EXCEL檔案，表格設計 - 工具以樞紐分析表摘要 - 新增此資料至資料模型，就會出現能自動偵測的樞紐分析表了

先來學學 EXCEL 進階功能【Power Map】

什麼是 Power Map?

透過 Power Map，您可以在3D 地球或自訂地圖上繪製地理和時態資料、在一段時間內顯示

怎麼開啟 Power Map?

第一步: 到 選項 - 增益集 - COM 增益集 - 執行 - 勾選 Power Map for Excel

怎麼匯入檔案到建立 Power Map?

第一步: 建立地區銷售表 - 匯入Power Pivot - 回到EXCEL 插入 - 3D 地圖 - 新導覽

也可到【政府資訊開放平台】，可以取得大數據資料

【台灣地理區劃】確認區域名稱

如果地區不明顯，用 DAX函數 =CONCATENATE("台北市",'台北市'[鄉鎮市區])

Power Map 有什麼功能?

場景 - 場景選項 設置影片時長

地圖 - 地圖標籤 可以打開，看詳細區名

地圖 - 自訂區域

Power Map 如何在自己的圖片上放座標?

用小畫家的座標功能!

第一步: 拉個表格，設置好 X & Y 軸位置

第二步: EXCEL 插入3D地圖 - 左邊變更場景選項 - 地圖變更 - 自訂新的地圖 - 瀏覽圖片

第三步: 匯入後，欄位選 X & Y 軸

PS 學了這麼多~ EXCEL 有好幾種建立樞紐分析的方法

假如今天有每月銷售報表，主管要我們做出樞紐分析，可以怎麼做?

1. EXCEL - 插入 - 樞紐分析 - 從表格/範圍

2. EXCEL - 插入 - 樞紐分析 - 從外部資料源 - 選擇連線 - 瀏覽更多

(這是最簡單的，如果表格很簡單也只有一個，用1.2 就夠了)

PS 這裡其實可以插入 - 樞紐分析 - 從外部資料源 - ( 新增資料表 + 新增此資料至資料模型)，直接插入Power Pivot 的資料表模型，省去開啟表格文件再進 Power Pivot 的步驟

3. EXCEL - 資料 - 取得資料 (用 Power Query，上面介紹過的方法，多個檔案標頭如果一樣，整理好後 關閉並載入至樞紐分析。用 Power Query 優點是，資料很雜或很多月份時，可以先整理好資料再匯入)

4. EXCEL - 插入 - 樞紐分析 - 從資料模型 (用 Power Pivot，普通EXCEL文件這裡會無法選，需要先轉換過一次Power Pivot 表格)

Power Pivot - 資料表 - 加入至資料表模型 (已在表格頁面)

or Power Pivot - 管理點兩下進入 - 取得外部資料 - 從其他來源 - EXCEL檔案 (可以從其他來源，匯入多個標頭不同檔案)

or Power Query 匯入時勾選 只建立連線 + 新增此資料至資料模型，再回到 Power Pivot - 管理 - 點兩下進入 - 整理好後匯出成樞紐分析表。用 Power Pivot 優點是，整理資料時可以加入DAX函數

接著我們真正進入【Power BI Desktop】

如何安裝?

到這個網站【Microsoft Power BI Desktop】，下載安裝電腦版就可以了

全部功能都和 Excel Power Query、Power Pivot (DAX函數) 、Power Map 很像!

使用【Power BI Desktop - Power Query (M)】

怎麼即時更新 Power Query? 用Google雲端試算表可以即時更新

如果是EXCEL檔 .xlsx

第一步: .xlsx 檔案上傳谷哥雲端

第二步: Google sheet 開啟

第三步: 檔案 - 共用 - 發佈到網路 - 選 .csv 取得網址

第四步: 回到 Power BI，轉換資料 - 新來源 Web 貼上網址

如果是純文字檔 .txt

第一步: .txt 檔案上傳谷哥雲端

第二步: 開啟取得連結 - 複製連結

第三步: 回到 Power BI，轉換資料 - 新來源建立空白查詢貼上網址

第四步: 上方擷取起始索引 (32) 字元數 (33) 上面Text.Range(來源,32,33) 就是 Power Query M函數

第五步: (新增首碼) 把首碼串 https://drive.google.com/uc?export=&confirm=no_antivirus&id= 貼到上方格式新增首碼，得到轉換後網址

第五步: 回到 Power BI，轉換資料 - 新來源文件/CSV - 貼上轉換後網址

or 如果要進 EXCEL，EXCEL 檔案 - 開啟瀏覽 - 貼上轉換後網址

如果是純文字檔 .txt (2)

第一步: 直接把附檔名 .txt 改成 .csv

第二步: 檔案上傳谷哥雲端

第三步: Google sheet 開啟

第四步: 檔案共用 - 與他人共用 - 複製連結

第五步: 回到 Power BI，轉換資料 - 新來源建立空白查詢貼上網址

第六步: 上方擷取前幾個字元(83) = Text.Start(來源, 83) 就是 Power Query M函數

第七步: (新增尾碼) 把尾碼串 /export?format=xlsx 貼到上方格式新增尾碼，得到轉換後網址

第七步: 加入這串網址 = Excel.Workbook(Web.Contents("轉換後網址"), null, true)

怎用使用 Power Query(M) ?

會入資料後，上方有很多欄位可以操作，用來整理資料，右邊為記錄的步驟，可以按x返回

怎用使用 Power Query (M)?

Power Query M 是Power Query 整理資料時，上方的程式碼

大部分還是使用上方功能，但有時需要更改工作表設定，就需要使用到 M語言

如果有不同用份報表，可以點 Power Query 右側的【導覽】，更改上方的 M語言

簡單的輸入數值

let...in 用法 (一定要小寫)

例如:　= 來源{[Item="Q1業績總表",Kind="Sheet"]}[Data]

= 來源{[Item="Q2業績總表",Kind="Sheet"]}[Data]

也可設成清單　

進入Power BI Power Query －【轉換資料】導入報表

常用－參數－管理參數－新增參數－名稱取名為「月份」

回到報表，點 Power Query 右側的【導覽】，更改上方的 M語言

例如:　= 來源{[Item="Q1業績總表",Kind="Sheet"]}[Data]

= 來源{[Item=月份,Kind="Sheet"]}[Data]

也可設成下拉式清單

進入Power BI Power Query －【轉換資料】導入報表

常用－參數－管理參數－新增參數－名稱取名為「下拉月份」－建議值改成「下拉值清單」，全部清單名稱打上去

回到報表，點 Power Query 右側的【導覽】，更改上方的 M語言

例如:　= 來源{[Item="Q1業績總表",Kind="Sheet"]}[Data]

= 來源{[Item=下拉月份,Kind="Sheet"]}[Data]

也可設成自動化清單

進入Power BI Power Query －【轉換資料】導入報表

常用－參數－管理參數－新增參數1－目前的值，貼上報表 2022 業績總表的「路徑」

常用－參數－管理參數－新增參數2－目前的值 Q1業績總表

回到報表，點 Power Query 右側的【來源】，更改上方的 M語言

= Excel.Workbook(File.Contents(參數1), null, true)

回到報表，點 Power Query 右側的【導覽】，更改上方的 M語言

= Excel.Workbook(File.Contents(參數2), null, true)

*可以整個複製後，右鍵【建立函數】，快速複製到別的報表

兩份文件，可設成自動化清單

進入Power BI Power Query －【轉換資料】導入報表 應付貨款

常用－參數－管理參數－新增參數1(應付貨款)－建議值改成「下拉值清單」，全部清單名稱打上去 應收貨款應付貨款

回到報表 應付貨款 - 套用步驟【來源】- 設定進階 - 刪除路徑\ 後面 應付貨款.xlsx - 組件一(參數) 選參數1 - 新增組件二(文字) .xlsx

回到報表 應付貨款 - 套用步驟【導覽】- 刪除上方的M語言 Item="應付貨款"

參數1(應付貨款) 就能更改「下拉清單」不出錯

Power Query (M) 有甚麼功能?

設置日期提醒

進入Power BI Power Query －轉換資料 - 新增資料行 - 加入條件資料行 - 設置條件

類似EXCEL資料剖析

進入Power BI Power Query －轉換資料 - 轉換 - 分割資料行 - 依符號、字元數...

輸入表格內容

Power BI 不能直接輸入表格內容，需要使用【新增資料行】/【自訂資料行】來輸入內容，IF函數的概念

更改表格內容

Power BI 不能直接改表格大小寫數字，需要使用【文字資料行】-【格式】來改大小寫、新增首碼尾碼，IF函數的概念

PS 用【新增資料行】/【自訂資料行】，可以保留原資料，另外新增一欄

導出最大值最小值中間值標準差

進入Power BI Power Query －轉換資料 - 轉換 - 數字資料行 - 最大值最小值中間值標準差(毛利率)...

合併不同月份的表格

進入Power BI Power Query －常用 - 合併 - 附加查詢

合併同內容的兩份表格 (各部門詳細清單費用) (部門名稱 業務部- 費用名稱 銷售費用, 行政部-管理費用...)

進入Power BI Power Query －常用 - 合併 - 合併查詢 - 選擇兩份表格都有的部門名稱

新合併表格拉到最後面，展開所有

常用 - 轉換 - 分組依據 - 進階選擇兩份表格 - 下方選彙總金額加總金額

在兩份表格中，增加關聯性

實例

同份檔案中，有許多表格，要建立關聯

# 全部資料一定要先設成表格，資料 - 取得資料 - 插入空白查詢

# 輸入 = Excel.CurrentWorknook() 叫出所有表格

# 取消勾選【資料行名稱作為前置詞】來展開 table

# 關閉並載入至

# 載入結果，會出現一頁新的【查詢1】

# 如果我要連線【客戶】呢?

在【客戶】資料 - 從表格/範圍

# 會進到 Power Query，選取關閉並載入- 關閉並載入至

# 選擇只建立連線

#回到【查詢1】，資料 - 取得資料 - 結合查詢 - 合併

# 選取一樣的資料建立關聯

# 現在只要更改【客戶】，【查詢1】也會更改

一個外部資料夾，有許多不同檔案，要建立關聯

# 資料 - 取得資料 - 從檔案 - 從資料夾

# 選擇下方轉換資料

# 全部匯入 Power Query

# 刪除不要的欄位

# 選擇自訂資料行 - 輸入公式 = Excel.Workbook([cotent]) 把 Binary打開

# Binary打開後會出現table，這時就可以把 Binary 欄位刪除了

# 取消勾選【資料行名稱作為前置詞】來展開 table

# 展開後

# 刪除後方不要資料欄後，再展開下一層table

# 第一行設為標頭、移除column1 的日期欄名稱，整理後長這樣

# 最後關閉並載入，只要原始的 excel 有更動，新的 excel 內容也會更動

什麼是 Power Pivot (DAX)?

Power Pivot DAX 能夠把 Power Query 整理好的資料表，建立關聯和計算

在Power BI 中導入，DAX函數如同 EXCEL函數，可以編寫，呈現想要的計算。PS 只差在，DAX函數可以輸入值或其他資料表

什麼是 Power Pivot DAX?

全名 Data Analysis Expressions

* DAX 運算子參考

如果以普通 EXCEL 操作

EXCEL 建立表格後，以欄位相加

=SUM(表格名稱[金額])

同表格內要加 @

=COUNT([@科目代碼])

=DAY([@活動日期])

如果以 Power Pivot 操作

Power Pivot 中設 DAX函數，要加 :

總交易金額 :=SUM('表格名稱'[金額])

再建立樞紐分析表，會出現 DAX函數量值可選

#模型化 - 新增資料表

# 之後跳出資料表工具，可以開始輸入 DAX函數

# 或是新增資料行，輸入想要提取的資料公式

ALT + ENTER or SHIFT+ ENTER 換行

用逗號隔行

【//】【--】單行註解 (= VBA「*」)

首【/*】+ 尾【*/】多行註解

【{}】中括號 
【'別的表格'】帶入別的表格，前後要加' 例如: = {MAX('活動1'[Date])} (= EXCEL「!」)
 
【CALENDER】
 
【SUM】
 
【SUMX】篩選後加總其中一欄
PowerBI公式-SUMX 函数
PowerBI函数SUMX

 
 
【AVERAGEX】篩選後，算業務平均抽成乘數
 
【SELECTVALUE】直接提取另一張報表的業績乘數，沒有就顯示 ''1''
 
【FLITER】篩選某列中(下拉選單)想要的條件
 
【SELECTCOLUMNS】從ASSETS表中提取想要的行，取名叫"尾款"
基础手札丨创建表
 
【DIVIDE】除
 
 
【COUNTENATEX】文字接龍在同一行中
 
【CONCATENATEX】如果A客戶這格是空白，則回傳 '抽成表' 的編號
 
【COUNTROWS】VALUES= 移除重複值(空白值會呈現1) (DISTINCT=移除重複值, 忽略空白值) COUNTROWS 計算列數
 
【RELATED】帶入別張表格中欄位，取名叫「業績乘數」
 
【EXACT】檢查兩欄，相同為TRUE, 不同為FALSE
 
【CALCULATE】
理解 CALCULATE
 
【ENDOFYEAR】計算表中當年最後一天
【ENDOFMONTH】計算表中當月最後一天
【EOMONTH】= EOMONTH(ENDOFMONTH('日期表'[Date]),0) 計算表中，指定前後0個月的最後一天
例如，表中作後一天為6/25，此函數答案為6/30
 
【DATESYTD】目前年度到目前日期為止的日期組
【DATEDD】給指定日期移動指定的間隔 
例如, = DATEADD(  '日期表'[日期],-1,MONTH) ，得到上月的日期。可以搭配SUMX，算出上月金額
= DATEDD('日期表'[日期],-1,YEAR) 去年同期
= SAMEPERIODLASTYEAR('日期表'[日期])
 
【DATEBETWEEN】
計算到年底總收款
= CALCULATE([當期收款],DATESBETWEEN( '日期表'[Date],DATE(YEAR(FIRSTDATE('日期表'[Date])),1,1),LASTDATE('日期表'[Date]))
= CALCULATE([當期收款],DATESYTD('日期表'[Date]))
= TOTALYTD([當期收款],'日期表'[Date])
計算到去年同期
= CALCULATE([當期收款],SAMEPERIODLASTYEAR(DATESYTD('日期表'[Date])))
= CALCULATE([當期收款],DATESYTD(SAMEPERIODLASTYEAR('日期表'[Date]))) 
 
【DATEPERIOD】求上一季第一天和最後一天
= FIRSTDATE(DATESINPERIOD('日期表'[Date],MAX('日期表'[Date]),-1,QUARTER))
= LASTDATE(DATESINPERIOD('日期表'[Date],MAX('日期表'[Date]),-1,QUARTER))
 
【DATEIFF】求兩日期中間天數 *可改MONTH, QUARTER, YEAR)
= DATEIFF(DATE( , , , ),DATE( , , ),DAY)
 
 
【ADDCOLUMNS】提取想要的資料，成為新的列
 
【ROW】提取想要的資料，成為新的行