Làm việc với cột Danh sách, Bản ghi hoặc Bảng có cấu trúc (Power Query)

Có ba loại cột có cấu trúc:

• Danh sách    Lưu trữ danh sách các giá trị, Ví dụ: { 1, 2, 5, 10 } hoặc { "hello", "hi", "good bye" }. Giá trị của Danh sách trong một ô là cột có cấu trúc đơn giản nhất và không có mối quan hệ bảng.

• Ghi    Lưu trữ một tập hợp các trường có tên được nhóm thành một đơn vị. Ví dụ: [ FirstName = "Joe", LastName = "Smith", Birthdate = #date(2010, 1, 2) ]. Giá trị của Bản ghi trong ô cho biết mối quan hệ bảng một đối một với bảng hiện tại hoặc bảng chính.

• Bàn    Lưu trữ bảng có mối quan hệ phụ với dữ liệu hiện tại, là bảng chính. Giá trị của Bảng trong ô cho biết mối quan hệ một đối nhiều với bảng hiện tại hoặc bảng chính.

Cột có cấu trúc Bản ghi và Bảng thể hiện mối quan hệ trong nguồn dữ liệu có mô hình quan hệ, chẳng hạn như thực thể có liên kết khóa ngoại trong nguồn cấp OData hoặc mối quan hệ khóa ngoại trong cơ sở dữ liệu SQL Server.

Bạn có thể sử dụng biểu tượng mở rộng để xem các cột từ bảng có liên quan. Ví dụ: trong bảng Đơn hàng, một thao tác bung rộng tập hợp các Order_Details liên quan đến bảng Đơn hàng để kết hợp các mục dòng đơn hàng với từng đơn hàng. Thao tác bung rộng sẽ mở rộng bảng chính để bao gồm các cột từ bảng phụ và bảng có liên quan. Để minh họa:

Bảng chính có các cột A và B.

Một bảng liên quan có cột C.

Thao tác bung rộng sẽ mở rộng bảng chính để bao gồm cột C và bung rộng bảng chính có các giá trị liên quan từ bảng phụ và bảng liên quan có chứa cột C.

Để tóm tắt, khi bạn bung rộng cột có cấu trúc bảng, các giá trị được hiển thị dọc theo các cột khác trong Bản xem trước Dữ liệu. Để biết thêm thông tin về việc kết hợp các bảng chính và bảng liên quan, hãy xem Tìm hiểu cách kết hợp nhiều nguồn dữ liệu. 

Trong ví dụ này, thao tác bung rộng sẽ mở rộng bảng Đơn hàng để bao gồm các cột Order_Details.ProductID, Order_Details.UnitPrice và Order_Details.Quantity để tập hợp các hàng bảng chính và các hàng bảng Order_Details liên quan.

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.
   
   Ghi    Truy vấn phải có ít nhất hai bảng được nối trong một mối quan hệ. Để biết thêm thông tin, hãy xem Phối truy vấn. 

1. Bấm vào biểu tượng bung rộng () trong tiêu đề cột của cột có cấu trúc.

2. Trong danh sách thả xuống Tên Cột, hãy chọn những cột bạn muốn và xóa những cột bạn không muốn. 
   
   

3. Chọn OK.

Kết quả

Bảng hiện có chứa một cột mới cho mỗi cột được chọn ở bước 3. 

Khi bạn bung rộng cột Bản ghi có cấu trúc, các giá trị được hiển thị dưới dạng cột mới trong Bản xem trước Dữ liệu. Nếu bạn không bung rộng nó và sau đó tải một truy vấn vào một trang tính, bạn sẽ thấy giá trị chỗ dành sẵn của [Bản ghi] cho mỗi ô trong cột.

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. Để xem một giá trị cụ thể mà không bung rộng cột, hãy chọn một giá trị trong cột có cấu trúc có giá trị Bản ghi. Ví dụ:
   
   

3. Chọn biểu tượng bung rộng () trong tiêu đề cột của cột có cấu trúc.

4. Trong danh sách thả xuống Tên Cột, hãy chọn những cột bạn muốn và xóa những cột bạn không muốn.
   
   

5. Chọn OK.

Kết quả

Bảng hiện có chứa một cột mới cho mỗi cột được chọn ở bước 4. 

Khi bạn bung rộng cột Có cấu trúc danh sách, các giá trị được hiển thị dưới dạng cột mới trong Bản xem trước Dữ liệu. Nếu bạn không bung rộng nó và sau đó tải một truy vấn vào một trang tính, bạn sẽ thấy giá trị chỗ dành sẵn của [Danh sách] cho mỗi ô trong cột.

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. Để xem một giá trị cụ thể mà không bung rộng cột, hãy chọn một giá trị trong cột có cấu trúc có giá trị Danh sách. Ví dụ:
   
   

3. Chọn biểu tượng bung rộng () trong tiêu đề cột của cột có cấu trúc.

4. Thực hiện một trong những thao tác sau:

   • Để tạo một hàng cho mỗi giá trị danh sách, hãy chọn Bung rộng đến Hàng Mới.

   • Để tạo danh sách các giá trị văn bản được phân tách bằng dấu tách, hãy chọn Trích xuất Giá trị.

5. Chọn OK.

Kết quả bung rộng đến Hàng Mới

Kết quả trích xuất Giá trị bằng dấu chấm phẩy (;) dấu tách

Bạn có thể dùng các lệnh bổ sung sau đây trên ribbon Trình soạn thảo Power Query để làm việc với các cột có cấu trúc:

1. Chọn Trang > Biến đổi , rồi chọn một lệnh sau đây trong nhóm Cột có cấu trúc.

   Mở rộng    Tăng cấp cột có cấu trúc (Danh sách, Bản ghi hoặc Bảng) thành các cột và hàng mới trong bảng truy vấn hiện tại. Lệnh này giống như biểu tượng Bung rộng .
   
   Tổng    Tóm tắt các giá trị trong cột có cấu trúc của Bảng bằng cách sử dụng các hàm tổng hợp, bao gồm Sum và Count. Lệnh này giống như việc chọn biểu tượng Bung , rồi chọn Tổng hợp trong hộp thoại thả xuống Tên Cột. Nó chỉ sẵn dùng cho cột có cấu trúc bảng.
   
   Trích xuất Giá trị Trích xuất các giá trị của cột Danh sách có cấu trúc bằng cách kết hợp chúng thành một giá trị văn bản duy nhất bằng một dấu tách đã xác định.

Lưu ý    Bạn cũng có thể sử dụng lệnh Thống kê trong nhóm Cột Số (Chọn Chuyển đổi > thống kê) với một cột có cấu trúc Bảng. Nó có cùng danh sách các hàm tổng hợp như lệnh Thống kê trong nhóm Danh sách Số của tab ngữ cảnh Chuyển đổi bên dưới Công cụ Danh sách trong ribbon Trình soạn thảo Power Query.

Bạn có thể dùng lệnh Truy sâu Xuống để hiển thị tab ngữ cảnh Chuyển đổi bên dưới Công cụ Danh sách trong ribbon Trình soạn thảo Power Query và hiển thị các lệnh bổ sung để làm việc với các cột có cấu trúc. Đừng nhầm lẫn tab Chuyển đổi này với tab Chuyển đổi khác sẵn dùng thường xuyên với ribbon Trình soạn thảo Power Query.

1. Bấm chuột phải vào tiêu đề cột, rồi chọn Truy sâu Xuống.

2. Để chuyển đổi Danh sách hoặc Bản ghi thành Bảng, bên dưới Công cụ Danh sách, chọn Chuyển đổi > thành Bảng.

3. Để giữ trên cùng, dưới cùng hoặc một phạm vi các mục, bên dưới Công cụ Danh sách, chọn Chuyển đổi> Giữ Mục.

4. Để loại bỏ các mục trên cùng, dưới cùng hoặc thay thế, bên dưới Công cụ Danh sách, hãy chọn Chuyển >Loại bỏ Mục.

5. Để loại bỏ các giá trị trùng lặp, bên dưới Công cụ Danh sách, hãy chọn Chuyển đổi > Loại bỏ Trùng lặp. Để biết thêm thông tin, hãy xem giữ hoặc loại bỏ các hàng trùng lặp.

6. Để đảo ngược các mục, bên dưới Công cụ Danh sách, chọn Chuyển đổi > Đảo ngược Mục.

7. Để sắp xếp dữ liệu, bên dưới Công cụ Danh sách, chọn Sắp xếp tăng dần hoặc Sắp xếp giảm dần . Để biết thêm thông tin, hãy xem Sắp xếp dữ liệu.

8. Để hiển thị các số liệu thống kê khác nhau, bên dưới Công cụ Danhsách, chọn Chuyển đổi> liệu thống kê. Các thống kê sau đây sẵn dùng: Sum, Minimum, Maximum, Median, Average, Standard, Deviation, Count Values và Count Distinct Values. Để biết thêm thông tin, xem mục Tổng hợp dữ liệu từ một cột.