หา Mismatch Rows ของ Data table ด้วย Power Automate

Content difficulty — moderate

จากตอนที่แล้วได้นำเสนอเรื่องของการใช้ lookup value ระหว่าง 2 Data sources โดยใช้ actions ใน Power Automate มาคราวนี้มีโจทย์มาให้โดยคิดว่าเราใช้ Power Automate ในการแก้ปัญหาครับ

ตอนที่แล้วสามรถไปดูได้ครับ https://link.medium.com/AUXHHY2y8qb

โดยทางบริษัทมีโจทย์มาให้ประมาณว่าเรามี Master Data source ที่เป็น table เก็บราคาหรือค่าๆที่เป็นอ้างอิง reference เอาไว้ แต่จะมีอีก table นึงเป็น table ที่มาจาก Database เช่น Oracle, SQL Server ที่จะมีคนมาใส่ Data (input orders) ในแต่ละวันซึ่งราคาจะมีค่าที่ตรงและไม่ตรงกับ อ้างอิงของเรา ซึ่งทางเราเองอยากจะรู้ว่าในแต่ละวันมี order row ไหนบ้างที่ไม่ตรงกับค่า data อ้างอิง จากนั้นก็มาแจ้งเตือนเรา

ซึ่งเดียวผมจะทำมา 3 tables เป็นเหตุการณ์สมมุติดังนี้ครับ

1. Master เป็นตัวที่เราจะ reference มี value ที่อ้างอิงไว้
2. MainTB เป็นตัวที่ user จะมากรอก input order และเป็นตัวที่เราจะตรวจว่าแต่ละวันมี row ไหนไม่ตรงกับ reference
3. Mismatch เป็นตัวที่จะมาเก็บข้อมูลว่า row ไหนใน MainTB ที่ไม่ตรงกับ Master

ที่สำคัญคือ value ของสินค้า (Package) และราคา CurrentPrice ที่เรา Reference กับ RecordPrice ที่ตาราง orders (ที่ชื่อ MainTB) ตาราง Data จะง่ายๆไม่ซับซ้อนตามนี้

สมมุติว่าวันนี้มี Order Input มา 11 orders

รอเอาค่าที่ผิดจาก ref มาใส่

จากตรงนี้มองเร็วก็จะมี MainTB ค่าที่เป็น RecordPrice ที่ไม่ตรงราคา CurrentPrice อยู่นะครับ ภายใต้ Package เดียวกัน ซึ่งมีทั้งหมด 6 rows ที่อยากจะเอามาใส่ในตาราง Mismatch และทำแบบนี้ในทุกๆวัน

ทำยังไงจะเอาค่าที่ไม่ตรงมาใส่ตาราง Mismatch

ในกรณีนี้ผมจะใช้ Excel เป็น Data source นะครับ และอยู่ใน file เดียวกัน แต่จริงๆแล้วเรา apply ตารางที่ต่าง Excel file ต่าง SharePoint Site หรือ File Sharingได้ หรือกับ Database ของ SQL Server, Oracle หรือ Service อย่าง Dataverse, SharePoint List ครับ ก็แล้วแต่ว่าเรามี License สามารถ connect ได้หรือไม่ อีกอย่างก็ต้องมี permission ในการ connect read/write และ Data source นั้นจะบน Cloud หรือ On premise ก็ได้ ถ้าเป็น On premise ก็จะต้องหา Server มาตั้งเป็น Role ของ On-premise gateway ก่อนนะครับ

ข้อมูล On premise gateway https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

เริ่มโดยใช้ Schedule Flow หรือ Instant (Manual) Flow

ในโจทย์จริงเราก็จะใช้ Schedule Flow เพิ่อให้ทุกวันมัน trigger run เองวันละ 1 ครั้งตามเวลาที่เรากำหนดเช่น หกโมงเย็น หรือ เที่ยงคืนเป็นต้น

ถ้าไม่ได้ใส่มันจะเริ่มเองเลยก่อนเวลาที่เรา set ครั้งนึงก่อน

ในกรณีเรา test ระบบก็จะต้องมี run test ลองผิดลองถูก ฉะนั้นผมจะใช้ Instant Flow เพื่อให้กด run เองได้แบบ manual

Instant Flow (Manual run)

จากนั้นเราจะดึงค่าจาก Excel ทั้ง 2 ตารางมาโดยใช้ Action ชื่อ List rows present in a table เพื่อความง่าย 2 ตารางนี้จะอยู่ใน File เดียวกันซึ่งจริงๆแล้วจะอยู่ในคนละไฟล์ก็ได้ถ้าเรามี permission เข้าได้หมด

ผมมีการ rename ชื่อ action เพื่อให้อ่านง่าบโดยเติมชื่อตารางที่หัว

ถ้าเป็นพวก Database ต่างๆก็จะเป็น Action ชื่อ Get Rows หรือถ้าใน Datavere จะชื่อ List Rows ถ้า SharePoint List ก็จะชื่อ List Items ซึ่ง action ของ พวกนี้สามารถ filter ได้ เช่น ถ้าจะเอาแค่ data input วันนี้เราก็ filter ตรง action ของ MainTB เพื่อจะได้ไม่ต้องโหลด Data มาเยอะเกินไปและทำให้ optimized มากขึ้น

สมมุติถ้าเรามี column ที่เป็นวันที่ เช่น Created ก็จะใช้ Expression คือ formatDateTime(utcNow(), ‘yyyy-MM-dd’) (ใช้ ‘ เครื่องหมาย single quote แบบตรง)

ถ้าลอง run ตอนนี้ Flow ก็จะดึงค่าตารางของ MainTB และ Master มาได้ ซึ่งถ้าตาม license จะได้ตารางละ 5,000 rows (ถ้าเป็น license ตัวที่มากับ Office 365) และ 100,000 rows ถ้าซึ่ง Premium license เช่น Power Automate per user หรือ Power Automate per flow แล้วไปปรับค่า Pagination ของ Action ทั้ง 2 อัน แต่ก็อย่างที่บอกยังไงควร Filter row ที่ไม่ใช้ออกก่อนดีที่สุด

ถ้ามี Premium license ก็ปรับ max rows ได้เลยไม่เกิน 100,000

ถ้าทำงานในระดับ row เยอะๆหลักหมื่นควรจะใช้ License ของ Power Automate per flow จะมี performance ที่ดีกว่า

จากนั้นเราจะมาจัดตาราง output ที่ดึงมาใหม่โดยใช Action ชื่อ Select เพราะจริงๆแล้วตัว List rows present in a table จะมี column ที่ไม่ได้อยู่ใน data เราจริงๆ ด้วยซึ่งเป็น system column นั่นคือ ItemInternalId ซึ่งตรงนี้ระบบจะ genernate มาเพื่อให้ unique เราก็เลยใช้ Select เพื่อกรองออก (ในตารางเรามีค่า OrderID อยู่แล้ว)

และการใช้ Select เราก็จะสามารถ map column โดยตั้งชื่อได้ ้ชื่อ column ในตัวอย่างนี้เราจะใช้เหมือนเดิมและใส่ Dynamic content ตามนั้น ยกเว้นใน MainTB ซึ่งเราจะตั้งชื่อเป็น CurrentPrice แทนที่จะเป็น RecordPrice ให้เหมือน Master เพื่อที่ว่าเวลา flow เราจะไปเทียบจะได้เทียบได้ไม่มองว่า CurrentPrice และ RecordPrice คือ คนละค่ากัน ไม่งั้นระบบจะมองว่าไม่เหมือนกันเลย และไม่ Detect ว่าราคาจะต่างกับที่ reference หรือไม่ ปล่อยผ่านหมด

Select เพื่อ Rename ชื่อ Column และ Rename ให้เหมือนกัน

ต่อจากนั้นเราจะใช้ Condition เพื่อแยก case ว่า row ไหนตรงกับราคา ref ถ้า row ไหนไม่ตรงจะให้ไป write ในตารางชื่อ mismatch ก่อนอื่นเราจะสร้าง apply to each แล้วใส่ค่า value ของ output ที่ได้มาจาก select ของ MainTB

Output นี้จาก Select ของ MainTB

จากนั้นข้างใน loop ของ apply to each เราจะใส่ compose เพื่อสร้างค่าของ row ที่มาเทียบของ MasterTB ใน loop แต่ละครั้งว่าค่าปัจจุบันเป็นราคาเท่าไหร่

ซึ่งอันนี้เราจะอิง column แค่ Package ของ กับ CurrentPrice ของ Master เทียบกับ Package ของ กับ CurrentPrice ของ MasterTB

ใส่ค่าของ row นั้นๆเวลาวน loop แต่ละครั้ง

{
“Package”: “xxx”,
“CurrentPrice”: “yyy”
}

ใช้ฟันหนูแบบตรง

โดย xxx คือ Expression ของค่าใน current item ของ package จากตาราง MasterTB ใน Apply_to_each loop นั้น

items(‘Apply_to_each’)?[‘Package’] ใช้ single quote แบบตรง

ส่วน yyy คือ Expression ของค่าใน current item ของ CurrentPrice จากตาราง MasterTB ใน Apply_to_each loop นั้น

items(‘Apply_to_each’)?[‘CurrentPrice’] ใช้ single quote แบบตรง

เดี๋ยวเหตุที่ต้องมี Compose เดี๋ยวอธิบายใน step ต่อไปครับว่าทำไมต้องมี

จากนั้นใน Loop เราจะมาเทียบกันว่า ราคาตรงกับ reference หรือไม่ โดยใช้ Conditions ครับเอา Output จาก Select ของ Master มาเทียบ Contains กับ Output ของ Compose ที่เราทำก่อนหน้า

ใช้ Contains ใน Condition action

ตรงนี้จะกลายเป็นแบบนี้ครับ กรณีนี้ Apply to each จะ วน loop ทั้งหมด 11 ครั้งตามจำนวน Row ของ MasterTB และ นำค่า Package และ ราคา ณ row นั้นๆมาเทียบกับ ราคา Reference ว่า ตรงกันหรือไม่ โดยไม่มี Column อื่นมาเทียบเลย

ก่อนเข้า Apply to each loop

พอเข้า loop ก็จะเช็คที่จะ rows จนครบ 11 rows 11 loops

ณ Apply to each loop ที่ 1 (case ที่ตรงกับ reference คือ aaa = 10)

ณ loop ที่ 2 (case ที่ไม่ตรงกับ reference คือ aaa ไม่เท่ากับ 10)

เนื่องจากเราจะเอาแค่ค่าที่ไม่ตรงมา record ในตารางชื่อ Mismatch เพราะฉะนั้นเราก็ไปใส่ action ใน No อย่างเดียว

ใส่แค่ใน Condition ตัว If no อย่างเดียว (ส่วน CurrentPrice ก็ปรับกลับไปใส่ใน RecordPrice ของ Mismatch)

items(‘Apply_to_each’)?[‘Package’]

items(‘Apply_to_each’)?[‘Seller’]

items(‘Apply_to_each’)?[‘CurrentPrice’]

items(‘Apply_to_each’)?[‘OrderID’]

ตรงนี้จะอธิบายทำไมต้อง Compose เมื่อทำไมไม่เอา Current Item ของ MasterTB ใน loop นั้นๆ เทียบกับ Master ที่เป็น reference เลย เพราะถ้าทำแบบนั้น Output จะเหลือแค่ 2 column คือ Package และ CurrentPrice ซึ่งเราก็ทราบว่า มี pakage ไหผิดราคาเท่าไหร่ แต่ไม่ไม่รู้ว่า Seller ใครกรอก หรือ ID ของ Order คืออันไหน พอมี Compose เอาไปเทียบแทน เรายังสามารถเก็บ Output ทั้งหมดได้

ทดลอง run

พอ run ก็จะพบว่าตัวที่ไม่ตรงกับราคา references จะถูกไปเขียน row ใน mismatch ทั้งหมด

Flow result

หลัง run เสร็จมาเช็คค่า ณ loop แต่ละครั้งได้ว่าออกค่าเป็นอย่างไร

ตาราง Mismatch มีการ insert ตัวที่ไม่ตรงกับ reference มาเรียบร้อย

ซึ่ง loop จะทำเป็นลำดับคือ row แรกเสร็จก่อนมา row ที่สองไปจนครบ ซึ่งจะเห็นได้ว่า output ของตาราง Mismatch จะเรียงตาม OrderID แต่ถ้าเราไม่สนลำดับ แต่อยากได้ performance ที่ดีขึ้นก็สามารถปรับได้ที่ Apply to each ให้ run แบบ ขนานกันได้ในส่วนของ Concurrency Control ให้ process พร้อมกันที่หลายๆ row (สูงสุด 50 ณ ตอนนี้)

Concurrency Control

Loop ก็จะทำงานเร็วขึ้น

แต่ Output ก็จะไม่เรียง loop อันไหนเสร็จก่อนก็ออกเลย

ตรงจุดนี้เราอาจจะสร้างอีก schedule flow หรือ add action ก่อนหน้าการ update เพื่อทำหน้าที่ไปลบตัว row ที่ ตาราง Mismatch ที่ flow กรอกมาของวันก่อนอีกทีก็ได้เพื่อ clean data วันเก่าๆออกไป

เพิ่มเติม

ในกรณีอยากออกเป็น email แจ้งเตือนก็ทำได้ โดยเพิ่ม Initialize variable ตัวแปรแบบ array แล้วตั้งชื่อ เช่น mismatch ให้ค่าว่างไว้

Initialize variable

ส่วนใน condition ใน apply to each ที่เป็นฝั่ง No ก็เพิ่ม Append to array variable ไป โดยใส่ Expression ในแต่ละ column เหมือนตอน update ไปที่ตาราง Mismatch

{
“Package”: “xxx”,
“RecordPrice”: “yyy”,
“Seller”: “zzz”,
“OrderID”: “aaa”
}

xxx, yyy, zzz, aaa ก็ใช้ items(‘Apply_to_each’)?[‘ชื่อ column’]

Append to array variable

ใต่ Apply to each ก็เพิ่ม 2 actions ไปคือ Create HTML Table กับ Send an email เพื่อเอาค่า output ของ Array variable ไปส่ง email

Create HTML Table กับ Send an email

ทดลอง run ก็จะเห็นว่ามีทั้ง ข้อมูลไปกรอก Mismatch และส่ง email มาหา

ตรงนี้จะทำ flow ว่าถ้าไม่มีตัว mismatch ก็ไม่ต้องส่ง email หรือ ส่งแล้วบอกว่าไม่มีก็ได้

Email มาแจ้งเรียบร้อย

สุดท้ายพอ test เสร็จก็เปลี่ยนจาก manual trigger เป็น schedule เพื่อให้มัน run ตามเวลาที่เรากำหนดครับ