Power Query – Câu lệnh If cho Logic có điều kiện
Trong bài viết trước, chúng ta đã cùng tìm hiểu sơ về cách hoạt động của Power Query, bây giờ Học Excel Online cùng bạn sẽ đi sâu hơn một chút và hiểu về câu lệnh If Power Query nhé!
Trong Excel, IF có thể nói là một hàm cốt lõi, nó là một trong những hàm đầu tiên chúng ta phải học. Ở bài trước chúng ta đã xem xét các Hàm trong Power Query nhưng chưa đề cập đến phiên bản Power Query của hàm IF. Chắc chắn có một hàm if… phải không? … Có lẽ vậy.
Để sử dụng logic if Power Query, chúng ta cần một phương pháp dựa trên lập trình thay vì ràng buộc phương pháp dựa trên hàm mà chúng ta tìm thấy trong Excel. Nếu bạn đã từng lập trình VBA hoặc các ngôn ngữ khác, chắc bạn sẽ quen với cách tiếp cận này. Tuy nhiên, nếu bạn chỉ tiếp cận Excel ở mức cơ bản, điều này có thể mới đối với bạn.
Bạn có hai lựa chọn:
- Sử dụng tính năng cột có điều kiện cho logic if cơ bản
- Viết mã M để nâng cao hơn nếu logic.
Mình sẽ đề cập đến cả hai trong bài viết này.
Xem nhanh
Tải xuống
Như thường lệ để có tài liệu cho tiết học này bạn hãy xem lại bài viết của mình về Power Query. Thêm vào đó là để tiếp cận các ví dụ bên dưới, bạn hãy tải xuống các tệp mẫu nhé. Nhấp vào đây để đăng ký và có quyền truy cập vào phần Tải xuống.
Các ví dụ trong bài này sử dụng tệp có tên Example 15 – If Function.xlsx
Ví dụ
Tệp chỉ chứa một Bảng. Chọn bất kỳ ô nào trong Bảng đó và nhấp vào Data -> From Table/Range để tải dữ liệu vào Power Query.
Trong ví dụ này, chúng ta sẽ tính toán giá trị giả sử như sau:
Tình huống 1: Vào chủ nhật có thêm 10% phí bảo hiểm (yêu cầu logic cơ bản)
Tình huống 2: Vào chủ nhật có thêm 10% phí bảo hiểm, với hai sản phẩm (Tiger & Farmhouse Bloomer) cũng được giảm giá 5% vào ngày hôm đó (yêu cầu logic nâng cao).
Logic If sử dụng cột điều kiện
Hãy bắt đầu với Tình huống 1 và sử dụng tính năng Conditional Column (Cột có điều kiện)
Tình huống 1 – Phí bảo hiểm ngày Chủ Nhật
Chọn cột Date, sau đó thêm cột chứa ngày trong tuần bằng cách nhấp vào Add Column -> Date -> Day -> Day Name.
Tiếp theo, nhấp vào Add Column -> Conditional Column
Nhập các tùy chọn sau vào hộp thoại Add Conditional Column:
Tên cột mới: Sunday Premium
Nếu Day Name bằng Sunday thì 1.1 khác 1
Bấm OK để chấp nhận công thức.
Trong trường hợp trên, chúng ta chỉ sử dụng một số tùy chọn có sẵn trong hộp thoại Conditional Column.
Các tùy chọn có sẵn khác:
- Equals: Bằng
- Does not equal: không bằng
- Begins with: Bắt đầu với
- Does not begin with: không bắt đầu với
- Ends with: Kết thúc với
- Does not end with: Không kết thúc với
- Contains: Chứa đựng
- Does not contain: Không chứa
Trong hộp thoại Add Conditional Column, chúng ta có thể nhấp vào nút 123ABC để chèn giá trị cột hoặc tham số thay vì giá trị mã cứng.
Ngoài ra, chúng ta có thể tạo bao nhiêu câu lệnh IF tùy thích bằng cách nhấp vào nút Add rule. Các câu lệnh IF sẽ được thực hiện lần lượt, nghĩa là, khi câu lệnh if đầu tiên không được kích hoạt, nó sẽ chuyển sang câu lệnh thứ hai, rồi đến câu lệnh thứ ba, v.v. Tuy nhiên, nếu câu lệnh if đầu tiên là đúng, logic còn lại sẽ bị bỏ qua.
Cuối cùng, hãy thêm một công thức. Bấm Add Column -> Custom Column để hiển thị hộp thoại Custom Column.
Sau đó nhập công thức sau:
=[Value] * [Sunday Premium]
Chọn công thức Adjusted Value, sau đó bấm OK.
Ngoài ra, đối với giai đoạn cuối cùng này, bạn có thể sử dụng một cột nhân bằng cách Add Column -> Standard -> Multiply, nhưng mình thích cách tiếp cận Custom Column hơn.
Chắn hẳn bạn đã cảm thấy logic có điều kiện là khá dễ dàng… đúng không?
Tình huống 2 – Phí bảo hiểm ngày Chủ Nhật và giảm giá sản phẩm
Nếu bạn đang cố gắng sử dụng Cột có điều kiện với hai điều kiện trở lên, mọi thứ sẽ trở nên rất phức tạp, nếu không muốn nói là rất khó.
Mình đã xem qua một danh sách dài các chuyển đổi để minh họa cách làm với tình huống 2, nhưng mình sẽ không làm như vậy .. Tính năng Cột có điều kiện thực sự dành cho các yêu cầu cơ bản; nó không cho phép chúng ta sử dụng logic AND hoặc OR.
Thay vào đó, chúng ta sẽ chuyển sang tùy chọn 2, đó là tự viết mã Power Query M. Đừng lo lắng, mình tin tưởng vào bạn. Bạn chắc chắn làm được điều này 🙂
Logic IF Power Query bằng cách viết mã M
Trước khi bắt đầu với các ví dụ, hãy dành vài phút để hiểu cách hoạt động của Power Query
Trong Excel, hàm IF sẽ được viết dưới dạng:
=IF( [thing] = “thing 2”, [do this if true] , [do this if false] )
Vì vậy bạn có thể dễ dàng hơn khi nghĩ về nó như thế này:
bỏ dấu ngoặc mở và đóng
thay đổi dấu phẩy đầu tiên bằng then
thay đổi dấu phẩy cuối cùng bằng else
thay đổi IF cho if
Điều này sẽ tạo ra mã M sau đây
= if [thing] = “thing 2” then [do this if true] else [do this if false]
Bạn cứ làm vài lần thì sẽ nhớ. Nó có một lợi thế nữa là nó nghe giống như một câu tiếng Anh tiêu chuẩn, làm cho nó dễ hiểu hơn nhiều so với Excel.
Để lồng một if trong một if khác nếu chúng ta sử dụng cùng một phương pháp if – then – else, như được hiển thị bên dưới
= if [thing to to test #1] = “something else” then [do this if true]
else if [thing to to test #2] = “something else” then [do this if true] else
[do this if false]
Có thể có nhiều if lồng nha. Điều này hoạt động theo cách tương tự như việc thêm các quy tắc mới vào Cột có điều kiện.
Sử dụng logic AND hoặc OR cũng khá đơn giản, chúng ta chỉ sử dụng các từ and hoặc or trong mã. Đoạn mã dưới đây ví dụ sử dụng logic and.
= if [thing to to test #1] = “something else”
and [thing to to test #2] = “something else #2”
then [do this if true] else [do this if false]
Khi có sự kết hợp của cả logic and và logic or, cần có dấu ngoặc để đảm bảo logic đánh giá theo đúng thứ tự.
Đó là những điều cơ bản về if trong Power Query, còn bây giờ là lúc dành cho các ví dụ.
Bắt đầu lại từ đầu bằng cách tải lại Bảng vào Power Query. Sau đó, thêm cột tên Week Day như chúng ta đã làm trong ví dụ trên.
Tình huống 1 – Phí bảo hiểm Chủ Nhật
Thêm một cột tùy chỉnh vào bảng bằng cách nhấp vào Add Column -> Custom Column
Sử dụng công thức sau:
= if [Day Name] = “Sunday” then [Value] * 1.1 else [Value]
Hãy chú ý đến các từ if, then và else, chúng đều là chữ thường. Power Query có phân biệt chữ hoa chữ thường, vì vậy nếu chúng ta mắc lỗi này, công thức sẽ không hoạt động.
Bấm OK để thêm công thức.
Tình huống 2 – Phí bảo hiểm Chủ Nhật và giảm giá sản phẩm
Có một số cách để viết công thức này. Dựa trên tập dữ liệu của chúng ta, có ba kết quả có thể xảy ra cho tình huống này:
- Giảm giá sản phẩm & phí bảo hiểm Chủ nhật
- Chi phí bảo hiểm chủ nhật
- Không có phí bảo hiểm hay giảm giá
Giải pháp số 1:
= if [Day Name] = “Sunday” and ([Product] = “Tiger” or [Product] = “Farmhouse Bloomer”) then
[Value] * 1.1 * 0.95
else if [Day Name] = “Sunday”
then [Value] * 1.1
else [Value]
Chúng ta cần thêm một số dấu ngoặc vào đây để đảm bảo công thức thực thi theo đúng thứ tự. Chúng ta muốn phần tử or được tính toán trước, vì vậy chúng ta đặt phần này trong ngoặc.
Giải pháp # 2:
Đây là một giải pháp khác mà bạn có thể thử.
= if [Day Name] = “Sunday” then
if [Product] = “Tiger” or [Product] = “Farmhouse Bloomer” then [Value] * 1.1 * 0.95
else [Value] * 1.1
else [Value]
Kết luận
Logic có điều kiện sử dụng câu lệnh if trong Power Query khác với Excel. Tuy nhiên, nó không khó hiểu, chỉ cần bạn thực hành vài lần là sẽ thành thạo ngay. Mình tin bạn!
