หลายคนน่าจะใช้ Power Query (ทั้งใน Excel และ Power BI) ดึงข้อมูลจากไฟล์ต่างๆ ทั้งไฟล์ Excel / CSV หรือแม้กระทั่งดึงหลายๆ ไฟล์จาก Folder ในเครื่องตัวเองมารวมกันได้แล้ว… แต่อาจเริ่มอยากพัฒนาไปอีกขึ้น คือ การเอาแหล่ง Data Source ไปไว้บน Online เลย
เพราะถ้าเราดึงจากแหล่งออนไลน์ได้ มันดีกว่าการดึงจากเครื่องตรงที่สามารถทำการ Schedule Refresh ให้ Power BI ได้ รวมถึงแหล่งข้อมูลเอาวางไว้ตรงกลาง ให้เพื่อนๆ มาดึงไฟล์ไปใช้ได้
ว่าแต่การดึงข้อมูลจากแหล่งออนไลน์ด้วย Power Query มันทำยังไงกันแน่? เพราะวิธีมันไม่ได้ตรงไปตรงมานัก
คนที่เคยพยายาลอง Get Data จากบริการ Online ของ Microsoft เองจะพบว่ามันไม่ง่ายเลย บอกเลยว่า จริงๆ แล้วการดึงจากระบบ Microsoft เผลอๆ อาจยากกว่าดึงจากบริการของ Google ซะอย่างงั้น 555
ไม่ต้องห่วง เพราะในบทความนี้ผมจะบอกวิธีการดึงโดยละเอียด และท้ายสุด เราจะมีวิธีดึงข้อมูลจาก Folder ที่มันอยู่ในระบบ Online ยังไงให้มัน Work? ใครอยากรู้อ่านต่อได้เลยครับ!
สารบัญ
ดึงข้อมูลจาก Google Sheets
การดึงข้อมูลจาก Google Sheets ที่ง่ายที่สุดคือให้เรา Publish ข้อมูลบน Google Sheets ก่อน โดยไปที่ File- > Share -> Publish to web (ไม่ใช่กด share link ปกตินะ)
การใช้ Publish to web ข้อเสีย คือถ้าใครก็ตามที่มี Link URL ก็จะโหลดไฟล์ไปได้เลย

แล้วกดเลือกว่าจะ Publish เป็นอะไร ซึ่งตัวที่ผมขอแนะนำคือเป็น Microsoft Excel (.xlsx) เพราะเผื่อว่ามีข้อมูลหลายชีท จะได้ไม่มีปัญหา

จากนั้นเราก็จะได้ Link มาซึ่งสามารถเอา URL ที่ได้นี้ไปใช้ใน Power Query โดยเลือก Get Data from Web แล้วแปะ URL แล้ว ok ได้เลย

แล้วผลลัพธ์ก็จะเหมือนกับการ Get Data from Excel ปกติ คือเลือก Sheet เลือก Table ได้เลย
ดึงข้อมูลจาก Google Form
อันนี้แค่เราทำการกดไปที่ Response ของ Google Form แล้วกด Link to Sheets ก็สามารถทำต่อตามหัวข้อตอนที่ดึงจาก Google Sheets ได้เลย

ดึงข้อมูลจาก One Drive Business
สำหรับอันนี้ วิธีทำ คือให้เข้า one drive for business จนเจอไฟล์ที่ต้องการ แล้วต้องกด … แล้วเลือกเปิดไฟล์ใน Desktop App ก่อน เพื่อให้ได้ File Path มา

แล้วกดไปที่ File -> Info -> Copy Path (ไม่ใช่ local นะ)

มันจะได้ Link ประมาณนี้
https://thepexcelcom-my.sharepoint.com/personal/sira_thepexcel_com/Documents/xxxxxx/ThepExcel-data-new.xlsx?web=1
ให้ตัดที่เขียนว่า ?web=1 ออกไปซะ เหลือแค่ถึง .xlsx ปกติ
มันจะได้ Link ประมาณนี้
https://thepexcelcom-my.sharepoint.com/personal/sira_thepexcel_com/Documents/xxxxxx/ThepExcel-data-new.xlsx
ดึงข้อมูลจาก Microsoft Form
ปกติ Microsoft Form จะสามารถกดเปิด Response ใน Excel ได้แบบนี้

ก็ให้กด Open in desktop app ต่อแบบนี้

แล้วก็เข้าไป Copy Path เหมือนเดิม

จะได้ Link มา (อย่าลืมลบ ?web=1 ออกล่ะ) แล้วก็ Get Data From Web ได้เลย (อาจต้อง log in ตามปกติ )
ดึงข้อมูลจาก One Drive Personal
ถ้าเราดึงข้อมูลจาก OneDrive แบบ personal เช่น เราวาง Excel ไฟล์ไว้ในนั้น ให้เราแชร์มาด้วยวีธีการนี้ ซึ่งค่อนข้าง Tricky มากๆ
ให้เรากด Share แล้ว Copy Link ตามปกติออกมา จะได้ Link ย่อแบบนี้
https://1drv.ms/x/s!xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

ให้เอา Link ย่อไปแปะบน Browser หน้าใหม่ แล้ว ok จะได้ Link เต็มแบบนี้
https://onedrive.live.com/edit?id=xxxxxxx&resid=กกกกกกกกกกกกกก&ithint=file%2cxlsx&authkey=ขขขขขขขขขขขข&wdo=2&cid=xxxxxxx

ให้ Copy Link เต็มออกมาแล้วจัดให้อยู่ในรูปแบบนี้ให้ได้
หลักๆ แล้วคือเราต้องการค่าของ resid กับ authkey มาให้ได้ ก็จะสามารถ Get Data from Web ปกติแล้วแปะ URL เข้าไปได้เลย
https://onedrive.live.com/download?resid=กกกกกกกกกกกกกก&authkey=ขขขขขขขขขขขข
ดึงข้อมูลจาก SharePoint Folder
ก่อนอื่นให้ไปที่ Folder ที่ต้องการ ซึ่งต้องบอกก่อนว่า วิธีที่ถูกต้องคือต้องวางไฟล์ไว้ใน SharePoint Site ไม่ใช่ One Drive นะครับ (แม้ว่าการวางไว้ใน One Drive พอเข้าไปในเว็บมันจะแสดงว่าอยู่ในเว็บ SharePoint ก็เถอะ)

ที่ถูกต้องคือมันต้องมี url ที่มีคำว่า sites แบบนี้
ให้เรา Copy มาแค่คำที่ต่อจากคำว่า Sites เช่น
https://thepexcelcom.sharepoint.com/sites/ThepExcel
ให้เราลอง Get Data จาก SharePoint Folder ด้วย Power BI ดู (เพราะจาก Excel บางทีมันไม่มีปุ่มให้กด)

ใส่ Site URL เข้าไป เช่น
https://thepexcelcom.sharepoint.com/sites/ThepExcel
มันจะดึงข้อมูลมาทั้ง Site เลยแบบนี้ ด้วยสูตร M Code ประมาณนี้
= SharePoint.Files("https://thepexcelcom.sharepoint.com/sites/ThepExcel/", [ApiVersion = 15])

กลายเป็นว่ามันเยอะเกินไป และถ้าเราอยากจะเลือกเฉพาะบาง Folder ต้องไป Filter เอาจาก Folder Path คอลัมน์สุดท้ายทีหลัง ซึ่งจะช้ามากเพราะมันต้องอ่านทุกไฟล์เลย

เคล็ดลับคือ ให้เปลี่ยน M Code จาก SharePoint.Files เป็น SharePoint.Contents
เราจะเห็น Structure ของ Site ออกมาก่อน ไม่ใช่เห็นทุกไฟล์ตามปกติ

ถ้า Copy Query หรือ M Code ที่ได้ไปใช้ใน Excel ก็จะได้เหมือนกัน
แปลว่าสิ่งที่จำเป็นจริงๆ คือ M Code ส่วนแรก อันนี้ (ซึ่งในอนาคต ApiVersion อาจเปลี่ยนไปได้อีก ต้องไปคอยดูว่าใน Power BI เรียกแบบไหน)
=SharePoint.Contents("https://thepexcelcom.sharepoint.com/sites/ThepExcel/", [ApiVersion = 15])
พอเข้าไปใน SharePoint Site ด้วย SharePoint.Contents ได้แล้ว เราค่อยเข้าไปใน Object ที่เราต้องการ โดยกดเข้าไปตรง Table ตรงๆ เช่น Shared Documents มันจะเข้าไปทีละชั้นได้ แบบนี้จะ Work กว่ามาก


ดึงข้อมูลจาก Website ทั่วไป
ถ้าเว็บนั้นเป็น Public ใครๆ ก็เข้าได้ และมี Static Content พวกตารางอยู่ในหน้านั้น ก็ดึงมาตรงๆ ได้เลย เช่น
https://th.wikipedia.org/wiki/รายชื่อจังหวัดของประเทศไทยเรียงตามพื้นที่
เวลาเอาเข้า Power Query ก็จะมองเห็นข้อมูบลในเว็บนั้นตามปกติ แล้วก็เลือก Table ที่ต้องการได้เลย

ดึงข้อมูลจาก Web ผ่าน API
ถ้าเว็บที่เราจะดึงข้อมูลมี API (Application Programming Interfaces) หรือตัวกลางที่ไว้ใช้สื่อสารในการดึงข้อมูลให้ใช้ ก็สามารถใช้ Power Query ดึงข้อมูลจากเว็บนั้นมาได้ โดยทำตามนี้ (เขียนไว้หลายปีที่แล้ว มีหลายตอนนะ รายละเอียดเยอะ)
ซึ่งการใช้ API นั้นจริงๆ ค่อนข้างยืดหยุ่นมาก แต่ก็ใช้ความสามารถทางเทคนิคสูงหน่อย อย่างไรก็ตามในยุคนี้เราใช้ AI มาช่วยได้ด้วย ไว้ผมจะเขียนบทความใหม่ให้อีกทีนะครับ
Leave a Reply