SQL vốn đã trở thành một ngôn ngữ trích xuất dữ liệu (query language) chuẩn mực. Đối với các bạn analyst thì SQL là một ngôn ngữ không thể thiếu. Nhiều bảng báo cáo dữ liệu được trích xuất trên SQL Server bằng các câu lệnh được tạo sẵn để sử dụng và kể cả với những bạn dung Power BI để trực quan hóa dữ liệu thì việc sử dụng các câu lệnh SQL là một cách tiết kiệm thời gian. Tuy nhiên, những câu lệnh SQL được áp dụng bằng việc copy – paste vào trong Power Query có thể cần những thay đổi linh hoạt bởi người dùng hoặc người xem báo cáo.

Để làm được điều này, hôm nay mình sẽ đề cập đến cách dùng Parameter trong Power Query giúp thay đổi cách trích xuất dữ liệu và làm thay đổi báo cáo một cách linh hoạt. Nào, hãy cùng nhảy vào ví dụ thực tế nhé.

Kết nối với dữ liệu trên SQL Server

Trong ví dụ này, mình có cài đặt một kho dữ liệu WideWorldImporter trên SQL Server ở trên Azure Cloud và mình sẽ kết nối dữ liệu bán hàng bằng Power BI. Để kết nối với SQL Server dù là trong máy tính của bạn (On-premise) hay trên điện toán đám mây (Cloud) đều vô cùng đơn giản và rõ ràng. Chúng ta sẽ kết nối bằng Get Data -> SQL Server hoặc ấn ngay trên thanh công cụ SQL Server để kết nối với cơ sở dữ liệu này.

Sau đó, chúng ta sẽ điền các thông tin cần thiết để kết nối với dữ liệu này bao gồm:

  • Tên server: trong trường hợp này sẽ là server trên Azure của mình (mình tạm ẩn thông tin này)
  • Tên database: trong trường hợp này, ta sẽ để kho dữ liệu “WideWorldImportersDW-Standard”
  • SQL Statement: trong trường hợp này, chúng ta sẽ dùng query trích xuất dữ liệu bán hàng trong năm 2015 giới hạn bởi ngày xuất hóa đơn từ 01/01/2015 đến 31/12/2015

Kết quả là chúng ta có một bảng dữ liệu chính xác với yêu cầu chỉ với 1 bước trong Power Query:

Tạo parameter (giá trị biến đổi)

Giả sử sếp bạn muốn xem một báo cáo được dựng từ dữ liệu bán hàng như trên nhưng thay vì trong năm 2015, sếp bạn muốn xem dữ liệu này ở năm 2016 hoặc thậm chí là trong một khoảng thời gian bất kì với Start_Date và End_Date được đưa vào như những input? Tất nhiên là bạn hoàn toàn có thể tạo một file báo cáo tương tự và chỉnh 2 ngày trong Query trên. Tuy nhiên nếu làm như vậy thì chắc bạn phải tạo ra hàng chục thậm chí là trăm báo cáo khác nhau mất. Và việc lặp đi lặp lại quá trình thay đổi câu query một cách thủ công như vậy sẽ vô cùng tốn thời gian phải không nào?

Mình sẽ hướng dẫn mọi người cách sử dụng Parameter để thay đổi câu query trên cũng như dữ liệu của báo cáo này nhé! Tất nhiên để sử dụng parameter thì trước tiên, chúng ta phải tạo ra parameter rồi. Để tạo parameter, bạn đơn giản chỉ cần chọn Home -> Manage Parameter -> New Parameter

Một cửa sổ mới sẽ hiện ra để chúng ta bổ sung các thông tin của Parameter này. Trong trường hợp này, chúng ta cần tạo ra parameter với dạng ngày (Date) và chúng ta sẽ cài đặt như sau:

  • Tên Parameter (Name): chúng ta sẽ đặt là Start_Date để ám chỉ ngày bắt đầu
  • Loại dữ liệu (Type): chúng ta sẽ lựa chọn định dạng ngày cho parameter này
  • Giá trị hiện tại (Current Value): chúng ta sẽ lựa chọn giá trị là ngày đầu của năm 2015 theo đúng ví dụ

Sau khi ấn OK, chúng ta sẽ thấy Power Query tạo ra một giá trị biến số ở bên trái với kí hiệu biến số và giá trị hiện tại là 1/1/2015

Vậy là chúng ta đã có Input Query đầu tiên để trích xuất dữ liệu từ bảng Sales giữa trên ngày bắt đầu. Bước tiếp theo, chúng ta sẽ tạo nốt Parameter cho ngày kết thúc End_Date.

Kết hợp Parameter vào trong Query

Sau khi đã chuẩn bị 2 Parameter cho việc thay đổi query, giờ chúng ta chỉ cần gán những giá trị biến số trên vào các hàm SQL ở bước Source trong Power Query. Nếu các bạn để ý khi ấn vào bước source, trên thanh hàm M-language, chúng ta sẽ thấy công thức M đại diện cho bước trích xuất dữ liệu.

Trong trường hợp này, chúng ta có tên của Server, tên của cơ sở dữ liệu và câu Query chúng ta đã sử dụng. Tuy nhiên bởi vì câu Query được copy-paste nên chúng sẽ là các chuỗi text không đổi. Điệu chúng ta cần làm đó chính là thay thế các giá trị bất biến trong Query bằng các giá trị biến số Start_Date và End_Date. Chúng ta sẽ làm như sau:

  • Tập trung vào giá trị cần thay đổi và highlight giá trị đó, Parameter trong trường hợp này chính là thay thế cho 2 giá trị ngày trong Query đó là 01/01/2015 và 12/31/2015
  • Sau khi highlight, chúng ta sẽ thay đổi giá trị ngày khởi đầu 01/01/2020 bằng Start_Date

Bởi vì chúng ta sẽ sử dụng một biến số thay cho một giá trị không đổi, biến số của chúng ta sẽ cần bổ sung dấu “” trước câu lệnh trước để kết thúc đoạn text và sau đó đê chèn giá trị biến Start_Date, chúng ta sẽ bổ sung dấu & ở trước nó.

Kết cục là chúng ta sẽ có một Query thay vì sử dụng 2 ngày được nhập chuẩn không đổi dưới dạng text là 01/01/2015, chúng ta có một query sử dụng 2 biến Start_Date và End_Date giúp thay đổi trực tiếp câu trích xuất dựa trên 2 biến ngày này. Tuy nhiên khi ấn Enter, chúng ta sẽ gặp lỗi sau:

Tại sao vậy? đơn giản thôi bởi vì 2 giá trị Start_Date và End_Date của chúng ta được coi là ngày và bạn có thể thấy lỗi báo thể hiện rằng chúng ta không thể kết hợp giá trị Text và giá trị Date bằng dấu logic &. Điều chúng ta đang muốn làm ở đây đó chính là sử dụng Start_Date và End_Date như những giá trị biến động nhưng được thay thế vào câu query là một chuỗi text không đổi. Vì vậy, để làm được điều này, chúng ta phải biến 2 giá trị ngày này thành giá trị text nhưng thay đổi theo giá trị parameter.

Để làm được như vậy, chúng ta sẽ bổ sung một chút công thức vào trong 2 giá trị Start_Date và End_Date với hàm Text.From như dưới đây:

Và sau khi thay thế với công thức trên, Query của chúng ta thành công trả về bảng dữ liệu chuẩn mực cho bảng Sales giữa 2 ngày Start_Date và End_Date.

Và khi thay đổi 2 giá trị Start_Date và End_Date này sang 2016, chúng ta sẽ có bảng Sales được cập nhập tương ứng dựa trên câu query của chúng ta như dưới đây.

Cập nhập Parameter và refresh trên Power BI Service

Sau khi sử dụng Parameter trong Power Query thành công, chúng ta sẽ tạo nhanh một Area Chart cơ bản để thể hiện xu hướng về Profit qua từng tháng theo ngày cấp hóa đơn như dưới đây. Sau đó chúng ta sẽ upload lên Power Bi Service để kiểm tra cách cập nhập parameter và khiến biểu đồ này thay đổi ra sao.

Sau khi upload báo cáo này lên Power BI Service, chúng ta sẽ có báo cáo tương tự hiện tại đang nhìn vào xu hướng bán hàng của 5 tháng đầu 2016 (lí do vì trong dữ liệu mẫu này chỉ có đến tháng 5 của 2016 mà thôi mặc dù trong parameter ta để End_Date đến tháng 12).

Như mình đã nói, Power BI Service hỗ trợ cập nhập dữ liệu từ Data Source bao gồm cả việc thay đổi Parameter thay vì việc bạn phải quay trở về Power BI Desktop để thay đổi rồi upload lại. Sau đây sẽ là cách chúng ta dung Power BI Service để cập nhập Query của data và biểu đồ trên để thể hiện xu hướng của lợi nhuận từ năm 2016 sang năm 2015. Ở trên Power BI service, chúng ta sẽ tìm Data của báo cáo trên bằng cách vào

Workspace -> Datasets + dataflows -> chọn kí hiệu 3 chấm -> Settings (mọi người chú ý mình đang có New look on hiện ở trên nên có thể giao diện sẽ khác mọi người một chút nếu mọi người đang nhìn vào giao diện cũ)

Sau khi chọn Settings, chúng ta sẽ thấy một cửa sổ hiện ra với các cài đặt về dữ liệu của báo cáo này. Chúng ta sẽ mở phần Parameters ra và sẽ thấy ngay 2 parameter mà chúng ta đã cài trong Power Query

Chúng ta sẽ thay đổi giá trị của 2 ngày này sang thành 01/01/2015 và 12/31/2015 (mình hiện đang dung giao diện tiếng Anh vì vậy loại ngày sẽ sử dụng format tiếng Anh nhé mọi người) và sau đó ấn Apply để thay đổi.

Việc thay đổi Parameter sẽ không thay đổi dữ liệu của báo cáo hiện tại ngay vì bước thay đổi này ở trong Power Query và các bạn hãy nhớ Power Query không giống như Power Pivot là thay đổi trên giao diện báo cáo. Nó thay đổi trong query và cần được refresh dữ liệu để thực hiện lại quy trình cập nhập dữ liệu cũng như áp dụng parameter này lên các query để cập nhập bảng dữ liệu trong báo cáo. Do đó, chúng ta sẽ quay lại giao diện trước và ấn nút refresh data để cập nhập dữ liệu sử dụng 2 Parameter mới năm 2015.

Sau khi dữ liệu được cập nhập, tức là Power Query sẽ chạy lại các bước trích xuất và thay đổi dữ liệu khiến dữ liệu trong báo cáo của chúng ta không còn nhìn vào 2016 nữa mà được cập nhập để nhìn vào 2015.

Kết luận

Vậy là qua bài blog này, mình đã chỉ cho các bạn cách sử dụng Parameter trong Power Query để thay đổi cấu trúc query trích từ SQL server. Không chỉ là đối với SQL server hay parameter dạng Date, mọi người có thể sử dụng với bất cứ nguồn dữ liệu bất kỳ và với bất kỳ loại parameter nào. Tuy nhiên, hãy nhớ rằng khi thay đổi cấu trúc của M-language, hãy sử dụng những Parameter này để thay đổi query như một kí tự text từ đó có thể đưa các biến số vào trong một dãy Query bất biến và giúp chúng ta có thể cập nhập dữ liệu một cách linh hoạt hơn.

Nào, còn chờ đợi gì nữa mà hãy thử ứng dụng bài học ngày hôm nay vào nghiệp vụ của bản thân nhé. Chúc các bạn có một bản báo cáo linh hoạt hơn bao giờ hết. Xin chào và hẹn gặp lại trong những thử thách tiếp theo. Happy Analyzing!

—————————————

Thông tin liên hệ: 

CÔNG TY CỔ PHẦN KPIM 

Email: [email protected] 

Facebook: https://www.facebook.com/kpim.vn 

LinkedIn: https://www.linkedin.com/company/kpim/ 

Instagram: https://www.instagram.com/kpim.vn/ 

Phone: 091.668.2020