ช่วงนี้เพื่อนๆ หลายคนเริ่มใช้ Power Query กันเยอะมากขึ้นแล้ว ทำให้เริ่มเจอปัญหาการใช้งานมากขึ้น (ซึ่งเป็นเรื่องที่ดี) มีคำถามจาก Inbox ที่ช่วงนี้ผมได้รับมาบ่อยนั่นคือ จะสามารถ Export ข้อมูลที่สร้างจาก Power Query แล้วเยอะเกินล้านบรรทัดได้ยังไง? เพราะจะเอาออกมาเป็น Table ใน Sheet ของ Excel ก็ไม่ได้ เพราะมันรับได้แค่ 1,048,576 บรรทัด หรือ ล้านกว่าบรรทัด (ต่อ 1 Sheet) เท่านั้น
ผมคิดว่าคงติดปัญหานี้กันหลายคนพอสมควร การเขียนบทความอธิบายน่าจะเกิดประโยชน์สูงสุดครับ
สารบัญ
แนวทางแก้ไขปัญหา
หลักการโดยรวมคือ เราจะเอาผลลัพธ์จาก Power Query Load เข้าสู่ Data Model ก่อน (เพราะมันรับข้อมูลได้ไม่จำกัด ต่างจาก Sheet Excel) แล้วค่อย Export ข้อมูลจาก Data Model ออกมาเป็น Text File ด้วยโปรแกรม DAX Studio ครับ
วิธีนี้เรียกได้ว่าขี้โกงเล็กน้อย เพราะเราไม่ได้ Export จาก Power Query ตรงๆ พูดให้ถูกคือเป็นเอาข้อมูล Export จาก Data Model ซะมากกว่า แต่ใครจะสนล่ะ ขอแค่มันทำได้ได้ก็พอแล้วล่ะ จริงมั้ย?
ติดตั้งโปรแกรม DAX Studio
ก่อนอื่นต้องบอกว่าเราต้องไปโหลดโปรแกรมตัวนึงมาช่วยซะก่อนนั่นคือ DAX Studio ซึ่งสามารถโหลดมา install ได้ฟรีครับ เครื่องมือนี้มีความสามารถในการช่วยเขียน DAX ซึ่งเป็นภาษาของ Power Pivot กับ Power BI แต่เราจะใช้มันเพราะมันมีความสามารถในการ Export ข้อมูลออกมาเป็น Text File ได้ด้วย
ให้โหลดมาแล้ว install ซะ ที่สำคัญคือต้องลง Excel Addin ด้วยนะ (ปกติก็ติ๊กไว้อยู่แล้ว อย่าไปติ๊กออกล่ะ)
พอ Install เสร็จแล้ว หากเราลองเข้า Excel จะเห็น Ribbon พิเศษขึ้นมา ดังนี้
ทำ Query ตามต้องการ
จากนั้นก็ให้เราทำ Query ของเราตามต้องการได้เลย อันนี้ก็แล้วแต่งานของเพื่อนๆ ว่าจะเป็นยังไง ซึ่งจะมีบรรทัดเยอะหลายล้านบรรทัดก็ได้นะ
ในที่นี้ผมสร้างเลข Running 1-5 ล้านใน Power Query ไว้ละกัน ใครอยากลองทำตามก็เขียน Query ใน Formula Bar ของ Power Query ว่า
= {1..5000000}
จากนั้นกด Enter มันจะสร้าง List ออกมาให้ก่อน แล้วเราก็กด Convert to Table ได้เลยครับ (ใครงงกับวิธีสร้าง List นี้ลองไปอ่านเพิ่มได้ที่นี่)
เอาผลลัพธ์จาก Query ออกไปยัง Data Model
จากนั้นก็กด Close & Load to… เพื่อเอาผลลัพธ์ของ Query ที่เราทำออกมา
จากนั้นให้เลือก Connection Only และติ๊ก Add this data to the Data Model ด้วย
ตรงนี้สำคัญมาก เพราะจะเป็นการส่ง Data ผลลัพธ์จาก Power Query ไปใช้งานต่อใน Data Model ล่ะครับ
จากนั้นจะเห็นว่า Data ถูกโหลดออกมา 5 ล้านบรรทัดจริงๆ (แต่โหลดเข้าไปใน Data Model นะ ไม่ใช่ Sheet ของ Excel)
เอา Data จาก Data Model เข้า DAX Studio
พอเรามีข้อมูลใน Data Model แล้ว จากนั้นกดเรียกใช้โปรแกรม DAX Studio ได้เลย
จากนั้นมันจะเปิดโปรแกรม DAX Studio ขึ้นมา ให้เรากด Connect ได้เลย
สังเกตว่า Data Source เอาจาก Data Model ของ Power Pivot ใน Excel ที่เป็นชื่อไฟล์ของเรา (ของผมไฟล์ชื่อ Book1)
ให้เราพิมพ์ DAX Query ว่า EVALUATE ตามด้วยชื่อ table (ที่เราเอาเข้าไปใน Data Model) เช่น
EVALUATE Query1
จากนั้นให้เลือกว่าจะเอา Output ออกมาแบบ File
แล้วก็กดปุ่ม Run ได้เลย
จากนั้นเค้าจะให้เลือกว่า จะ Export ไปที่ไหน? ไฟล์ชื่ออะไร? รูปแบบ Format แบบไหน? ซึ่งเลือกได้ทั้ง Text และ CSV เลย
จากนั้นก็รอ จนมันบอกว่าเขียนไฟล์เสร็จแล้ว เป็นอันจบครับ
