Lookup value ระหว่างไฟล์บน Excel Online ด้วย Power Automate

Content difficulty — user

วันนี้เราจะมาทำ lookup value ระหว่างไฟล์ MS Excel ทั้ง 2 ไฟล์ ที่อยู่ระหว่าง 2 locations ซึ่งจะใช้ Power Automate โดยความคาดหมายคือจะพยายามจำลอง VLOOKUP ให้ update ค่าจากไฟล์แรกที่เป็นไฟล์อ้างอิงไปสู่ไฟล์ที่สอง เพิ่ออาจจะนำไปใช้ในแนวทางแก้ปัญหาการที่เราจะต้องคีย์ข้อมูล manual กับงานเดิมๆที่ต้องไป lookup ไฟล์กลาง ซึ่งในบทนี้จากที่ test ก็จะมีทั้งกรณีที่สามารถทำได้และทำไม่ได้

ปัญหา และ แนวทางการแก้ปัญหา

สมมุติว่ามีไฟล์ 2 ไฟล์ ชื่อ ref.xlsx และ test.xlsx ซึ่งอยู่ต่างที่กัน ไฟล์ ref.xlsx จะเป็นไฟล์ตารางไว้อ้างอิงกลางอยู่ใน SharePoint Online ส่วน test.xlsx จะอยู่ใน OneDrive for Business ซึ่งเป็นไฟล์ตารางที่เราอยากจะ lookup ข้อมูลจากไฟล์อ้างอิง โดยที่ naming ของตารางทั้ง 2 ไฟล์จะชื่อเดียวกันว่า Table1

ในตัวอย่าง naming ตารางชื่อเดียวกัน แต่จริงๆจะคนละชื่อก็ได้

ไฟล์ ref.xlsx อยู่ใน SharePoint Online ส่วน test.xlsx อยู่ใน OneDrive for Business ซึ่งจะเห็นว่าคนละ site กัน

ต้องการเอาค่า refvalue ไปเติมที่ lookup-result (ปลายทางมี 8 rows)

ตารางทั้งสองไฟล์ชื่อ Table1 เหมือนกัน

ให้เราสร้าง flow ประเภท instant โดยใช้ manually trigger the flow (การ start flow โดยการ กด run แบบ manual)

สร้าง flow แบบ Instant-from blank

ระบบก็จะให้ trigger เป็นแบบ manual (ซึ่งจริงๆเราใน input parameter ได้)

ต่อมาหา action ชื่อ List rows present in a table ของ connector ชื่อ Excel Online (Business) ซึ่งไว้ไป get file แบบ .xlsx บน OneDrive for Business และ SharePoint Online ซึ่งให้ใส่ File ที่เป็น directory path กับชื่อ Table ของไฟล์ปลายทางที่เราจะเอาผล lookup มาซึ่งในกรณีนี้คือ test.xlsx

Excel file version เก่าๆอย่าง .xls อาจจะไม่ support กับ connector อันนี้ถึงแม้ว่า upload ขึ้นและดูผ่าน Excel Online ได้ก็ตาม

ตัว Power Automate action ของ Excel อาจจะไม่ support ตัว Excel ที่ใช้ VBA Toolbar พวก ActiveX Control

List rows present in a table แล้วก็เลือกชื่อ Table1

ผลก็คือตัว flow จะไป get schema โครงสร้างตารางและเห็น row ทั้งหมด (ในกรณีนี้มี 8 rows) ต่อมาให้เราใช้ connector เดิมแต่ action คือ Get a row โดย path จะไปเอาไฟล์ reference คือ ref.xlsx มา โดย Key Column จะเป็น refID ของไฟล์ ref.xlsx แต่ส่วน Key Value จะเอา column ของ ไฟล์ test.xlsx (testID) มาใส่แทนเพื่อทำการ lookup มาได้ ซึ่ง testID จะมาจาก Dynamic content ของ action ที่แล้ว

ผลก็คือ ตัว flow จะสร้าง Apply to each แล้วใส่ value มาครอบเนื่องจากค่า testID เป็น Array เพราะ action ที่แล้วเรา List row present in a table มาได้ 8 rows เพราะฉะนั้น action ทั้งหมดภายใต้ Apply to each จะวน loop มา 8 ครั้งตามค่า testID ในแต่ละ row

ใส่ตัวแปรและระบบเห็นว่าเป็น array เลยใส่ Apply to each มาให้

จากนั้น Action สุดท้ายใน Apply to each คือ Update a row ซึ่งให้ update กลับไปที่ ไฟล์ test.xlsx โดย Key Column และ Key Value เป็น column ตัว testID ของไฟล์ test.xlsx ส่วน lookup-result ให้เติมโดยใช้ column ของไฟล์ ref.xlsx (refvalue) ซึ่งตัวแปรได้มาจาก Dynamic content ของ action ที่แล้ว

Test Run ผ่านเป็นอันใช้ได้

เมื่อทดลอง run จะพบว่าระบบจะกรอกค่าไม่ครบ ถ้าค่าไหนซ้ำก็จะกรอกแค่เฉพาะ row แรกที่ flow เจอสาเหตุเป็นเพราะว่าตัว flow จะกรอก unique value แรกที่เจอเท่านั้น เนื่องจาก Update a row ของเรา Key Column กับ Key Value เราเลือกมาจาก Column ที่ไม่ unique นั่นเอง ซึ่งในกรณีนี้ testID เรามีค่าซ้ำอยู่พอสมควร ซึ่งตรงนี้ก็เป็นสถานการณ์ที่ไม่สำเร็จ

lookup value สำเร็จแต่มีค่าว่าง (missing values) ตัว flow กรอก unique value แรกที่เจอเท่านั้น

คราวนี้ถ้าในกรณี test.xlsx มี column ใหม่เพิ่มเข้ามาเป็น unique value ชื่อว่า name

ในกรณีนี้มีการเพิ่ม column ชื่อ name ที่มีค่า unique ในทุกๆ row ในไฟล์ test.xlsx

เราไปแก้ flow ก็จะสามารถให้ lookup value กับ row ที่เคยซ้ำไปแล้วได้โดยใน flow จะต้อง เปลี่ยน action ตัว Update Row ให้มี Key Column กับ Key Value เป็น column ของ name เนื่องด้วย value ในทุกๆ row ของ name ไม่มีการซ้ำกันเลย (unique row) ก็จะทำให้การ update นั้นไป update กับ value ของ testID ที่ซ้ำได้

ถ้าเพิ่งมีการ edit/updateชื่อ column หรือ เพิ่มลด column ตัว flow อาจจะยังมองไม่เห็นค่าที่ update ให้ delete ตัว action นั้นๆก่อนแล้ว add ใหม่ (เช่นของ Update a row)

เปลี่ยน Key column เป็น column ที่มี unique row

พอ run flow แล้ว lookup value สำเร็จไม่มี missing values

ข้อสังเกต

ในกรณี Action ตัว Update a row ช่องที่ไม่ใส่ (testID, name) จะไม่ได้ทำให้ไฟล์ปลายทางมีค่าว่าง (ไม่ได้เอาค่าว่างไปทับของเดิม) จะทำงานต่อเมื่อเราใส่ text หรือสูตร Excel เท่านั้น

จากตัวอย่างที่แล้ว เว้นค่าไว้ไมได้เอาไปทับของเก่าตอน run

ใส่ text หรือ สูตรได้

แต่ถ้าอยากใส่ค่าว่างแนะนำให้สร้างตัวแปรว่างขึ้นมาซะก่อน เช่นก่อนเข้า Apply to each ให้ Initialize variable โดยไม่ต้องกำหนด value แล้วก็ ใส่ตัวแปรนั้นไปช่องที่ต้องการค่าว่าง หรือถ้าไม่ใช่วิธีนี้สามารถเขียน fuction คือ concat(‘’) เพื่อทำค่าว่างไว้ก็ได้

ใส่ตัวแปรว่างเพื่อการลบ cell (update เป็นค่าว่าง)

หรือโดยใช้ Expression function แทนการใช้ตัวแปร