การใช้ Power Query M Code จัดการข้อมูลที่อยู่ในตำแหน่งไม่แน่นอน

หลายๆ คนน่าจะได้ลองใช้ Power Query ผ่านเครื่องมือบน User Interface ปกติไปจนเป็นแล้ว และหลายคนก็จะเริ่มรู้แล้วว่าเครื่องมือ User Interface ปกตินั้นมันไม่สามารถตอบโจทย์ในงานบางอย่างได้ เรียกได้ว่าเครื่องมือ Power Query ปกตินั้นสามารถตอบโจทย์งานทั่วไป 80% ได้แหละ แต่งานอีก 20% จะต้องมีการยุ่งกับ M Code ถึงจะสามารถทำได้สำเร็จ

ซึ่งผมได้เคยมีการเขียนบทความเรื่อง บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query ซึ่งเป็นแนวทางในการศึกษา M Code ว่ามีประเด็นไหนที่ควรจะต้องศึกษาบ้าง รวมถึงมีการเขียน บทความรายละเอียดเรื่อง M Code โดยเริ่มจากพื้นฐาน ไปแล้วเช่นกัน

แต่ในบทความนี้ผมจะเน้นไปที่ M Code ที่เราจะสามารถนำมาใช้ในงานของเราได้จริงๆ ในกรณีที่พบได้บ่อย เช่น เราสามารถใช้ M Code ใช้แก้ปัญหาเมื่อข้อมูลอยู่ตำแหน่งไม่แน่นอนได้ ทุกคนจะได้เห็นภาพมากขึ้นว่าเราจะเรียนรู้ M Code ไปทำไมกัน??

ปัญหาของ Remove Top Rows แบบปกติ

ปกติแล้ว ถ้าเรามีข้อมูลที่ต้องการอยู่ในแถวที่ไม่ใช่แถวแรกสุด เราก็ต้องทำการ Remove Top Rows ก่อน ซึ่งจะต้องระบุจำนวนแถวของข้อมูลด้วย เช่น ตามในรูปต้องเอาออก 3 แถว

สมมติเราทำให้ MyTable เป็นตัวแปรที่เก็บตารางต้นฉบับก่อนจะตัดบรรทัดไว้ โปรแกรมก็จะเรียกใช้ฟังก์ชัน Table.Skip ให้

Table.Skip(MyTable,3)

แต่วิธีนี้จะ Work ได้ก็ต่อเมื่อ “ข้อมูลที่เราต้องการต้องอยู่แถว 4 อย่างแน่นอน” จริงมั้ยครับ?

และจะเกิดอะไรขึ้นถ้าข้อมูลที่เราจะต้องเจอ “มันอาจจะอยู่ที่แถวอื่นก็ได้” แบบนี้เราก็ต้องใช้ M Code มาช่วยแล้วล่ะ

หลักการคิดคือ เราจะต้องหาวิธีที่จะรู้ให้ได้ว่าหัวตารางอยู่ในบรรทัดที่เท่าไหร่?

การจะรู้ว่าหัวตารางอยู่บรรทัดที่เท่าไหร่ได้นั้น ก็ขึ้นอยู่กับตัวข้อมูลด้วย ว่าข้อมูลมาแบบไหน ซึ่งผมจะเริ่มจากรณีที่มันง่ายก่อน

หัวตารางอยู่ในคอลัมน์ที่แน่นอน และเป็นคำที่แน่นอน

สมมติว่าเรารู้แน่ๆ ว่า หัวตารางเป็นคำว่าผลไม้ และอยู่ใน Column1 เสมอ

วิธีการหามีหลายวิธี

วิธีที่ 1 : ใช้ Add Index Column และการ Filter

วิธีแรกเป็นวิธีที่ไม่ได้ใช้ M Code อะไรมากมายนอกจากการรวบ Step บางอันเข้าด้วยกัน แนวทางคือ เราใช้การ Add Index Column เริ่มจากเลข 1 เข้าไป

แล้ว Filter Column1 (ที่เรารู้แน่นอน) ให้เป็นคำว่าผลไม้

จากนั้น Drilldown เข้าไปที่ Index ของบรรทัดแรก (เผื่อมีผลไม้หลายบรรทัด) ก็จะรู้แล้วว่าคำว่าผลไม้อยู่บรรทัดที่เท่าไหร่เป็นครั้งแรก

จากนั้นเอาตัวแปร FruitRow ไปเรียกใช้ใน Table.Skip แต่อย่าลืมลบไปอีก 1 แถว

สรุปแล้วเขียน MCode แบบนี้

let
    Source = ...,
    MyTable = ตารางต้นฉบับที่เตรียมไว้,
    MyTable = #"Replaced Value",
    AddIndex = Table.AddIndexColumn(MyTable, "Index", 1, 1, Int64.Type),
    FilterRows = Table.SelectRows(AddIndex, each ([Column1] = "ผลไม้")),
    FruitRow = FilterRows{0}[Index],
    Result = Table.Skip(MyTable,FruitRow-1)
in
    Result

ได้ผลลัพธ์แบบนี้

วิธีที่ 2 : ใช้ความรู้เรื่อง List มาช่วย

แบบนี้เราสามารถทำให้ข้อมูลใน Column1 เป็น List แล้วใช้ List.PositionOf เพื่อหาตำแหน่งของคำที่ต้องการได้ เช่น

ทำให้คอลัมน์แรกเป็น List ก่อนด้วยการคลิ๊กขวาที่คอลัมน์แรกแล้วกด Drill Down เพื่อให้อ้างอิงข้อมูลคอลัมน์ให้เป็น List

= MyTable[Column1]

จากนั้นใช้ List.PositionOf ครอบเข้าไป ก็จะหาตำแหน่งของผลไม้ ออกมาเป็น index ที่ 3

= List.PositionOf(MyTable[Column1],"ผลไม้")

แล้วเราก็อาจเก็บค่านี้ไว้ในตัวแปร PositionFruit

แล้วเอาไปเรียกใช้ใน Table.Skip ได้เลย เช่น

= Table.Skip(MyTable,PositionFruit)

ก็จะได้ผลลัพธ์ที่ต้องการครับ สรุป M Code ทั้งหมดเป็นแบบนี้

let
    Source = ...,
    MyTable = ตารางต้นฉบับที่เตรียมไว้,
    PositionFruit = List.PositionOf(MyTable[Column1],"ผลไม้"),
    Result = Table.Skip(MyTable,PositionFruit)
in
    Result

หัวตารางอยู่ในคอลัมน์ที่ไม่แน่นอน แต่เป็นคำที่แน่นอน

สมมติว่าคราวนี้ผมรู้ว่าหัวตารางจะต้องมีคำว่า “เกรด” แต่ไม่รู้ว่ามันจะอยู่ในคอลัมน์ไหน แถวไหนกันแน่ เราจะมีวิธีการหาได้ยังไงมาดูกันครับ

เราสามารถใช้ Table.FindText มาช่วยได้ เช่น

= Table.FindText(MyTable,"เกรด")

ผลลัพธ์จะได้แบบนี้ (ถ้ามีคำว่าเกรดอยู่หลาย Row มันก็จะออกมาหลายบรรทัดได้)

วิธีที่จะหาว่าคำว่าเกรดอยู่ในคอลัมน์ที่ชื่อว่าอะไรนั้นทำได้หลายวิธี เช่น

วิธีที่1 : ใช้การหา item ใน List

• เอา Record แรกออกมาเลยใส่ {0} ต่อท้าย
• จากนั้นเราแปลง Record Value เป็น List ด้วย Record.FieldValues
• แล้วหาตำแหน่งของคำว่า เกรด ใน List ด้วย List.PositionOf

สรุปแล้วเขียนแบบนี้

= List.PositionOf(Record.FieldValues(Custom1),"เกรด")

จะได้ว่าอยู่ใน index ที่ 1 หรือ เป็น item ลำดับที่ 2 ซึ่งก็คือ Column2 นั่นเอง

หรือจะหาชื่อ Field แบบเป๊ะๆ เลยก็ได้ อาจใช้ Record.FieldNames มาช่วย ก็ได้

= Record.FieldNames(Custom1)

ซึ่งเราเอาเลข Index จาก Custom2 มาใส่ เพื่อเอาชื่อคอลัมน์ออกมาก็ได้

สรุปว่าเขียนแบบนี้

    Custom1 = Table.FindText(MyTable,"เกรด"){0},
    Custom2 = List.PositionOf(Record.FieldValues(Custom1),"เกรด"),
    Custom3 = Record.FieldNames(Custom1){Custom2}

วิธีที่ 2 : Transpose ตารางแล้ว Filter หา

หรือเราจะใช้อีกวิธีนึงคือ Demote Header แล้ว Transpose ตารางให้ออกมาเป็นแบบนี้

= Table.Transpose(Table.DemoteHeaders( Table.FindText(MyTable,"เกรด")))

แล้ว Filter หาคำว่าเกรดใน Column ขวา (เพื่อให้ได้เกรดแถวแรกที่เจอก่อนที่จะ Transpose)

แค่นี้ก็จะรู้ชื่อคอลัมน์ว่าเป็น Column2 นั่นเอง

สรุปแล้วเขียนแบบนี้

    Custom1 = Table.Transpose(Table.DemoteHeaders( Table.FindText(MyTable,"เกรด"))),
    FilterRow = Table.SelectRows(Custom1, each ([Column2] = "เกรด")),
    Column1 = FilterRow{0}[Column1]

พอได้ชื่อคอลัมน์ที่แน่นอนแบบนี้ เราค่อยเอาไปหาตามเคสแรกอีกทีก็จะจบแล้ว

หัวตารางอยู่ในคอลัมน์ที่แน่นอน แต่เป็นคำที่ไม่แน่นอน

สมมติว่าเรารู้ว่าหัวตารางจะอยู่ใน Column1 แน่ๆ แต่ไม่รู้ว่าจะเป็นคำว่าอะไร ซึ่งเราจะต้องมี Logic เพิ่มเติม เช่น มันจะเป็น คำแรกหลังจากข้อมูลเป็นค่า null ตั้งแต่ 2 ตัวขึ้นไป

แบบนี้ก็สามารถใช้ MCode มาช่วยได้อยู่ แต่จะยากขึ้นกว่าเดิมเล็กน้อย

ในเคสนี้ผมจะใช้ Group By มาช่วย

แต่จะทำตรงๆ ไม่ได้เพราะอาจเกิดปัญหาที่มันดันรวบ null ทุกตัวเข้าด้วยกันไปเลยแบบนี้

เราเลยจะมีการแก้ Parameter เล็กน้อยให้มีการ GroupKind แบบ Local เพื่อให้ Group ขึ้นอยู่กับการเรียงลำดับต่อเนื่องด้วย เช่น

= Table.Group(MyTable, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local)

จากนั้นเราจะทำ Count สะสมเพิ่มอีกคอลัมน์ เพื่อที่จะได้รู้ว่า null ตัวแรกที่ติดต่อกัน 2 ค่าขึ้นไป มันคือบรรทัดที่เท่าไหร่กันแน่??

โดยสร้าง Index Column เพื่อช่วยกำหนดลำดับในการสะสมค่า แล้วเขียน Custom Column ใหม่เพื่อสร้าง Count แบบสะสมด้วยสูตรนี้

List.Sum(List.FirstN(AddIndex[Count],[Index]))

จากนั้น Filter เลือก Column1 ที่เป็น null และ Column2 >=2

= Table.SelectRows(AddAccumCount, each [Column1] = null and [Count] >= 2)

แล้วเลือกเอาแถวแรกออกมา จากนั้นเลือกเอา AccumCount จะได้เลข 3 ออกมาแบบนี้

สรุปแล้วเขียนแบบนี้

let
    Source = ...,
    MyTable = ตารางต้นฉบับที่เตรียมไว้,
    GroupTable = Table.Group(MyTable, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local),
    AddIndex = Table.AddIndexColumn(GroupTable, "Index", 1, 1, Int64.Type),
    AddAccumCount = Table.AddColumn(AddIndex, "AccumCount", each List.Sum(List.FirstN(AddIndex[Count],[Index]))),
    FilterRows = Table.SelectRows(AddAccumCount, each [Column1] = null and [Count] >= 2),
    PositionFruit = Table.FirstN(FilterRows,1){0}[AccumCount],
    Result = Table.Skip(MyTable,PositionFruit)
in
    Result

นี่เป็นแค่แนวทางที่เป็นไปได้

วิธีแก้ปัญหาใน Power Query นั้นสามารถแก้ผัญหาได้หลายวิธี ดังนั้นยังมีความเป็นไปได้อื่นๆ อีกมากมายที่จะช่วยแก้ปัญหาให้เราได้ และถ้าเรารู้ M Code มากขึ้นก็จะช่วยให้มีวิธีแก้ปัญหามากขึ้นไปด้วยนะครับ ดังนั้นอย่าลืมลองสำรวจหาและเรียนรู้ฟังก์ชันที่คุณยังไม่รู้จัด้วยนะครับ เผื่อชีวิตจะสบายขึ้นเยอะ