Tìm hiểu về Power Query / M Language (Part 11): Kiểu dữ liệu – Table (cú pháp)
Như hầu hết người dùng, bảng (table) là lý do chính khiến bạn sử dụng Power Query. Bạn yêu cầu Power Query lấy dữ liệu từ một hoặc nhiều nguồn dữ liệu, trộn nó và sau đó trả về kết quả trong một bảng sau đó được chuyển đến ứng dụng lưu trữ (Microsoft Excel, Power BI, SSAS, SSIS, v.v.).
với các bảng có tầm quan trọng cơ bản như vậy, chúng ta có thể khám phá rất nhiều điều. Cả một loạt bài có thể được viết về các hàm bảng thư viện tiêu chuẩn. Mặc dù điều đó rất thú vị nhưng trọng tâm của loạt bài này là ngôn ngữ M, không phải thư viện, vì vậy những thứ liên quan đến ngôn ngữ này là những gì tôi sẽ đi sâu sau đây.
Trong bài trước đã kết luận với suy nghĩ rằng các bảng có hoạt động tương tự như danh sách (list) và bản ghi (record) nhưng vượt xa những gì chúng ta sẽ nhận được nếu chúng ta cố gắng tạo bảng mô phỏng của riêng mình bằng cách sử dụng hai loại đó. Hãy bắt đầu bằng cách xem xét một cách mà bảng và danh sách tương tự nhau như thế nào.
Lựa chọn vị trí
Với kiểu danh sách, cách các phần tử có thể được truy cập bằng chỉ mục vị trí bằng cách sử dụng toán tử lựa chọn Ví dụ: myList{2} trả về phần tử thứ ba từ myList. (Chỉ mục của M dựa trên 0, vì vậy phần tử đầu tiên được coi là ở chỉ mục 0, phần tử thứ hai ở chỉ mục 1, v.v.)
Kiểu bảng cũng hỗ trợ toán tử lựa chọn, chỉ mục xác định hàng nào sẽ trả về. Hàng đã xác định được trả về dưới dạng bản ghi (record), với mỗi cột được biểu diễn dưới dạng một trường trong bản ghi.
let
PlatformParts = #table(
{ "Code", "Category", "Group" },
{
{ 123, "PowerBI", "A" },
{ 456, "PowerApps", "B" },
{ 789, "PowerBI", "B" }
}
)
in
PlatformParts{1}
// trả về hàng thứ hai dưới dạng bản ghi: [Code = 456, Category = "PowerApps", Group = "B"]
Ở ví dụ trên, hàm thư viện #table được sử dụng để tạo bảng. Trong ví dụ này, đối số đầu tiên là danh sách tên cột. Thứ hai là danh sách các danh sách xác định giá trị hàng: mỗi mục trong danh sách ngoài tương ứng với một hàng; mỗi mục trong danh sách bên trong tương ứng với một giá trị cột cho hàng đó.
Nếu chỉ mục được yêu cầu không tồn tại, hành vi tìm kiếm phần tử giống như với kiểu danh sách: một lỗi được trả về nhưng điều này có thể được thay đổi thành null bằng cách thực hiện lựa chọn tùy chọn bằng cách thêm dấu hỏi ? Vào sau dấu ngoặc nhọn.
PlatformParts{3} // Expression.Error: Không có đủ phần tử trong bảng liệt kê để hoàn thành thao tác.
PlatformParts{3}? // null Thư viện hàm tiêu chuẩn chứa nhiều hàm hoạt động với các hàng trong bảng dựa trên vị trí. Ví dụ: Table.SingleRow, Table.First, Table.Last, Table.Skip, Table.FirstN, Table.LastN, Table.Range, Table.PositionOf, Table.ReverseRows, ….
Lựa chọn dựa trên giá trị
Với kiểu bảng, toán tử lựa chọn cũng có thể được sử dụng để chọn một hàng dựa trên (các) giá trị trường. Để thực hiện việc này, bên trong dấu ngoặc nhọn, hãy chuyển một bản ghi xác định tiêu chí tìm kiếm để sử dụng. Mỗi tên trường phải tương ứng với một cột bảng để tìm kiếm và mỗi giá trị trường phải tương ứng với giá trị cần tìm kiếm trong cột cụ thể đó. Bản ghi không cần thiết phải chứa các trường cho mọi cột trong bảng; chỉ những trường muốn tìm kiếm mới cần đưa vào.
PlatformParts{[Code = 123]} // Kết quả [Code = 123, Category = "PowerBI", Group = "A"]
PlatformParts{[Category = "PowerApps"]} // [Code = 456, Category = "PowerApps", Group = "B"]
PlatformParts{[Category = "PowerBI", PriceGroup = "B"]} // [Code = 789, Category = "PowerBI", Group = "B"]Toán tử lựa chọn trả về, nhiều nhất một hàng, được biểu diễn dưới dạng một bản ghi. Nếu tiêu chí tìm kiếm khớp với nhiều hơn một hàng, lỗi sẽ được trả về. Sử dụng lựa chọn tùy chọn (bằng cách thêm dấu chấm hỏi như {[…]}?) Không loại bỏ lỗi. Lựa chọn tùy chọn chỉ ảnh hưởng đến hành vi khi không tìm thấy kết quả phù hợp, không ảnh hưởng khi tìm thấy nhiều kết quả phù hợp.
PlatformParts{[Category = "Other"]} // error - Expression.Error: từ khóa tìm kiếm không khớp với bất kỳ hàng nào trong bảng.
PlatformParts{[Category = "Other" ]}? // trả về null
PlatformParts{[Category = "PowerBI"]} // error - Expression.Error: từ khóa tìm kiếm khớp với nhiều hơn một hàng trong bảng.
PlatformParts{[Category = "PowerBI"]}? // lỗi tương tự như trên (sử dụng?)
Tìm kiếm cũng được thực hiện bằng cách sử dụng các giá trị của bản ghi tiêu chí. Nếu bạn muốn tự động quyết định hàng nào sẽ trả về, bạn sẽ cần sử dụng một biểu thức – có thể sử dụng thư viện hàm chuẩn
Truy cập cột (hay còn gọi là chọn trường)
Giống như kiểu bản ghi, kiểu bảng cũng hỗ trợ lựa chọn trường. Với bản ghi, tên trường quan tâm nằm giữa dấu ngoặc vuông (myRecord [First Name]) và giá trị của trường đó được trả về. Khi lựa chọn trường được thực hiện trên một bảng, tên cột quan tâm sẽ nằm giữa dấu ngoặc vuông (myTable [tên cột])… nhưng vì một cột có thể chứa toàn bộ danh sách giá trị, vậy nên giá trị được trả về là gì?
Danh sách được trả về chứa giá trị từ mỗi hàng cho cột được chỉ định, theo thứ tự các giá trị đó xuất hiện trong cột (vì vậy mục đầu tiên trong danh sách sẽ tương ứng với giá trị của cột từ hàng đầu tiên, mục thứ hai có giá trị từ hàng thứ hai, v.v.).
PlatformParts[Category] // returns { "PowerBI", "PowerApps", "PowerBI" }Bạn có thể xem xét chi tiết các phân nhánh của hành vi này. Bằng cách trả về một danh sách, M cho phép bạn làm việc với tập hợp các giá trị trong một cột bằng cách sử dụng các chức năng tương tự mà bạn sẽ sử dụng trên bất kỳ danh sách nào khác. Thư viện chuẩn của M không cần cung cấp một tập hợp các hàm bảng riêng biệt cho các thao tác trên một cột (Ví dụ: Table.DistinctValuesInColumn(PlatformParts, “Group”)) vì nó có thể hoạt động với một cột duy nhất dưới dạng danh sách (như List.Distinct ( PlatformParts[Group]))). Một cột là một danh sách, ít nhất là đối với các hàm danh sách có liên quan.
Phép chiếu
Có lẽ bạn có một bảng mà bạn chỉ muốn làm việc với (hoặc xuất) một số cột. Giống như kiểu bản ghi, kiểu bảng cũng hỗ trợ phép chiếu. Trong trường hợp bảng, phép chiếu tạo ra một bảng mới có chứa một tập hợp các cột được giảm bớt. Toán tử phép chiếu sử dụng cú pháp dấu ngoặc vuông với lựa chọn trường / cột.
PlatformParts[[Code], [Category]] // trả về một bảng hai cột bao gồm Code & CategoryNếu một cột được chỉ định không tồn tại, một lỗi sẽ được trả về; tuy nhiên, nếu phép chiếu được thực hiện thêm tùy chọn (bằng cách thêm dấu chấm hỏi ? vào sau dấu ngoặc vuông), (các) cột không tồn tại sẽ được đưa vào bảng mới với các giá trị được đặt thành null.
PlatformParts[[Code], [Price]] // Expression.Error: Không tìm thấy cột 'Price' của bảng.
PlatformParts[[Code], [Price]]? // trả về một bảng hai cột, trong đó các giá trị của 'Price' đều null (vì cột đó không tồn tại trong bảng nguồn) Tên cột phải được nhập cứng. Nếu bạn muốn cung cấp tên cột dưới dạng danh sách, chức năng thư viện hàm sẽ hỗ trợ Table.SelectColumns.
So sánh bảng
Nếu hai bảng chứa cùng số cột, có cùng tên cột và cùng số hàng, trong đó các cột cùng tên từ mỗi bảng có cùng giá trị ở mỗi vị trí (ví dụ: Col1, chỉ số 0 trong bảng 1 bằng Col1, chỉ số 0 trong bảng 2), toán tử bình đẳng (=) so sánh các bảng là bằng nhau. Nếu không (rõ ràng), sử dụng toán tử so sánh không bằng nhau (khác) (<>).
#table({"Col1"}, {{1}, {2}}) = #table({"Col1"}, {{1}, {2}}) // true
#table({"Col1"}, {{1}, {2}}) <> #table({"Col1"}, {{1}, {2}}) // false
#table({"Col1", "Col2"}, {{"A", "B"}, {1,2}, {3,4}}) = #table({"Col2", "Col1"}, {{"B", "A"}, {2,1}, {4,3}}) // true -- mặc dù các cột ở các vị trí khác nhau, chúng chứa các giá trị giống nhau ở các vị trí giống nhau, vì vậy bảng tương đương
#table({"Col1"}, {{1}, {2}}) = #table({"Col1"}, {{2}, {1}}) // false -- mặc dù các bảng chứa các hàng giống nhau, chúng được sắp xếp khác nhau, vì vậy các bảng không tương đương schema cột và metadata không nhất thiết phải khớp để các bảng được coi là bằng nhau.
let
Table1 = Table.TransformColumnTypes(#table({"Col1"}, {{1}}),{{"Col1", type any}}),
Table2 = Table.TransformColumnTypes(#table({"Col1"}, {{1}}),{{"Col1", type number}})
in
Table1 = Table2 // trả về true mặc dù các kiểu dự liệu cột khác nhau (any vs number)Có thể giải thích theo cách này: M coi hai bảng là bằng nhau nếu chúng chứa cùng một dữ liệu. Những thứ như thứ tự cột, schema và metadata là các mục phụ, không phải dữ liệu phù hợp, vì vậy không được đưa vào so sánh bình đẳng. (Ví dụ: bạn có thể lấy cùng một dữ liệu từ một bảng bất kể các cột của nó được đặt thứ tự như thế nào, vì vậy thứ tự cột không được tính vào quyết định “các bảng này có bằng nhau không?”.)
Kết hợp các bảng
Hai bảng có thể được kết hợp bằng cách sử dụng toán tử kết hợp (&).
#table({"TinhThanh", "QuanHuyen"}, {{"HCM", "Binh Thanh"}}) & #table({"TinhThanh", "QuanHuyen"}, {{"HN", "Hoan Kiem"}})Các cột được ghép nối theo tên, không phải vị trí. Nếu một cột chỉ tồn tại trong một trong các bảng, thì giá trị của cột đó sẽ được đặt thành null cho các hàng từ bảng khác.
#table({"TinhThanh"}, {{"HCM"}}) & #table({"QuanHuyen"}, {{"Tan Binh"}})Trong bảng trả về, tất cả các cột từ bảng đầu tiên được xuất đầu tiên, được định vị dựa trên thứ tự xuất hiện trong bảng đó (ví dụ: cột đầu tiên từ bảng đó được xuất đầu tiên, cột thứ hai đến thứ hai, v.v.). Sau đó, bất kỳ cột nào chỉ có trong bảng thứ hai sẽ được xuất ra, được định vị dựa trên vị trí chúng xuất hiện trong bảng đó (ví dụ: cột đầu tiên dành riêng cho bảng đó được xuất, tiếp theo là cột dành riêng thứ hai, v.v.).
#table({"TinhThanh", "QuanHuyen"}, {{"HCM", "Binh Thanh"}}) & #table({"QuanHuyen", "TinhThanh"}, {{"Hoan Kiem", "HN"}})Trong M không yêu cầu rằng các kiểu dữ liệu cột phải tương thích. Kết quả từ ví dụ bên dưới chứa một giá trị chuỗi và một giá trị số.
#table({"Age"}, {{18}}) & #table({"Age"}, {{"23"}})
// "23" là kiểu dự liệu văn bản 'text'Ba hành vi sau hơi khác so với toán tử UNION ALL của SQL. Với SQL, việc kết hợp hai bảng yêu cầu các bảng chứa cùng số cột, các cột được kết hợp dựa trên vị trí (không phải tên) và các cột được kết hợp phải có kiểu dữ liệu tương thích. Với Power Query M, các bảng có số lượng cột không giống nhau có thể được nối với nhau, các cột được ghép nối theo tên (không phải vị trí) và kiểu dữ liệu cột không liên quan cho các mục đích kết hợp bảng.
Kết luận
Như đã được đề cập, thư viện tiêu chuẩn chứa nhiều hàm xung quanh các bảng, nhưng vì nằm ngoài ngữ cảnh của loạt bài này về ngôn ngữ Power Query M.
Có cú pháp mà chúng ta vừa đề cập, và sau đó là môi trường mà cú pháp đó được thực thi. Về bản chất, Power Query M xử lý các bảng như thế nào. Streaming, query folding, lớp bảo vệ dữ liệu / tường lửa… tất cả những điều này có thể có tác động lớn đến hiệu suất và / hoặc hành vi của cách M xử lý bảng của bạn.
