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

Công cụ Trình soạn thảo Power Query cung cấp một truy vấn dữ liệu và trải nghiệm định hình cho Excel mà bạn có thể dùng để định hình lại dữ liệu từ nhiều nguồn dữ liệu. Để hiển thị cửa sổ Trình soạn thảo Power Query, hãynhập dữ liệu từ các nguồn dữ liệu ngoài trong trang tính Excel, chọn một ô trong dữ liệu, rồi chọn Truy vấn >Sửa. Sau đây là tóm tắt các thành phần chính.

1. Dải Trình soạn thảo Power Query dùng để định hình dữ liệu của bạn

2. Ngăn Truy vấn mà bạn sử dụng để định vị nguồn dữ liệu và bảng

3. Menu ngữ cảnh là các lối tắt thuận tiện cho các lệnh trong dải băng

4. Bản xem trước Dữ liệu hiển thị kết quả của các bước được áp dụng cho dữ liệu

5. Ngăn Thiết đặt Truy vấn liệt kê các thuộc tính và từng bước trong truy vấn

Ở hậu trường, mỗi bước trong truy vấn được dựa trên công thức hiển thị trong thanh công thức.

Có thể có những lúc bạn muốn sửa đổi hoặc tạo công thức. Công thức dùng ngôn ngữ Power Query thức mà bạn có thể dùng để xây dựng cả biểu thức đơn giản lẫn phức tạp. Để biết thêm thông tin về cú pháp, đối số, chú thích, hàm và ví dụ, hãy Power Query ngữ công thức M.

Sử dụng danh sách các chức vô địch bóng đá làm ví dụ, hãy sử dụng Power Query để lấy dữ liệu thô mà bạn tìm thấy trên trang web và biến nó thành bảng có định dạng tốt. Xem các bước truy vấn và công thức tương ứng được tạo ra như thế nào cho từng tác vụ trong ngăn Thiết đặt Truy vấn bên dưới Các bước Áp dụng và trong thanh Công thức.

Quy trình

1. Để nhập dữ liệu, hãy chọn > liệu Từ Web, nhập "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" vào hộp URL , rồi chọn OK.

2. Trong hộp thoại Bộ dẫn hướng , chọn bảng Results [Edit] ở bên trái, rồi chọn Transform Data ở dưới cùng. Trình soạn Power Query xuất hiện.

3. Để thay đổi tên truy vấn mặc định, trong ngăn Cài đặt truy vấn, bên dưới Thuộc tính, xóa "Kết quả [Chỉnh sửa]" rồi nhập "UEFA champs".

4. Để loại bỏ các cột không mong muốn, hãy chọn cột đầu tiên, thứ tư và thứ năm, rồi chọn Trang đầu> Loại bỏ >Loại bỏ Cột Khác.

5. Để loại bỏ các giá trị không mong muốn, hãy chọn Cột1, chọn Trang đầu> Thay thế Giá trị, nhập "chi tiết" vào hộp Giá trị để Tìm, rồi chọn OK.

6. Để loại bỏ các hàng có từ "Năm" trong đó, hãy chọn mũi tên bộ lọc trong Cột 1, bỏ chọn hộp kiểm bên cạnh "Năm", rồi chọn OK.

7. Để đổi tên tiêu đề cột, hãy bấm đúp vào từng tiêu đề cột, rồi thay đổi "Cột1" thành "Năm", "Cột4" thành "Người chiến thắng" và "Cột5" thành "Điểm Cuối cùng".

8. Để lưu truy vấn, hãy chọn Trang > Đóng & Tải.

Kết quả

Bảng sau đây tóm tắt từng bước được áp dụng và công thức tương ứng.

Bước và tác vụ truy vấn	Công thức
Nguồn Kết nối với nguồn dữ liệu web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Dẫn hướng Chọn bảng để kết nối	=Source{2}[Data]
Loại đã Thay đổi Thay đổi kiểu dữ liệu (kiểu Power Query tự động)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Đã loại bỏ cột khác Xóa các cột khác để chỉ hiển thị các cột bạn muốn	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Giá trị Đã thay thế Thay thế giá trị để dọn sạch các giá trị trong cột đã chọn	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Hàng đã lọc Lọc các giá trị trong một cột	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Cột được đổi tên Đã thay đổi tiêu đề cột thành có ý nghĩa	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Quan trọng    Hãy cẩn thận khi chỉnh sửa các bước Nguồn,  Dẫn hướng và Loại đã Thay đổi vì chúng được tạo bởi Power Query để xác định và thiết lập nguồn dữ liệu.

Hiện hoặc ẩn thanh công thức

Thanh công thức được hiển thị theo mặc định, nhưng nếu nó không hiển thị, bạn có thể hiển thị lại nó.

• Chọn Dạng xem > bố > công thức.

Edit một công thức trong thanh công thức

1. Để mở truy vấn, hãy định vị truy vấn đã tải trước đó từ Trình soạn thảo Power Query, chọn một ô trong dữ liệu, rồi chọn Truy vấn> Sửa. Để biết thêm thông tin , hãy xem mục Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.

2. Trong ngăn Thiết đặt Truy vấn, bên dưới Các bước Áp dụng, chọn bước bạn muốn chỉnh sửa.

3. Trong thanh công thức, định vị và thay đổi các giá trị tham số, rồi chọn biểu tượng Nhập hoặc nhấn Enter. Ví dụ, thay đổi công thức này để giữ Cột2:
   
   Trước: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Sau:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Chọn biểu tượng Nhập hoặc nhấn Enter để xem kết quả mới được hiển thị trong Bản xem trước Dữ liệu.

5. Để xem kết quả trong trang tính Excel, hãy chọn Trang đầu> Đóng & Tải.

Tạo công thức trong thanh công thức

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

1. Để mở một truy vấn trống, trong Excel, chọn Truy vấn> Lấy dữ liệu >từ các nguồn khác > truy vấn trống. Để biết thêm thông tin , hãy xem mục Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.

2. Trong thanh công thức, nhập=Text.Proper("text value"), rồi chọn biểu tượng Nhập công hoặc nhấn Enter.
   
   Kết quả hiển thị trong Bản xem trước Dữ liệu .

3. Để xem kết quả trong trang tính Excel, hãy chọn Trang đầu> Đóng & Tải.

Kết quả:

 Khi bạn tạo công thức, công Power Query xác thực cú pháp công thức. Tuy nhiên, khi bạn chèn, sắp xếp lại hoặc xóa bước trung gian trong truy vấn, bạn có thể làm hỏng truy vấn.  Luôn xác minh kết quả trong Bản xem trước Dữ liệu.

Quan trọng    Hãy cẩn thận khi chỉnh sửa các bước Nguồn,  Dẫn hướng và Loại đã Thay đổi vì chúng được tạo bởi Power Query để xác định và thiết lập nguồn dữ liệu.

Sửa công thức bằng cách sử dụng hộp thoại

Phương pháp này sử dụng hộp thoại khác nhau tùy theo bước. Bạn không cần biết cú pháp của công thức.

1. Để mở truy vấn, hãy định vị truy vấn đã tải trước đó từ Trình soạn thảo Power Query, chọn một ô trong dữ liệu, rồi chọn Truy vấn> Sửa. Để biết thêm thông tin , hãy xem mục Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.

2. Trong ngăn Thiết đặt Truy vấn, bên dưới Các bước Áp dụng, chọn biểu tượng Sửa Thiết đặt của bước bạn muốn chỉnh sửa hoặc bấm chuột phải vào bước, rồi chọn Sửa Thiết đặt.

3. Trong hộp thoại, hãy thực hiện thay đổi của bạn, rồi chọn OK.

Chèn một bước

Sau khi bạn hoàn thành bước truy vấn định hình lại dữ liệu của mình, một bước truy vấn sẽ được thêm vào bên dưới bước truy vấn hiện tại. nhưng khi bạn chèn một bước truy vấn ở giữa các bước, lỗi có thể xảy ra trong các bước tiếp theo. Power Query hiển thị cảnh báo Bước Chèn khi bạn tìm cách chèn một bước mới và bước mới sẽ thay đổi các trường, chẳng hạn như tên cột, được sử dụng trong bất kỳ bước nào làm theo bước được chèn.

1. Trong ngăn Thiết đặt Truy vấn, bên dưới Các bước Áp dụng, hãy chọn bước bạn muốn ngay trước bước mới và công thức tương ứng.

2. Chọn biểu tượng Thêm bước ở bên trái thanh công thức. Ngoài ra, bấm chuột phải vào một bước, rồi chọn Chèn Bước Sau. Một công thức mới được tạo theo định dạng :
   
   = <nameOfTheStepToReference>, chẳng hạn như =Production.WorkOrder.

3. Nhập công thức mới bằng cách dùng định dạng:=Class.Function(ReferenceStep[,otherparameters])
   
   
   
   Ví dụ: giả sử bạn có một bảng với cột Gender và bạn muốn thêm một cột có giá trị "Ms". hoặc "Mr.", tùy thuộc vào giới tính của người đó. Công thức sẽ là:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Sắp xếp lại một bước

• Trong ngăn Thiết đặt Truy vấn bên dưới Các bước Áp dụng, bấm chuột phải vào bước, rồi chọn Di chuyển Lên hoặc Di chuyển Xuống.

Xóa bước

• Chọn biểu tượng Xóa ở bên trái của bước hoặc bấm chuột phải vào bước, rồi chọn Xóa hoặc Xóa Cho đến khi Kết thúc. Biểu tượng Xóa công thức cũng sẵn dùng ở bên trái thanh công thức.

Trong ví dụ này, 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 các công thức trong Trình chỉnh sửa nâng cao. 

Ví dụ: bạn có một bảng Excel, được gọi là Đơn hàng, với cột Tên Sản phẩm mà bạn muốn chuyển đổi thành kiểu chữ thích hợp. 

Trước:

Sau:

Khi tạo truy vấn nâng cao, bạn tạo một chuỗi các bước công thức truy vấn dựa trên biểu thức cho phép. Sử dụng biểu thức let để gán tên và tính toán các giá trị mà sau đó được tham chiếu bởi mệnh đề trong đó xác định Bước. Ví dụ này trả về kết quả giống như kết quả trong phần "Tạo công thức trong thanh công thức".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Bạn sẽ thấy rằng mỗi bước được xây dựng dựa trên một bước trước đó bằng cách tham chiếu đến một bước theo tên. Lưu ý, Ngôn ngữ Công thức Power Query phân biệt chữ hoa, chữ thường.

Giai đoạn 1: Mở Trình chỉnh sửa nâng cao

1. Trong Excel, hãy chọn Dữ > Lấy Dữ >Nguồn Khác >Truy vấn Trống. Để biết thêm thông tin , hãy xem mục Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.

2. Trong hộp Trình soạn thảo Power Query, chọn Trang đầu> Trình chỉnh sửa nâng cao, thao tác này sẽ mở ra với mẫu biểu thức let.

Giai đoạn 2: Xác định nguồn dữ liệu

1. Tạo biểu thức let bằng cách sử dụng hàm Excel.CurrentWorkbook như sau:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Để tải truy vấn vào trang tính, hãy chọn Xong, rồi chọn Trang đầu> Đóng Truy & Tải > Đóng & Tải.

Kết quả:

Pha 3: Tăng cấp hàng đầu tiên thành tiêu đề

1. Để mở truy vấn, từ trang tính, hãy chọn một ô trong dữ liệu, rồi chọn Truy vấn> Sửa. Để biết thêm thông tin, hãy xem mục Tạo, tải hoặc chỉnh sửa truy vấn trong Excel (Power Query).

2. Trong hộp Trình soạn thảo Power Query, chọn Trang đầu> Trình chỉnh sửa nâng cao, tùy chọn này sẽ mở ra với câu lệnh bạn đã tạo trong Giai đoạn 2: Xác định nguồn dữ liệu.

3. Trong biểu thức let, thêm #"First Row as Header" và Table.PromoteHeaders như sau:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. Để tải truy vấn vào trang tính, hãy chọn Xong, rồi chọn Trang đầu> Đóng Truy & Tải > Đóng & Tải.

Kết quả:

Giai đoạn 4: Thay đổi mỗi giá trị trong một cột thành kiểu chữ thích hợp

1. Để mở truy vấn, từ trang tính, hãy chọn một ô trong dữ liệu, rồi chọn Truy vấn> Sửa. Để biết thêm thông tin , hãy xem mục Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.

2. Trong hộp Trình soạn thảo Power Query, chọn Trang đầu> Trình chỉnh sửa nâng cao, tùy chọn này sẽ mở ra với câu lệnh bạn đã tạo trong Giai đoạn 3: Tăng cấp hàng đầu tiên thành tiêu đề.

3. Trong biểu thức let, chuyển đổi mỗi giá trị cột ProductName thành kiểu chữ thích hợp bằng cách dùng hàm Table.TransformColumns, tham chiếu đến bước công thức truy vấn "First Row as Header" trước đó, thêm #"Capitalized Each Word" vào nguồn dữ liệu, rồi gán #"Capitalized Each Word" cho kết quả trong kết quả.
   
   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"

4. Để tải truy vấn vào trang tính, hãy chọn Xong, rồi chọn Trang đầu> Đóng Truy & Tải > Đóng & Tải.

Kết quả:

Bạn có thể kiểm soát hành vi của thanh công thức trong thanh công thức Trình soạn thảo Power Query tất cả các sổ làm việc của bạn.

Hiển thị hoặc ẩn thanh công thức

1. Chọn Tệp để > chọn và Thiết đặt >Chọn Truy vấn.

2. Trong ngăn bên trái, dưới GLOBAL, chọn Trình soạn thảo Power Query.

3. Trong ngăn bên phải, dưới Bố trí, chọn hoặc bỏ chọn Hiển thị Thanh Công thức.

Bật hoặc tắt M Intellisense

1. Chọn Tệp và > Chọn và Thiết đặt >Chọn Truy vấn .

2. Trong ngăn bên trái, dưới GLOBAL, chọn Trình soạn thảo Power Query.

3. Trong ngăn bên phải, bên dưới Công thức, chọn hoặc bỏ chọn Bật Chuột Intellisense M trong thanh công thức, trình soạn thảo nâng cao và hộp thoại cột tùy chỉnh.

Lưu ý    Việc thay đổi cài đặt này sẽ có hiệu lực vào lần tiếp theo bạn mở cửa Trình soạn thảo Power Query này.