[Power Query Tutorial] Cách sử dụng Split Column

Chuẩn hóa dữ liệu là bước đầu tiên và quan trọng nhất đối với bất kỳ chuyên gia phân tích dữ liệu nào. Một trong những thách thức phổ biến nhất khi xử lý dữ liệu thô là các cột dữ liệu gộp – nơi nhiều thông tin độc lập bị dồn chung vào một ô duy nhất như họ và tên, số điện thoại kèm mã quốc gia, mã sản phẩm phức tạp hay các cột dữ liệu đa trị.

Để giải quyết vấn đề này một cách tự động, nhanh chóng và tối ưu nhất, Split Column trong Power Query chính là công cụ hàng đầu. Trong bài viết này, chúng ta sẽ cùng đi sâu tìm hiểu chi tiết về cơ chế hoạt động, các phương pháp chia cột từ cơ bản đến nâng cao, đi kèm ví dụ thực tế và khám phá cả mã nguồn M-Code đứng sau tính năng mạnh mẽ này.

Chức năng Split Column trong Power Query là gì?

Chức năng Split Column (Chia cột) trong Power Query cho phép người dùng tách một cột chứa dữ liệu dạng văn bản (Text) hoặc hỗn hợp thành nhiều cột riêng biệt (hoặc nhiều dòng riêng biệt) dựa trên các quy tắc xác định trước. Đây là một tính năng cực kỳ linh hoạt nằm trong tab Transform hoặc tab Add Column trên thanh công cụ của Power Query Editor.

Split Column by Delimiter

Đây là phương pháp thông dụng nhất trong thực tế. Bạn có thể chọn các ký tự phân cách có sẵn như khoảng trắng (Space), dấu phẩy (Comma), dấu chấm phẩy (Semicolon), dấu tab hoặc tự nhập ký tự tùy chỉnh (Custom Delimiter) như dấu gạch chéo (/), gạch ngang (-), …

Khi sử dụng phương pháp này, Power Query cung cấp các tùy chọn nâng cao vô cùng quan trọng:

• Split at (Vị trí tách):
  • Left-most delimiter: Chỉ tách tại ký tự phân cách xuất hiện đầu tiên từ phía bên trái chuỗi văn bản.
  • Right-most delimiter: Chỉ tách tại ký tự phân cách xuất hiện đầu tiên từ phía bên phải chuỗi văn bản ngược lại.
  • Each occurrence of the delimiter: Tách tại mọi vị trí xuất hiện của ký tự phân cách (tạo ra số cột nhiều nhất có thể).
• Advanced options (Tùy chọn nâng cao):
  • Split into (Tách thành): Bạn có thể chọn tách thành các cột mới (Columns) hoặc chuyển đổi trực tiếp thành các hàng mới (Rows). Tùy chọn tách thành hàng (Rows) cực kỳ hữu ích khi bạn muốn chuẩn hóa dữ liệu danh sách để đưa vào mô hình quan hệ (Data Model) mà không làm phình to số lượng cột.
  • Quote Character (Ký tự trích dẫn): Giúp xác định cách Power Query xử lý các ký tự phân cách nằm bên trong dấu ngoặc kép hoặc dấu nháy đơn để tránh chia nhầm dữ liệu gốc nằm trong cụm văn bản trích dẫn.

Split Column by Number of Characters

Phương pháp này giúp bạn chia cột dựa trên độ dài cố định của chuỗi ký tự mà không cần quan tâm đến ký tự phân cách bên trong. Bạn cần nhập số lượng ký tự mong muốn và chọn một trong ba cơ chế hoạt động:

• Once, as far left as possible: Chỉ tách một lần duy nhất từ phía bên trái với độ dài đã chọn.
• Once, as far right as possible: Chỉ tách một lần duy nhất từ phía bên phải ngược lại.
• Repeatedly: Tách lặp đi lặp lại liên tục cho đến hết chuỗi ký tự (mỗi cột mới sẽ có độ dài đúng bằng số ký tự bạn đã nhập).

Split Column by Positions

Phương pháp này chia cột dựa trên các vị trí số thứ tự ký tự (bắt đầu tính từ vị trí số 0). Các vị trí cách nhau bằng dấu phẩy. Ví dụ, nếu bạn nhập “0, 3”, Power Query sẽ cắt chuỗi tại vị trí bắt đầu và vị trí ký tự thứ 3.

Split Column by Case Transition

• By Lowercase to Uppercase: Tách cột tại vị trí có sự chuyển đổi từ chữ thường sang chữ viết hoa (ví dụ: “productName” sẽ thành “product” và “Name”).
• By Uppercase to Lowercase: Tách cột tại vị trí chuyển từ chữ viết hoa sang chữ viết thường (ví dụ: “ABCDef” thành “ABCD” và “ef”).

Split Column by Type Transition

• By Digit to Non-Digit: Tách cột khi gặp sự chuyển tiếp từ ký tự số sang ký tự chữ/ký hiệu (ví dụ: “123USD” thành “123” và “USD”).
• By Non-Digit to Digit: Tách cột khi gặp sự chuyển tiếp từ ký tự chữ/ký hiệu sang ký tự số (ví dụ: “USD123” thành “USD” và “123”).

Ví dụ minh họa thực tế cho chức năng Split Column

Bảng dữ liệu gốc ban đầu

Dữ liệu ban đầu gồm các cột dữ liệu thô thực tế như:

• FullName (Họ và tên chứa khoảng trắng)
• PhoneCode (Số điện thoại quốc gia dạng chuỗi số liên tục)
• IDNumber (Mã định danh chứa cả chữ và số có độ dài quy chuẩn)
• MixedCase (Dữ liệu viết hoa viết thường xen kẽ kiểu CamelCase)
• Acronym (Ký tự viết tắt dạng chữ hoa kết hợp chữ thường)
• AlphaNumeric (Mã hỗn hợp chữ – số – chữ)
• CodeWithNumber (Mã định danh phân tách chữ và số ở cuối)
• MultipleEmails (Danh sách email gộp chung ngăn cách bởi dấu phẩy)
• TagList (Danh mục phân tách bằng dấu gạch đứng)

Để bắt đầu thao tác chia cột, chúng ta chọn cột cần xử lý, sau đó vào tab Transform trên thanh công cụ và nhấp chọn tính năng Split Column.

Tách họ và tên bằng phương pháp Split Column by Delimiter

Chúng ta chọn cột FullName chứa các tên như “Nguyen Van An”, “Tran Thi Binh”, … Mỗi từ được ngăn cách bởi một khoảng trắng (Space).

Trường hợp A: Tách tại mọi vị trí xuất hiện khoảng trắng (Each occurrence)

• Trong hộp thoại thiết lập, chúng ta chọn ký tự phân cách là Space và tích chọn Each occurrence of the delimiter.

• Sau khi nhấn OK, cột FullName ban đầu đã được tách thành 3 cột riêng biệt gồm FullName.1, FullName.2, và FullName.3, tương ứng với Họ, Tên đệm và Tên của nhân sự.

Trường hợp B: Tách tại khoảng trắng ngoài cùng bên phải (Right-most delimiter)

Nếu mục tiêu của bạn chỉ là tách riêng phần “Tên” ra khỏi cụm “Họ và tên đệm”, bạn sẽ chọn tùy chọn Right-most delimiter trong hộp thoại thiết lập.

Kết quả thực tế cho thấy cột được chia làm 2 phần: FullName.1 chứa toàn bộ Họ và Tên đệm (ví dụ: “Nguyen Van”), và FullName.2 chứa phần Tên chính (ví dụ: “An”).

Trường hợp C: Tách tại khoảng trắng ngoài cùng bên trái (Left-most delimiter)

Ngược lại, nếu bạn muốn tách riêng phần “Họ” và giữ nguyên cụm “Tên đệm và Tên”, hãy sử dụng tùy chọn Left-most delimiter.

Khi đó, Power Query sẽ tách cột tại khoảng trắng đầu tiên bên trái. Kết quả là cột FullName.1.1 chứa Họ (ví dụ: “Nguyen”), cột FullName.1.2 chứa tên đệm (ví dụ: “Van”) và cột FullName.2 chứa phần còn lại (ví dụ: “An”).

Ví dụ 2: Tách số điện thoại bằng phương pháp Split Column by Number of Characters

Bây giờ chúng ta sẽ làm việc với cột số điện thoại PhoneCode chứa các chuỗi số dài liên tục. Để mở tính năng này, từ menu thả xuống của Split Column, bạn chọn By Number of Characters.

Trường hợp A: Chia nhỏ lặp đi lặp lại liên tục (Repeatedly)

Giả sử chúng ta muốn chia chuỗi số điện thoại thành các cặp gồm 2 chữ số một. Trong hộp thoại thiết lập, nhập số 2 vào phần Number of characters và chọn tùy chọn Repeatedly như minh họa.

Kết quả sau khi thực hiện là chuỗi số điện thoại ban đầu đã được cắt nhỏ thành một loạt cột mới (PhoneCode.1, PhoneCode.2, PhoneCode.3…) với độ dài đúng 2 chữ số mỗi cột.

Trường hợp B: Tách một lần duy nhất từ ngoài cùng bên trái (Once, as far left as possible)

Nếu chúng ta muốn tách 2 chữ số mã quốc gia đầu tiên từ phía bên trái ra khỏi chuỗi số điện thoại còn lại, hãy nhập số 2 vào phần Number of characters và chọn tùy chọn Once, as far left as possible trong hộp thoại thiết lập.

Kết quả đạt được hiển thị: cột PhoneCode ban đầu tách thành PhoneCode.1 chỉ chứa 2 ký tự đầu tiên bên trái (“84”), và cột PhoneCode.2 chứa toàn bộ dãy số phía sau (“901234567”).

Trường hợp C: Tách một lần duy nhất từ ngoài cùng bên phải (Once, as far right as possible)

Ngược lại, nếu bạn muốn lấy ra 2 số cuối cùng ở đuôi số điện thoại, hãy chọn tùy chọn Once, as far right as possible.

Kết quả thực tế hiển thị cho thấy cột đã được chia làm 2 phần: PhoneCode.1 chứa phần đầu số dài, và PhoneCode.2 chứa đúng 2 ký tự cuối cùng bên phải (“67”).

Ví dụ 3: Chia cột dựa trên vị trí Index cố định (Split Column by Positions)

Phương pháp này cực kỳ hữu ích khi bạn xử lý các mã định danh có cấu trúc độ dài quy chuẩn cố định. Trong menu của Split Column, bạn chọn mục By Positions.

Chúng ta tiến hành thử nghiệm trên cột IDNumber (ví dụ chuỗi: “HCMAB12345”). Chúng ta muốn tách 3 ký tự đầu đại diện cho chi nhánh tỉnh thành (HCM) ra khỏi phần mã số phía sau. Trong hộp thoại thiết lập, chúng ta nhập vị trí là 0, 3.

Nhấn OK, cột IDNumber ngay lập tức được chia thành 2 cột: IDNumber.1 chứa mã tỉnh thành “HCM” và IDNumber.2 chứa phần mã số “AB12345”.

Ví dụ 4: Chia cột dựa trên sự chuyển đổi chữ viết Hoa/Thường

Trường hợp dữ liệu tiếp theo là xử lý các chuỗi viết liền không có ký tự phân cách nhưng có sự thay đổi định dạng chữ viết Hoa và viết Thường.

Trường hợp A: Tách từ chữ thường sang chữ viết hoa (By Lowercase to Uppercase)

Để xử lý chuỗi viết liền dạng CamelCase như “FirstNameLastName” trên cột MixedCase, bạn chọn tính năng By Lowercase to Uppercase trong danh sách.

Sau khi áp dụng, Power Query tự động phát hiện mọi vị trí có chữ thường đứng trước chữ viết hoa để thực hiện cắt cột. Kết quả là chuỗi “FirstNameLastName” được tách sạch sẽ thành các cột độc lập chứa “First”, “Name”, “Last”, “Name” như minh họa.

Trường hợp B: Tách từ chữ viết hoa sang chữ thường (By Uppercase to Lowercase)

Tương tự, chúng ta có cột Acronym chứa dữ liệu dạng “ABCDef”. Để tách phần viết tắt chữ hoa ra khỏi cụm chữ thường phía sau, chúng ta chọn tính năng By Uppercase to Lowercase trong menu thả xuống.

Kết quả hiển thị: Cột Acronym được tách thành Acronym.1 chứa toàn bộ phần chữ hoa đầu tiên (“ABCD”) và Acronym.2 chứa phần chữ thường phía sau (“ef”).

Ví dụ 5: Tách cột theo sự chuyển đổi định dạng ký tự (Type Transition)

Tính năng chuyển đổi định dạng giúp dọn dẹp các mã định danh pha trộn giữa chữ và số một cách thông minh mà không cần đếm số lượng ký tự thủ công.

Trường hợp A: Tách từ số sang chữ (By Digit to Non-Digit)

Chúng ta có cột AlphaNumeric chứa chuỗi dạng “ABC123XYZ456”. Để tách khi gặp điểm giao thoa từ số sang chữ, chúng ta chọn cột này và nhấp chọn tính năng By Digit to Non-Digit từ menu.

Kết quả hiển thị: Power Query phát hiện vị trí chuyển tiếp từ số “3” sang chữ “X”, tách cột thành AlphaNumeric.1 (“ABC123”) và AlphaNumeric.2 (“XYZ456”).

Trường hợp B: Tách từ chữ sang số (By Non-Digit to Digit)

Áp dụng trên cột CodeWithNumber chứa chuỗi dạng “Item001”. Để tách phần tên chữ ra khỏi phần mã số định danh ở đuôi, chúng ta nhấp chọn tính năng By Non-Digit to Digit.

Kết quả hiển thị: Cột được chia làm 2 phần gồm CodeWithNumber.1 chứa chữ (“Item”) và CodeWithNumber.2 chứa số (“001”).

Ví dụ 6: Kỹ thuật tách cột nâng cao thành dòng (Split Column into Rows)

Thông thường, việc chia cột sẽ tạo ra thêm các cột mới nằm ngang. Tuy nhiên, trong mô hình hóa dữ liệu (Data Modeling), việc phình to cột làm giảm hiệu suất bảng. Khi đó, chia dữ liệu thành các dòng (Rows) là giải pháp tối ưu nhất.

Chúng ta thực hiện trên cột MultipleEmails chứa danh sách email gộp chung ngăn cách bằng dấu phẩy. Chúng ta chọn tính năng chia cột By Delimiter.

Trong hộp thoại thiết lập, ta thực hiện các bước sau:

• Tại mục Select or enter delimiter, chọn dấu phẩy (,).
• Mở phần Advanced options (Tùy chọn nâng cao).
• Tại mục Split into, tích chọn Rows thay vì Columns mặc định.

Sau khi nhấn OK, kết quả đạt được hiển thị như sau: Từ bảng dữ liệu gốc chỉ có 8 dòng, Power Query đã tự động tách các địa chỉ email gộp và trải phẳng bảng dữ liệu thành 21 dòng riêng biệt. Mỗi dòng đại diện cho một địa chỉ email duy nhất, trong khi các thông tin ở các cột khác của nhân sự đó được sao chép tương ứng một cách chuẩn xác.

Mã M đằng sau chức năng Split Column

Một trong những yếu tố giúp bạn làm chủ Power Query là hiểu rõ ngôn ngữ công thức M (M-Code) hoạt động phía sau các cú nhấp chuột trên giao diện. Khi bạn thực hiện thao tác Split Column, Power Query thực tế sẽ tự động tạo ra một bước biến đổi sử dụng hàm chủ đạo là Table.SplitColumn.

Dưới đây là cấu trúc cú pháp của các hàm M-Code tương ứng với từng phương pháp xử lý thực tế:

Hàm tách theo ký tự phân cách

Công thức M-Code tương ứng được tạo ra có dạng:

Table.SplitColumn(

    Source, 

    “FullName”, 

    Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), 

    {“FullName.1”, “FullName.2”, “FullName.3”}

)

• Table.SplitColumn: Hàm thực thi chia cột trên một bảng dữ liệu.
• Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv): Định nghĩa bộ chia văn bản (Splitter) dựa vào ký tự phân cách là khoảng trắng ” “. QuoteStyle.Csv giúp xử lý an toàn dữ liệu định dạng chuẩn CSV.
• {“FullName.1”, “FullName.2”, “FullName.3”}: Danh sách tên các cột mới được sinh ra sau khi tách.

Hàm tách theo số lượng ký tự

Khi thực hiện tách cột lặp lại theo độ dài cố định, mã nguồn M-Code sẽ sử dụng bộ chia Splitter.SplitTextByRepeatedLengths:

Table.SplitColumn(

    Source, 

    “PhoneCode”, 

    Splitter.SplitTextByRepeatedLengths(2), 

    {“PhoneCode.1”, “PhoneCode.2”, “PhoneCode.3”, “PhoneCode.4”, “PhoneCode.5”, “PhoneCode.6”}

)

• Splitter.SplitTextByRepeatedLengths(2): Chỉ thị cho hệ thống cắt liên tục mỗi nhóm có độ dài đúng bằng 2 ký tự.

Hàm tách theo vị trí Index

Khi bạn chỉ định các tọa độ vị trí cắt cụ thể, mã M-Code sẽ áp dụng hàm dưới đây:

Table.SplitColumn(

    Source, 

    “IDNumber”, 

    Splitter.SplitTextByPositions({0, 3}), 

    {“IDNumber.1”, “IDNumber.2”}

)

• Splitter.SplitTextByPositions({0, 3}): Thực hiện cắt chuỗi chính xác tại các điểm index 0 và 3 đã được định nghĩa trong danh sách.

Hàm tách theo sự chuyển đổi ký tự

Đối với việc tách dựa trên sự thay đổi kiểu chữ (Ví dụ: Từ viết thường sang viết hoa trên cột MixedCase), Power Query sinh ra mã M-Code sử dụng hàm Splitter.SplitTextByCharacterTransition:

Table.SplitColumn(

    Source, 

    “MixedCase”, 

    Splitter.SplitTextByCharacterTransition({“a”..”z”}, {“A”..”Z”}), 

    {“MixedCase.1”, “MixedCase.2”, “MixedCase.3”, “MixedCase.4”}

)

• Splitter.SplitTextByCharacterTransition({“a”..”z”}, {“A”..”Z”}): Xác định điểm cắt khi có sự chuyển tiếp từ bất kỳ ký tự thường nào trong khoảng từ “a” đến “z” sang bất kỳ ký tự viết hoa nào trong khoảng từ “A” đến “Z”.

Sự khác biệt giữa Split Column trong Transform và trong Add Column

Một điểm mấu chốt mà các nhà phân tích dữ liệu cần lưu ý là vị trí thực hiện thao tác Split Column trên thanh công cụ, bởi nó quyết định cách Power Query quản lý dữ liệu gốc:

• Split Column trong tab Transform: Khi bạn thực hiện chia cột từ tab Transform (hoặc tab Home), Power Query sẽ tác động và thay thế trực tiếp trên cột hiện tại. Cột ban đầu sẽ biến mất, thay thế hoàn toàn bằng các cột mới sau khi tách. Cách này giúp tối ưu hóa bộ nhớ và giữ giao diện bảng gọn gàng khi bạn không còn nhu cầu sử dụng lại chuỗi dữ liệu gộp ban đầu.
• Split Column thông qua tab Add Column: Trong thiết kế giao diện của Power Query, nút chức năng “Split Column” không xuất hiện trực tiếp trên tab Add Column. Tuy nhiên, nếu muốn chia cột mà vẫn bảo toàn cột dữ liệu gốc để đối chiếu hoặc sử dụng cho các bước sau, quy trình chuẩn là: Bạn chọn cột ban đầu -> vào tab Add Column -> nhấp chọn Duplicate Column (Nhân bản cột) để tạo một bản sao -> Sau đó mới tiến hành thực hiện Split Column trên cột vừa nhân bản này. Ngoài ra, bạn cũng có thể tận dụng tính năng Extract hoặc Column from Examples trong tab Add Column để trích xuất dữ liệu ra một cột mới mà không làm mất cột ban đầu.

Kết luận

Việc làm chủ chức năng Split Column trong Power Query không chỉ đơn thuần là việc áp dụng một công cụ dọn dẹp dữ liệu thông thường, mà còn là bước đi chiến lược giúp xây dựng các hệ thống báo cáo tự động hóa thông minh, chuẩn hóa và tối ưu.

Việc lựa chọn cơ chế chia cột khôn ngoan như ưu tiên tách một lần (Left-most hoặc Right-most) thay vì tách lặp lại liên tục sẽ giúp lược bỏ bớt các cột dư thừa giúp giảm thiểu dung lượng RAM khi vận hành. Đối với các danh sách chứa nhiều danh mục phân tách phức tạp, kỹ thuật tách thành hàng được ưu tiên sử dụng nhằm đưa bảng về dạng cấu trúc phẳng tối ưu.

Hy vọng những chia sẻ thực tế này của Starttrain sẽ giúp bạn nâng tầm kỹ năng chuẩn hóa dữ liệu và bứt phá hiệu năng làm việc mỗi ngày.

Nếu bạn muốn học Excel nâng cao cấp tốc, có lộ trình học rõ ràng bạn có thể tham khảo khoá học phân tích dữ liệu bằng Excel nâng cao và Dashboard báo cáo chuyên nghiệp tại Starttrain