Thay thế hàng loạt văn bản trong Power Query bằng chức năng List.Accumulate

Cách thay thế nhanh chóng và hàng loạt văn bản theo danh sách tham chiếu bằng các công thức - chúng tôi đã sắp xếp rồi. Bây giờ chúng ta hãy thử làm điều đó trong Power Query.

Như thường xuyên xảy ra thực hiện nhiệm vụ này dễ dàng hơn nhiều so với việc giải thích tại sao nó hoạt động, nhưng chúng ta hãy cố gắng làm cả hai 🙂

Vì vậy, chúng tôi có hai bảng động "thông minh" được tạo từ các phạm vi thông thường bằng phím tắt Ctrl+T hoặc đội Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng):

Tôi đã gọi bàn đầu tiên Ngày, bảng thứ hai - thư mụcsử dụng trường Tên bảng (Tên bảng) chuyển hướng xây dựng (Thiết kế).

Nhiệm vụ: thay thế các địa chỉ trong bảng Ngày tất cả các lần xuất hiện từ một cột Để tìm Sổ tay với các đối tác chính xác tương ứng của chúng từ cột Thay thế. Phần còn lại của văn bản trong các ô sẽ được giữ nguyên.

Bước 1. Tải thư mục vào Power Query và biến nó thành danh sách

Sau khi đặt ô hiện hoạt vào bất kỳ vị trí nào trong bảng tham chiếu, hãy nhấp vào tab Ngày (Ngày)hoặc trên tab Truy vấn nguồn (nếu bạn có phiên bản Excel cũ và bạn đã cài đặt Power Query dưới dạng bổ trợ trên một tab riêng biệt) trên nút Từ bảng / phạm vi (Từ Bảng / Phạm vi).

Bảng tham chiếu sẽ được tải vào trình chỉnh sửa truy vấn Power Query:

Để không can thiệp, một bước được thêm tự động loại sửa đổi (Loại đã thay đổi) trong bảng điều khiển bên phải, các bước đã áp dụng có thể được xóa một cách an toàn, chỉ để lại bước nguồn (Nguồn):

Bây giờ, để thực hiện các phép biến đổi và thay thế tiếp theo, chúng ta cần biến bảng này thành một danh sách (list).

Lạc đề trữ tình

• Bàn là một mảng hai chiều bao gồm một số hàng và cột.
• Record (Ghi lại) - chuỗi-mảng một chiều, bao gồm một số trường-phần tử có tên, chẳng hạn [Tên = “Masha”, Giới tính = “f”, Tuổi = 25]
  
• Danh sách - một cột mảng một chiều, bao gồm một số phần tử, ví dụ: {1, 2, 3, 10, 42} or { "Đức tin hy vọng tình yêu" }
  

Để giải quyết vấn đề của chúng tôi, chúng tôi sẽ chủ yếu quan tâm đến loại Danh sách.

Mẹo ở đây là các mục danh sách trong Power Query không chỉ có thể là số hoặc văn bản tầm thường mà còn có thể là các danh sách hoặc bản ghi khác. Nó nằm trong một danh sách (list) phức tạp như vậy, bao gồm các bản ghi (record) mà chúng ta cần lật lại thư mục của mình. Trong ký hiệu cú pháp Power Query (các mục nhập trong dấu ngoặc vuông, danh sách trong dấu ngoặc nhọn), điều này sẽ giống như sau:

{

    [Tìm = “St. Petersburg ”, Replace =“ St. Petersburg ”] ,

    [Tìm = “St. Petersburg ”, Replace =“ St. Petersburg ”] ,

    [Find = “Peter”, Replace = “St. Petersburg ”] ,

và vv

}

Việc chuyển đổi như vậy được thực hiện bằng cách sử dụng một hàm đặc biệt của ngôn ngữ M được tích hợp trong Power Query - Bảng.ToRecords. Để áp dụng nó trực tiếp trong thanh công thức, hãy thêm chức năng này vào mã bước ở đó nguồn.

Đó là:

Sau:

Sau khi thêm hàm Table.ToRecords, giao diện bảng của chúng ta sẽ thay đổi - nó sẽ chuyển thành danh sách các bản ghi. Nội dung của các bản ghi riêng lẻ có thể được nhìn thấy ở cuối khung xem bằng cách nhấp vào nền ô bên cạnh bất kỳ từ nào Kỷ lục (nhưng không phải trong một từ duy nhất!)

Ngoài những điều trên, bạn nên thêm một nét nữa - vào bộ đệm (đệm) danh sách đã tạo của chúng tôi. Điều này sẽ buộc Power Query tải danh sách tra cứu của chúng tôi một lần vào bộ nhớ và không tính toán lại danh sách đó khi chúng tôi truy cập sau đó để thay thế nó. Để làm điều này, hãy bọc công thức của chúng ta trong một hàm khác - Danh sách. Bộ đệm:

Bộ nhớ đệm như vậy sẽ làm tăng tốc độ rất đáng chú ý (gấp vài lần!) Với một lượng lớn dữ liệu ban đầu được xóa.

Điều này hoàn thành việc chuẩn bị cuốn sổ tay.

Nó vẫn còn để nhấp vào Trang chủ - Đóng và tải - Đóng và tải vào… (Trang chủ - Đóng & Tải - Đóng & Tải vào ..), chọn một tùy chọn Chỉ cần tạo kết nối (Chỉ tạo kết nối) và quay lại Excel.

Bước 2. Tải bảng dữ liệu

Mọi thứ đều sáo mòn ở đây. Như trước đây với sách tham khảo, chúng ta lên bất kỳ vị trí nào trong bảng, bấm vào tab Ngày nút Từ Bảng / Phạm vi và bàn của chúng tôi Ngày vào Power Query. Đã tự động thêm bước loại sửa đổi (Loại đã thay đổi) bạn cũng có thể xóa:

Không có hành động chuẩn bị đặc biệt nào được yêu cầu phải thực hiện với nó, và chúng tôi chuyển sang điều quan trọng nhất.

Bước 3. Thực hiện thay thế bằng hàm List.Accumulate

Hãy thêm một cột được tính toán vào bảng dữ liệu của chúng tôi bằng lệnh Thêm một cột - Cột tùy chỉnh (Thêm cột - Cột tùy chỉnh): và nhập tên của cột đã thêm vào cửa sổ mở ra (ví dụ: địa chỉ đã sửa) và chức năng kỳ diệu của chúng tôi Danh sách. Tích lũy:

Nó vẫn còn để nhấp vào OK - và chúng tôi nhận được một cột với các thay thế được thực hiện:

Lưu ý rằng:

• Vì Power Query phân biệt chữ hoa chữ thường, nên không có sự thay thế nào ở dòng áp chót, vì trong thư mục chúng ta có “SPb”, không phải “SPb”.
• Nếu có một số chuỗi con cần thay thế cùng một lúc trong dữ liệu nguồn (ví dụ: ở dòng thứ 7, bạn cần thay thế cả “S-Pb” và “Bản cáo bạch”), thì điều này không tạo ra bất kỳ vấn đề nào (không giống như việc thay thế bằng các công thức từ phương pháp trước đó).
• Nếu không có gì để thay thế trong văn bản nguồn (dòng thứ 9), thì không có lỗi nào xảy ra (không giống như, một lần nữa, từ thay thế bằng công thức).

Tốc độ của một yêu cầu như vậy là rất, rất tốt. Ví dụ: đối với một bảng dữ liệu ban đầu có kích thước 5000 hàng, truy vấn này được cập nhật trong vòng chưa đầy một giây (nhân tiện, không có bộ đệm, khoảng 3 giây!)

Cách hoạt động của hàm List.Accumulate

Về nguyên tắc, đây có thể là phần cuối (để tôi viết, và cho bạn đọc) bài báo này. Nếu bạn muốn không chỉ có thể mà còn hiểu được cách nó hoạt động "dưới mui xe", thì bạn sẽ phải lặn sâu hơn một chút vào lỗ thỏ và xử lý chức năng List. làm việc cho chúng tôi.

Cú pháp cho hàm này là:

= List.Accumulate ( , hạt giống, Accumulator)

Ở đâu

• là danh sách có các phần tử mà chúng ta đang lặp lại. 
• hạt giống - trạng thái ban đầu
• Accumulator - một hàm thực hiện một số hoạt động (toán học, văn bản, v.v.) trên phần tử tiếp theo của danh sách và tích lũy kết quả xử lý trong một biến đặc biệt.
  

Nói chung, cú pháp để viết các hàm trong Power Query trông giống như sau:

(đối số1, đối số2,… đối sốN) => một số hành động với đối số

Ví dụ, hàm tổng kết có thể được biểu diễn như sau:

(a, b) => a + b

Đối với List.Accumulate, hàm tích lũy này có hai đối số bắt buộc (chúng có thể được đặt tên bất kỳ thứ gì, nhưng các tên thông thường là nhà nước и hiện hành, như trong trợ giúp chính thức cho chức năng này, trong đó:

• nhà nước - một biến trong đó kết quả được tích lũy (giá trị ban đầu của nó là biến được đề cập ở trên hạt giống)
  
• hiện hành - giá trị lặp lại tiếp theo từ danh sách
  

Ví dụ, chúng ta hãy xem xét các bước của logic của cấu trúc sau:

= List.Accumulate ({3, 2, 5}, 10, (trạng thái, hiện tại) => trạng thái + hiện tại)

Đây là tích lũy mới nhất nhà nước giá trị là Danh sách của chúng ta. Hàm tích lũy và kết quả là kết quả:

Nếu bạn mơ mộng một chút, thì bằng cách sử dụng hàm List.Accumulate, bạn có thể mô phỏng, ví dụ, hàm CONCATENATE trong Excel (trong Power Query, hàm tương tự của nó được gọi là Văn bản.Combine) bằng cách sử dụng biểu thức:

Hoặc thậm chí tìm kiếm giá trị lớn nhất (giả hàm MAX của Excel, trong Power Query được gọi là Danh sách.Max):

Tuy nhiên, tính năng chính của List.Accumulate là khả năng xử lý không chỉ các danh sách văn bản hoặc số đơn giản dưới dạng đối số mà còn các đối tượng phức tạp hơn - ví dụ: danh sách từ danh sách hoặc danh sách từ bản ghi (xin chào, Directory!)

Chúng ta hãy nhìn lại công trình đã thực hiện thay thế trong vấn đề của chúng tôi:

List.Accumulate (thư mục, [Địa chỉ nhà], (trạng thái, hiện tại) => Text.Replace (trạng thái, hiện tại [Tìm], hiện tại [Thay thế]) )

Điều gì đang thực sự xảy ra ở đây?

1. Như giá trị ban đầu (hạt giống) chúng tôi lấy văn bản vụng về đầu tiên từ cột [Địa chỉ nhà] bàn của chúng ta: 199034, St. Petersburg, str. Beringa, d. 1
2. Sau đó, List.Accumulate lặp lại lần lượt các phần tử của danh sách - Sổ tay. Mỗi phần tử của danh sách này là một bản ghi bao gồm một cặp trường “Tìm gì - Thay thế bằng gì” hay nói cách khác là dòng tiếp theo trong thư mục.
3. Hàm tích lũy đặt vào một biến nhà nước giá trị ban đầu (địa chỉ đầu tiên 199034, St. Petersburg, str. Beringa, d. 1) và thực hiện một chức năng tích lũy trên nó - hoạt động thay thế bằng cách sử dụng chức năng M tiêu chuẩn Văn bản.Thay thế (tương tự như hàm SUBSTITUTE của Excel). Cú pháp của nó là:

   Text.Replace (văn bản gốc, những gì chúng tôi đang tìm kiếm, những gì chúng tôi đang thay thế bằng)

   và ở đây chúng tôi có:

• nhà nước là địa chỉ bẩn thỉu của chúng tôi, nằm ở nhà nước (đến đó từ hạt giống)
  
• [Tìm kiếm] hiện tại - giá trị trường Để tìm từ mục nhập lặp lại tiếp theo của danh sách thư mục, nằm trong biến hiện hành
• hiện tại [Thay thế] - giá trị trường Thay thế từ mục nhập lặp lại tiếp theo của danh sách thư mụcnằm trong hiện hành

Do đó, đối với mỗi địa chỉ, một chu kỳ liệt kê đầy đủ của tất cả các dòng trong thư mục được chạy mỗi lần, thay thế văn bản từ trường [Tìm] bằng giá trị từ trường [Thay thế].

Hy vọng bạn có ý tưởng 🙂

• Thay thế hàng loạt văn bản trong danh sách bằng công thức
  
• Biểu thức chính quy (RegExp) trong Power Query