Microsoft Power BI là một phần mềm trực quan hóa dữ liệu tương tác tuyệt vời tập trung vào trí tuệ kinh doanh. Đó là một tập hợp các ứng dụng, dịch vụ phần mềm và trình kết nối làm việc cùng nhau để chuyển đổi các nguồn dữ liệu không liên quan thành thông tin chi tiết mạch lạc, trực quan và tương tác.
Bạn đã bao giờ bị mắc kẹt trong một tình huống liên quan đến tập dữ liệu và muốn giải quyết các công thức khác nhau bằng cách thêm, bớt hoặc sửa đổi chúng chưa? Đôi khi chúng ta có thể muốn tìm, thay đổi và thay thế các công thức. Hãy để UniTrain hướng dẫn bạn về cách xử lý những tình huống này.
Thanh công thức trong Power Query Editor
Power BI cung cấp cho chúng ta một thanh công thức Power-Query. Hãy đi từng bước và khám phá Formula Bar trong Power Query Editor:
Đi tới File ở menu trên cùng bên trái:
Đi tới Options and Settings:
Nhấp vào tùy chọn “Display the Formula Bar” trong “Options and Settings“.
Bây giờ bạn có thể thấy Formula Bar trong Query Editor.
Công thức thực hiện tất cả các chuyển đổi truy vấn trong Power Query. Một công thức được thêm vào thanh công thức khi bạn sử dụng trình tạo Query Editor. Bạn có thể muốn thêm một công thức khác với những công thức được liên kết với một trình tạo hoặc bạn có thể muốn thay đổi một công thức hiện có.
Trước khi đi sâu hơn, chúng ta sẽ tìm hiểu sự khác biệt giữa DAX và M Language.
Sự khác biệt giữa ngôn ngữ DAX và ngôn ngữ M là gì?
Có hai ngôn ngữ mà Microsoft Power BI sử dụng để chuyển đổi, lọc, quản lý và trực quan hóa dữ liệu; Ngôn ngữ M và DAX (Data Analysis Expression).
Ngôn ngữ M có thể được coi là ngôn ngữ công thức truy vấn được sử dụng trong Power BI Query Editor để chuẩn bị dữ liệu trước khi tải vào mô hình Power BI. Ngược lại, DAX là một ngôn ngữ tính toán dữ liệu phân tích được sử dụng để phân tích dữ liệu chuyên sâu trong giai đoạn Data View.
Công cụ thước đo/cột cung cấp bổ sung tất cả thông tin liên quan đến thước đo hoặc cột, tương ứng. Biểu thức DAX bao gồm một công thức theo sau là một số đo hoặc một tham chiếu cột.
Cách thêm công thức vào truy vấn Power BI
Các truy vấn có thể được thêm vào formulas trong Power BI. Khi tạo một công thức, Power Query xác nhận cú pháp công thức. Khi chèn hoặc xóa một bước trung gian trong một truy vấn, ta có thể làm hỏng một truy vấn. Power Query sẽ hiển thị cảnh báo Insert Step khi cố gắng chèn một bước mới.
Các bước thực hiện quy trình thêm công thức vào truy vấn:
- Trong ngăn bước truy vấn, hãy chọn bước bạn muốn trước bước mới ngay lúc đó.
- Nhấp vào biểu tượng hàm hoặc fx ở bên trái thanh công thức. Một công thức mới được tạo ở dạng = <nameOfTheStepToReference>. Ví dụ, = Production.Work.Order .
- Nhập công thức mới theo định dạng = Class.Function (ReferenceStep [, other-parameter]).
- Ví dụ: bạn có một bảng với cột Giới tính 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.”)
Tạo Calculated Column
Để tạo Calculated Column:
- Nhấp chuột phải vào Table.
- Nhấp vào New Column.
Hãy lấy Product Cost trong Sales Table từ Product Table.
Product Cost (CC) = RELATED(‘Product Table'[Product Cost])
Bây giờ, hãy tạo cột Revenue bằng cách nhân Quantity Sold đã bán với Sale Price.
Revenue (CC) = ‘Sales Table'[Quantity Sold]*’Sales Table'[Sale Price]
Bây giờ, hãy tạo cột Total Cost bằng cách nhân Quantity Sold với Product Cost.
- Nhấp chuột phải vào Sales Table.
- Nhấp vào New Column.
- Nhập công thức DAX sau vào Formula Bar.
Total Cost (CC) = ‘Sales Table'[Quantity Sold]*’Sales Table'[Product Cost (CC)]
Tất cả ba cột, tức là cột Total Cost (CC), cột Revenue (CC) và cột Product Cost (CC), đã được tạo, đưa ra các giá trị cho hàng nào của Bảng.
Các cột được tính toán có một biểu tượng riêng biệt, như được thấy trong ảnh chụp màn hình, để phân biệt chúng với các thực thể dữ liệu khác.
Cách chỉnh sửa công thức Power BI
Hai cách để thay đổi công thức BI nguồn là sử dụng hộp thoại và thanh công thức.
Chỉnh sửa công thức bằng hộp thoại của công thức
- Trong ngăn bước truy vấn, bấm chuột phải vào bước bạn muốn chỉnh sửa.
- Từ menu ngữ cảnh, chọn Edit Settings.
- Trong hộp thoại, hãy chỉnh sửa công thức.
Chỉnh sửa công thức trong thanh công thức
- Trong ngăn bước truy vấn, hãy chọn bước bạn muốn chỉnh sửa.
- Trong thanh công thức, xác định vị trí và thay đổi các giá trị tham số thành các giá trị bạn muốn.
- Nhấp vào Refresh.
Trình Query-Editor xuất hiện khi bạn tải, chỉnh sửa hoặc tạo một truy vấn mới bằng Power Query. Để xem Query Editor mà không cần tải hoặc chỉnh sửa truy vấn hiện có trên sổ làm việc, hãy chọn From Other Sources >> Blank Query, từ phần Get External Data trong tab ribbon Power Query.
Công thức Filter Power BI
Tạo Filter trên Bảng được sử dụng trong Công thức
Power BI cho phép chúng ta áp dụng các Filter trong các công thức lấy bảng làm đầu vào. Vì vậy, thay vì nhập tên bảng, chúng ta có thể sử dụng hàm FILTER để xác định một tập hợp con các hàng từ Bảng. Tập hợp con đó có thể được chuyển cho một hàm khác cho các hoạt động khác nhau như tổng hợp tùy chỉnh.
Ví dụ: giả sử chúng ta có một bảng dữ liệu chứa thông tin đặt hàng về người bán lại và UniTrain muốn tính toán công thức cho doanh số bán hàng từ mỗi người bán lại. Tuy nhiên, UniTrain muốn hiển thị số lượng bán hàng chỉ dành cho những người bán lại đã bán nhiều đơn vị sản phẩm có giá trị cao.
Dựa trên sổ làm việc mẫu Power BI DAX, sử dụng công thức Filter, UniTrain có thể tạo phép tính:
=SUMX(FILTER (‘ResellerSales_EURO’, ‘ResellerSales_EURO'[Quantity] > 10 &&
‘ResellerSales_EURO'[ProductStandardCost_EURO] > 200),
‘ResellerSales_EURO'[SalesAmount])
Các hàm trả về một bảng, chẳng hạn như FILTER, sẽ không bao giờ trực tiếp trả về các hàng hoặc Bảng mà sẽ luôn được nhúng trong một hàm khác.
Biểu thức FILTER bị ảnh hưởng bởi ngữ cảnh nơi nó được sử dụng, ví dụ: nếu chúng ta sử dụng FILTER trong một thước đo và thước đo được sử dụng trong PivotChart hoặc PivotTable, thì tập hợp con dữ liệu được trả về có thể bị ảnh hưởng bởi các bộ lọc hoặc Slice bổ sung mà người dùng đã áp dụng trong PivotTable.
Làm thế nào để sử dụng chức năng thay thế trong Power BI?
Hàm Replace trong DAX cho phép chúng ta thay thế một phần của chuỗi văn bản, dựa trên số ký tự được chỉ định, bằng một chuỗi văn bản khác. MS Excel có các hàm khác nhau để sử dụng với các ngôn ngữ ký tự byte đơn và byte kép nhưng DAX sử dụng Unicode. Do đó DAX lưu trữ tất cả các ký tự có cùng độ dài. Chức năng thay thế không được hỗ trợ trong chế độ DirectQuery khi được sử dụng trong các quy tắc bảo mật cấp hàng (RLS) hoặc các cột được tính toán.
Cú pháp Hàm Replace điển hình trông giống như sau:
REPLACE (<old_text>, <start_num>, <num_chars>, <new_text>)
Ví dụ: để tạo một cột được tính toán mới thay thế hai ký tự đầu tiên của cột mã sản phẩm [ProductCode] bằng mã hai ký tự mới, OB, UniTrain sẽ sử dụng công thức sau:
= REPLACE(‘New Products'[Product Code],1,2,”OB”
SUBSTITUTE khác với REPLACE như thế nào?
Chức năng Substitute sẽ thay thế văn bản hiện có bằng văn bản mới trong một chuỗi văn bản. Sử dụng hàm SUBSTITUTE khi UniTrain muốn thay thế văn bản cụ thể trong một chuỗi văn bản. Sử dụng hàm REPLACE khi muốn thay thế bất kỳ văn bản nào có độ dài thay đổi xuất hiện ở một vị trí cụ thể trong một chuỗi văn bản. Hàm SUBSTITUTE phân biệt chữ hoa chữ thường, tức là nếu chữ hoa không khớp giữa văn bản và văn bản cũ, hàm SUBSTITUTE sẽ không thay thế văn bản đã cho. Nó không được hỗ trợ trong chế độ DirectQuery khi được sử dụng trong các quy tắc bảo mật cấp hàng (RLS) hoặc các cột được tính toán.
Cú pháp xuất hiện như sau:
SUBSTITUTE (<text>, <old_text>, <new_text>, <instance_num>)
Để tạo một bản sao của cột [Mã sản phẩm] để thay thế mã sản phẩm mới NW cho mã sản phẩm cũ PA khi nó xảy ra trong cột, chúng ta sẽ sử dụng mã sau:
= SUBSTITUTE([Product Code], “NW”, “PA”)
UniTrain lược dịch
Xem thêm