[Power BI] Data Cleansing #1


* 前言

在製作dashboard之前,首先要檢視手邊的資料,以便清楚下一步要怎麼針對這些資料做整理,這部份會影響到後續的建模順暢度和資料呈現的正確性。第一篇先介紹一般使用者做資料清洗時也需要注意的小地方。

* 資料整理目的 & 小技巧

1. 去蕪存菁
留下可用來分析的欄位,無用的欄位拿掉
(1) 可減少資料匯入時的記憶體用量
(2) 可以減少製作報表時的雜訊
(3) 撰寫DAX時不會看到太多無用的欄位


2. 了解每個欄位的資料狀況
可用來分析的欄位格式通常是數字或時間,有時候資料品質不是很好,會造成製作圖表或撰寫DAX時出現不必要的exception。此時可以利用Power Query建立「取代值」的步驟,或是針對源頭處理(建議)。


(1) 數字欄位
有時候在缺值的時候會出現NA或其他字串表示「沒有值」,這時候Power BI可能會判斷「單價」欄位的格式是字串,這樣就不能拿單價來做DAX計算。

品名 單價 幣別
芭樂 10 USD
草莓 14 USD
奇異果 12.5 USD
香蕉 NA NA



(2) 時間欄位
一樣也會有缺值的問題,也可能會有資料型式不一致的問題,會被判定為字串格式,這些也都會導致Power BI無法做時間序列的分析。

人員 打卡時間
Amy 2023-03-11 08:35:55
Brain 2023-03-11
Cindy 03-11-2023
Denny NA



(3) 字串欄位
針對字串欄位要注意大小寫的問題,尤其是當這個欄位是Dimension Table跟Fact Table的關聯欄位時,一定會遇到問題。因為 * 在建立關聯時,是不分大小寫的 * 。例如下方Dimension Table的客戶欄位,Power BI會將SONY和Sony視為同一個值。

客戶 辦公室
SONY 台北
Sony 日本
Apple 紐約
Samsung 首爾



3. 整理呈現時的格式
這個步驟需要會一點Power Query M Language,因為需要自定義欄位。這對於無法更動資料來源,或是IT人員來不及更動給你時,是很方便的技能。一樣針對三種常用的格式說明:


(1) 數字欄位
例如想呈現台幣:

[台幣] = If Value.Is([單價], Number.Type) Then [單價] * 30
        Else "NA"
品名 單價 幣別 台幣
芭樂 10 USD 300
草莓 14 USD 420
奇異果 12.5 USD 375
香蕉 NA NA NA



(2) 時間欄位
例如想呈現自定義月份:

[月份] = If Value.Is(Date.From([打卡時間]), type date) Then Date.Year([打卡時間]) & "/" & Date.Month([打卡時間])
         Then "NA"
人員 打卡時間 月份
Amy 2023-03-11 08:35:55 2023/03
Brain 2023-03-11 00:00:00 2023/03
Cindy 2023-03-11 07:55:12 2023/03
Denny NA NA



(3) 字串欄位
例如想呈現客戶-辦公室

[辦公室New] = [客戶] & "-" & [辦公室]
客戶 辦公室 辦公室New
SONY 台北 SONY-台北
Sony 日本 Sony-日本
Apple 紐約 Apple-紐約
Samsung 首爾 Samsung-首爾



4. 優化效能
提供一種簡單的方式,在Power Query中預先建立Group By Table


例如有以下Raw Data,欲分析各水果在9月的銷售額。以Power BI引擎VertiPaq的機制,它會iterate金額欄位的6筆資料。

品名 日期 金額
芭樂 2023/09/01 431
芭樂 2023/09/02 546
芭樂 2023/09/03 125
香蕉 2023/09/01 1313
香蕉 2023/09/02 324
香蕉 2023/09/03 3514



如果預先做Group By Table變成下表,就會在記憶體裡有一塊是紀錄Aggregate後的結果,這樣就只要iterate金額欄位的2筆資料即可。

品名 日期 金額
芭樂 2023/09 1102
香蕉 2023/09 5151


* 小結

本篇先介紹一些一般使用者也可以藉由Power Query做到的資料清洗技巧,下一篇介紹進階使用者或IT可以使用的技巧。

#Power BI #Power Query #Data Cleansing







你可能感興趣的文章

ASP.NET Core Web API 入門教學 - 創建第一支HelloWorld API

ASP.NET Core Web API 入門教學 - 創建第一支HelloWorld API

[工作日誌 2021.11.10] 第一次面試紀錄

[工作日誌 2021.11.10] 第一次面試紀錄

PHP會員管理系統 - 會員列表

PHP會員管理系統 - 會員列表






留言討論