เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Power QueryData Model

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File

export power query

ช่วงนี้เพื่อนๆ หลายคนเริ่มใช้ Power Query กันเยอะมากขึ้นแล้ว ทำให้เริ่มเจอปัญหาการใช้งานมากขึ้น (ซึ่งเป็นเรื่องที่ดี) มีคำถามจาก Inbox ที่ช่วงนี้ผมได้รับมาบ่อยนั่นคือ จะสามารถ Export ข้อมูลที่สร้างจาก Power Query แล้วเยอะเกินล้านบรรทัดได้ยังไง? เพราะจะเอาออกมาเป็น Table ใน Sheet ของ Excel ก็ไม่ได้ เพราะมันรับได้แค่ 1,048,576 บรรทัด หรือ ล้านกว่าบรรทัด (ต่อ 1 Sheet) เท่านั้น

ผมคิดว่าคงติดปัญหานี้กันหลายคนพอสมควร การเขียนบทความอธิบายน่าจะเกิดประโยชน์สูงสุดครับ

แนวทางแก้ไขปัญหา

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 1

หลักการโดยรวมคือ เราจะเอาผลลัพธ์จาก 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 ด้วยนะ (ปกติก็ติ๊กไว้อยู่แล้ว อย่าไปติ๊กออกล่ะ)

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 2

พอ Install เสร็จแล้ว หากเราลองเข้า Excel จะเห็น Ribbon พิเศษขึ้นมา ดังนี้

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 3

ทำ Query ตามต้องการ

จากนั้นก็ให้เราทำ Query ของเราตามต้องการได้เลย อันนี้ก็แล้วแต่งานของเพื่อนๆ ว่าจะเป็นยังไง ซึ่งจะมีบรรทัดเยอะหลายล้านบรรทัดก็ได้นะ

ในที่นี้ผมสร้างเลข Running 1-5 ล้านใน Power Query ไว้ละกัน ใครอยากลองทำตามก็เขียน Query ใน Formula Bar ของ Power Query ว่า

= {1..5000000}

จากนั้นกด Enter มันจะสร้าง List ออกมาให้ก่อน แล้วเราก็กด Convert to Table ได้เลยครับ (ใครงงกับวิธีสร้าง List นี้ลองไปอ่านเพิ่มได้ที่นี่)

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 4

เอาผลลัพธ์จาก Query ออกไปยัง Data Model

จากนั้นก็กด Close & Load to… เพื่อเอาผลลัพธ์ของ Query ที่เราทำออกมา

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 5

จากนั้นให้เลือก Connection Only และติ๊ก Add this data to the Data Model ด้วย
ตรงนี้สำคัญมาก เพราะจะเป็นการส่ง Data ผลลัพธ์จาก Power Query ไปใช้งานต่อใน Data Model ล่ะครับ

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 6

จากนั้นจะเห็นว่า Data ถูกโหลดออกมา 5 ล้านบรรทัดจริงๆ (แต่โหลดเข้าไปใน Data Model นะ ไม่ใช่ Sheet ของ Excel)

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 7

เอา Data จาก Data Model เข้า DAX Studio

พอเรามีข้อมูลใน Data Model แล้ว จากนั้นกดเรียกใช้โปรแกรม DAX Studio ได้เลย

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 8

จากนั้นมันจะเปิดโปรแกรม DAX Studio ขึ้นมา ให้เรากด Connect ได้เลย
สังเกตว่า Data Source เอาจาก Data Model ของ Power Pivot ใน Excel ที่เป็นชื่อไฟล์ของเรา (ของผมไฟล์ชื่อ Book1)

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 9

ให้เราพิมพ์ DAX Query ว่า EVALUATE ตามด้วยชื่อ table (ที่เราเอาเข้าไปใน Data Model) เช่น

EVALUATE Query1
วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 10

จากนั้นให้เลือกว่าจะเอา Output ออกมาแบบ File

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 11

แล้วก็กดปุ่ม Run ได้เลย

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 12

จากนั้นเค้าจะให้เลือกว่า จะ Export ไปที่ไหน? ไฟล์ชื่ออะไร? รูปแบบ Format แบบไหน? ซึ่งเลือกได้ทั้ง Text และ CSV เลย

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 13

จากนั้นก็รอ จนมันบอกว่าเขียนไฟล์เสร็จแล้ว เป็นอันจบครับ

วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File 14
แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 581
  •  
  •  
  •  
  •  
  • 581
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply