Hi vọng sau bài viết về tạo Custom Date Dimension bằng DAX, các bạn có thể dễ dàng tạo bảng Date theo yêu cầu. Tuy nhiên, DAX không phải công cụ duy nhất để tạo các bảng Date này một cách tự động. Đôi khi, chúng ta phải tạo bảng Date này từ Power Query và sử dụng M hoặc thậm chí tạo dựa trên một khoảng thời gian cụ thể hoặc cập nhập theo ngày, parameter. Trong bài blog này, mình sẽ giới thiệu với các bạn các cách tạo Custom Date Dimension bằng Power Query.
Trong các bài viết về Power Query và M, mình sẽ hướng dẫn theo phong cách kết hợp sử dụng các thanh công cụ (User Interface Tool Bar) và thay đổi code M để giúp các bạn vừa biết các tính năng này trên thanh công cụ vừa hiểu cách có thể hoàn toàn viết M trên Query Editor hoặc Formula Bar giúp thay đổi query theo cách bạn muốn. Đồng thời việc sử dụng thanh công cụ cũng là một cách khởi đầu giúp học M dễ dàng hơn.
Các bạn có thể lấy code mẫu ở cuối bài để sử dụng ngay trong báo cáo nhé và đừng quên đọc bài blog để biết thêm chi tiết.
Mục lục
Bước 1: Tạo List Danh Sách Ngày
Như chúng ta đã thấy, các bảng Custom Date Table bao giờ cũng sẽ bắt đầu bằng việc tạo ra bảng có trường là các ngày chạy liên tiếp từ Start Date đến End Date được chỉ định. Trong trường hợp các bạn muốn tạo Custom DateTime Table, đó có thể thay vì là các ngày mà là từng giờ trong ngày. Vì vậy, nhiệm vụ của chúng ta đó chính là tạo một bảng danh sách các ngày.
Power Query, các bạn có thể:
- Dùng chuột phải để tạo New Query -> Blank Query
- Hoặc có thể từ thanh công cụ ở trên -> Home -> New Source -> Blank Query
- Trong thanh công thức, đánh = List.Dates để tạo một function giúp tạo ra một dãy các ngày dựa trên ba giá trị đầu vào là ngày bắt đầu (start), số ngày (count), khoảng cách giữa từng ngày (step). Ở dưới các bạn có thể thấy 3 parameter được tạo sẵn và sau khi điền vào, ấn Invoke sẽ tạo ra một query trả về bảng danh sách ngày mà bạn muốn tạo.
- Các bạn chú ý khi điền Step, nếu các bạn chỉ cần cách theo ngày, ví dụ danh sách các ngày liên tiếp (step = 1 ngày) thì các bạn có thể chỉ cần điền số ngày vào là được. Trường hợp muốn đưa step bao gồm ngày và giờ (hoặc phút, giây) thì các bạn có thể bổ sung bằng cách thêm “.” và theo sau là thời gian dạng “hh:mm:ss”. Ví dụ muốn đặt step là 1 ngày, 6 tiếng, 6 phút, 6 giây thì sẽ điền vào step: 1.6:6:6
- Hãy thử ví dụ tạo một bảng Date gồm các ngày trong năm 2020, chúng ta sẽ bỏ parameter như sau và được bảng dưới đây:
- Sau khi bỏ parameter và ấn Invoke, các bạn sẽ thấy Query1 được áp dụng vào trạo thành một List (các bạn để ý kí hiệu List để nhận diện khác với table). Đồng thời trong thanh công thức, các bạn có thể thấy công thức để tạo list này chính là công thức của List.Dates nhưng chỉ khác thay vì List.Dates, nó thay bằng Query1. Như vậy có thừa không nhỉ? Có mà không, vì nó có nghĩa bạn hoàn toàn có thể bỏ Query1 và viết Blank Query để tạo ngay list này bằng công thức List.Dates dưới đây để tạo ra 1 list Date mà k cần hiện Query1.
- Tuy nhiên sử dụng Query 1 cũng có cái hay của nó bởi vì nó tạo ra cho bạn 3 parameter giúp người dung có thể dể dàng sử dụng để thay đổi query của mình. Tuy nhiên nếu kết hợp với parameter hoặc thời gian ngày hiện tại, mình ủng hộ sử dụng ngay M để tạo 1 query cho bạn ngay bảng Date cần dùng.Một số trường hữu dụng và công thức để bổ sung
Bước 2: Biến List Thành Bảng Và Tạo Các Trường Liên Quan Cần Thiết
Sau khi có danh sách các ngày, việc tiếp theo của chúng ta đó là biến List thành bảng. Việc này vô cùng đơn giản vì ngay trên thanh công cụ List Tools, các bạn sẽ thấy ngay tính năng To Table giúp chuyển List thành bảng. Và trong trường hợp này, chúng ta chỉ có duy nhất một trường và không cần tách nó ra thành các trường khác nhau nên các bạn có thể tiếp tục ấn OK và Power BI sẽ bổ sung thêm step thứ 2 cùng công thức các bạn có thể thấy của bước này. Icon của bảng Date giờ thay đổi xác định chúng ta có bảng thay vì là list.
- Để tạo các trường về năm, tháng, quý, tuần thời trường Date trong Power Query cũng rất đơn giản, bạn có thể tìm thấy tính năng này bằng cách tìm tab Add Column -> Date -> chọn trường bạn muốn thêm.
- Đây cũng là một cách tự học M và các công thức tạo trường thời gian liên quan. Ví dụ, tạo tháng của Ngày đó hoặc đặc biệt hơn là ngày cuối của tháng đó chả hạn.
Figure : Bổ sung trường tháng
Figure : Bổ sung trường ngày dầu của tháng
- Mình sẽ bổ sung các trường minh thấy phụ hợp trong code M mẫu cuối bài và tiếp theo chúng ta sẽ tìm hiểu làm sao để chỉnh Source để ta có thể tạo được một dãy ngày cập nhập theo ngày hiện tại nhé.
Bước 3: Bổ Sung Parameter Để Chỉnh Source Cập Nhập Đến Ngày Hiện Tại
Vì khi sử dụng List.Dates, chúng ta phụ thuộc vào parameter là Start_Date, Count và Step vì vậy để có một bảng date luôn cập nhập đến ngày hiện tại (Today), chúng ta sẽ tạo parameter là ngày hiện tại và biến Count thay đổi theo Start_Date và Today.
- Để tạo bước custom tiếp theo bằng thanh công cụ, các bạn có thể ấn vào biểu tượng bên cạnh thanh công thức, và sau đó một bước Custom sẽ được tạo ra hiện đang chỉ về bước cuối cùng của chúng ta chính là bước “Inserted Start of Month”. Tuy nhiên chúng ta có thể thay thế bằng công thức riêng.
- Trong bước này, chúng ta cần tạo ra một parameter trả về ngày hiện tại và ta sẽ sử dụng công thức DateTime.LocalNow. Bạn có thể gõ bào LocalNow và sẽ thấy có 4 công thức khác nhau để trả về thời gian hiện tại trên hệ thống.
- Chọn DateTime.LocalNow và được trả về giá trị là thời điểm hiện tại. Các bạn chú ý bổ sung () vào cuối công thức để có thể chạy ra giá trị mà không phải đưa ra parameter.
- LocalNow() sẽ luôn trả về giá trị kèm thời gian, mà trong trường hợp này, chúng ta cần giá trị ngày thôi vì vậy ta sẽ sử dụng công thức DateTime.Date() bao ở ngoài để đổi format chỉ bao gồm ngày.
- Nếu bạn mở Edit Query sẽ để ý thấy, tên của parameter này chính là tên Step của chúng ta. Chúng ta có thể đổi tên bước này để dễ dàng nhận biết parameter, và hãy gọi nó là Today
- Tiếp theo, chúng ta sẽ tạo parameter là Start_Date và trong trường hợp này, start date của ta sẽ là ngày không đổi đó là 01/01/2020. Để tạo parameter này, bạn sẽ ấn vào kí hiệu để tạo custom step, và vì lần này chúng ta sử dụng một giá trị không đổi nên chúng ta sẽ không cho dấu “=” vào trong thanh công thức mà chỉ cẩn đánh đúng ngày cần là được.
- Sau khi đã có StartDate và Today, chúng ta sẽ tạo parameter là số ngày giữa 2 ngày này để đưa vào giá trị Step ở công thức Source tạo list date. Bằng cánh dùng Duration.Days( Today – StartDate), Power BI sẽ trả về giá trị là số ngày và chúng ta có thể gọi Parameter này là Step.
- Sau khi có đầy đủ các parameter là StartDate và Steps để thay đổi trong Source. Chúng ta sẽ ấn vào bước Source trong PowerQuery và thay các giá trị tương ứng với parameter như dưới đây và chúng ta sẽ có một bảng Date tự cập nhập các ngày từ đầu năm 2020 đến thời điểm hiện tại.
Code M Mẫu Có Thể Copy Và Sử Dụng Ngay Lập Tức
Vậy là các bạn đã có thể sử dụng phương pháp trên để tự tạo cho minh một bảng Date có thể tự cập nhập trong Power Query. Và dưới đây là đoạn code bằng M mẫu với đầy đủ note các bạn có thể Copy và Paste vào Blank Query để sử dụng. Trong code mẫu này, mình tạo Fiscal Year theo tháng cuối năm là tháng 6 và mọi người có thể dễ dàng chỉnh trong công thức.
// Create parameter
Today = DateTime.Date(DateTime.LocalNow()),
StartDate = #date(2020, 1, 1),
Step = Duration.Days(Today – StartDate),
// Create the List of date
Source = List.Dates(StartDate,Step, #duration(1,0,0,0)),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Create additional columns
#”Inserted Year” = Table.AddColumn(#”Converted to Table”, “Year”, each Date.Year([Column1]), Int64.Type),
#”Inserted Quarter” = Table.AddColumn(#”Inserted Year”, “Quarter”, each “Q” & Text.From(Date.QuarterOfYear([Column1]))),
#”Inserted Month” = Table.AddColumn(#”Inserted Quarter”, “Month”, each Date.Month([Column1]), Int64.Type),
#”Inserted Month Name” = Table.AddColumn(#”Inserted Month”, “Month Name”, each Text.Start(Date.MonthName([Column1]),3), type text),
#”Inserted Week of Year” = Table.AddColumn(#”Inserted Month Name”, “Week of Year”, each Date.WeekOfYear([Column1]), Int64.Type),
#”Inserted Day of Week” = Table.AddColumn(#”Inserted Week of Year”, “Day of Week”, each Date.DayOfWeek([Column1]), Int64.Type),
#”Inserted Day Name” = Table.AddColumn(#”Inserted Day of Week”, “Day Name”, each Text.Start(Date.DayOfWeekName([Column1]),3), type text),
// Add Fiscal Year based on month-end is June
#”Inserted Fiscal Year” = Table.AddColumn(#”Inserted Day Name”, “Fiscal Year”, each if [Month] = 6 then [Year] else [Year]+1),
// Add Month Sort for fiscal year
#”Inserted Month Fiscal Sort” = Table.AddColumn(#”Inserted Fiscal Year”, “Month Fiscal Sort”, each if [Month] = 6 then [Month] +6 else [Month] – 6),
// Add working date
#”Inserted Working Date” = Table.AddColumn(#”Inserted Month Fiscal Sort”, “Working Date”, each if [Day Name] = “Sun” then “No” else “Yes”),
// Rename and Change Type
#”Renamed Columns” = Table.RenameColumns(#”Inserted Working Date”,{{“Column1”, “Date”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}})
in
#”Changed Type”
[/code]
Nguồn: KPIM
Bài viết được sưu tầm trên mạng với mục đích chia sẻ kiến thức tới mọi người