ปกติแล้วเวลาเราใช้ Power Query ดึงข้อมูลจาก Website เราก็มักจะต้องดึงข้อมูลที่มีลักษณะเป็นตารางแบบ Static คือเป็น Html นิ่งๆ โง่ๆ ถึงจะดึงได้ แต่ปัญหาก็คือเว็บไซต์สมัยใหม่ๆ มันจะไม่ค่อยเก็บข้อมูลในลักษณะตารางนิ่งๆ แต่จะเป็นเว็บในลักษณะที่เป็น AJAX และดึงข้อมูลกลับมาเป็นไฟล์รูปแบบที่เรียกว่า JSON ผ่าน API (หรือ Application Programming Interface) ซึ่งเป็นวิธีที่โปรแกรมแต่ะตัวใช้สื่อสารแลกเปลี่ยนข้อมูลกันซะมากกว่า
ซึ่งถ้าเราสามารถใช้ Power Query ดึงข้อมูลผ่าน API ได้ เราจะได้ข้อมูลที่ Clean และยืดหยุ่นว่าการดึงจาก Website ตรงๆ มากเลย นอกจากนั้น บางเว็บไซต์ก็จะมีการเปิด API ให้คนมาเชื่อมต่อได้เลย ซึ่งมักจะมีคู่มือบอกด้วยว่าจะติดต่อ API ของเว็บนั้นๆ ได้ยังไง ซึ่งดีมากๆ เลย
**แต่ถ้าเว็บไหนไม่ได้บอกตรงๆ เราก็ต้องไปใช้ Inspector ของ Web Browser ดูเอาได้ว่ามีการส่งขอ API ที่ Address ไหน (ใน Chrome กด F12 แล้วดูใน Tab Network ในส่วนของ XHR ได้) ซึ่งเดี๋ยวจะทำให้ดูอีกทีในบทความถัดๆ ไปครับ
Power Query นั้นอ่าน JSON จาก API ได้สบายๆ
ปกติแล้วเวลาเชื่อมต่อ API ก็จะได้ผลลัพธ์กลับมาเป็นข้อมูลในลักษณะ JSON (JavaScript Object Notation ซึ่งจะเก็บข้อมูลเป็น Text ในลักษณะของ Key คู่กับ Value คล้ายกับ Dictionary ใน Python หรือ Record ของ Power Query) ซึ่งมันจะเก็บข้อมูลซ้อนกันได้หลายชั้นเลย
ปกติเค้าก็จะใช้การเขียนโปรแกรมเช่น R, Python เพื่อดึงเอาข้อมูลที่ต้องการออกมาอีกที แต่ถ้าเราใช้ Power Query มันจะง่ายกว่ามากๆ เลย เพราะเราสามารถอ่านข้อมูล JSON แล้วแตกออกมาเป็นตารางได้แบบสบายๆ
GET vs POST
ปกติแล้วการที่เราเข้าไปใน Web แล้วจะเห็นข้อมูลในเว็บได้นั้น Web Browser ทางฝั่ง Client จะมีการส่ง HTTP Request ไปขอข้อมูลจากฝั่ง Server แล้วฝั่ง Server จึงค่อยส่งข้อมูลที่ขอกลับมาแสดงผลยังฝั่ง Client อีกที
ซึ่งการส่งข้อมูลจากฝั่ง Client ไป Server นั้นที่พบบ่อยๆ และเกี่ยวข้องกับ Power Query จะมีอยู่ 2 แบบ คือ GET และ POST ซึ่งแตกต่างกันดังนี้
GET Method
GET เป็นวิธีการสื่อสารที่มีหน้าที่ดึงข้อมูลมาจากฝั่ง Server โดยสามารถกำหนดเงื่อนไขได้ภายใต้สิ่งที่เรียกว่า Query String ซึ่งจะระบุหลังเครื่องหมาย ? ใน URL ของ website
ที่ทุกคนเจอการทำงานนี้ได้บ่อยสุดๆ คือ google search นี่แหละ เช่น https://www.google.com/search?q=thepexcel ก็แปรว่ามีการระบุ parameter q ที่มีค่าเป็นคำว่า thepexcel ไปให้ฝั่ง server ด้วย
ซึ่ง query string สามารถระบุได้หลาย parameter โดยให้คั่นด้วยเครื่องหมาย & นะครับ
หรืออย่างเว็บ https://twitter.com/search?q=%23BLINK&src=trend_click&vertical=trends แปลว่ามีการส่ง parameter ไป 3 ตัว คือ q มีค่าเป็น #BLINK (%23 คือ #) , src มีค่าเป็น trend_click และ vertical มีค่าเป็น trends นั่นเอง
การที่ต้องส่ง Parameter กลับไปใน URL ของเว็บนั้นค่อนข้างมีข้อจำกัด คือ มันจะยาวมากก็ไม่ได้ แถมยังโผล่มาให้เห็นจะๆ ใน URL อีก ดังนั้นจึงมีวิธีการสื่อสารอีกแบบนั่นก็คือ Post นั่นเอง (แต่ก็ดีตรงที่เรา Bookmark URL ไว้ได้เลย)
POST Method
เป็นวิธีการสื่อสารที่มีการส่งข้อมูลกลับไปที่ Server โดยที่จะไม่ได้มีการบันทึก Parameter ใน URL แต่อย่างใด แต่ว่าจะบันทึกไว้ในส่วนของ Body Message เลย ซึ่งเหมาะกับข้อมูลที่มีจำนวนมาก และมักใช้ในการใช้ Create ข้อมูลในฝั่ง Server (มักใช้ในการ Submit Form )
ซึ่งการใช้ Power Query กับ GET จะง่ายกว่า POST ครับ เดี๋ยวบทความตอนนี้เรามาดูวิธีใช้กับ GET กันก่อนนะครับ
ลองดึง Data จาก API แบบง่าย
เราจะลองดึงข้อมูล Covid-19 API ที่เปิดให้คนที่สนใจเข้าไปดึงได้ โดยไม่ต้องมี Key ประจำตัว ซึ่งจะง่ายมากๆ ครับ โดยที่ข้อมูลที่เราจะดึงมาคือข้อมูลสรุปตามช่วงเวลา ซึ่งมี URL ดังนี้ https://covid19.th-stat.com/api/open/timeline
ถ้ากด URL เข้าไปตรงๆ ก็จะเจอข้อมูลแบบนี้ เรียกว่าเป็นข้อมูลในรูปแบบ JSON ซึ่งจะเห็นว่าหน้าตามันจะคล้ายๆ XML ที่ไม่ต้องมี Tag เปิดปิด แต่ใช้การเก็บข้อมูลในลักษณะของ {“key”: “value”} แทน ซึ่งใน value เราจะเก็บข้อมูลเป็น Array หรือ List ด้วยรูปแบบ [ a,b,c ] ก็ได้
แต่ถ้าใครมี Extension พวก JSON View ก็จะเห็นสวยงามขึ้นเป็นแบบนี้
ซึ่งถ้าเราลองใช้ Power Query Get Data from Web แล้วใส่ URL ที่เป็น API ลงไป ก็จะได้ผลลัพธ์แบบนี้ ซึ่งในนี้เราเห็นเป็น List ที่เก็บข้อมูลเอาไว้ 5 ส่วน แล้วในส่วนของ Data ก็เก็บ List ซ้อนไว้ข้างในอีก แล้วใน List นั้นก็มี Record เต็มเลย
ก่อนอื่นใก้เรากด Convert into Table (ปุ่มซ้ายบน) ซะจะได้แบบนี้
แล้วเราก็ Transpose แล้ว Use First Row as Header ซะ เพื่อเตรียมตัวแตก List ในคอลัมน์ Data ออกมา
จากนั้นกดปุ่ม Expand ที่หัวตารางของคอลัมน์ Data แล้วกด Expand to new Row เราก็จะได้ Record ออกมาในแต่ละบรรทัดแล้ว
จากนั้นก็ Expand อีกรอบเพื่อกระจายหัวตารางที่ต้องการออกมา
อย่าลืมเปลี่ยน Data Type ให้เรียบร้อย โดยเฉพาะวันที่ อาจต้องใช้ Change Type using Locale… แบบ English US (เพราะเป็นเดือน/วัน/ปี)
พอเปลี่ยน Data Type เรียบร้อยมันก็จะกลายเป็นวันที่ ที่แสดงใน Format ตาม Control Panel เครื่องเรา
จากนั้นก็กด Close & Load ออกมาเป็นตาราง หรือจะทำกราฟอะไรก็ทำไปได้เลย จบแล้ว
ลองดึง Data จาก API ด้วย GET Method แบบทำเป็น Function
ผมจะลองใช้ API ของที่ https://weatherstack.com/ เพื่อดูสภาพอากาศของที่ต่างๆ บนโลกดูนะครับ ก่อนอื่นก็ให้ไปสมัคร account ในเว็บนั้นเพื่อเอา API Key มาก่อน (กรอกข้อมูลนิดหน่อย ก็ใช้ Account ฟรีได้แล้ว)
สร้าง Query ที่ดึงข้อมูล 1 ชุดให้ได้ก่อน
โดยที่เราจะเอา Current Weather มาดูรายละเอียดของแต่ละจังหวัด/ประเทศออกมาดู โดยที่รูปแบบการใช้ API เป็นดังนี้ (ดูได้ใน Documentation)
http://api.weatherstack.com/current? access_key = API_KEYของคุณ & query = ชื่อสถานที่เช่น ถ้าผมอยากได้สภาพอากาศของกรุงเทพ ก็จะเขียนแบบนี้
http://api.weatherstack.com/current? access_key = API_KEYของคุณ & query = Bangkokถ้าดูใน Google Chrome จะเห็นแบบนี้
ซึ่งเอาไประบุได้ในส่วนของ URL ในเมนู Get Data from Web ของ Power Query เลยครับ ก็จะเห็นแบบนี้ ซึ่งจะเห็น Record ซ้อน Record อยู่ ซึ่งในชั้นแรกจะมี 3 Record คือ request, location, current และในแต่ละอันก็จะมี record ซ้อนอยู่ข้างในอีก เช่น ใน request ก็จะมี type, query, language และ unit
ผมแนะนำให้กด convert into Table ก่อน (ซ้ายบน) เพื่อแปลง record นี้ให้กลายเป็น Table ซึ่งจะได้ดังนี้
ถ้าเรากด Expand Column Value ออกมาตรงๆ เราจะเจอปัญหาว่า มีผลลัพธ์ออกมา 3 บรรทัด และมีคอลัมน์หลายๆ อันเป็น Blank ดังนี้ (ซึ่งเป็นผลลัพธ์ที่ไม่ ok เราจะไม่ทำแบบนี้ ถ้าใครกด expand ไปแล้วก็ให้ลบ step นี้ทิ้งซะ)
สิ่งที่เราจะทำคือ หลัง Convert เป็น Table แล้ว เราจะ Transpose ข้อมูล แล้ว Use First Row as Header ซะ (ลบ step change type ไปเลยก็ได้ เพราะยังไม่ใช้)
พอเป็นแบบนี้เราก็พร้อมที่จะ Expand คอลัมน์ทั้ง 3 ชุด ออกมาแล้ว (Expand ทีละชุด) จากนั้นแก้ชื่อ Query ให้เรียบร้อย
ทำไมต้องทำ Query ให้เป็น Function
ทีนี้จะเห็นว่า Query เราสามารถดึงข้อมูลจาก Web API ได้แล้ว แต่มันดึงแค่ Bangkok ตามที่เราระบุไป ซึ่งมันจะลำบากถ้าเราต้องมานั่งแก้ Source ของ Query ให้กลายเป็นเมืองอื่นนอกจาก Bangkok อีกไปเรื่อยๆ และถ้าเรามีเป็นสิบเป็นร้อยเมืองที่ต้องการข้อมูลภูมิอากาศล่ะ?
ดังนั้นมันจะดีกว่ามาก ถ้าเราสามารถทำให้ชื่อเมืองเป็น Parameter แล้วสร้าง Function ขึ้นมารับค่า Parameter นั้นแล้วค่อยทำงานดึงข้อมูลจาก API ตาม Parameter ที่ใส่ลงไป ซึ่งฟังดูเหมือนยาก แต่วิธีทำใน Power Query นั้นง่ายมาก
ขั้นแรกให้สร้าง New Parameter ขึ้นมา อาจใช้ชื่อเป็น SearchTerm ก็ได้
จากนั้นเอา Parameter นี้ไปใส่แทน Bangkok ใน step Source ดังนี้ ซึ่งผลลัพธ์จะยังคงเหมือนเดิม เพราะ Current Value ของ Parameter ยังเป็นคำว่า Bangkok อยู่
แปลง Query ให้เป็นฟังก์ชัน
ให้คลิ๊กขวาที่ Query GetCurrentWeather แล้วกด Create Function… แล้วตั้งชื่อตามต้องการ เช่น GetCurrentWeatherFX
ซึ่งมันจะสร้างฟังก์ชันให้เราโดยกำหนดให้ Parameter ของ Function คือ SearchTerm นั่นเอง
ทีนี้เราก็จะมีฟังก์ชันที่พร้อมใช้งานแล้วครับ
ลองดึงสภาพภูมิอากาศของทั้ง 76 จังหวัดในประเทศไทย
คราวนี้เรามาลอง List ชื่อจังหวัดทั้ง 76 จังหวัด ซึ่งอาจเอาจากเว็บไหนก็ได้ที่สะกดเป็นภาษาอังกฤษ หรือจะเอาจาก Database ที่ผมเคยทำไว้ให้ก็ได้ โดยผมตั้งชื่อ Query ที่เก็บรายชื่อจังหวัดไว้ว่า ProvinceList ดังนี้
ให้เรากด Add Column -> Invoke Custom Function เพื่อเรียกใช้ GetCurrentWeatherFX โดยใช้ Parameter จากชื่อจังหวัด ดังนี้
ในแต่ละบรรทัดเราจะได้ผลลัพธ์เป็น Table ที่มีค่าต่างๆ ที่เราต้องการ
จากนั้นเราสามารถกด Expand Column ออกมาได้เลย
ปรากฏว่าผลลัพธ์ดันปนประเทศอื่นมาด้วย เพราะ Search Term อาจจะซ้ำหลายที่
ทางแก้ก็อาจจะระบุประเทศลงไปให้ชัดเจนเลยก็ได้ โดยให้เอา City Name, Country Name เช่น Nan, Thailand แบบนี้ (ใน Document ของ API มีเขียนบอกไว้)
เราจะแก้ตอนที่ Invoke Function หรือจะไปแก้ใน Query GetCurrentWeather ก็ได้ ซึ่งอันนี้ผมจะแก้ตอน Invoke Function เป็นดังนี้
จาก
= Table.AddColumn(#"Changed Type", "GetCurrentWeatherFX",
each GetCurrentWeatherFX([ProvinceEng]))เป็น
= Table.AddColumn(#"Changed Type", "GetCurrentWeatherFX",
each GetCurrentWeatherFX([ProvinceEng]&", Thailand"))พอแก้ใส่ Thailand เพิ่มไปผลลัพธ์ก็จะแทบไม่ผิดแล้ว (ถูกต้องประมาณ 90%++ แต่ก็มีผิดอยู่ดี 555)
ถ้าจะให้ดีต้องระบุเป็น lat, long ไปเลยครับ เช่น SearchTerm = 40.7831,-73.9712 แบบนี้ก็จะเป๊ะที่สุดละ ไม่มีทางผิดแน่นอน
แต่ว่า API แบบฟรีนี้จะดึงได้แค่เดือนละไม่กี่ทีนะครับ ถ้ามันถึง Limit แล้วจะดึงไม่ได้ ก็จะขึ้น Error ประมาณนี้
หวังว่าเพื่อนๆ คงพอจะเห็นภาพการใช้ Power Query ทำงานกับ API แบบ Get Method แล้วนะครับ ไว้เดี๋ยวตอนถัดไปมาดูวิธีเชื่อมต่อแบบ Post Method กัน ซึ่งจะซับซ้อนกว่านี้นิดหน่อยครับ
