Microsoft Power BI 教學：資料視覺化、Power Query、DAX 函數完整介紹

PowerBI 介紹

全名【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 Query (M)】整理資料、【Power Pivot (DAX函數) 】新增新的圖表欄位建立關聯性、【Power Map】製作地圖表

Power Query
整理&整併資料庫，將數個格式不同的資料庫快速整併成同一個、做資料清理(類似 Python pandas/pyspark)，方便用來分析

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

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

實際應用可以看網友的這篇，以前想學是覺得可以做這樣很酷
【Power BI 財務分析教學】Power BI 只要五步驟就可以做財務分析! 以Tesla特斯拉為例

試著做的，公開觀測站有資料，應該能更完整

PowerBI 基礎設置

有次要做文字雲，試著用家用版office登入，但因為不是公司email，無法登入，所以這裡只介紹免費版的

安裝
15 分鐘上手 Power BI！我一旦認真起來連我自己都會害怕 ~

預覽功能先打開，以後比較方便

Power Query 數據清理: 新增、刪除、轉換、連接、存儲

匯入資料

查看錯誤數量、資料內容
如果有error，鼠標移過去，可以remove error
右鍵keep error，可以細看錯誤點

查看詳細資料

按載入，進到power query，可以開始清理資料

上方公式區，只要點工具列操作，不需要自己打公式。操作複製、貼上、資料移動都類似 excel，能很快上手

資料清理完，按【關閉並套用】會到”報表檢視”視覺化的地方

PS 回到資料清理: 資料表檢視 => 編輯查詢

或是直接按上面【轉換資料】

PS 假設有已經在EXCEL分析好的檔案，要使用import – Power Query、Power Pivot、Power View

新增、刪除資料

重新命名資料

第一列設為標頭

可以保留、刪除前/後幾列、重複列

可以刪除特定行(特定行之外)

擷取 (也可以合併資料行)

可以擷取第一個-到第二個-

上方是: 新增資料行，做的操作都會自動新增到最後一欄，如果不要新增，上方要選【轉換】

資料分割

返回
旁邊套用的步驟，直接按【X】就可以返回了

也可以重新命名

轉換資料形式

轉換大小寫

轉換資料欄位Type，點選欄名旁邊的數字

上面power query(m) 會自動更改

資料取代

資料欄、列轉置

計算數值

統計資料

(我選中間值)

每行做計算

計數相異值
代表我有293個不一樣的產品

做其他的計算

提取年/月/日、時間

type更改地區

如果日期(因為地區)顯示錯誤

產生到今天的日期
用空白query產生日期
-會產生到今天的日期
-duration(days, hours, minutes, seconds)

=#date(2023,10,1)

= List.Dates(
    來源,
    Number.From(DateTime.LocalNow()) - Number.From(來源),
    #duration(1, 0, 0, 0)

下條件、索引

groupby

合併查詢，選取另一張表的相同欄位，加到原表

連接、帶入另一張表

附加查詢，可以帶入多張表列疊加

PowerBI 連接mysql

選擇MySQL資料庫

網頁會跳出 MYSQL Community Downloads Connector/NET 下載頁面

安裝

安裝好後整個程式關閉，再次連接
伺服器 127.0.0.1 (因為我用 mysql workbench)、你的database name

沒截到圖，左邊是要選【資料庫】，旁邊輸入自己資料庫的使用者+密碼

匯入後看來源

選擇table匯入後，可以關閉【啟用載入】
取消勾選 “啟用載入” 選項， Power BI 不會立即載入數據，可以對數據轉換操作，預覽數據變化後，再載入

Data Model

基礎觀念(Data Modeling 101)

實體（Entity）和屬性（Attribute）：實體是現實中的一個對象，例如顧客或訂單。屬性是描述實體的特徵，例如顧客的姓名、訂單的日期

關係（Relationship）：一個顧客可以擁有多個訂單，這種關係可以用來建立數據模型

主鍵（Primary Key）和外鍵（Foreign Key）：主鍵是一個能夠唯一識別實體的屬性。外鍵是一個表中的字段，它建立了與另一個表的關聯

規範化（Normalization）：個數據庫設計過程，旨在減少數據冗余和提高數據一致性

星型模型和雪花模型：星型模型和雪花模型是常見的數據倉庫設計模型，用於組織事實表和維度表的結構

OLAP（Online Analytical Processing）：OLAP 是一種用於多維分析的數據處理技術，通常用於支援數據模型中的複雜查詢

ETL（Extract, Transform, Load）：從不同數據來源提取數據、進行轉換、載入到目標數據庫中

primary key、foreing key

-新增關聯性

自動偵測

手動

連接好後，就可以回到視覺化開始拉圖表
(連接端如果是*，代表連過來的資料，不只出現一次，這裡是 Many-to-Many)

如果有多個，下面要選才讀的到資料

標準化：是一個數據庫設計原則，旨在減少數據冗余並提高數據的一致性，例如將數據結構化成多個表，以減少重複的信息
過濾流：是資料模型中的一個概念，將數據過濾或限制為特定的條件，以提高查詢和報表的效能
星型模式（Star Schema）：一種資料結構，一個中心的事實表和相連的維度表組成，結構形狀像一顆星星。用於簡單的報告需求，主要聚焦在單一主要事實表上
雪花模式（Snowflake Schema）: 是星型模式的一種變體，其中維度表進一步規範為包含次級維度表。用於大量維度屬性，需要更多正規化和層次化結構的情況

Power Pivot (DAX)

改格式、新增階層、跌代（iteration）、計算、Implicit Measures（隱式度量）v.s Explicit Measures（顯式度量）

改格式

時間

貨幣

新增階層

右建建立階層

其他項，可以右鍵新增進去

視覺化就能看到了(類似excel樞紐分析放兩項)

PS 時間序列要防止訊息過於密集，可以整理出YEAR, START OF YEAR, MOMTH, START OF MONTH, DATE 到階層，拉圖表時便會出現 ↑ ↓ ⇓ 可以查看

Query(M)和 Pivot(DAX) 是太相同概念，兩者皆類似EXCEL FUNCTION
M: 主要用於數據提取、轉換和載入（ETL）的任務
DAX: 只能對”數據”作用

新增資料行

跌代（iteration）、計算
:1234: 加上判斷條件，類似EXCEL IF

資料行 = IF(
'Sales Data'[OrderQuantity]>1,
"Multiple Items",
"Single Item"
)

1702549262167

計算總額

Total Quantity = SUM('Sales Data'[OrderQuantity])

基本計算

2+1
2-1
2*1
2/1
2^1

基本條件

大於等於小於不等於

[City]="Taipei"
[Quantity]>10
[Quantity]<10
[Unit Price]>=200
[Unit Price]<=200
[Country]<>"Mexico City"

&& 同 Python”==”

[Country]&""&[City]
([Country]="TW")&&([Quantity]>10)
([Country]="TW")||([City]="TP")
'Store Lookup'[City] IN ( "TP', "NTP, "TY")

SUM

Total Sales = SUM('Sales'[SalesAmount])

AVERAGE

Average Quantity = AVERAGE('Sales'[Quantity])

MAX

Highest Price = MAX('Products'[Price])

MIN

Lowest Temperature = MIN('Weather'[Temperature])

DIVIDE

Profit Margin = DIVIDE('Sales'[Profit], 'Sales'[Revenue], 0)

進階條件計算

計算行數

'Sales' 表中 'OrderID' 列中的非空值的數量
Number of Orders = COUNT('Sales'[OrderID])

 'Sales' 表中特定列（'ColumnName'）中的非空值的數量
Total Rows = COUNTA('Sales'[ColumnName])

'CustomerID' 列中的唯一客戶的數量
Unique Customers = DISTINCTCOUNT('Sales'[CustomerID])

Sales表中總行數
Total Rows in Table = COUNTROWS('Sales')

多條件判斷

=SWITCH (
    expression,
    value1, result1,
    value2, result2,
    ...,
    defaultResult
)

文字

文字數量
=LEN(A1)

文本連接
=CONCATENATE(A1, " ", B1)

大小寫
=UPPER(A1)
=LOWER(A1)


左/右 N 個字
=LEFT(A1, 5)
=RIGHT(A1, 3)

從指定位置開始的 N 個字
=MID(A1, 3, 4)

某些子字串替換為新的子字串
=SUBSTITUTE(A1, "old", "new")

查找，會返回子字串的起始位置
=SEARCH("find", A1)

日期時間

當前日期和時間
=NOW()

=YEAR(A1)
=MONTH(A1)
=WEEKDAY(A1)
=WEEKNUM(A1, 2)  -- 2 表示從星期一開始
=DAY(A1)

=HOUR(B1)
=MINUTE(B1)
=SECOND(B1)

=EOMONTH(A1, 0)  -- 0 表示當前月份
=DATEDIF(A1, B1, "Y")  -- 返回兩日期的年數差異

加上特定條件後

All Orders 將返回 'Sales Data' 表中所有訂單的總數，忽略任何可能存在的過濾器或上下文
All Orders = 
CALCULATE(
    [Total Orders],    -- 計算 [Total Orders] 的度量
    ALL(
        'Sales Data'   -- 移除 'Sales Data' 表上的所有過濾器
    )
)

過濾
=FILTER('Sales', 'Sales'[Quantity] > 10)

日期過濾
=CALCULATE([TotalSales], DATESYTD(Calendar[Date]))

值相加
=SUMX('Sales', 'Sales'[Quantity] * 'Sales'[UnitPrice])

計算
=CALCULATE([SalesAmount], 'Product'[Category] = "Electronics")

日期偏移 向前一個月
=CALCULATE([TotalSales], DATEADD(Calendar[Date], -1, MONTH))

指定日期區間的度量
=CALCULATE([TotalSales], DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -10, DAY))

Implicit Measures（隱式度量）v.s Explicit Measures（顯式度量）

Implicit Measures（隱式度量）: 是工具自動創建的度量，例如總和、計數等

Explicit Measures（顯式度量）: 我們自己創建自定義的計算字段，如年度增長率、加權平均等

量值 = AVERAGE('Sales Data'[OrderQuantity])

旁邊會自動出現

可以再拖進去

measure table

1. 新增MEASURE TEBLE資料夾
為何要設MEASURE TEBLE 資料夾? 為了放入【DAX計算後】的量值表，之後視覺化使用

新增資料表 (不會出現在Power Query轉換資料裡)

Measure Table (DAX) = {""}

也可以輸入資料 – 建立表格 (會出現在Power Query轉換資料裡)

再把前面量值的主資料表改成”measure table”，檔案就會自己跑到measure table下了

之後好整理可以設不同層資料夾

2. 新增計算的量值表

新增

新增的表都會出現在measure table 下

連接表格

在原本表，add new column

=RELATED('RelatedTable'[RelatedColumn])

資料視覺化

(分群)長/橫條圖、直方圖 (分佈 DISTRIBUTION)、圓形圖 (組成 COMPOSITION)、關聯圖 (RELATIONSHIP)、折線圖、地圖 (MAP)、柏拉圖 (Pareto)

(分群)長/橫條圖 – 比較 (COMPARISON): 用於比較資料的不連續性，如性別、品種等
其他可能的呈現方式：熱力圖、雷達圖、折線圖（搭配時間序列）、面積圖（搭配時間序列）
直方圖 (分佈 DISTRIBUTION):用於顯示資料的連續性，如年齡、金額等，x軸要有連續性
其他可能的呈現方式：密度圖、箱形圖、散點圖、熱力圖
圓形圖 (組成 COMPOSITION):用於顯示總額的百分比，展示各項類別的占比
關聯圖 (RELATIONSHIP): 用於顯示多個變數之間的相關性
其他可能的呈現方式：散點圖、氣泡圖、熱力圖、相關矩陣
折線圖: 用於顯示數據的趨勢變化，特別是在時間序列分析中
地圖 (MAP): 用於呈現數據的地理擴散，如銷售地點、人口密度等
通常與其他指標搭配使用，例如使用不同的顏色、大小（條圖/圓餅圖）表示數據的屬性
柏拉圖 (Pareto): 用於找到對企業貢獻80%的產品，並進行重要性分析