Tìm kiếm trang web

Công cụ Excel ẩn này có thể nhanh chóng dọn dẹp dữ liệu của bạn một cách tự động

M là ngôn ngữ công thức mạnh mẽ điều khiển Power Query phía sau. Mặc dù nó có thể trông như mã phức tạp, nhiều tác vụ phổ biến trong M lại khá thân thiện với người mới bắt đầu và có thể tự động hóa việc dọn dẹp dữ liệu một cách nhanh chóng. Nếu bạn có thể viết một hàm Excel cơ bản, bạn có thể bắt đầu học M để xử lý các bộ dữ liệu lớn nhất của mình.

Nhiều người bắt đầu trong Power Query Editor bằng cách sử dụng các nút trên ribbon, và điều đó là ổn. Nhưng phương pháp này buộc bạn phải nhấp chuột lâu. M giúp cắt giảm sự lộn xộn bằng cách cho phép bạn kết hợp nhiều biến đổi thành một bước duy nhất dễ quản lý, tái sử dụng và duy trì hơn.

Ngôn ngữ M: Làm thế nào để bắt đầu

Làm quen với giao diện Power Query

Hãy tưởng tượng bạn vừa nhận được bộ dữ liệu trên. Nó là một mớ lộn xộn các ID đơn hàng khó hiểu, tên được viết hoa không nhất quán và các vùng chưa đầy đủ.

Để theo dõi khi bạn đọc hướng dẫn này, hãy tải xuống một bản sao miễn phí của sổ làm việc Excel được sử dụng trong các ví dụ. Sau khi bạn nhấp vào liên kết, bạn sẽ thấy nút tải xuống ở góc trên bên phải của màn hình.

Để sửa nó bằng cách sử dụng M, trước tiên bạn cần chuẩn bị môi trường của mình. Giả sử dữ liệu của bạn đã được định dạng dưới dạng bảng Excel có tên T_SalesData, hãy làm theo các bước sau:

  1. Chọn bất kỳ ô nào trong bảng của bạn, và trong tab Dữ liệu, nhấp vào Từ Bảng/Mảng.
  2. Khi Power Query Editor mở ra, hãy đảm bảo rằng thanh trắng phía trên bảng dữ liệu của bạn (thanh công thức) hiển thị. Nếu không, hãy mở tab View và đánh dấu chọn Formula Bar.
  3. Xác định nút fx ở bên trái thanh công thức. Khi bạn nhấp vào biểu tượng này, bạn sẽ tạo một Bước Tùy Chỉnh nơi bạn có thể gõ hoặc dán mã của mình.

Đoạn 1: Chuẩn hóa cách viết tên lộn xộn

Sử dụng Text.Proper để sửa lỗi viết hoa tại chỗ

Mục tiêu: Sửa tên trong cột CustomerName nếu chúng viết hoa toàn bộ, viết thường toàn bộ, hoặc viết lộn xộn để chỉ có chữ cái đầu của mỗi từ được viết hoa.

Nhấp vào biểu tượng fx bên trái thanh công thức để tạo một bước mới. Sau đó, thay thế mọi thứ trong thanh công thức bằng mã sau và nhấn Enter:

= Table.TransformColumns(#"Changed Type", {{"CustomerName", each Text.Proper(_), type text}})

Power Query gán cho bước một tên chung như Custom1. Nhấp chuột phải vào tên bước chung này trong bảng Applied Steps và nhấp Rename để đặt cho nó một tên mô tả rõ hơn như FixCasing.

Đối với đoạn mã đầu tiên này, tôi sẽ phân tích đầy đủ cấu trúc của nó trong bảng dưới đây. Đối với ba đoạn mã còn lại, tôi sẽ chỉ tập trung vào những phần mới hoặc thay đổi, vì mẫu tổng thể vẫn giữ nguyên.

Nguyên tố

Mô tả

Bảng.BiếnĐổiCột(...)

Chức năng chính được sử dụng để sửa đổi các cột hiện có. Các dấu ngoặc theo sau 'bao bọc' các đối số của hàm.

#"Đã Thay Đổi Kiểu"

Tham khảo bước ngay trước đó trong danh sách Các bước đã áp dụng. Mỗi bước cần biết phiên bản của bảng mà nó sẽ xử lý tiếp theo.

Dấu ngoặc nhọn: { }

Biểu diễn một danh sách. Vì bạn có thể chuyển đổi nhiều cột cùng lúc, M yêu cầu các hướng dẫn của bạn phải được chứa trong các dấu ngoặc này.

TênKháchHàng

Xác định cột cụ thể để thay đổi. Dấu ngoặc kép định nghĩa tên như văn bản nguyên bản. Chỉ cần gõ tên khác để áp dụng cùng một bước cho cột khác.

nhập văn bản

Ép cột sang một loại dữ liệu cụ thể. Điều này ngăn Excel xác định sai tên là số hoặc ngày.

mỗi

Một từ khóa cho Power Query biết để lặp lại logic sau cho từng hàng riêng lẻ trong cột.

Văn Bản.Chính Xác

Chức năng cụ thể đang được áp dụng. Trong trường hợp này, nó chuyển đổi văn bản thành Chữ Hoa Đầu Từ. Nếu bạn muốn tất cả in hoa, hãy thay đổi điều này thành Text.Upper.

Gạch dưới: _

Một trình giữ chỗ đại diện cho "giá trị hiện tại trong ô này."

Không giống như các công thức Excel tiêu chuẩn, ngôn ngữ M phân biệt chữ hoa chữ thường. Việc viết table.transformcolumns thay vì Table.TransformColumns sẽ dẫn đến lỗi, vì vậy hãy chú ý kỹ đến cách viết hoa chữ cái của bạn!

Đoạn 2: Xóa lỗi định dạng ẩn

Dọn dẹp bố cục của một cột

Mục tiêu: Loại bỏ các khoảng trắng đầu dòng và các ngắt dòng khỏi cột CustomerName. Quan trọng là, bạn muốn thay thế các ngắt dòng bằng một khoảng trắng để "jOHN [ngắt dòng] sMITH" trở thành "John Smith" thay vì "JohnSmith."

Như trước đây, nhấp vào fx, thay thế văn bản hiện có bằng mã dưới đây, và nhấn Enter:

= Table.TransformColumns(FixCasing, {{"CustomerName", each Text.Clean(Text.Trim(Text.Replace(_, "#(lf)", " "))), type text}})

Hãy nhớ rằng phần tử đầu tiên bên trong dấu ngoặc đơn luôn phải khớp với tên của bước trước trong danh sách Các bước đã áp dụng của bạn.

Đổi tên bước CleanTrim.

Đây là những gì từng phần chính thực hiện:

  • #(lf): Đây là ký hiệu tắt của M cho một dòng xuống (ký tự vô hình được tạo bằng Alt+Enter trong Excel—một ngắt dòng).
  • Text.Replace(_, "#(lf)", " "): Điều này tìm ký tự xuống dòng và thay nó bằng một dấu cách.
  • Text.Trim: Loại bỏ các khoảng trắng thừa từ đầu và cuối của một ô.
  • Text.Clean: Loại bỏ các ký tự điều khiển không in còn sót lại (chẳng hạn như các ký tự ASCII ẩn).

Đoạn 3: Trích xuất các chữ số từ một chuỗi hỗn độn

Lọc ký tự với Text.Select

Mục tiêu: Loại bỏ mọi thứ ngoại trừ các chữ số từ cột OrderID.

Sau khi nhấp vào fx, dán mã bên dưới và nhấn Enter:

= Table.TransformColumns(CleanTrim, {{"OrderID", each Text.Select(_, {"0".."9"}), type text}})

Đổi tên bước ExtractNumbers.

Hãy cùng xem xét những yếu tố mới mà chúng ta đã giới thiệu ở đây:

  • Text.Select: Bảo M "chọn" chỉ những ký tự cụ thể bạn liệt kê và loại bỏ mọi thứ khác.
  • {"0".."9"}: Điều này định nghĩa một danh sách các ký tự được phép. Hai dấu chấm (..) nghĩa là "và tất cả những gì ở giữa," có nghĩa là tất cả các số từ 0 đến 9 đều được trích xuất. Vì các chữ cái và ký hiệu không có trong danh sách này, chúng sẽ bị loại hoàn toàn.

Đoạn trích 4: Hợp nhất các cột và xử lý lỗi "null"

Tạo một mã ID-Vùng sạch

Mục tiêu: Kết hợp OrderID đã được làm sạch và Region thành một mã duy nhất (như 101N). Nếu vùng trống (null), bạn muốn chỉ hiển thị ID mà không xuất hiện từ "null", sau đó xóa các cột gốc để giữ cho bảng gọn nhẹ.

Nhấp vào fx, chèn mã sau, và nhấn Enter:

= Table.SelectColumns(Table.AddColumn(ExtractNumbers, "ID", each [OrderID] & (if [Region] = null then "" else [Region])), {"ID", "CustomerName"})

Đổi tên bước MergeIDRegion.

Đây là cách mã này hoạt động:

  • if [Region]=null then "" else [Region]: Nói với Power Query để xem xét Region. Nếu nó thấy giá trị null, nó sẽ coi đó là một chuỗi rỗng ("") thay vào đó. Điều này đảm bảo rằng việc kết hợp 103 với một giá trị null sẽ cho kết quả là 103, không phải 103null.
  • [OrderID] &...: Lấy ID số đã được làm sạch từ Đoạn mã 3, và ký hiệu & nói với Power Query để gắn trực tiếp văn bản vùng vào cuối nó.
  • "ID": Xác định tên tiêu đề cho cột kết hợp mới của bạn.
  • {"ID", "CustomerName"}: Bằng cách chỉ liệt kê hai cột này ở cuối câu lệnh, bạn đang nói với Power Query để loại bỏ các cột OrderID và Region gốc.

Khi bạn nhận ra rằng ngôn ngữ M chỉ là một tập hợp các công thức mạnh mẽ, yếu tố sợ hãi sẽ biến mất. Bốn đoạn mã này là bước đầu tiên của bạn hướng tới hiệu quả, độ chính xác và cứu bạn khỏi mệt mỏi khi nhấp chuột bằng cách sử dụng mã tinh gọn, dễ quản lý. Bây giờ khi bạn đã bắt đầu suy nghĩ như một nhà phát triển, tại sao không chuyển sang sử dụng Python trong Excel — một công cụ cung cấp nhiều sức mạnh hơn cho các nhiệm vụ hàng ngày của bạn.

Bài viết liên quan