สร้าง Date table สำหรับ Power BI ด้วย Power Query

4 min readMay 22, 2021

แนะนำวิธีการสร้าง Date table ด้วย Power Query สำหรับใช้งานใน Power BI

หนึ่งในความสามารถของ Power BI ที่เพิ่มความสะดวกให้กับผู้ใช้มากๆ นั่นคือ Time Intelligence ที่ทำให้การคำนวณต่างๆ เกี่ยวกับเวลา ไม่ว่าจะเป็น YTD, Same Period Last Year และอื่นๆ ทำได้ง่ายมาก แต่น่าเสียดายที่ผู้ใช้มือใหม่ส่วนใหญ่ยังไม่ทราบว่า หากจะใช้ time intelligence จำเป็นต้องมี Date table แยกต่างหาก ทำให้หลายคนไม่สามารถใช้ความสามารถของ Time Intelligence ได้อย่างเต็มที่

บทความนี้จะแนะนำวิธีการสร้าง Date table ด้วย Power Query ซึ่งเป็นหนึ่งวิธีที่ทำได้ง่ายและรวดเร็ว ลงแรงเพิ่มอีกนิดหนึ่งแต่รับรองว่าคุ้มค่าแน่นอนครับ

ประโยชน์ของการสร้าง Date table

หลายคนอาจจะสงสัยว่า ทำไมต้องมีการสร้างตาราง Date แยกออกมาด้วย ในเมื่อหากข้อมูลของเรามีฟิลด์วันที่อยู่ และนำเข้าข้อมูลเป็นชนิดวันที่แล้ว Power BI จะสร้าง Date Hierarchy ขึ้นมาให้เองโดยอัตโนมัติ มีทั้งปี ไตรมาศ เดือน และ วัน

การสร้างตาราง Date แยกต่างหาก นอกจากจะทำให้เราสามารถใช้ความสามารถ Time Intelligence ได้อย่างเต็มที่แล้ว ยังทำให้เราสามารถเพิ่ม attributes ของวันที่แบบอื่นๆเข้าไปใน Date table เพื่อตอบสนองความต้องการที่เพิ่มเติมขึ้นได้ เช่น วันหยุด วันในสัปดาห์ หรือปีบัญชี เป็นต้น โดยไม่กระทบกับข้อมูลต้นทางดังในภาพ

เราสามารถนำฟิลด์ข้อมูลอื่นๆ มาใช้ในการ filter หรือสร้างรายงานได้หลากหลายเพิ่มมากขึ้น

ลักษณะของ Date table ที่ดี

หนึ่งแถวแทนวันที่ 1 วัน
ต้องไม่มีช่องว่างระหว่างวันที่ใน Date table ถึงแม้ว่าในตารางข้อมูลอื่นๆ จะไม่มีข้อมูลบางวันก็ตาม เช่น ถึงแม้ว่าจะไม่มียอดขายในวันเสาร์อาทิตย์ แต่ Date table ต้องมีวันเสาร์และวันอาทิตย์ด้วย
ต้องมีระยะเวลาครอบคลุมขอบเขตข้อมูลทั้งหมด และควรเผื่อไปในอนาคตด้วย เช่น ถ้าเรามียอดขายเริ่มตั้งแต่เดือนมิถุนายน 2018 ถึง สิงหาคม 2020 ก็ควรจะสร้าง Date table เริ่มต้นตั้งแต่ 1 มกราคม 2018 ไปจนถึง 31 ธันวาคม 2020

สร้าง Date table ด้วย Power Query

เราจะใช้ Power Query script ที่ได้มาจาก blog “Creating a Date Dimension with Power Query” โดย Devin Knight (Twitter @knight_devin)

บน Power BI Desktop เลือก Get Data -> Blank Query

เมื่ออยู่ใน Power Query Editor แล้ว ให้เลือก Advanced Editor

ลบ code เดิมที่อยู่ใน Advance Editor ออก แล้ว copy & paste โค้ดนี้ลงไปแทน แล้วกด Done

//Create Date Dimension
(StartDate as date, EndDate as date)=>let
    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    Date.Day(StartDate)),
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
    Date.Day(EndDate)),//Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate),//Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount, 
    #duration(1,0,0,0)),//Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),//Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year(\[Date\])),//Add Quarter Column
    QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
    each "Q" & Number.ToText(Date.QuarterOfYear(\[Date\]))),//Add Week Number Column
    WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
    each Date.WeekOfYear(\[Date\])),//Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month(\[Date\])),//Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText(\[Date\],"MMMM")),//Add Day of Week Column
    DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
    each Date.ToText(\[Date\],"dddd"))in
    DayOfWeek

Power Query จะให้เราป้อนค่าวันที่เริ่มต้น และวันที่สิ้นสุดของ Date table ที่จะสร้างขึ้น ป้อนไปแล้วกดปุ่ม Invoke

เท่านี้เราก็จะได้ Date table ตามที่ต้องการ โดยตารางที่สร้างขึ้นจะมีชื่อว่า Invoked Function เราสามารถเข้าไปเปลี่ยนชื่อตารางได้ตามต้องการ ผมมักจะเปลี่ยนเป็น Date

และสุดท้ายอย่าลืมเข้าไปใน Model View เพื่อสร้าง relationship กับตารางข้อมูลอื่นๆ

การปรับแต่ง script เพิ่มเติม

ใน Date table ที่ถูกสร้างขึ้น ประกอบด้วยฟิลด์ต่างๆ ดังนี้คือ Year, Quarter, Week Number, Month Number, Month, และ Day of Week

หากใครลองอ่านโค้ดดู จะเห็นได้ว่า Script นี้เริ่มจากการรับ parameter มาสองตัว คือ StartDate กับ EndDate แล้วก็เอามาคำนวณหาจำนวนวัน สร้าง list จากจำนวนวัน แปลง list เป็นตาราง เสร็จแล้วก็ค่อยๆ เพิ่ม column ต่างๆ เข้ามาทีละคอลัมน์ ใครอยากได้คอลัมน์อะไรเพิ่มก็สามารถลองปรับแต่ง script นี้ได้ครับ หรือจะมาสร้าง Calculated Columns เพิ่มเติมต่างหากก็ได้เหมือนกัน

แนวทางอื่นๆ ในการสร้าง Date table

จะเห็นได้ว่าเราสามารถสร้าง Date table จาก Power Query script ได้อย่างง่ายดาย แต่ความจริงแล้ว ยังมีอีกหลายวิธีที่เราจะสามารถสร้าง Date table ได้ อาทิเช่น

หากในบริษัทของเรา มีการสร้าง standard date table เอาไว้แล้ว และมีฟิลด์ต่างๆ ครบถ้วน ให้ลองติดต่อฝ่าย IT หรือผู้ที่ดูแล standard date table ของบริษัทดู เผื่อจะสามารถขอเชื่อมต่อกับตารางมาตรฐานได้
ใครที่มี Date หรือ Calendar อยู่แล้ว ในลักษณะของ Excel ไฟล์ ก็สามารถนำมา import ได้เช่นเดียวกัน ขอให้มีลักษณะตามที่กล่าวไว้แล้วข้างต้น
นอกจากสร้างจาก Power Query แล้ว เรายังสามารถสร้าง Date table จาก DAX หรือ Data Analytics Expression ได้อีกด้วย

ส่งท้าย

ถึงแม้ว่าการสร้าง Date table จะเป็นขั้นตอนที่เพิ่มขึ้นมา สำหรับผู้ใช้มือใหม่ แต่เนื่องจากผลประโยชน์ที่จะได้รับถือว่าคุ้มค่ามาก ผมอยากชวนให้มาสร้าง Date table กันนะครับ จะใช้วิธีไหนก็ได้ รับรองว่าจะทำให้การใช้งาน Power BI ในส่วนที่เกี่ยวข้องกับวันที่มีประสิทธิภาพสูงขึ้นมากเแน่นอนครับ

ที่มา : Creating a Date Dimension with Power Query

Originally published at http://thaipowerbiusergroup.wordpress.com on May 22, 2021.