ในบทความนี้จะเป็นการรวบรวมเทคนิคและความรู้ความเข้าใจในการที่จะแก้ปัญหา Power Query ช้า ให้ทำงานเร็วขึ้น เพราะหากคุณเริ่มใช้ Power Query เป็น คุณจะเริ่มใช้มันกับทุกสถานการณ์ หากคุณเริ่มทำมันเยอะพอ คุณคงต้องเคยเจอปัญหาว่าบางทีมันทำงานช้ามากตอนที่กด Refresh All เพื่ออัปเดทข้อมูล ไม่ว่าจะเป็นใน Excel หรือ Power BI ก็ตาม เพื่อไม่ให้เสียเวลา เรามาเริ่มกันเลยครับ
ตั้งค่า Data Load ให้เหมาะสม
ปกติแล้วเวลาเราทำงานกับ Power Query เรามักจะสร้าง Query Step ย่อยๆ ขึ้นมาระหว่างทาง แล้วสุดท้ายก็เอามาใช้จริงแค่บาง Query เท่านั้น นอกนั้นเป็นตัวทด ซึ่งตัวทดควรจะ Load ออกมาแบบ Connection Only จริงมั้ยครับ?
ทีนี้ถ้าเราต้องมานั่งกด Close & Load to… Connection Only ทุกอันก็คงเสียเวลาแย่เลย ดังนั้นเราสามารถตั้งค่า Default การ Load เอาไว้ให้เป็น Connection Only ไว้ก่อน โดยไปที่ File –>Option & Settings –> Query Options –> Data Load
ตรงนี้มีจุดที่ปรับได้ 3อันคือ
1.Default Load ซึ่งผมแนะนำให้กดแบบ Specific custom default แล้วเอาติ๊กออกไปให้หมด เพื่อให้มันออกมาเป็น Connection Only นั่นเอง (ถ้าอยากจะ Load Query ไหนออกมาเป็นอย่างอื่น ก็กดคลิ๊กขวาที่ Query แล้วกด Load to… ได้)
2. โหมด Fast Data Load : ถ้าติ๊กอันนี้ ไอ้เครื่องหมาย i เค้าบอกว่า Query จะ Load ผลลัพธ์ออกไปใช้งานเร็วขึ้น แต่ระหว่างโหลดเครื่องอาจจะนิ่งๆ แฮงๆ ไปชั่วครู่ (เพราะว่ามันทุ่มเทพลังทั้งหมดไปที่การ Load ละมั้ง) แต่ที่ผมเคยลองผลลัพธ์ก็ไม่เห็นจะต่างกันเท่าไหร่เลยครับ 555
3. ตั้งค่า Cache : ตัวนี้จริงๆ ไม่ควรเกี่ยวกับการ Load โดยตรง แต่เกี่ยวกับการ Preview Data ให้ดูใน Query Editor มากกว่า (เพราะการ Preview มันเอามาจาก Data ที่เก็บใน Cache) แต่ตรงนี้ผมลองตั้งเยอะๆ แล้วทำให้เร็วขึ้นเล็กน้อยเฉยเลย… ดังนั้นใครมี Hdd เหลือก็ตั้งเยอะหน่อยก็ได้ครับ ไม่เสียหาย
ตั้งค่า Allow data preview to download in the background ให้เหมาะสม
คำสั่ง Allow data preview to download in the background นี้อยู่ใน Query Option -> Data Load
คำสั่งนี้เอาไว้ทำอะไร?
ปกติแล้วเวลา Power Query Get Data มาเตรียมให้เรา Transform เล่น ใน Power Query Editor มันจะเอาข้อมูลมา 1000 แถว ให้เราเห็นภาพผลลัพธ์ก่อน อันนี้แหละที่เรียกว่า Data Preview (ที่เอาจากข้อมูล Cache ที่เก็บในข้อที่แล้ว)
หากเราติั๊ก Allow data preview to download in the background เอาไว้ มันจะเป็นการบอกให้ Power Query คอย Update ตัว Preview สำหรับทุกๆ Query และที่สำคัญเวลาที่เรากด Refresh All มันก็จะ Load Preview สำหรับทุก Query ใหม่ด้วย!! ซึ่งตรงนี้แหละที่มันเสียเวลา
ข้อสรุปคือ
- หากคุณมี Query เยอะๆ โดยเฉพาะ Query ที่ไม่ได้ Load Data ออกมาจริงๆ การไม่ติ๊กจะทำให้ Load เร็วกว่า
- อย่างไรก็ตาม ข้อเสียของการเอาออก คือ เวลากดเปลี่ยน Step หรือกดไปดู Query ตัวอื่นใน Query Editor เจ้า Data Preview จะโหลดช้ากว่าเดิม
รายละเอียดเคสที่ disable option นี้ช่วยได้ สามารถอ่านได้ในนี้
ใช้ Power Query ใน Power BI แทน Excel !
ไม่รู้เหมือนกันว่าทำไม เหมือน 2 ตัวนี้มันจะทำงานไม่เหมือนกัน 100% ถ้าใช้ Power Query ใน Power BI มันจะ Load เร็วกว่า Excel นิดหน่อย
ซึ่งถ้าเราเคยทำงานใน Excel อยู่แล้ว ไม่จำเป็นต้องไปสร้าง Query ใน Power BI ใหม่ทั้งหมดนะ เราสามารถคลิ๊กขวา Copy Query จาก Excel ไป Power BI ได้เลย
จากนั้นไป Paste ใน Power Query Editor ของ Power BI
ผลลัพธ์ : เคสผมลองแล้วเร็วขึ้น 10% ครับ
อย่างไรก็ตาม หาก Query มีการดึงไฟล์จาก Workbook ตัวเองไว้ มันจะเอามาลง Power BI ตรงๆ ไม่ได้ เพราะใน Power BI มันไม่มี Excel.CurrentWorkbook ครับ มีแต่ Excel.Workbook
เข้าใจหลักการของการ Reference / Duplicate Table
หลายๆ คนอาจจะคิดว่า หากเราเขียน Query1 ไว้แล้ว จากนั้นทำการ Reference Query1 ไปใช้ใน Query2,3,4,… อีกหลายๆ อัน แล้วต้องการ Load ผลลัพธ์ออกหมดทุกอัน มันจะคำนวณ Query1 แค่ครั้งเดียวตอนแรก หลังจากนั้นไม่ต้องคำนวณอีกแล้ว อันนี้เป็นความเข้าใจที่ผิดนะครับ
เพราะการ Reference Query จริงๆ มันก็คือเหมือนเอา Code ของ Query1 มาใส่ใน Query อื่นๆ ที่อ้างอิงมันไปด้วยอยู่ดี แต่เหมือนเป็น Let…in… ซ้อนใน Let…in… ของ Query อื่น เช่น
Query1 ผมเขียนว่า
let
Step1 = 3+2,
Step2 = Step1*100
in
Step2จากนั้นผมสร้าง Query 2 แล้ว Reference ข้อมูลจาก Query1 จะได้สูตรใน Query2 ว่า
let
Source = Query1
in
Sourceจากนั้นผมก็ทำนู่นนี่ใน Query 2 ไปอีก เช่น
let
Source = Query1,
Step1 = Source+55,
Step2 = Step1/10
in
Step2แบบนี้เวลามันทำงานจริง มันจะเหมือนว่า Copy Code ใน Query1 มาใส่ใน Query 2 ก่อนจะทำงาน เช่น
let
Source =
let
Step1 = 3+2,
Step2 = Step1*100
in
Step2
,
Step1 = Source+55,
Step2 = Step1/10
in
Step2ซึ่งเวลามันทำงานก็จะประมาณนี้
ดังนั้นมันก็จะคำนวณทุกสิ่งทุกอย่างใน Query1 ใหม่อยู่ดี (ยกเว้นว่า Query1 ไม่ต้อง Load ออกมา คือเป็นตัวทดเฉยๆ มันก็จะรันแค่ที่ Query2 นี่แหละ)
ดังนั้นประโยชน์ของ Reference Query ก็แค่ว่า หากเราไปแก้ Query1 แล้ว ตัวต้นทางของ Query2 จะเปลี่ยนตามอัตโนมัติ ซึ่งต่างจาก Duplicate ซึ่งหากเราไปแก้ Query1 แล้ว ตัว Code ใน Query2 จะไม่ได้เปลี่ยนตามนั่นเอง
ใช้ Table.Buffer อย่างเหมาะสม
คำสั่ง Table.Buffer มีความสามารถในการเก็บข้อมูลในตารางเข้าสู่ Memory (คนละอันกับ Cache ที่จะเก็บบน HDD) เพื่อที่เวลามีการเรียกใช้อีก มันจะได้ไม่ต้องไปเอาข้อมูลจาก Data Source ใหม่ทุกครั้ง
Table.Buffer(table as table) as table
เคสที่ผมเคยใช้ ผมใช้มันกับ Table ที่ต้องมีการอ้างถึงหลายรอบใน Query เดียวกัน (ที่อ้างถึงเพราะเป็นการ Filter ช้อมูลจาก Table นั้นคล้ายๆ Vlookup Approximate Match) ซึ่งมีการอ้างถึง Table นั้นเท่ากับจำนวน Row ใน Table หลัก ซึ่งมีหลายพันครั้ง
Code ก่อน buffer
let
//Source เป็นการดึงผลลัพธ์จากอีก Table นึงมาทำงานต่อ
Source=SystemEmployee,
#"Added Custom" = Table.AddColumn(Source, "Custom",
(main)=>Table.SelectRows(Source,
(sub)=>sub[EnNo]=main[EnNo] and (sub[DateonlyNum]=main[DateonlyNum] or (sub[DateonlyNum]=(main[DateonlyNum]+1) and sub[Mode]=6 )))),
....พบว่าไฟล์ที่เป็นต้นทางจริงๆ ขนาดแค่ 209K แต่ Power Query ดันขึ้นว่า Read Data ไป 900 MB++ (เพราะมันอ่านจาก Source ใหม่ไม่รู้กี่รอบ)
Code หลัง buffer
let
//เพิ่มการ Buffer ให้ Source เพราะว่ามีการเรียกใช้ Source หลายรอบด้วย Table.SelectRows ใน Step ถัดไป
Source=Table.Buffer(SystemEmployee),
#"Added Custom" = Table.AddColumn(Source, "Custom",
(main)=>Table.SelectRows(Source,
(sub)=>sub[EnNo]=main[EnNo] and (sub[DateonlyNum]=main[DateonlyNum] or (sub[DateonlyNum]=(main[DateonlyNum]+1) and sub[Mode]=6 )))),
....
หลังจากใช้เทคนิคTable.Buffer แล้ว ในเคสของผมก็ช่วยลดเวลา Load ไปได้ประมาณ 10-15% (ควรลองทดสอบด้วยนะ เพราะบางเคสทำแล้วอาจจะแย่ลงได้ เนื่องจาก Table.Buffer มันจะทำการอ่านข้อมูลเข้า Ram ทั้งหมดตอนที่ Buffer เลย)
อีกทั้งเลขที่ขึ้นว่า Load ข้อมูลจาก Data เท่าไหร่แล้ว มีขนาดใกล้เคียงกับ Data จริงคือ 209kb ไม่ใช่ 900MB++ เหมือนตอนที่ไม่ได้ Buffer (เห็นแล้วตกใจมาก!!)
แปลว่าในตัวอย่างที่แล้วเรื่อง Reference/Duplicate Query สมมติเราไปสร้าง Query ซักตัวมาใช้ Table.Buffer Query1 ไว้ จากนั้นให้ Table อื่นอ้างอิง Query นั้นไปใช้ มันก็ไม่ช่วยอะไรอยู่ดี เพราะจะกลายเป็นทุก Query จะต้อง Buffer ใหม่ตลอดเวลานั่นเอง
สรุปแล้ว Table.Buffer จะมีประโยชน์ก็ต่อเมื่อ ใช้กับ Query ที่มีการเรียก Table นั้นๆ หลายรอบใน Query เดียวกันเท่านั้น
พยายามทำให้ข้อมูลให้เหลือน้อยก่อนทำ Operation อื่น
สมมติการ Transform ข้อมูลเรามี 2 เรื่องที่ต้องทำ คือ Add Column คำนวณบางอย่าง กับ Filter ให้เหลือข้อมูลบรรทัดที่ต้องการ
ถ้าเป็นไปได้เราควรจะ Filter ก่อน Add Column เพื่อที่ว่า Power Query จะได้คำนวณน้อยลงเท่าที่จำเป็นจริงๆ ไม่ใช่คำนวณทั้งหมดแล้วดันไป Filter ทิ้งทีหลัง แบบนั้นเสีย Resource เปล่าๆ (ยกเว้นว่า ไอ้ Column ที่ add มาคือเงื่อนไขของการ Filter ถ้างั้นเราก็จำเป็นต้อง Add ก่อน จริงมะ?)
นอกจากนั้น ก่อนจะใช้ Table.Buffer ในตัวอย่างที่แล้ว หากเราเลือกให้เหลือเฉพาะข้อมูลที่จำเป็นก่อนจะทำการ Buffer มันก็น่าจะช่วยให้ Load Query เร็วขึ้นเช่นกัน ซึ่งผมลองปรับ Query แล้วปรากฏว่าเร็วขึ้น 30%
let
Source = SystemEmployee,
MyBufferTable = Table.Buffer(
Table.SelectColumns(Source, {"EnNo", "Mode", "DateonlyNum", "DateTime"}) ),
#"Added Custom" = Table.AddColumn(
Source, "Custom",
(main) => Table.SelectRows(
MyBufferTable,
(sub) => sub[EnNo] = main[EnNo] and (sub[DateonlyNum] = main[DateonlyNum]
or (sub[DateonlyNum] = (main[DateonlyNum] + 1) and sub[Mode] = 6))
)
),
...พยายามทำให้ Step เหลือน้อยๆ
เราสามารถแก้ Code ให้มีจำนวน Step น้อยลงได้ ซึ่งเท่าที่ทดสอบดู Query ก็จะเร็วขึ้นเล็กน้อยด้วย
แต่การทำแบบนี้ได้ ต้องเข้าใจโครงสร้างของ M Code ที่อ้างอิง Step ก่อนหน้ามาทำงานต่อ
เช่น แทนที่จะเขียนแบบนี้
let
Source = Query1,
Step1 = Source+55,
Step2 = Step1/10
in
Step2การเขียนแบบนี้จะเร็วกว่าเล็กน้อย
let
Step2 = (Query1+55)/10,
in
Step2รวมถึงการกำจัด Step ที่ไม่จำเป็น เช่น Add Custom Column ไปแล้ว มาเปลี่ยนชื่อทีหลัง หากเราเปลี่ยนชื่อไปเลยใน Step Add Custom Column ก็จะเร็วกว่า เป็นต้น
คลิปทดสอบการแก้ปัญหา Power Query ช้า
รายละเอียดของเทคนิคในคลิปนี้ค่อนข้างซับซ้อน ใครสนใจสามารถดูได้ที่นี่ ซึ่งรวมหลายเทคนิคด้วยกันคือ
- การยกเลิก Allow data preview to download in the background
- ใช้ Table.View มาช่วยบอกให้ Power Query รู้ลักษณะ Structure ของ Data Source โดยไม่ต้องไปลองดึงมาจริงๆ
- ยกเลิกการเช็ค Data Privacy
- ยกเลิก Parallel Load กรณีที่เอา Data เดิมหลายรอบ
เทคนิคอื่นๆ
ใครมีเทคนิคอื่นๆ ก็อย่าลืมมาแชร์กันด้วยนะครับ
