Cleaning Investment Data with Power Query in Power BI

Dữ liệu thô từ Excel, Market API hay Bloomberg Feed thường chứa null values, date format không nhất quán và duplicate records. Bỏ qua bước này, mọi DAX measure và dashboard phía sau đều có thể cho kết quả sai.

Bài viết hướng dẫn toàn bộ quy trình — từ xử lý null, chuẩn hóa dữ liệu, merge nhiều nguồn, đến xây dựng data quality pipeline tự động cho môi trường Wealth Management.

Mục Lục

Tại sao Data Cleaning quan trọng trong Investment Analytics?
Xử lý Null Values và Missing Data
Chuẩn hóa Date Format
Loại bỏ Duplicate Records
Type Casting và Data Validation
Merge và Append từ nhiều nguồn
Conditional Columns và Business Rules
Xây dựng Data Quality Pipeline
Best Practices cho Investment Data
Kết luận

1. Tại Sao Data Cleaning Quan Trọng Trong Investment Analytics?

Một con số sai, một quyết định sai

Trong Financial domain, dữ liệu sai không chỉ là lỗi kỹ thuật — nó ảnh hưởng trực tiếp đến quyết định đầu tư.

Cleaning investment data with Power Query không phải bước tùy chọn. Đây là bước bắt buộc trước khi bất kỳ KPI nào được tính toán.

Các vấn đề dữ liệu phổ biến nhất

Dưới đây là những vấn đề thường gặp nhất trong Investment Data và hậu quả nếu không xử lý:

Vấn đề	Nguồn gốc	Hậu quả nếu không xử lý
Null / blank values	Excel nhập tay, API timeout	DAX measure trả về BLANK hoặc sai
Date format không nhất quán	Nhiều nguồn, nhiều locale	Time Intelligence tính sai kỳ
Duplicate transaction	File gửi lại, merge lỗi	AUM và return bị double-count
Sai kiểu dữ liệu	Power BI auto-detect type	Phép tính số học bị lỗi
Schema thay đổi	Bloomberg, báo cáo nội bộ	Query crash khi refresh
Mã chứng khoán không chuẩn	Nhiều hệ thống khác nhau	Join/Merge bị missing records

💡 Nguyên tắc: Garbage in, garbage out. Pipeline financial analytics chỉ đáng tin khi data đầu vào đã được validate và clean trước khi vào model.

2. Xử Lý Null Values và Missing Data

Null values xuất hiện từ đâu?

Null values trong investment data có nhiều dạng khác nhau.

Có thể là ô trống trong Excel, null từ API khi mã không có giao dịch, hoặc giá trị 0 được nhập thay cho null.

Cleaning investment data with Power Query cần xử lý từng trường hợp theo đúng business logic — không áp dụng một công thức chung cho tất cả.

Xử lý Null theo từng cột

// Xử lý null values theo business logic từng cột
let
    Source = /* your data source */,

    // 1. Thay null ở cột số bằng 0
    FillNumericNulls = Table.FillDown(
        Table.ReplaceValue(Source, null, 0,
            Replacer.ReplaceValue, {"Quantity", "CostPrice"}),
        {"MarketValue"}
    ),

    // 2. Xóa hẳn row nếu ClientID hoặc AssetCode là null
    // (đây là key fields — không thể impute)
    RemoveKeyNulls = Table.SelectRows(FillNumericNulls,
        each [ClientID] <> null and [ClientID] <> ""
          and [AssetCode] <> null and [AssetCode] <> ""),

    // 3. Thay null ở text field bằng giá trị mặc định
    FillTextNulls = Table.ReplaceValue(RemoveKeyNulls,
        null, "UNKNOWN",
        Replacer.ReplaceValue, {"Sector", "AssetClass"})
in
    FillTextNulls

Phát hiện và log null values trước khi clean

// Tạo null report trước khi clean
let
    Source = /* your data source */,
    Cols   = Table.ColumnNames(Source),

    NullReport = Table.FromList(
        List.Transform(Cols, (col) => [
            Column    = col,
            NullCount = Table.RowCount(
                Table.SelectRows(Source,
                    each Record.Field(_, col) = null)),
            TotalRows = Table.RowCount(Source),
            NullPct   = Number.Round(
                Table.RowCount(
                    Table.SelectRows(Source,
                        each Record.Field(_, col) = null))
                / Table.RowCount(Source) * 100, 2)
        ]),
        Splitter.SplitByNothing(),
        {"Column", "NullCount", "TotalRows", "NullPct"}
    ),
    Expanded = Table.ExpandRecordColumn(NullReport, "Column1",
        {"Column", "NullCount", "TotalRows", "NullPct"})
in
    Expanded

✅ Best practice: Luôn tạo NullReport trước khi clean. Biết được cột nào có null nhiều nhất giúp bạn quyết định đúng chiến lược: impute, remove hay flag.

3. Chuẩn Hóa Date Format

Vì sao date format là vấn đề thường gặp nhất?

Đây là vấn đề thực tế nhất khi cleaning investment data with Power Query tại Việt Nam.

Excel từ RM team thường có format dd/mm/yyyy. Bloomberg dùng yyyyMMdd. API trả về ISO 8601.

Nếu không chuẩn hóa, Time Intelligence trong DAX sẽ tính sai kỳ hoàn toàn.

Nhận diện và chuẩn hóa nhiều date format

// Hàm chuẩn hóa date từ nhiều format khác nhau
let
    ParseDate = (rawDate as any) as nullable date =>
    let
        Str    = if rawDate = null then null
                 else Text.From(rawDate),
        Result = if Str = null then null
                 // Format: dd/mm/yyyy (Excel VN)
                 else if Text.Length(Str) = 10
                      and Text.Middle(Str, 2, 1) = "/"
                 then Date.FromText(Str, [Format="dd/MM/yyyy"])
                 // Format: yyyyMMdd (Bloomberg)
                 else if Text.Length(Str) = 8
                      and Value.Is(Value.FromText(Str), type number)
                 then Date.FromText(
                     Text.Start(Str,4) & "-" &
                     Text.Middle(Str,4,2) & "-" &
                     Text.End(Str,2))
                 // Format: yyyy-MM-dd (ISO 8601 / API)
                 else if Text.Length(Str) = 10
                      and Text.Middle(Str, 4, 1) = "-"
                 then Date.FromText(Str, [Format="yyyy-MM-dd"])
                 else null
    in
        Result,

    Source     = /* your data source */,
    CleanDates = Table.TransformColumns(Source, {
        {"TradeDate",    each ParseDate(_), type date},
        {"SettleDate",   each ParseDate(_), type date},
        {"MaturityDate", each ParseDate(_), type date}
    })
in
    CleanDates

Validate date range hợp lệ

// Loại bỏ trades có ngày không hợp lệ
let
    Source     = /* your data source */,
    MinDate    = #date(2000, 1, 1),
    MaxDate    = Date.From(DateTime.LocalNow()),

    ValidDates = Table.SelectRows(Source,
        each [TradeDate] <> null
          and [TradeDate] >= MinDate
          and [TradeDate] <= MaxDate),

    FlagFuture = Table.AddColumn(ValidDates, "DateFlag",
        each if [TradeDate] > MaxDate      then "FUTURE_DATE"
             else if [TradeDate] < #date(2010,1,1) then "OLD_DATE"
             else "OK",
        type text)
in
    FlagFuture

4. Loại Bỏ Duplicate Records

Tại sao duplicate nguy hiểm với investment data?

Duplicate trong investment data là rủi ro nghiêm trọng.

Một giao dịch bị đếm hai lần sẽ làm sai AUM, return và tất cả KPI phụ thuộc.

Trước khi deduplicate, cần xác định đúng business key. Đây là bước nhiều DA bỏ qua, dẫn đến xóa nhầm records hợp lệ.

Deduplicate theo business key

// Deduplicate dựa trên business key thực sự
let
    Source      = /* your data source */,
    BusinessKey = {"ClientID", "AssetCode", "TradeDate", "TradeType"},

    // Giữ record mới nhất nếu có duplicate
    Sorted  = Table.Sort(Source,
        {{"LoadTimestamp", Order.Descending}}),
    Deduped = Table.Distinct(Sorted, BusinessKey)
in
    Deduped

Phát hiện duplicate trước khi xóa

// Tìm và hiển thị các duplicate để review
let
    Source      = /* your data source */,
    BusinessKey = {"ClientID", "AssetCode", "TradeDate", "TradeType"},

    Grouped    = Table.Group(Source, BusinessKey,
        {{"Count",   each Table.RowCount(_), Int64.Type},
         {"Records", each _, type table}}),

    Duplicates = Table.SelectRows(Grouped,
        each [Count] > 1),

    Expanded   = Table.ExpandTableColumn(Duplicates,
        "Records", Table.ColumnNames(Source))
in
    Expanded

5. Type Casting và Data Validation

Vì sao không nên tin vào auto-detect của Power BI?

Power BI auto-detect kiểu dữ liệu thường sai trong thực tế.

Số có dấu phẩy ngăn cách hàng nghìn bị đọc thành text. Mã chứng khoán như "007" bị convert thành số 7. Percentage bị đọc thành decimal.

Đây là core của cleaning investment data with Power Query — cần làm thủ công, không phó mặc cho Power BI.

Type casting toàn diện cho investment data

// Type casting chuẩn cho investment portfolio table
let
    Source = /* your data source */,

    CleanText = Table.TransformColumns(Source, {
        // Trim và chuẩn hóa uppercase cho mã CK
        {"AssetCode", each Text.Upper(Text.Trim(_)), type text},
        {"ClientID",  each Text.Trim(_),             type text},
        {"Currency",  each Text.Upper(Text.Trim(_)), type text},

        // Xử lý số có dấu phẩy: "1,234,567" → 1234567
        {"Quantity",  each
            if _ = null then null
            else Int64.From(
                Text.Replace(Text.Trim(Text.From(_)), ",", "")),
            Int64.Type},

        // Xử lý giá với nhiều format
        {"CostPrice", each
            if _ = null then null
            else Number.From(
                Text.Replace(Text.Trim(Text.From(_)), ",", "")),
            type number},

        // Percentage: "12.5%" → 0.125
        {"WeightPct", each
            if _ = null then null
            else if Text.EndsWith(Text.Trim(Text.From(_)), "%")
            then Number.From(
                Text.RemoveRange(Text.Trim(Text.From(_)),
                    Text.Length(Text.Trim(Text.From(_)))-1, 1)) / 100
            else Number.From(_),
            type number}
    })
in
    CleanText

⚠️ Lưu ý quan trọng: Mã chứng khoán như "007", "013" — luôn giữ type text. Mất số 0 đầu sẽ làm hỏng toàn bộ join với bảng reference.

6. Merge và Append từ Nhiều Nguồn

Append vs Merge — khi nào dùng cái nào?

Append dùng khi gộp các bảng có cùng cấu trúc — ví dụ portfolio từ nhiều RM.

Merge dùng khi join hai bảng khác nhau theo key — ví dụ portfolio với market price.

Nhầm hai khái niệm này là nguyên nhân phổ biến dẫn đến data bị sai sau khi combining.

Append: Gộp nhiều portfolio files

// Append portfolio từ nhiều RM — sau khi đã clean từng file
let
    RM_North = /* query: cleaned portfolio RM Bắc */,
    RM_South = /* query: cleaned portfolio RM Nam */,
    RM_HCM   = /* query: cleaned portfolio RM HCM */,

    AddTag_N = Table.AddColumn(RM_North, "RMRegion", each "North", type text),
    AddTag_S = Table.AddColumn(RM_South, "RMRegion", each "South", type text),
    AddTag_H = Table.AddColumn(RM_HCM,   "RMRegion", each "HCM",   type text),

    Combined = Table.Combine({AddTag_N, AddTag_S, AddTag_H})
in
    Combined

Merge: Join portfolio với market price

// Left Join: portfolio với bảng market price hiện tại
let
    Portfolio   = /* cleaned portfolio table */,
    MarketPrice = /* cleaned market price table */,

    Merged = Table.NestedJoin(
        Portfolio, {"AssetCode"},
        MarketPrice, {"Symbol"},
        "PriceData",
        JoinKind.LeftOuter
    ),

    Expanded = Table.ExpandTableColumn(Merged, "PriceData",
        {"ClosePrice", "PriceDate", "Change1D"},
        {"MarketPrice", "PriceDate", "Change1D"}),

    AddMktValue = Table.AddColumn(Expanded, "MarketValue",
        each if [MarketPrice] = null then null
             else [Quantity] * [MarketPrice],
        type number),

    FlagNullPrice = Table.AddColumn(AddMktValue, "PriceFlag",
        each if [MarketPrice] = null then "PRICE_MISSING" else "OK",
        type text)
in
    FlagNullPrice

7. Conditional Columns và Business Rules

Chuẩn hóa theo logic nghiệp vụ

Sau khi dữ liệu sạch về mặt kỹ thuật, cần áp dụng business rules.

Bước này đòi hỏi phối hợp với business stakeholder. Mỗi tổ chức có cách phân loại Asset Class và Risk Tier khác nhau.

Phân loại Asset Class theo business rule

// Chuẩn hóa Asset Class theo quy tắc nội bộ
let
    Source = /* your data source */,

    ClassifyAsset = Table.AddColumn(Source, "AssetClassStd",
        each
            let code = Text.Upper(Text.Trim([AssetCode]))
            in
            if Text.StartsWith(code, "VN") and
               Text.Length(code) = 3             then "Vietnam Equity"
            else if List.Contains(
               {"BND","TCB","VPB","MBB","CTG"}, code) then "Banking Equity"
            else if Text.Contains(code, "BOND") or
                    Text.Contains(code, "TCK")   then "Fixed Income"
            else if Text.Contains(code, "ETF")   then "ETF"
            else if [AssetClass] = "CASH"        then "Cash & Equiv"
            else "Other",
        type text),

    AddRiskTier = Table.AddColumn(ClassifyAsset, "RiskTier",
        each
            if [AssetClassStd] = "Cash & Equiv"      then 1
            else if [AssetClassStd] = "Fixed Income"  then 2
            else if [AssetClassStd] = "ETF"           then 3
            else if Text.Contains([AssetClassStd], "Equity") then 4
            else 5,
        Int64.Type)
in
    AddRiskTier

8. Xây Dựng Data Quality Pipeline

Tại sao cần một pipeline riêng cho data quality?

Cleaning investment data with Power Query ở mức production cần nhiều hơn một query đơn lẻ.

Bạn cần một pipeline có thể chạy tự động và tạo report về tình trạng dữ liệu sau mỗi lần refresh.

Master Cleaning Function — tái sử dụng cho nhiều nguồn

// Hàm clean chuẩn áp dụng cho mọi portfolio table
let
    CleanPortfolioTable = (rawTable as table) as table =>
    let
        // Step 1: Remove empty rows
        RemoveEmpty = Table.SelectRows(rawTable,
            each not List.IsEmpty(
                List.RemoveMatchingItems(
                    Record.FieldValues(_), {null, "", 0}))),

        // Step 2: Trim all text columns
        TrimText = Table.TransformColumns(RemoveEmpty,
            List.Transform(
                List.Select(Table.ColumnNames(RemoveEmpty),
                    each Table.Column(RemoveEmpty, _) is text),
                (col) => {col, Text.Trim})),

        // Step 3: Remove duplicate theo business key
        Deduped = Table.Distinct(TrimText,
            {"ClientID", "AssetCode", "TradeDate"}),

        // Step 4: Add data quality score per row
        TotalCols  = List.Count(Table.ColumnNames(Deduped)),
        AddDQScore = Table.AddColumn(Deduped, "_DQ_Score",
            each
                let
                    vals     = Record.FieldValues(_),
                    nonNulls = List.Count(
                        List.RemoveMatchingItems(vals, {null, ""})),
                    score    = Number.Round(nonNulls / TotalCols * 100, 1)
                in score,
            type number)
    in
        AddDQScore,

    CleanedPortfolio = CleanPortfolioTable(/* raw portfolio */)
in
    CleanedPortfolio

Data Quality Summary Report

// Tổng hợp kết quả cleaning thành report
let
    Cleaned = /* cleaned table */,
    Raw     = /* raw table */,

    QualityReport = #table(
        {"Metric", "Value", "Status"},
        {
            {"Total Raw Rows",
             Table.RowCount(Raw), "INFO"},

            {"Total Clean Rows",
             Table.RowCount(Cleaned), "INFO"},

            {"Rows Removed",
             Table.RowCount(Raw) - Table.RowCount(Cleaned),
             if Table.RowCount(Raw) - Table.RowCount(Cleaned)
                > Table.RowCount(Raw) * 0.05
             then "WARN" else "OK"},

            {"Avg DQ Score",
             Number.Round(List.Average(
                 Table.Column(Cleaned, "_DQ_Score")), 1),
             if List.Average(
                 Table.Column(Cleaned, "_DQ_Score")) >= 95
             then "PASS" else "WARN"},

            {"Price Missing",
             Table.RowCount(Table.SelectRows(Cleaned,
                 each [PriceFlag] = "PRICE_MISSING")),
             if Table.RowCount(Table.SelectRows(Cleaned,
                 each [PriceFlag] = "PRICE_MISSING")) = 0
             then "PASS" else "WARN"}
        })
in
    QualityReport

⚠️ Production tip: Load QualityReport vào Power BI như một table riêng. Tạo trang "Data Health" trong dashboard. Portfolio Manager sẽ thấy ngay tình trạng dữ liệu mỗi buổi sáng.

9. Best Practices cho Investment Data Cleaning

Thứ tự bước cleaning chuẩn

Sau nhiều lần triển khai trong môi trường Wealth Management, đây là thứ tự đúng:

Schema validation — kiểm tra cột tồn tại trước khi transform
Type casting — cast đúng kiểu ngay từ đầu
Null handling — xử lý null theo business logic từng cột
Date normalization — chuẩn hóa về type date thống nhất
Text standardization — trim, upper/lower, remove special chars
Deduplication — theo đúng business key
Business rule application — phân loại, flag, enrich
Quality scoring — gán DQ score và log kết quả

Bảng tóm tắt kỹ thuật thường dùng

Vấn đề	Hàm M Language	Ghi chú
Null numeric	Table.ReplaceValue(..., null, 0, ...)	Chỉ dùng khi 0 có nghĩa hợp lệ
Trim text	Text.Trim([Column])	Luôn làm trước khi join/match
Date parse	Date.FromText(_, [Format="..."])	Chỉ định format explicit
Dedup	Table.Distinct(table, keyColumns)	Dùng business key, không dùng all columns
Conditional column	Table.AddColumn(..., each if...then...else...)	Đặt tên cột rõ nghĩa
Schema check	List.Intersect({required}, Table.ColumnNames(t))	Luôn validate trước khi SelectColumns
Số có dấu phẩy	Text.Replace([Col], ",", "")	Làm trước Number.From()
Uppercase mã CK	Text.Upper(Text.Trim([AssetCode]))	Bắt buộc trước khi join với dim table

10. Kết Luận

Data cleaning là kỹ năng, không phải công việc một lần

Cleaning investment data with Power Query không phải công việc làm một lần rồi thôi.

Đây là pipeline liên tục. Nó cần được maintain và cải tiến theo sự thay đổi của data sources.

Một DA giỏi trong Financial domain phải đảm bảo data đầu vào đủ tin cậy. Chỉ khi đó, DAX và dashboard mới có giá trị thực sự.

Ba điều cốt lõi cần nhớ

Luôn validate schema trước khi transform. Một cột đổi tên có thể crash toàn bộ pipeline.
Luôn log kết quả cleaning vào QualityReport table. Bạn cần biết bao nhiêu rows bị remove và tại sao.
Luôn dùng business key khi dedup. Không bao giờ dùng row index hoặc auto-generated ID.

Bài tiếp theo trong series: Connecting Financial Data Sources in Power BI — kết nối Excel, Market API và Bloomberg Feed vào một pipeline tự động.

Tham khảo thêm: Microsoft Power Query M Reference — tài liệu chính thức đầy đủ các hàm M Language.

Về tác giả

Triều Đặng — Middle Data Analyst tại MCNA Technology School. Chuyên về financial data analytics, Power BI và automated reporting trong môi trường Wealth Management.

MCNA Technology School là đơn vị tiên phong đào tạo AI, Big Data, Business Intelligence, Power BI, Python, SQL tại Việt Nam. Đối tác đào tạo của 300+ doanh nghiệp bao gồm Viettel Global, Masan Group, Techcombank, VPBank, Daikin.

📍 30 Trung Liệt, Đống Đa, Hà Nội
📍 Liền kề 44B, TT2, Văn Quán, Hà Đông, Hà Nội
📍 The BIB Space, 50B Phan Tây Hồ, Cầu Kiệu, TPHCM