การรวมข้อมูลหลายๆ Sheet จาก Excel หลายๆ ไฟล์ใน Folder แบบที่ต้อง Transform ข้อมูลก่อนรวม

การรวมข้อมูลจาก Excel หลายๆ ไฟล์ใน Folder หากข้อมูลแต่ละอันเป็น Table ที่มีหัวตารางเรียบร้อย การรวมจะใช้เครื่องมืออัตโนมัติกด Combine ได้เลย หรือจะใช้สูตร Excel.Workbook รวมได้เลยแบบง่ายๆ ซึ่งรายละเอียดอยู่ใน https://www.thepexcel.com/get-data-from-excel/

แต่ถ้าข้อมูลในแต่ละชีท ไม่ได้อยู่ในรูปแบบที่เหมาะสม เช่น ต้องมีการ Transform ข้อมูลก่อน ถึงจะเอามา Combine รวมกันได้ แบบนี้จะใช้เครื่องมืออัตโนมัติไม่ได้แล้ว และในชีวิตจริงเราก็มักจะเจอกับข้อมูลแบบนี้ซะด้วย

พูดง่ายๆ สถานการณ์คือ

1 Folder มีหลายไฟล์
1 ไฟล์มีหลาย Sheet
แต่ละ Sheet มีความเน่า !!

สิ่งที่จะทำในบทความนี้

เราจะทำการรวมข้อมูลจาก Excel ทุกไฟล์ใน Folder ซึ่งในแต่ละไฟล์มีข้อมูลหลายชีทที่หน้าแบบนี้ ซึ่งแต่ละชีทเป็นรหัสพนักงานขาย และชื่อไฟล์เป็นปีที่ขาย

แต่ลองดูข้อมูลในชีทสิ มันอยู่ในรูปแบบที่ไม่ใช่ข้อมูลตาราง Database เลยซักนิด! หัวตารางดันอยู่บรรทัดที่ 5 แถมดันมีรหัสลูกค้าเป็นหัวตารางหลายๆ คอลัมน์อีก ซึ่งทั้งหมดนั้นควรจะมาอยู่ในคอลัมน์เดียวกัน (แปลว่าต้องมีการใช้ unpivot พลิกลงมา) และต้องกำจัดพวก subtotal/total ที่เกินๆ มา และต้องมีการ Fill Down ถมสินค้ามาให้เต็มด้วย ซึ่งจะเห็นว่าต้อง Transform หลายอย่างเลย (แต่ปัญหาหลักคือเรื่องหัวตารางนั่นแหละ)

ดังนั้นเราจะต้องใช้ฟังก์ชันรับ input ข้อมูลแต่ละชีทที่เน่าๆ มาจัดการดัดแปลงให้เป็น output ที่เรียบร้อยซะก่อนแล้วค่อยเอามารวมกัน ซึ่งเราจะเขียน Query ขึ้นมาแล้วแปลงเป็นฟังก์ชันได้ง่ายๆ ด้วยการใส่ (input)=> เข้าไปใน M Code แต่ว่าวิธีนี้จะทำให้แก้ M Code ด้วย UI ง่ายๆ ไม่ได้…

จากในบทความ https://www.thepexcel.com/4-steps-function-query/ ผมได้อธิบายวิธีการสร้างฟังก์ชันใน Power Query แบบที่สามารถปรับแก้ Step ด้วย User Interface ง่ายๆ ได้อยู่ ซึ่งในบทความก่อนผมใช้ Parameter ที่เป็นประเภท Text ซึ่งจะเป็นแบบง่าย แต่ในบทความนี้เราจะใช้ Parameter ที่เป็น Table ซึ่งจะยุ่งยากกว่า แต่มีประโยชน์สุดๆ ไปเลยครับ ดังนั้นใครอยากรู้ พลาดไม่ได้เด็ดขาด

ไฟล์ประกอบ

โหลดไฟล์ได้ที่นี่

เริ่มลงมือทำ

เปิดไฟล์ Excel ใหม่ขึ้นมาแล้ว Get Data from Folder แล้วกด Transform แล้วตั้งชื่อ Query เป็น MyResult ซะ (ถ้าอยากให้ Dynamic ก็ทำ Path เป็น Parameter ซะจะได้เปลี่ยนได้ง่าย แต่ในบทความนี้ผมขี้เกียจทำ 55)

จากหลักการ 4 ขั้นตอนที่บอกไปในบทความก่อน ดังนี้

1. สร้าง New Parameter ขึ้นมา 
2. เอา Parameter จากข้อ 1 ไปเรียกใช้ใน Query ที่ต้องการจะให้เป็นต้นแบบ 
3. สร้างฟังก์ชัน (จาก Query ใน Step2)
4. นำฟังก์ชันไปใช้งาน 

ในบทความนี้เราก็จะใช้หลักการเดียวกันนั่นแหละ แต่มันจะมีความยุ่งยากเกิดขั้นในขั้นตอนแรกสุด นั่นก็คือการสร้าง Parameter ขึ้นมา เพราะว่าฟังก์ชันของเราคราวนี้เป็นการ Transform ตัวข้อมูลในแต่ละชีทซึ่งมี Data Type เป็น Table หรือตารางนั่นเอง (พูดถึงเรื่อง Data Type ของ PQ ซึ่งเป็นคนละเรื่องกับ Table ของ Excel นะ)

การที่ Parameter เป็นแบบ Table นั้น ในความเป็นจริงต้องสร้าง Parameter ที่มี Type แบบ Any แต่ถ้าเราเลือก Any ไปตรงๆ ตัวหน้าตา Parameter มันจะไม่ยอมให้ไปต่อ เราก็เลยต้องเลือกหลอกๆ ให้เป็น Binary ก่อน และการจะเลือกแบบ Binary ได้ เราก็ต้องสร้าง Query ที่ชี้ไปที่ตัวไฟล์ Binary ซึ่งก็คือไฟล์ Excel ของเราก่อนอีกทีนึง (เห็นมะว่ายุ่งยากแค่ไหน 555)

ดังนั้นผมจะขอเพิ่ม Step0 ขึ้นมา นั่นก็คือการเตรียม Sample สำหรับ Parameter ดังนี้

Step 0 : เตรียม Query สำหรับสร้าง Parameter

ใน MyResult ให้คลิ๊กขวาที่คำว่า Binary แล้วเลือก Add as New Query เพื่อสร้าง Query ใหม่ที่อ้างไปถึง Binary นั้นๆ (Binary ในที่นี้คือ ตัวไฟล์ Excel)

มันจะได้ Query ใหม่ที่อ้างไปถึง Binary ตามที่เราต้องการ ให้เราเปลี่ยนชื่อ Query เป็น MyValue ซะ

จากนั้นก็กลับไปสู่ Step ปกติได้ละ

Step 1 : สร้าง New Parameter ขึ้นมา 

กด Manage Parameter แล้ว New ขึ้นมาใหม่ สมมติชื่อว่า MyPara ซึ่งให้เลือก Type หลอกเป็น Binary ก่อน แล้วเลือก MyValue ไปเป็น Default Value และ Current Value ซะ

จากนั้นกด Advanced Editor แล้วให้แก้ Code เปลี่ยนประเภทจาก Binary เป็น Any (เพราะ input ที่เป็น Table ต้องเป็น Any)

MyPara meta [IsParameterQuery=true, BinaryIdentifier=MySample, Type="Binary", IsParameterQueryRequired=true]

MyPara meta [IsParameterQuery=true, BinaryIdentifier=MySample, Type="Any", IsParameterQueryRequired=true]

จากนั้นให้กลับไปใน Query MyValue เพื่อให้อ้างอิงไปที่ Table จริงๆ โดยการ Double Click ที่ตัวไฟล์เพื่อ Drill Down ลงไป (เดิมเราอ้างอิงไปที่ตัวไฟล์)

จากนั้นเราคลิ๊กขวา Drill Down ลงไปใน Table ซักอันนึงในไฟล์นั้น

จากนั้น MyValue ของเราก็จะอ้างอิงไปที่ Table แล้วล่ะ

Step 2 : เอา Parameter จากข้อ 1 ไปเรียกใช้ใน Query ที่ต้องการจะให้เป็นต้นแบบ

เนื่องจาก Parameter เราเป็นข้อมูลที่มี Data Type แบบ Table ดังนั้นเราจะสร้าง Query ต้นแบบขึ้นมาใน Blank Query ใหม่ โดยอ้างอิงไปที่ Parameter ตรงๆ เลย

แค่เขียนสูตรว่า =MyPara

จากนั้นตั้งชื่อ Query ว่า MySteps

Step 3 : สร้างฟังก์ชัน (จาก Query ใน Step2)

แค่กดคลิ๊กขวาที่ MySteps แล้วกด Create Function

จากนั้นตั้งชื่อว่า MyFunction ก็ได้ (จะเห็นว่าฟังก์ชันนี้มี Parameter คือ MyPara)

จากนั้น PQ จะสร้าง Group ของ Query ให้เรา (เพื่อความเรียบร้อยเฉยๆ) และเพื่อให้เรียบร้อยขึ้น ให้เราลาก MyValue ขึ้นไปอยู่ Group MyFunction ด้วย

ดัดแปลง MySteps ตามใจชอบ

จากนั้นให้เราเข้า MySteps เพื่อทำการดัดแปลงข้อมูลตามใจชอบ ซึ่งฟังก์ชันเราจะทำการดัดแปลงตามสิ่งที่เราทำใน MySteps ทั้งหมดกับทุกๆ Sheet ก่อนจะเอามา Combine รวมกันนั่นเอง

ซึ่งสิ่งที่ผมทำมีดังนี้

• Remove Top Rows -> 4 แถว
• Use First Row as Header
• ลบ Step Change Type ออก เพื่อไม่ให้จำชื่อหัวตาราง (เพราะแต่ละ Table หัวตารางไม่ตรงกัน)
• Fill Down สินค้า
• Filter สินค้า not Contain คำว่า Total
• ลบคอลัมน์ Grand Total ขวาสุดออก
• เลือกสินค้าและวิธีการชำระเงิน แล้ว Unpivot Other Columns
• เปลี่ยนชื่อคอลัมน์ Attribute กับ Value เป็น รหัสลูกค้า และ ยอดขาย

สรุปใน MySteps ได้ออกมาแบบนี้

ซึ่งมี M Code ใน Advanced Editor ดังนี้

let
    Source = MyPara,
    #"Removed Top Rows" = Table.Skip(Source,4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filled Down" = Table.FillDown(#"Promoted Headers",{"สินค้า"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([สินค้า], "Total")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Grand Total"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"สินค้า", "วิธีการชำระเงิน"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "รหัสลูกค้า"}, {"Value", "ยอดขาย"}})
in
    #"Renamed Columns"

Step 4 : นำฟังก์ชันไปใช้งาน 

เรากลับมาที่ MyResult เพื่อทำการเรียกใช้ฟังก์ชัน MyFunction ที่เพิ่งสร้างขึ้นมา แต่ฟังก์ชันเราต้องการ Input ที่เป็น Table ดังนั้นเราต้องทำการอ่านข้อมูลใน Binary ก่อนโดย Add Custom Column ขึ้นมาแล้วเขียนสูตรดังนี้

=Excel.Workbook([Content])

จากนั้นเราจะได้คอลัมน์ Custom ขึ้นมา ซึ่งคราวนี้เราต้องการข้อมูลที่เป็นชื่อไฟล์ด้วย ดังนั้นเราจะเก็บคอลัมน์ Custom กับ Name เอาไว้ ดังนั้น Remove Other Column ซะ แล้ว Expand เจ้า Custom ออกมา

จะพบว่าข้อมูลจริงที่ยังไม่ได้ Transform ซึ่งเราต้องใช้เป็นตัว Input ของฟังก์ชัน อยู่ใน Data นี่แหละ

ดังนั้นเราจะเรียกใช้ฟังก์ชันของเราโดย Invoke Custom Function

จะพบว่าจะได้คอลัมน์ MyFunction ใหม่ ที่เป็น Output แบบ Table ที่แปลงข้อมูลเรียบร้อยแล้ว

เราต้องการชื่อไฟล์ ชื่อชีท และ Data หลังแปลงแล้ว ดังนั้นเราจะเก็บไว้แค่คอลัมน์ Name, Item และ MyFunction จากนั้น Expand MyFunction ออกมา

Extract Name แบบ Before Delimiter จุด เปลี่ยนชื่อคอลัมน์ให้เรียบร้อย และเปลี่ยน Data Type ก็จะได้ดังภาพเป็นอันจบ

ถ้าอยากจะปรับการ Transform ก่อนจะรวมไฟล์ ก็ไปทำที่ MySteps ได้เลยแบบง่ายๆ นี่แหละคือข้อดีของการสร้าง Function แบบใช้ Parameter จริงจังครับ

ถ้าใครอ่านแล้วสงสัยตรงไหนก็ถามได้ หรือถ้าอยากดูแบบคลิป vdo ก็มีที่คุณโบ Excel Wizard เคยทำไว้ดังนี้ครับ (อย่างที่บอกในบทความที่แล้วว่าผมเอาเทคนิคนี้มาจากคุณโบนี่แหละ 55)