Tạo các công thức Power Query trong Excel

Tạo một công thức đơn giản

Để xem ví dụ về công thức đơn giản, chúng ta hãy chuyển đổi một giá trị văn bản thành kiểu chữ thích hợp bằng cách dùng công thức Text.Proper() .

1. Trong tab ribbon POWER QUERY, hãy chọn Từ Nguồn Khác > Truy vấn Trống.

   

2. Trong thanh công thức Trình soạn thảo Truy vấn, hãy nhập = Text.Proper("text value"), và nhấn Enter hoặc chọn biểu tượng Enter.

3. Power Query cho bạn thấy kết quả trong ngăn kết quả công thức.

4. Để xem kết quả trong trang tính Excel, hãy chọn Đóng & Tải.

Kết quả sẽ trông như thế này trong một trang tính:

Bạn cũng có thể tạo công thức truy vấn nâng cao trong Trình soạn thảo Truy vấn.

Tạo công thức nâng cao

Để xem ví dụ về công thức nâng cao, chúng ta hãy chuyển đổi văn bản trong một cột thành kiểu chữ thích hợp bằng cách kết hợp nhiều công thức. Bạn có thể sử dụng Ngôn ngữ Công thức Power Query để kết hợp nhiều công thức thành các bước truy vấn có kết quả tập dữ liệu. Có thể nhập kết quả vào một trang tính Excel.

Chẳng hạn, giả sử bạn có một bảng Excel chứa các tên sản phẩm mà bạn muốn chuyển thành kiểu chữ thích hợp.

Bảng ban đầu trông như thế này:

Và bạn muốn bảng kết quả trông giống như thế này:

Chúng ta hãy xem qua các bước công thức truy vấn để thay đổi bảng ban đầu sao cho các giá trị trong cột Tên Sản phẩm có kiểu chữ thích hợp.

Ví dụ về truy vấn nâng cao bằng Trình soạn thảo Nâng cao

Để dọn dẹp bảng ban đầu, bạn dùng Trình soạn thảo Nâng cao để tạo các bước công thức truy vấn. Chúng ta hãy xây dựng từng bước công thức truy vấn để cho thấy cách tạo truy vấn nâng cao. Các bước công thức truy vấn hoàn chỉnh được liệt kê dưới đây. Khi tạo truy vấn nâng cao, bạn làm theo quy trình này:

• Tạo một chuỗi các bước công thức truy vấn bắt đầu bằng câu lệnh let. Vui lòng lưu ý rằng Ngôn ngữ Công thức Power Query có phân biệt chữ hoa, chữ thường.

• Mỗi bước công thức truy vấn xây dựng trên bước trước đó bằng cách tham chiếu đến một bước theo tên.

• Tạo đầu ra cho công thức truy vấn bằng câu lệnh in. Thông thường, bước truy vấn sau cùng được dùng làm kết quả tập dữ liệu cuối cùng.

Bước 1 – Mở Trình soạn thảo Nâng cao

1. Trong tab ribbon POWER QUERY, hãy chọn Từ Nguồn Khác > Truy vấn Trống.

2. Trong Trình soạn thảo Truy vấn, hãy chọn Trình soạn thảo Nâng cao.

   

3. Bạn sẽ thấy Trình soạn thảo Nâng cao.

   

Bước 2 – Xác định nguồn ban đầu

Trong Trình soạn thảo Nâng cao:

1. Dùng câu lệnh let gán Source = công thức Excel.CurrentWorkbook(). Thao tác này sẽ sử dụng bảng Excel làm nguồn dữ liệu. Để biết thêm thông tin về công thức Excel.CurrentWorkbook(), hãy xem Excel.CurrentWorkbook.

2. Gán Source cho kết quả in.

   let Source =
   Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in Source

3. Truy vấn nâng cao của bạn sẽ trông giống như thế này trong Trình soạn thảo Nâng cao.

   

4. Để xem kết quả trong một trang tính:

   1. Bấm Xong.

   2. Trong ribbon Trình soạn thảo Truy vấn, bấm Đóng & Tải.

Kết quả sẽ trông như thế này trong một trang tính:

Bước 3 – Tăng cấp hàng đầu tiên thành tiêu đề

Để chuyển đổi các giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp, trước tiên bạn cần tăng cấp hàng đầu tiên thành tiêu đề cột. Bạn thực hiện điều này trong Trình soạn thảo Nâng cao:

1. Thêm #"First Row as Header" = công thức Table.PromoteHeaders() vào các bước công thức truy vấn của bạn và tham chiếu đến Source là nguồn dữ liệu. Để biết thêm thông tin về công thức Table.PromoteHeaders(), hãy xem Table.PromoteHeaders.

2. Gán #"First Row as Header" cho kết quả in.

   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source)
   in
       #"First Row as Header"

Kết quả sẽ trông như thế này trong một trang tính:

Bước 4 – Thay đổi mỗi giá trị trong một cột thành kiểu chữ thích hợp

Để chuyển đổi mỗi giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp, bạn dùng Table.TransformColumns() và tham chiếu đến bước công thức truy vấn "First Row as Header”. Bạn thực hiện điều này trong Trình soạn thảo Nâng cao:

1. Thêm #"Capitalized Each Word" = công thức Table.TransformColumns() vào các bước công thức truy vấn của bạn và tham chiếu đến #"First Row as Header" là nguồn dữ liệu. Để biết thêm thông tin về công thức Table.TransformColumns(), hãy xem Table.TransformColumns.

2. Gán #"Capitalized Each Word" cho kết quả in.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    #"First Row as Header" = Table.PromoteHeaders(Source),
    #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
in
    #"Capitalized Each Word"

Kết quả cuối cùng sẽ thay đổi mỗi giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp và trông giống như thế này trong trang tính:

Với Ngôn ngữ Công thức Power Query bạn có thể tạo các truy vấn dữ liệu từ đơn giản đến nâng cao để khám phá, kết hợp và tinh chỉnh dữ liệu. Để tìm hiểu thêm về Power Query, hãy xem Trợ giúp Microsoft Power Query cho Excel.