Hướng dẫn tạo bảng date trong Power BI bằng Excel

Sử dụng Excel để tạo bảng date trong Power BI không chỉ giúp tiết kiệm thời gian mà còn tăng tính linh hoạt trong phân tích dữ liệu và tạo báo cáo. Bởi vậy, bài viết này sẽ hướng dẫn các bảng sử dụng Excel để tạo bảng date hoàn chỉnh, và sử dụng nó trong Power BI. Đi kèm với các thao tác tạo bảng date, bài viết cũng sẽ hướng dẫn các bạn thêm cột trong bảng date để đánh dấu (flag) ngày đó có phải là ngày như nghỉ lễ hay ngày mở cửa giao dịch (đối với chứng khoán).

Tìm hiểu về bảng date trong Power BI

Bảng date trong Power BI là gì?

Bảng Date (Calendar Table hay Date Table) là bảng chứa dữ liệu liên quan đến thời gian (bao gồm ngày-tháng-năm-giờ) và rất hữu hiệu trong việc sử dụng để tính toán các chỉ số cần tham chiếu theo thời gian.

Vì sao cần tạo bảng date trong Power BI, và khi tạo thì cần đáp ứng một số tiêu chí như nào để hiệu quả? Chi tiết bạn có thể tham khảo tại bài viết: Tự học Power BI 15: Điều kiện và cách xây dựng Date Table

Cách tạo bảng date trong Power BI bằng Excel

Cách 1. Dùng Series để tạo cột Date

• Điền giá trị ngày mở đầu bạn mong muốn. Trong thẻ Home\Fill\Series.

• Trong bảng Series, lần lượt click chọn các giá trị trong ‘Series in’, ‘Type’, ‘Date unit’ và điền giá trị ngày cuối cùng trong mục ̀ ‘Stop value’

Dùng Series để tạo cột Date sẽ nhanh và dễ dàng. Tuy nhiên khi dữ liệu được cập nhập thêm thì người dùng sẽ phải thao tác lại một lần nữa để thêm dữ liệu mới cho cột Date. Bởi vậy, chúng ta sẽ đến với cách tối ưu hơn, dùng hàm SEQUENCE để tạo cột Date, giúp chúng ta bớt thao tác và cập nhật lại dữ liệu cho cột Date linh hoạt hơn.

Cách 2: Tạo cột Date bằng hàm SEQUENCE

• Tại cột Date dùng hàm SEQUENCE
• Cú pháp của hàm: SEQUENCE(rows, [columns], [start], [step])

= SEQUENCE($I$2- $I$1, ,I1,1)

Giải thích thông số

• Rows: Số hàng nhất định cho dãy số (I$2- $I$1: Giá trị ngày cuối trừ đi ngày đầu)
• Columns: Số cột của dãy số. Nếu không cho giá trị, theo công thức sẽ tự mặc định là 1.
• Start: Giá trị bắt đầu của dãy số. Nếu không cho giá trị, theo công thức sẽ tự mặc định là 1 (I1: ngày đầu tiên)
• Step: Khoảng cách giữa các giá trị. Nếu không cho giá trị, theo công thức sẽ tự mặc định là 1 (1: Khoảng cách là 1 ngày)

Kết quả

Ngoài cột Date, báo cáo của bạn có thể mở rộng thêm các cột:

• day_number_of_week (ngày trong tuần) = WEEKDAY([cột date])
• english_day_name_of_week (thứ trong tuần) = TEXT(WEEKDAY([cột date]),”dddd”)
• day_number_of_month (ngày trong tháng) = DAY([cột date])
• week_number_of_month (tuần trong tháng) = WEEKNUM(B2,)
• month_number_of_year (tháng trong năm) = MONTH([cột date])
• english_month_name (định dạng tháng theo tên) =TEXT(MONTH([cột date]),”mmmm”)
• Year (năm) = YEAR([cột date])
• English_year (định dạng năm theo tên) = TEXT(YEAR([cột date]),”mmmm”)

Sau khi tạo bảng date chính, cùng với bảng phụ (Flag) sẵn có chứa giá trị ngày nghỉ lễ và ngày giao dịch, chúng ta sẽ dùng hàm VLOOKUP để lookup giá trị cột flag_holidayda, flag_trading_day trong bảng date chính:

VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])

Lookup giá trị cột flag_holiday: = VLOOKUP(B2,Flag!$B$2:$C$2704,2,FALSE)

Lookup giá trị cột flag_trading_day: = VLOOKUP(B2,Flag!$B$2:$D$2704,3,FALSE)

Trong đó:

• Lookup_values: Cột tham chiếu
• Table_array: Là vùng phạm vi dò dữ liệu. Lưu ý: Để tham chiếu đến khu vực này, mình đặt tiền tố cho tham chiếu phạm vi theo tên của trang tính và thêm dấu chấm than ở sau. Ví dụ: Flag!$B$2:$C$2704
• Col_index_num: thứ tự cột cần lấy dữ liệu trong phạm vi của Table_array
• Range_lookup: Được mặc định là FALSE để tìm kiếm kết quả chuẩn xác nhất.

Kết quả:

Upload và đánh dấu bảng date trên Power BI

Bước 1: Upload dữ liệu lên Power BI

• Mở file Power BI, trong giao diện Report View, chọn Home\Get Data\Excel workbook

• Chọn đường dẫn đến nơi chứa file Date và load dữ liệu vào Power BI

Kết quả:

Bước 2: Đánh dấu Date Table (Mark as Date Table

Thao tác đánh dấu 1 bảng là bảng ngày tháng chính thức (Date Table chính thức) được xem là “best practice” trong Power BI vì thao tác này giúp Power BI nhận diện đâu là bảng Date chính trong trường hợp bộ dữ liệu có nhiều cột giá trị thời gian. Các bước thực hiện tao tác này như sau:

• Click chuột phải vào Date Table vừa tạo, chọn Mark as date table

• Khi cửa sổ mới xuất hiện, chọn cột Date trong Date Table

Tổng kết

Một báo cáo Power BI tốt nên có 1 bảng Date để dễ dàng quản lý dữ liệu theo thời gian. Việc áp dụng kỹ thuật tạo bảng Date trên Excel hỗ trợ lưu trữ, quản lý và phân tích dữ liệu, tối ưu hóa các thao tác xử lý.

Tuy nhiên, trong trường hợp không có nhiều cột chứa giá trị có quy luật phức tạp như cột ngày nghỉ lễ hay cột ngày giao dịch, chúng ta có thể tạo bảng date trực tiếp trên Power BI để tối ưu hóa quy trình. Các bạn có thể đọc thêm mốt số cách như tạo bảng date sử dụng Power Query hoặc DAX tại bài viết: Tự học Power BI 15: Điều kiện và cách xây dựng Date Table

hoang