สูตรสำเร็จรูปสำหรับสร้าง Date Table ด้วย DAX และ Power Query M Code

จากที่ผมได้เคยอธิบายไปว่า Date Table มีความสำคัญต่อการทำ Data Model เป็นอย่างยิ่ง โดยเฉพาะการใช้ฟังก์ชัน Date Intelligence ยิ่งจำเป็นต้องใช้ Date Table เลยล่ะ

แปลว่า Data Model ของทุกคนควรมี Date Table ซึ่งกลายเป็นเรื่องที่ทุกๆ คนต้องทำเหมือนๆ กันหมด

ดังนั้นจะดีกว่ามั้ย ถ้าเราสามารถมีสูตรสำเร็จรูปอะไรซักอย่าง ที่สามารถ Copy Paste ไปใช้สร้าง Date Table ได้เลย? และนั่นคือที่มาของบทความนี้ครับ

ทีนี้การสร้าง Date Table นั้นทำได้ 2 วิธี คือใช้ DAX กับใช้ M Code ของ Power Query เรามาดูแต่ละวิธีกันครับ

สร้าง Date Table ด้วย DAX

แค่กด New Table ใน Power BI แล้วใส่ code นี้ลงไป

dDate = 
VAR MyCalendar=CALENDARAUTO()

RETURN

ADDCOLUMNS (MyCalendar,
"Year",YEAR([Date]),
"Quarter",FORMAT([Date],"\Qq"),
"Month",MONTH([Date]),
"Day",DAY([Date]),
"MonthName",FORMAT([Date],"mmmm"),
"YearMonth",FORMAT([Date],"yyyy-mm"),
"YearMonthName",FORMAT([Date],"yyyy-mmm"),
"WeekDayNum",WEEKDAY([Date],2),
"WeekDayName",FORMAT([Date],"dddd"),
"DateText",FORMAT([Date],"yyyy-mm-dd"),
"Start of Month",EOMONTH([Date],-1)+1,
"End of Month",EOMONTH([Date],0)
)

โดยที่ตาราง fSales มีข้อมูลรายการวันที่สั่งซื้อสินค้าอยู่ในคอลัมน์ OrderDate

ดังนั้นให้เปลี่ยนคอลัมน์วันที่ ที่จะใช้อ้างอิงได้จาก fSales[OrderDate] เป็นวันที่ใน Table ของคุณจริงๆ ได้เลย

Tips: ผมมีการ comment ใส่ startYear และ endYear อีก 2 แบบไว้ (ถ้าจะใช้ก็เอา — ข้างหน้าออกเพื่อ uncomment)

• แบบทางเลือก1 เอาไว้พิมพ์ปีที่ต้องการเริ่มและจบเอาเอง โดยที่ไม่ขึ้นกับ Field Data จริง
• แบบทางเลือก2 (ที่ยาวๆ) ซึ่งไว้เทียบ Field วันที่กรณีมีมากกว่า 1 Field ด้วย

แค่นี้ก็จะได้ Date Table มาใช้งานแล้วล่ะ

ถ้าใครจะเพิ่มคอลัมน์อีก ก็กด Add Column แล้วใส่สูตรตามปกติต่อไปได้เรื่อยๆ นะครับ เช่น วันแรก วันสุดท้ายของเดือน จำนวนวันในเดือนเป็นต้น

FirstDateofMonth = STARTOFMONTH(dDate[Date])

LastDateofMonth = ENDOFMONTH(dDate[Date])

NumDaysinMonth = DATEDIFF(dDate[FirstDateofMonth],dDate[LastDateofMonth],DAY)+1

หรือจะใช้ว่า

NumDaysinMonth = INT(dDate[LastDateofMonth]-dDate[FirstDateofMonth]+1)

ก็ได้

สร้าง Date Table ด้วย Power Query M Code

ให้เราสร้าง Blank Query (กด Get Data -> Blank Query) ขึ้นมาและใส่ Code นี้ลงไปใน Advanced Editor (กด View->Advanced Editor)

Edit : 23/3/2021 แก้ไข DayCount อันเดิมใส่วันขาดไป 1 วัน

(StartDate as date, EndDate as date) as table =>
let
displayRegion="en-GB",
//displayRegion="th-TH",
//StartDate=#date(2017, 1, 1),
//EndDate=#date(2019,12,31),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each "Q"& Text.From(Date.QuarterOfYear([Date])), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Quarter", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.ToText([Date],"MMMM",displayRegion), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date],1)+1, Int64.Type),
#"Added YearMonthNum" = Table.AddColumn(#"Inserted Day of Week", "YearMonthNum", each Date.ToText([Date],"yyyyMM",displayRegion)),
#"Added YearMonthText" = Table.AddColumn(#"Added YearMonthNum", "YearMonthText", each Date.ToText([Date],"yyyy-MMM",displayRegion))
in
    #"Added YearMonthText"

Tips: สังเกตว่าใน code มีการระบุรูปแบบ region ได้ ซึ่งผมใส่เป็น displayRegion=”en-GB” ไว้ ซึ่งจะเป็นภาษาอังกฤษครับ ถ้าจะให้เป็นภาษาไทยสามารถ uncomment // เปลี่ยน displayRegion=”th-TH” ได้นะครับ

แล้วตั้งชื่อ Query ตามต้องการ เช่น CreateDateTable

ผลลัพธ์ออกมาจะเป็น Function Query ให้เราใส่ Parameter วันเริ่มกับวันจบ (กดรูปปฏิทินได้นะ)

ถ้าใส่แล้วกด invoke จะได้ตารางออกมาดังนี้

แปลว่าถ้าอยากให้ตารางนี้ Dynamic ได้จะต้องไปแก้สูตรจาก

= CreateDateTable(#date(2018, 1, 1), #date(2020, 12, 31))

ให้เป็น

= CreateDateTable(วันเริ่มต้น, วันสิ้นสุด)

โดยอาจ Link สูตรวันเริ่มต้นจากการไป Date Start of Year ของซัก Field วันที่นึง เช่น

วันเริ่มต้น= 
Date.StartOfYear(List.Min(fSales[OrderDate]))

วันสิ้นสุด=
Date.EndOfYear(List.Max(fSales[OrderDate]))

โดยที่ตาราง fSales มีข้อมูลรายการวันที่สั่งซื้อสินค้าอยู่

แค่นี้เราก็จะได้ Date Table มาใช้ และยังคง Add อะไรเพิ่มได้อีกเช่นกัน โดยคลิกที่คอลัมน์ Date และไปที่ Add Column –> Date

เช่น ผมจะใส่จำนวนวันในเดือน ก็กดปุ่มได้เลยแบบชิลๆ ไม่ต้องใส่สูตรอะไรทั้งนั้น

เท่านี้เราก็สามารถสร้าง Date Table ได้ทั้ง 2 วิธีแล้ว และก็ยังเพิ่มคอลัมน์ได้ตามที่ต้องการด้วย

ว่าแต่เพื่อนๆ ชอบแบบไหนมากกว่ากันครับ ^^