Tạo bảng dữ liệu thời gian để liên kết và tham chiếu cũng như tạo bộ lọc thời gian bằng Power Query

Power Query ngoài việc lấy dữ liệu từ các nguồn khác nhau để lọc và làm sạch dữ liệu ứng dụng cho file Exel, Power Pivot hay Power BI thì còn có thể tạo ra những bảng dữ liệu thông qua các dòng lệnh để tạo các bảng dữ liệu.

Với Power Query bạn có thể tạo các bảng biểu thời gian để tạo một bảng dữ liệu để liên kết với các bảng phân tích nhằm tạo ra các bộ lọc dữ liệu trên Exel, Power Pivot hay Power BI.

Bước một: Tạo cột dữ liệu thời gian bằng công thức trong Power Query

Để làm được điều này đầu tiên bạn tạo một Blank Query như sau:

Tham khảo cách mở môt blank Query thông qua cách tạo các công thức Power Query ở đây

 

Cửa sổ Query Editor hiện ra và bạn chọn Advanced Editor 

Cửa sổ Advance Editor hiện ra và bạn dán đoạn mã sau vào:

let

startdate=#date(2020,1,1),

enddate=#date(2020,12,31),

Source = {Number.From(startdate)..Number.From(enddate)}

in

Source

Như sau, và chọn done

 

Dòng lệnh trên có nghĩa là tạo bảng dữ liệu từ ngày 1/1/2020 (startdate=#date(2020,1,1),) đến ngày 31/12/2020 (enddate=#date(2020,12,31),)

Cấu trúc của: date(Năm,tháng,ngày)

Sau khi chọn done thì trong Power query sẽ hiện ra bảng thời gian như sau:

 

Bước 2: Thay đổi định dạng dữ liệu từ dạng số sang định dạng thời gian và đổi tên côt trong Power Query

List thời gian có dạng serial number vì vậy cần chuyển về dạng Date như sau. 

Chọn cột list vưa được tạo ra và bôi xanh như trên trong bảng thời gian dạng serial number của Power Query, chọn tab Transform chọn To table

Hiện lên bảng trên và để các giá trị mặc định như sau và bấm OK

 

Lúc này bạn đã chuyển đổi được thành bảng dữ liệu thời gian, tuy nhiên dữ liệu thời gian vẫn ở dạng số. Bạn bấm chuột phải vào cột column1 để chuyển dữ liệu sang dạng thời gian như sau:

Kết quả như sau:

Bạn cũng có thể chuột phải ở Column1 để đổi tên cột bằng chọn Rename và đổi thành cột trong Power Query thành cột Time như sau:

Bước 3: Chèn thêm các cột thời gian tương ứng bằng chức năng chèn cột của Power Query

Bây giờ về cơ bản đã có dữ liệu về thời gian, tuy nhiên tùy theo các nhu cầu của bạn để tạo thêm các cột, năm, tháng, ngày, tuần, quý …. để hỗ trợ trong phân tích và lọc dữ liệu, thì bạn chọn như sau. tại Tab Add Column

Bôi chọn cột time và bạn chọn như sau: chọn bảng Date để chọn các cột chèn tương ứng

 

Tạo cột năm trong bảng dữ liệu thời gian bằng Power Query

Tại nút Date bạn chọn Year và chọn Year như sau:

Kết quả tạo cột năm tương ứng theo thời gian ở côt time

Tạo cột tháng trong bảng dữ liệu thời gian bằng Power Query

Tương tự như tạo cột năm thì bôi chọn cột time và  tại nút date bạn chọn như sau: Month và Month

Kết quả sẽ tạo ra cột tháng

Tương tự thì các cột còn lại như sau:

Tạo cột quý của năm trong bảng dữ liệu thời gian bằng Power Query

Tương tự như trên thì bôi chọn cột time và  tại nút date bạn chọn như sau: Quarter và Quarter of year

Tạo cột tuần của năm trong bảng dữ liệu thời gian bằng Power Query

Tương tự như trên thì bôi chọn cột time và  tại nút date bạn chọn như sau: Week sau đó chọn:

Week of year : Số tuần của năm (Thường từ 1 đến 52 tuần)

Week of Month : Số tuần của tháng (Thường từ 1 đến 5 tuần)

Tạo cột ngày trong bảng dữ liệu thời gian bằng Power Query

Tương tự như trên thì bôi chọn cột time và  tại nút date bạn chọn như sau: Day sau đó chọn:

Day: Ngày trong tháng, hiển thị ngày từ 1 đến 31

Day of Week: Ngày trong tuần thể hiện 0 là chủ nhật, 1 là thứ hai cho đến 6 là thứ bảy

Day of Year: Ngày trong năm thể hiện ngày từ 1 đến 365 ngày của năm

Name of day: Tên của ngày thể hiện tên thứ (Thứ hai, thứ ba….chủ nhật) trong tuần bằng tiếng anh hoặc tiếng việt hoặc các ngôn ngữ khác tùy vào cài đặt ngôn ngữ office của bạn.

Như vậy bạn đã tạo được một bảng dữ liệu thời gian với đầy đủ thông tin như sau

 

Như vậy bạn đã có thể tạo ra một bảng dữ liệu thời gian rất dễ dàng với Power Query, với phương pháp này có thể ứng dụng được cho Exel, Power Pivot, Power BI nhằm hỗ trợ tốt hơn công việc của bạn.