Tag: m code

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

    การใช้ 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 แถว

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

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

    Table.Skip(MyTable,3)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    จากนั้นเอาตัวแปร 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

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

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

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

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

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

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

    จากนั้นใช้ 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 มันก็จะออกมาหลายบรรทัดได้)

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

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

    วิธีที่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)
    การใช้ Power Query M Code จัดการข้อมูลที่อยู่ในตำแหน่งไม่แน่นอน 8

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

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

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

        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,"เกรด")))
    การใช้ Power Query M Code จัดการข้อมูลที่อยู่ในตำแหน่งไม่แน่นอน 10

    แล้ว 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 มาช่วย

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

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

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

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

    = Table.Group(MyTable, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local)
    การใช้ Power Query M Code จัดการข้อมูลที่อยู่ในตำแหน่งไม่แน่นอน 13

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

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

    List.Sum(List.FirstN(AddIndex[Count],[Index]))
    การใช้ Power Query M Code จัดการข้อมูลที่อยู่ในตำแหน่งไม่แน่นอน 14

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

    = Table.SelectRows(AddAccumCount, each [Column1] = null and [Count] >= 2)
    การใช้ Power Query M Code จัดการข้อมูลที่อยู่ในตำแหน่งไม่แน่นอน 15

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

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

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

    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 มากขึ้นก็จะช่วยให้มีวิธีแก้ปัญหามากขึ้นไปด้วยนะครับ ดังนั้นอย่าลืมลองสำรวจหาและเรียนรู้ฟังก์ชันที่คุณยังไม่รู้จัด้วยนะครับ เผื่อชีวิตจะสบายขึ้นเยอะ

  • บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query

    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query

    ในบทความนี้ผมจะพาเพื่อนๆ ไปเรียนรู้ M Code ใน Power Query ผ่านตัวอย่าง 10 อัน โดยไล่ระดับตั้งแต่ Basic ที่สุดไปตัวอย่างที่ยากขึ้นเรื่อยๆ เหมือนเป็นการพาขึ้นบันได 10 ขั้นแรกสำหรับการเรียนรู้ MCode ครับ

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

    ใครพร้อมจะก้าวเดินไปสู่ระดับที่สูงขึ้น ตามผมมาได้เลย!

    ป.ล. บทความนี้เหมาะกับคนที่ใช้ Power Query ผ่านเครื่องมือบน User Interface เป็นอยู่แล้วนะครับ ใครที่ยังไม่แม่นพื้นฐานลองไปดู Playlist นี้ได้

    วิธีสร้าง Blank Query

    โดยให้เพื่อนๆ สร้าง Blank Query ตามรูป (จะ add เข้า Quick Access Toolbar ไว้ก็ดีนะ)

    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 17

    จากนั้นไปที่ Advanced Editor แล้วลองทำตามได้เลย

    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 18

    เอาล่ะ ตัวอย่าง 10 อันแรกจะมีอะไรบ้าง? มาดูกันครับ

    ตัวอย่าง 1 : Basic let…in…

    • ประกาศตัวแปรด้วย let แล้วเรียกผลลัพธ์ออกมาหลัง in ได้
    • ในที่นี้ประกาศตัวแปรชื่อ MyNum, Factor และ Result ออกมา
    • เราจะประกาศตัวแปรโดยคั่นด้วย Comma ซึ่งจะเขียนต่อกันหรือแยกบรรทัดก็ได้ (ปกติจะนิยมแยกบรรทัด แบบตัวอย่างด้านล่างมากกว่า)
    • ตัวแปรนึงสามารถอ้างอิงค่าจากตัวแปรอื่นได้ เช่น Result อ้างอิง MyNum, Factor
    • ในที่นี้เราเรียกเอาตัวแปร Result ออกมาหลัง in เพื่อแสดงผลใน Query นี้
    let
        MyNum = 5, Factor=100,  Result=MyNum*Factor
    in
        Result
    let
        MyNum = 5,
        Factor=100,
        Result=MyNum*Factor
    in
        Result

    จะเห็นว่าผลลัพธ์ของ Query ได้ออกมาเป็นเลข 500 (ใช่… Power Query สามารถให้ผลลัพธ์ออกมาเป็น Value เดี่ยวๆ ได้ ไม่จำเป็นต้องออกมาเป็นตารางเสมอไป)

    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 19

    ตัวอย่าง 2 : ใส่ Comment

    • ใส่ Comment ด้วยการพิมพ์ // แล้วตามด้วย Comment ที่ต้องการ
    • ทุกอย่างที่ใส่เป็น Comment จะไม่ถูกประมวลผล
    • ประโยชน์คือเอาไว้กลับมาดู Code ตอนหลัง หรือส่งให้คนอื่นทำงานต่อจะได้ไม่งง
    • แล้ว comment เลห่านั้นจะมาโผล่เป็นตัว i ใน applied steps ด้วย
    let
        MyNum = 5,   //ให้ MyNum มีค่าเป็น 5
        Factor=100, //ให้ MultiplyFactor มีค่าเป็น 100
        Result=MyNum*Factor //เอาตัวแปร 2 ตัวคูณกัน 5*100 = 500
    in
        Result //ผลออกมา 500
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 20

    ตัวอย่าง 3 : เริ่มรู้จัก List

    • สร้าง List ได้ด้วย List = {item1,item2,item3}
    • item แต่ละอันใน List จะเป็นข้อมูลประเภทไหนก็ได้ ไม่เหมือนกันก็ได้
    • เราสามารถอ้างอิงเอาค่าใน List ออกมาได้ด้วย ListName{index} โดยที่ index ของ item ตัวแรก คือ 0
    • สร้าง MyList1 ที่ประกอบไปด้วยเลข 4, 29, 70
    • สร้าง MyList2 ที่ประกอบไปด้วยข้อความว่า ant, bat, cat
    • สร้าง MyList3 ที่ประกอบไปด้วยเลข 1-20
    • สร้าง MyList4 โดยรวม List1, List2 และ List3 เข้าด้วยกันด้วย &
    • อ้างอิงข้อมูล item ที่เป็นคำว่า bat ใน MyList4 ด้วย List{เลขindex}
    let
        MyList1 = {4, 29, 70},
        MyList2 ={"ant", "bat", "cat"},
        MyList3 = {1..20},
        MyList4 = MyList1&MyList2&MyList3
        
    in
        MyList4
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 21

    ลองอ้างอิง item ที่เป็นข้อความว่า bat ด้วย MyList{4} (ตัวที่ 5 คือ index 4 )

    ตัวอย่าง 4 : แปลงค่าในคอลัมน์ของตารางให้กลายเป็น List

    ที่สำคัญเลย หากเรามีตารางอยู่ เราสามารถอ้างอิงค่าในคอลัมน์ใดคอลัมน์นึ่งให้เป็น List ได้ด้วยการเขียนสูตรในรูปแบบ TableName[ColumnName] เช่น

    สมมติว่าเรามีตารางแบบนี้ ชื่อว่า MyData แล้วเราเอาดึงค่าไว้ในตัวแปร MyTable

    Order-IDDateProduct-NameUnit-CostUnit-PriceQuantity
    ORD-0011/4/2023Laptop5007003
    ORD-0022/4/2023Monitor1001505
    ORD-0034/4/2023Keyboard203510
    ORD-0045/4/2023Mouse10257
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 22

    หาเราเขียนสูตรว่า =MyTable[Quantity] เราจะได้ข้อมูลในคอลัมน์ Quantity ออกมาเป็น List

    let
        MyTable = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
        GetQty = MyTable[Quantity]
    in
        GetQty
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 23

    ตัวอย่าง 5 : ฟังก์ชันสำเร็จรูป

    • ดูฟังก์ชันมาตรฐานทั้งหมดได้ที่นี่
    • ฟังก์ชันใน MCode จะมีเยอะมากๆ วิธีเลือกฟังก์ชัน คือ มันมักจะขึ้นต้นด้วยประเภทข้อมูลที่เกี่ยวข้อง ตัวอย่างเช่น….
      • ฟังก์ชันเกี่ยวกับ Number เช่น Number.Abs, Number.Mod, Number.Sign,
      • ฟังก์ชันเกี่ยวกับ Text เช่น Text.Lower, Text.Contains, Text.Replace, Text.Split, Text.Combine
      • ฟังก์ชันเกี่ยวกับ List เช่น List.Sum, List.Min, List.Max, List.Distinct, List.Select, List.Transform, List.Zip
      • ฟังก์ชันเกี่ยวกับ Table เช่น Table.ColumnNames, Table.TransformColumnNames, Table.SelectRows, Table.SelectColumns
      • ฟังก์ชันเกี่ยวกับ Date เช่น Date.AddDays, Date.AddMonths, Date.ToText, Date.IsInCurrentYear
    let
        MyTable = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
        GetQty = MyTable[Quantity],
        ListMin = List.Min(GetQty), // คำนวณค่าน้อยสุดของ List
        ListMax = List.Max(GetQty), // คำนวณค่ามากสุดของ List
        Result= ListMax-ListMin //เอาค่ามากสุด ลบ น้อยสุด
    in
        Result
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 24

    จะเห็นว่าผลลัพธ์ออกมาได้เลข 7

    ตัวอย่าง 6 : สร้าง Custom Function ง่ายๆ

    • นอกจากฟังก์ชันมาตรฐาน ที่มีมาให้เยอะแยะแล้ว เรายังสามารถสร้างฟังก์ชันขึ้นมาใช้เองได้ด้วย ซึ่งประโยชน์คือเอาไว้ใช้ร่วมกับการ Transform ที่มีความซับซ้อนมากขึ้น
    • สร้าง Custom Function ขึ้นมาได้ ด้วย (parameter) => function expression
    • ฟังก์ชั่นจะรับค่า parameter แล้วส่งเข้าไปใน expression
    (MyList as list) as number =>
    let
        Result = List.Sum(MyList) // คำนวณผลรวมของ List
    in
        Result //แสดงผลรวมออกมา
    • สมมติเราตั้งชื่อ Query ว่า GetRange มันจะกลายเป็นชื่อฟังก์ชัน
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 25
    • เราจะสามารถเรียกใช้ Function ได้ด้วย =GetRange(List)
    • เช่น = GetRange(Listที่ต้องการ) จะได้ 7 เช่นเดียวกันกับตัวอย่างก่อนหน้านี้
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 26

    ตัวอย่าง 7 : เข้าใจคำว่า each

    • each คือ ตัวย่อของ (_) =>
    • ซึ่งเกิดจากการที่ใช้ฟังก์ชันมี input ตัวเดียว แล้วใช้ชื่อตัวแปร ว่า _
    • นั่นคือ (x) => x+5 จะเขียนได้ว่า
      • (_) => _ + 5 ซึ่งจะเขียนได้ว่า
      • each _ + 5
    • นั่นคือ (x) => Text.Replace(x,”A”,”B”) จะเขียนได้ว่า
      • (_) => Text.Replace(_,”A”,”B”) ซึ่งจะเขียนได้ว่า
      • each Text.Replace(_,”A”,”B”)

    ตัวอย่าง 8 : เข้าใจ each ในบริบทของแต่ละแถวของตาราง

    เวลาที่เรากดสร้างคอลัมน์ใหม่ ไม่ว่าจะใช้เครื่องมือ หรือใช้ฟังก์ชัน Table.AddColumn ก็ตาม มันมักจะมีคำสั่ง each โผล่มาด้วยเสมอ เช่น เรากด Add Custom Column เพื่อสร้างคอลัมน์ยอดขายจากตาราง จะได้แบบนี้ (ผมแก้ชื่อคอลัมน์เป็น UnitPrice จะได้อ่านง่ายๆ)

    สูตรใน Custom Column เขียนว่า

    [UnitPrice]*[Quantity]
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 27

    แต่สูตใน Step นั้นจริงๆ ออกมาว่า

    = Table.AddColumn(#"Removed Columns", "Revenue", each [UnitPrice]*[Quantity])

    จริงๆ แล้ว มันคือการย่อของ

    = Table.AddColumn(#"Removed Columns", "Revenue", each _[UnitPrice]*_[Quantity])

    ซึ่งคือการย่อของ

    = Table.AddColumn(#"Removed Columns", "Revenue", (_)=> _[UnitPrice]*_[Quantity])

    นั่ยแปลว่าจริงๆ แล้ว ฟังก์ชัน Table.AddColumn มี input ของฟังก์ชันคือ _ แล้วถ้าเราทดสอบว่ามันคืออะไร โดยให้เขียนสูตรว่า _ ไปเลย

    เราจะพบว่า _ ใน Table.AddColumn ก็คือ Record ของข้อมูลในแถวนั้นๆ นั่นเอง

    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 28

    ซึ่งการเข้าถึง item ใน Record ทำได้โดย RecordName[FieldName] ดังนั้นเราจึงเข้าถึงค่าของคอลัมน์ต่างๆ ได้ เช่น

    • _[Quantity] แปลว่า ให้เอา Field ชื่อ Quantity ของ Record นั้นๆ มา
    • ซึ่งเราย่อ _[Quantity] ให้กลายเป็น [Quantity] เฉยๆ ได้
    • นี่คือที่มาว่าทำไมเราอ้างอิงคอลัมน์ในแถวตัวเองได้ด้วย [ชื่อหัวตาราง]

    ตัวอย่าง 9 : แก้ชื่อคอลัมน์ จาก – เป็น _

    • แล้วเราต้องการแก้ชื่อคอลัมน์ในตาราง จากเครื่องหมาย – ให้กลายเป็น _ โดยไม่ต้องแก้ทีละอัน เราสามารถทำแบบนี้ได้
    • Table.TransformColumnNames ใช้เพื่อวน Loop แก้ชื่อคอลัมน์ในตารางทีละคอลัมน์ด้วยวิธีอะไรบางอย่างที่เราจะระบุในฟังก์ชัน ซึ่ง
      • ในที่นี้เราระบุว่า each Text.Replace(_,”-“,”_”)
    • เราสามารถใช้ Text.Replace เพื่อแทนที่ข้อความได้ (คล้ายฟังก์ชัน SUBSTITUTE ใน Excel) ซึ่งในที่นี้เราสั่งแทน “-” ด้วย “_”
    • ซึ่งโดยรวมแปลว่า ให้วน Loop ชื่อคอลัมน์ แล้วแทนค่าชื่อคอลัมน์ที่มี – ด้วย _
    let
        Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
        ReplaceColumnName=Table.TransformColumnNames(Source,each Text.Replace(_,"-","_"))
    in
        ReplaceColumnName
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 29

    ตัวอย่าง 10 : เลือกเฉพาะคอลัมน์ที่มี – อยู่

    • เราใช้ Table.ColumnNames เพื่อเอาชื่อคอลัมน์ทั้งหมดในตารางออกมาเป็น List
    • ใช้ List.Select เพื่อทำให้ List เหลือเฉพาะ item ที่ตรงตาม Criteria
    • ใช้ Text.Contains ในการช่วยคัดเลือก item เพื่อส่งไป List.Select อีกที
    • ใช้ Table.SelectColumns เพื่อ ลือกเอาเฉพาะคอลัมน์ที่ต้องการจาก List ที่ทำไว้
    let
        Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
        OriginalColumnName = Table.ColumnNames(Source), //เอาชื่อคอลัมน์ทั้งหมดออกมาเป็น List
        RequiredColumn=List.Select(OriginalColumnName,each Text.Contains(_,"-")), //ทำให้ใน List เหลือแค่ item ที่มี -
        SelectSpecificColumn=Table.SelectColumns(Source,RequiredColumn) // เลือกเอาเฉพาะคอลัมน์ที่ต้องการจาก List
    in
        SelectSpecificColumn
    บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query 30

    ตอนต่อไป

    หลังจากเรามีพื้นฐาน MCode จากบทความนี้แล้ว ในตอนต่อไปก็จะได้เรียนเรื่องซับซ้อนกว่านี้ซักทีครับ ใครสนใจรออ่านได้เลย ไม่นานเกินรอ

  • วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API

    ก่อนหน้านี้ผมได้เคยเขียนบทความวิธีใช้ Power Query ดึงข้อมูลจาก Web API ไปแล้ว 3 ตอน ถ้าใครยังไม่เคยเข้าไปอ่านก็สามารถเข้าไปอ่านดูก่อนได้นะครับ (ไม่อ่านก็ยังสามารถทำตามในบทความนี้ได้อยู่)

    ซึ่งบทความในวันนี้เนื้อหาจะคล้ายกับตอนที่ 2 ของเดิมมากๆ คือเป็น Web API แบบ Post แต่จะเป็นกรณีตัวอย่างการใช้กับ EMS Tracking ของไปรษณีย์ไทยโดยเฉพาะ ซึ่งหลายๆ คนน่าจะอยากใช้งานอยู่ ซึ่งวิธีทำนี้จะใช้ใน Excel หรือ Power BI ก็ได้นะครับ (พอดีมีลูกค้าที่เรียน Power BI กับผมถามมา เลยเอามาเขียนเป็นบทความเผื่อเพื่อนๆ คนอื่นด้วยเลยดีกว่า)

    เพื่อไม่ให้เสียเวลา เรามาเริ่มดูขั้นตอนกันเลยละกัน

    สมัครสมาชิกแล้วเอา Token มาก่อน

    ขั้นตอนแรกที่จำเป็นเลยคือเข้าไปในเว็บ https://track.thailandpost.co.th/ จากนั้นสมัครสมาชิกแล้วเข้าสู่ระบบให้เรียบร้อย (ขอไม่ลงรายละเอียดวิธีสมัครนะ)

    จากนั้นให้เข้าหน้า Dashboard ในส่วนของ <>สำหรับนักพัฒนา แล้วกด Create Token จากนั้น Copy Token ปัจจุบันออกมาซะ

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 31

    ศึกษาคู่มือซักหน่อย

    ผมเองก็ดูข้อมูลตามที่บอกในคู่มือ ในส่วนของ API REST แล้วก็ทำตามได้ดังนี้ (คู่มือมีจุดที่เขียนแล้วสับสนตรง Token ให้ดูตัวอย่างผมแทนได้)

    GetToken สำหรับการรับส่งข้อมูล

    โดย Token รับส่งข้อมูลนี้เป้นคนละตัวกับ Token จากการสมัครสมาชิกนะ… และจะมีการกำหนดวันหมดอายุ 1 เดือน

    ให้เรา Get Data from Web แบบ Advanced แล้วใส่ดังนี้

    • URL Parts : https://trackapi.thailandpost.co.th/post/api/v1/authenticate/token
    • HTTP Request Header
      • Authorization : คำว่า Token ตามด้วยเว้นวรรค 1 ที ตามด้วย Token ในหน้าสมาชิก
      • Content-Type : application/json
    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 32

    จากนั้นกด ok แล้วกด Connect

    มันก็จะทำการเข้าสู่ Power Query Editor แล้วได้ผลลัพธ์มาเป็น Token รับส่งข้อมูล ซึ่งจะยาวกว่า Token ในหน้าสมาชิกมากๆ ให้เรา Copy เก็บเอาไว้ใช้ได้ (Token รับส่งข้อมูลหมดอายุใน 1 เดือน)

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 33

    พอเราได้ Token รับส่งข้อมูลแล้ว คราวนี้เราถึงสามารถทำการค้นหาตามพวกรหัส EMS ได้ซักที

    สร้าง Query ดึงข้อมูลจากรหัส Tracking

    ให้เราสร้าง Blank Query (Get Data -> From Other Source -> Blank Query) ขึ้นมาแล้วเปลี่ยน Code ใน Advanced Editor ให้เป็นดังนี้

    let
    
        BarCodeList={"EDXXXXXXXX","RLXXXXXXXX","EDXXXXXXXX"}, //List เก็บรหัส Track
        DataToken="xxxxxxx",  //Token รับส่งข้อมูลยาวๆ 
        MyRecord=[status="all",language="TH",barcode=BarCodeList], //Record ที่จะ Post เข้า API
    
        Source = Json.Document(Web.Contents("https://trackapi.thailandpost.co.th/post/api/v1/track", 
        [
            Headers=
                [
                Authorization="Token "&DataToken,
                #"Content-Type"="application/json"
                ],
            Content=Json.FromValue(MyRecord)                  
        ] 
    
        ))
    in
        Source

    จากนั้นเปลี่ยนสิ่งที่ระบุใน BarCodeList และ DataToken ตามต้องการ เช่น

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 34

    จากนั้นพอ Run Query จะได้ดังนี้ แสดงว่าสำเร็จ

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 35

    ดูข้อมูลที่ Response กลับมา

    จากนั้นเราก็กดเข้าไปใน Record ที่ response กลับมา 1 ที แล้วกด Record ที่ items อีกที แล้วค่อยกด Convert เป็น Table

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 36

    จากนั้นให้ Expand List ออกมาเป็นหลายๆแถว

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 37

    จากนั้นค่อย Expand Record ออกมาโดยเลือกเอาเฉพาะคอลัมน์ที่ต้องการได้เลย

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 38

    แล้วเราก็จะได้ Data ที่ต้องการแบบละเอียดทุกขั้นตอนเลย เย้!

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 39

    ทำยังไงให้เพิ่ม List รหัสง่ายๆ

    ที่นี้ถ้าอยากให้ Dynamic มากขึ้น แทนที่เราจะพิมพ์ List ของรหัส EMS ที่ต้องการ Track เข้าไปเอง เราก็ดึง List นั้นมาจาก Table ของ Excel ได้ เช่น ผมสร้าง Table ชื่อ TrackTable แล้วมีคอลัมน์ TrackNumber เอาไว้ (เอาจริงๆ จะเป็น Data Source อะไรก็ได้อ่ะนะ)

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 40

    จากนั้น Get Data เข้า Power Query ซะ

    จากนั้นคลิ๊กขวาในคอลัมน์ที่ต้องการแล้ว DrillDown เราก็จะได้ List แล้ว

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 41

    เนี่ยได้ List แล้ว ซึ่ง List นี้ชื่อว่า TrackTable

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 42

    จากนั้นเราก็แค่เอา TrackTable ไปใส่ใน Code เดิมของเรา ก็จบ ง่ายขึ้นอีกเยอะ!!

    let
    
        BarCodeList=TrackTable, //List เก็บรหัส Track
        DataToken="xxxxxxx",  //Token รับส่งข้อมูลยาวๆ 
        MyRecord=[status="all",language="TH",barcode=BarCodeList], //Record ที่จะ Post เข้า API
    
        Source = Json.Document(Web.Contents("https://trackapi.thailandpost.co.th/post/api/v1/track", 
        [
            Headers=
                [
                Authorization="Token "&DataToken,
                #"Content-Type"="application/json"
                ],
            Content=Json.FromValue(MyRecord)                  
        ] 
    
        ))
    in
        Source

    เนี่ย ทุกอย่างยังทำงานได้เหมือนเดิม แต่เจ๋งกว่าเดิมตรงแก้รหัสที่จะ Track ได้ง่ายเลย!!

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 43

    และนี่คือผลลัพธ์ที่เราได้ครับ แจ่มมากๆ

    วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API 44

    ข้อควรระวัง ที่เขียนไว้ในระบบ คือ  สามารถใช้งาน API ได้สูงสุด 1,000 หมายเลขต่อการ Post 1 ครั้ง โดยสามารถใช้งานได้ 15 ครั้ง ต่อ 3 วินาที กรณีมีการใช้งานเกิน 15 ครั้ง ต่อ 3 วินาที ระบบจะทำการปิดกั้นการใช้งานชั่วคราว เป็นระยะเวลา 30 วินาที

    ดังนั้นอย่าส่งเยอะและถี่เกินไปนะครับ ^^

  • วิธีแก้ปัญหาเวลาเขียน M Code แบบ each ซ้อนกันหลายอัน

    วิธีแก้ปัญหาเวลาเขียน M Code แบบ each ซ้อนกันหลายอัน

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

    เช่น กรณีอ้างอิงชื่อสินค้าไปเลย สามารถทำได้ โดย Add Custom Column แบบนี้ โดยใช้ Table.SelectRows เพื่อ Filter ข้อมูล MyTable ให้เหลือเฉพาะบรรทัดที่ต้องการ

    Table.SelectRows(MyTable,each [สินค้า]="อาหาร")

    ซึ่งจะได้ผลเป็น M Code เต็มๆ แบบนี้ ซึ่งจะมี each 2 ตัว ซ้อนกันอยู่ ซึ่งนี่แหละจะทำให้เกิดปัญหา เวลาไปอ้างอิงคอลัมน์แบบปกติ

    = Table.AddColumn(MyTable, "ยอดขายของสินค้า", each Table.SelectRows(MyTable,each [สินค้า]="อาหาร"))
    วิธีแก้ปัญหาเวลาเขียน M Code แบบ each ซ้อนกันหลายอัน 45

    แต่ถ้าเราพยายามจะแก้คำว่า “อาหาร” ในสูตร ให้เป็น [สินค้า] เพื่อพยายามจะอ้างอิงสินค้าในบรรทัดนั้นๆ จะเจอปัญหาทันที เพราะสูตร [สินค้า]=[สินค้า] มันจะเป็นจริงเสมอนั่นเอง ผลลัพธ์จึงไม่มีการ Filter อะไรเลย

    Table.SelectRows(MyTable,each [สินค้า]=[สินค้า])
    วิธีแก้ปัญหาเวลาเขียน M Code แบบ each ซ้อนกันหลายอัน 46

    แล้วเราจะทำให้มันไปอ้างอิงว่า ให้ Filter คอลัมน์ สินค้า ของ MyTable ให้มีค่าเท่ากับสินค้าในบรรทัดนั้นๆ ได้ยังไง?

    เราต้องข้าใจก่อนว่า each [สินค้า] เนี่ย มันย่อมาจาก (_) => _[สินค้า] หรือ (x) =>x[สินค้า] โดย x คือ parameter ของฟังก์ชัน ซึ่งจะตั้งชื่ออะไรก็ได้ เช่น ผมอาจตั้งชื่อว่า main ก็ได้ เพื่ออ้างอิงถึง each ตัวข้างนอกสุด สรุปว่าได้แบบนี้

    ซึ่ง main[สินค้า] จะหมายถึงคอลัมน์ของ Table.AddColumn แต่ [สินค้า] จะหมายถึงคอลัมน์ตอนที่ใช้ Table.SelectRows

    = Table.AddColumn(MyTable, "ยอดขายของสินค้า", (main)=> Table.SelectRows(MyTable,each [สินค้า]=main[สินค้า]))
    วิธีแก้ปัญหาเวลาเขียน M Code แบบ each ซ้อนกันหลายอัน 47

    พออ้างอิงตารางได้แล้ว ก็เลือกเอาคอลัมน์ยอดขายออกมา ด้วยการใส่ [ยอดขาย] ต่อท้าย เช่น

    = Table.AddColumn(MyTable, "ยอดขายของสินค้า", (main)=> Table.SelectRows(MyTable,each [สินค้า]=main[สินค้า])[ยอดขาย])
    วิธีแก้ปัญหาเวลาเขียน M Code แบบ each ซ้อนกันหลายอัน 48

    สุดท้ายก็ใช้ List.Sum มาทำการหาผลรวมซะ จะได้แบบนี้

    = Table.AddColumn(MyTable, "ยอดขายของสินค้า", (main)=> List.Sum(Table.SelectRows(MyTable,each [สินค้า]=main[สินค้า])[ยอดขาย]))
    วิธีแก้ปัญหาเวลาเขียน M Code แบบ each ซ้อนกันหลายอัน 49

    และนี่คือแนวทางจัดการเวลามี each ซ้อนกันหลายชั้นครับ นั้นคือ ให้เปลี่ยน each แต่ละตัวเป็นชื่อ parameter เต็มๆ คู่กับ => แบบไม่ต้องย่อว่า each แบบปกตินั่นเอง

  • วิธีใช้ Regular Expression (RegEx) ใน Power Query

    วิธีใช้ Regular Expression (RegEx) ใน Power Query

    ตามปกติแล้ว ณ ตอนนี้ Power Query นั้นยังไม่ Support เรื่องการใช้ Regular Expression (RegEx) ทั้งใน Excel และ Power BI ก็ยังใช้ไม่ได้ แต่เจ้า Regular Expression นี้มีประโยชน์มากๆ จริงๆ จนหาสิ่งอื่นทดแทนไม่ได้เลย ดังนั้นผมก็เลยพยายามหาวิธีพลิกแพลงเพื่อทำมันให้ได้โดยเขียนฟังก์ชัน M Code ขึ้นมาใช้เองใน Power Query ซึ่งมีเทคนิคลับช่วยให้มันทำได้อยู่ครับ มาดูกันว่าทำได้ยังไง?

    เผื่อใครที่ยังไม่รู้จัก Regular Expression

    Regular Expression (RegEx) มีความสามารถจะช่วยให้เราสามารถค้นหา/แทนที่ข้อความที่ตรงกับ Pattern ที่เราระบุได้ครับ ซึ่งเรื่องของเรื่องคือ Pattern มันสามารถรองรับเคสที่ซับซ้อนได้มากๆ และดีกว่าการค้นหาปกติของ Excel ที่รองรับได้แค่ข้อความปกติ หรืออย่างมากก็มีแค่ Wildcard *กับ? เท่านั้น (ซึ่งมัน Simple มากเกินไป) แปลว่าการค้นหาด้วย RegEx จะทำให้เราค้นหาและดึงข้อความที่เราต้องการได้ดั่งใจ

    ซึ่งตอนนี้ Excel กับ Power Query ยังไม่มี RegEx ในตัว แต่ในเครื่องมืออื่น เช่น Google Sheets, Python, R, Java เค้าก็มีกันหมดแล้ว ทำให้นี่เป็นจุดอ่อนสำคัญของ Excel และ Power Query/Power BI เลย

    แม้ว่าใน Excel VBA ก็สามารถ add Library เกี่ยวกับ Regular Expression มาใช้ได้ แต่ในบางกรณีเราอาจใช้ VBA ไม่ได้ ดังนั้นผมก็เลยพยายามเขียนฟังก์ชันขึ้นมาใน Power Query ครับ ซึ่งผลลัพธ์ที่ได้ออกมาน่าพอใจสำหรับผมเองมากๆ

    และในระหว่างที่รอให้เค้าพัฒนา RegEX อย่างเป็นทางการ ก็สามารถไปช่วยกันโหวตได้ที่ https://ideas.powerbi.com/ideas/idea/?ideaid=1395c308-7f92-4321-8e35-6cd04d7108bf

    หัดใช้ Regular Expression

    ผมเคยเขียนบทความอธิบายการใช้งาน RegEx เบื้องต้นไว้นิดหน่อยที่นี่

    แนวทางของการใช้ Regular Expression ใน Power Query

    การจะใช้ Regular Expression ใน Power Query ได้นั้น มีอยู่ 2 แนวทางใหญ่ๆ นั่นคือ

    1. ใช้ Python มาช่วย (ทำได้แค่ใน Power BI เท่านั้น) ถ้าใครสนใจอ่านเพิ่มเติมได้ที่นี่
    2. ใช้ JavaScript มาช่วย (ใช้ได้ทั้ง Excel และ Power BI)

    ซึ่งแนวทางที่ผมจะใช้ในบทความนี้คือ การใช้ JavaScript มาช่วย ซึ่งใช้ได้ทั้ง Excel และ Power BI และไม่ต้องลงโปรแกรม Python แถมไม่ต้องต่อ Internet ด้วยซ้ำครับ และที่สำคัญ ความสามารถของมันมากกว่าที่ใช้ใน Google Sheets ด้วยนะครับ (อันนี้มันดึงได้แค่อันแรกที่เจอ แต่ของผมดึงได้ทุกอัน)

    แนวทางการใช้ JavaScript มาช่วยทำ Regular Expression ใน Power Query

    หลักการคือเราสามารถสร้าง Script ข้อความที่เป็นภาษา JavaScript แล้วใส่เข้าไปในคำสั่ง Web.Page() ของ Power Query ได้ ซึ่งจะได้ผลลัพธ์กลับมาเป็นตารางที่เก็บ Html ของเว็บนั้นๆ (แนวคิดนี้เอามาจากเว็บ biaccountant บทความนี้)

    ซึ่งใน JavaScript นั้นเอง เราจะสั่งใน Run คำสั่งที่รองรับ Regular Expression ใน JavaScript เช่น String.Match, String.Replace, String.Search, RegExp.exec ได้ (ความเศร้าคือมันใช้ String.MatchAll ไม่ได้ เพราะ Internet Explorer มันไม่รองรับ แต่ผมมีวิธีพลิกแพลงได้ อยู่ตอนจบของบทความ)

    ซึ่งสุดท้ายเราสามารถใช้คำสั่ง Document.Write ของ JavaScript เพื่อให้พ่นผลลัพธ์สุดท้ายที่เราต้องการออกมาในหน้า HTML ได้ แล้ว Power Query ก็เอาค่านั้นกลับมาใช้ด้วย Web.Page() นั่นเอง

    พอเรารู้แบบนี้แล้ว เราก็สามารถสร้างฟังก์ชันใน Power Query เพื่อให้รับค่าข้อความ และ Pattern ของ RegEx ที่ต้องการ แล้วไปทำงานตาม Concept ที่ผมเล่าให้ฟังได้ ตัวอย่างดังนี้

    ThepRegExExtract : ดึงข้อความที่ตรงกับ RegEx Pattern ที่ระบุออกมา

    (OriginalText as text,RegExPattern as text, optional RegExMode as text) as list

    RegExMode

    • ไม่ระบุ RegExMode (หรือ เป็นค่าว่าง) สามารถใช้ค้นหาข้อความแรกที่ตรง Pattern (เอา Capturing Group ด้วย)
    • RegExMode เป็น “i” สามารถใช้ค้นหาข้อความแบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่
    • RegExMode เป็น “g” สามารถใช้ค้นหาข้อความทุกตัวพร้อมกันได้ (ไม่เอา Capturing Group)
    • RegExMode เป็น “gi” สามารถใช้ค้นหาข้อความทุกตัวพร้อมกันได้ (ไม่เอา Capturing Group) และ ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่

    Function M Code ( Copy ใส่ Advanced Editor ได้เลย)

    (OriginalText as text,RegExPattern as text, optional RegExMode as text) as any=>
     let 
        // MCode created by ThepExcel.com
        MyTextFix=Text.Replace(OriginalText,"\","\\"),
        MyTextFix2=Text.Replace(Text.Replace(MyTextFix,"#(lf)","\n"),"#(cr)","\r"),
        //แปลงให้ข้อความที่มี \ หรือการขึ้นบรรทัดใหม่ ไปใช้ใน Javascript ได้ถูกต้อง
        re= if RegExMode is null then "/"&RegExPattern&"/" else "/"&RegExPattern&"/"&RegExMode,
        //สามารถใส่ RegExMode เป็น "g", "i" หรือ "gi" ได้
        //ถ้าไม่ใส่ Mode อะไรก็จะถือเป็นการ Single Match และ case sensitive
        getWeb=Web.Page(
        "<meta http-equiv='X-UA-Compatible' content='IE=edge'>
        <script>
        var MyText='"&MyTextFix2&"';
        var MyMatches=MyText.match("&re&");
        var MyResult=JSON.stringify(MyMatches);
        document.write(MyResult);
        </script>"),
        // ใช้ JavaScript ดึงผลลัพธ์ออกมาเป็น JSON String
        getWeb2=getWeb[Data]{0}[Children]{0}[Children]{1}[Text],
        finalResult=try Json.Document(Text.Combine(getWeb2)) otherwise null
        // ให้ PQ อ่านผลลัพธ์จาก JSON string ให้กลายเป็น List
    in  
        finalResult

    ซึ่งเวลาใช้งานสามารถเรียกใช้งานได้ผลลัพธ์ออกมาเป็น List ซึ่งเอาไว้รองรับกรณีใช้ Capturing Group ได้ด้วย

    ตัวอย่างการใช้งาน

    วิธีใช้ Regular Expression (RegEx) ใน Power Query 50
    หาแบบ default จะได้ผลลัพธ์ตัวแรก และ capturing group ของมัน
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 51
    ถ้าใส่ mode เป็น “g” จะหาแบบ global คือหาทุก match (แต่ไม่สนใจ capturing group)
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 52
    หากแบบ gi คือ global + case insensitive คือ ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่ แบบหาทุก match
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 53
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 54
    หากแบบ i คือ case insensitive คือ ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่ ได้ตัวแรกอันเดียว แต่เอา capturing group ด้วย
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 55
    ถ้าใส่ mode เป็น “g” จะหาแบบ global คือหาทุก match (แต่ไม่สนใจ capturing group)

    ThepRegExMatchCount : นับได้ว่าเจอผลลัพธ์ตาม Pattern กี่ชุด (ไม่สนใจ capturing Group)

    ในทำนองเดียวกันเราก็สามารถประยุกต์สร้างอีกฟังก์ชันขึ้นมาเพื่อนับได้ว่าเจอผลลัพธ์กี่ตัว ดังนี้

    (OriginalText as text,RegExPattern as text, optional RegExMode as text) as number

    RegExMode

    • RegExMode เป็นค่าว่าง จะหาแบบสนใจพิมพ์เล็กพิมพ์ใหญ่
    • RegExMode เป็น “i” สามารถใช้ค้นหาข้อความแบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่

    Function M Code

    (OriginalText as text,RegExPattern as text, optional RegExMode as text) as number=>
     let 
        // MCode created by ThepExcel.com
        MyTextFix=Text.Replace(OriginalText,"\","\\"),
        MyTextFix2=Text.Replace(Text.Replace(MyTextFix,"#(lf)","\n"),"#(cr)","\r"),
        //แปลงให้ข้อความที่มี \ หรือการขึ้นบรรทัดใหม่ ไปใช้ใน Javascript ได้ถูกต้อง
        re= if RegExMode is null then "/"&RegExPattern&"/g" else "/"&RegExPattern&"/g"&RegExMode,
        //สามารถใส่ RegExMode เป็น "i" ได้
        //ถ้าไม่ใส่ Mode อะไรก็จะถือเป็นการ Single Match และ case sensitive
        getWeb=Web.Page(
        "<meta http-equiv='X-UA-Compatible' content='IE=edge'>
        <script>
        var MyText='"&MyTextFix2&"';
        var MyMatches=MyText.match("&re&");
        document.write(MyMatches.length);
        </script>"),
        getWeb2=getWeb[Data]{0}[Children]{0}[Children]{1}[Text],
        finalResult=try Number.From(Text.Combine(getWeb2)) otherwise 0
        // ให้ PQ อ่านผลลัพธ์จาก JSON string ให้กลายเป็น List
    in  
        finalResult
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 56
    นับว่าเจอผลลัพธ์กี่อัน

    ThepRegExReplace : แทนที่ข้อความที่ตรงกับ Pattern ที่ระบุ

    (OriginalText as text,RegExPattern as text,NewText as text,optional RegExMode as text) as text

    RegExMode

    • ไม่ระบุ RegExMode (หรือ เป็นค่าว่าง) สามารถใช้แทนที่ข้อความแรกที่ตรง Pattern แบบสนใจพิมพ์เล็กพิมพ์ใหญ่
    • RegExMode เป็น “i” สามารถใช้แทนที่ข้อความแทนที่ข้อความแรกที่ตรง Pattern แบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่
    • RegExMode เป็น “g” สามารถใช้แทนที่ข้อความทุกตัวที่ตรง Pattern แบบสนใจพิมพ์เล็กพิมพ์ใหญ่
    • RegExMode เป็น “gi” สามารถใช้แทนที่ข้อความทุกตัวที่ตรง Pattern แบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่
    (OriginalText as text,RegExPattern as text,NewText as text,optional RegExMode as text) as text=>
     let 
        // MCode created by ThepExcel.com
        MyTextFix=Text.Replace(OriginalText,"\","\\"),
        MyTextFix2=Text.Replace(Text.Replace(MyTextFix,"#(lf)","\n"),"#(cr)","\r"),
        NewTextFix=Text.Replace(NewText,"\","\\"),
        re= if RegExMode is null then "/"&RegExPattern&"/" else "/"&RegExPattern&"/"&RegExMode,
        //สามารถใส่ RegExMode เป็น "g", "i" หรือ "gi" ได้
        //ถ้าไม่ใส่ Mode อะไรก็จะถือเป็นการ Single Match และ case sensitive
        getWeb=Web.Page(
        "<meta http-equiv='X-UA-Compatible' content='IE=edge'>
        <script>
        var MyText='"&MyTextFix2&"';
        var MyNewText='"&NewTextFix&"';
        var MyMatches=MyText.replace("&re&",MyNewText);
        var MyResult=JSON.stringify(MyMatches);
        document.write(MyResult);
        </script>"),
        getWeb2=getWeb[Data]{0}[Children]{0}[Children]{1}[Text],
        finalResult=Json.Document(Text.Combine(getWeb2))
    in  
        finalResult

    ตัวอย่างการใช้งาน

    วิธีใช้ Regular Expression (RegEx) ใน Power Query 57
    แทนที่ตัวแรกที่ตรงกับ Pattern
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 58
    แทนที่ตัวแรกที่ตรงกับ Pattern และใช้ Capturing Group
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 59
    แทนที่ทุกตัวที่ตรงกับ Pattern และใช้ Capturing Group
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 60
    แทนที่ตัวแรกที่ตรงกับ Pattern และใช้ Capturing Group หลายอัน

    ThepRegExExtractAll : ดึงผลลัพธ์ทุกตัวทุก SubGroup ออกมาเป็น List

    อย่างไรก็ตาม ตัวที่ท้าทายที่สุดก็คือ การ Extract ข้อมูลทุกตัวที่ตรงกับ Pattern และเอา Capturing Group ด้วย ซึ่งเราใช้ฟังก์ชัน String.MatchAll ของ JavaScript ตรงๆไม่ได้ เพราะ Internet Explorer มันไม่ Support

    แนวทางนึงที่ทำได้คือ “ใช้การวน Loop มาช่วย” ซึ่งเดิมทีผมลองใช้ List.Generate ของ Power Query ในการวน Loop ซึ่งแม้จะได้ผลลัพธ์ออกมาถูกต้อง แต่ Performance มันช้ามาก ทำให้สุดท้ายผมเปลี่ยนไปใช้อีกวิธีซึ่ง Performance ออกมาดีมากๆ เพราะไป Loop ใน JavaScript จนจบเลยแล้วค่อยส่งผลลัพธ์ออกมา

    ตัวอย่างการใช้งาน

    วิธีใช้ Regular Expression (RegEx) ใน Power Query 61
    ถ้าไม่ระบุ GroupIndex จะได้ออกมาทั้งหมดเลยทุกตัว ทุก Group เป็น List ซ้อน List
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 62
    ถ้าระบุ GroupIndex เป็น 0 คือจะได้ผลลัพธ์ทุกตัวแบบไม่สนใจ Capturing Group
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 63
    ถ้าระบุ GroupIndex เป็น 1 คือจะได้ผลลัพธ์ทุกตัวเฉพาะ Capturing Group อันแรก

    ใครสนใจอยากได้ฟังก์ชัน ThepRegExExtractAll ที่เต็มเปี่ยมไปด้วยความสามารถที่สุดยอดแบบนี้ (หรืออยากช่วยสนับสนุนผม) สามารถสั่งซื้อ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel แบบ Premium ได้นะครับ

    • Version Premium สั่งซื้อได้ที่นี่ (ใช้ได้ตลอดชีวิต ในราคา 590 บาท แต่ถ้าซื้อภายใน กค. 2564 นี้ จะเหลือ 490 บาท )
      (หลังจากซื้อและได้รับการตรวจสอบแล้ว ผมจะ Add คุณเข้า Group ที่จะมีสิทธิ์โหลดไฟล์แบบ Premium ได้ครับ)
    วิธีใช้ Regular Expression (RegEx) ใน Power Query 64
    ทำให้ดูว่าใน Power BI ก็ใช้ได้นะครับ

  • แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel

    หากใครได้ลองใช้ Power Query คงจะพอรู้ว่ามันช่วยลดเวลาในการทำงานของเราได้มหาศาลแค่ไหน (บอกเลยว่างานเตรียมข้อมูลนี่ผมใช้แต่ Power Query ล้วนๆ ไม่ได้แตะสูตร Excel ปกติมานานมากแล้ว ) แต่ก็ปฏิเสธไม่ได้เหมือนกันว่าเครื่องมือและฟังก์ชันสำเร็จรูปที่มาพร้อมกับตัวโปรแกรมนั้นก็ยังไม่สามารถตอบโจทย์การทำงานของเราได้เต็มที่

    ดังนั้นผมจึงคิดว่า ทำไมเราไม่เขียนฟังก์ชันที่ตอบโจทย์ขึ้นมาเองซะเลยล่ะ! จะได้แก้ Pain Point และให้การใช้งาน Power Query ง่ายขึ้นด้วย

    ในบทความนี้ผมก็จะมาแนะนำให้รู้จักว่าผมเขียนฟังก์ชันอะไรเจ๋งๆ ออกมาใน Package ThepExcel-Mfx บ้าง และความสามารถของมันคืออะไร? ซึ่งจะใช้ได้ทั้งกับ Excel และ Power BI เลยครับ สะดวกมากๆ ยกตัวอย่างสิ่งที่ผมทำไว้ เช่น

    • ดึงเฉพาะตัวเลขออกมาจากข้อความ
    • แปลงหัวตารางหลายชั้นให้เป็นหัวตารางชั้นเดียวแบบสูตรเดียวจบ
    • เปลี่ยนชื่อคอลัมน์ได้ด้วยลำดับคอลัมน์ แทนที่จะต้องอ้างอิงชื่อเดิม
    • ย้านคอลัมน์ไปหน้าสุดได้ โดยไม่ต้องอ้างอิงชื่อคอลัมน์อื่นที่ไม่เกี่ยวข้อง
    • แทนที่ข้อความทีเดียวหลายๆ ค่า

    และยังมีอีกมากมาย ลองอ่านดูได้เลยครับ ว่ามีตัวไหนน่าสนใจบ้าง (จะมีเพิ่มขึ้นเรื่อยๆ ด้วยนะ)

    Trim แบบเดียวกับ Excel ด้วย ThepTrim

    ใน Power Query นั้นมี Trim แต่ว่ามันดันไม่ตัดเว้นวรรคตรงกลาง วิธีแก้ปัญหาที่ค่อนข้างดีคือสร้างฟังก์ชันขึ้นมาให้มันทำแบบเดียวกับ Excel ซะเลย

    คุณสามารถเอา M code นี้ไปใช้เป็นฟังก์ชันของตัวเองได้ เช่น ตั้งชื่อว่า ThepTrim ก็ได้

    (OriginalText as text,optional TrimChar as text) as text=>
    let
        // MCode created by ThepExcel.com
        TrimCharFix = if TrimChar is null then " " else TrimChar,
        SplitStep = Text.Split(OriginalText,TrimCharFix),
        RemoveBlankStep = List.Select(SplitStep, each _ <> ""),
        CombineStep = Text.Combine(RemoveBlankStep,TrimCharFix)
    in
    CombineStep

    วิธีโหลด Package ThepExcel-Mfx

    แต่ก่อนจะใช้ฟังก์ชันที่ผมเขียนได้ ก็ต้องมีฟังก์ชันที่ผมเขียนก่อนนะครับ

    ใครสนใจไฟล์ Package ThepExcel-Mfx ที่ผมทำขึ้นมานี้สามารถโหลดไฟล์ได้ครับ
    มีทั้งแบบฟรี และแบบ Premium (ใครที่อยากได้ฟังก์ชันเจ๋งๆ เยอะกว่า หรือ อยากสนับสนุนผมก็ซื้อแบบ Premium ได้ครับ)
    ซึ่งทั้ง 2 แบบผมจะมีการเพิ่มฟังก์ชันให้เรื่อยๆ ตาม Feedback ของคนใช้งานด้วยครับ

    1. พอมีไฟล์ของผมแล้วทำดังนี้ ให้ Copy Query Folder ที่ชื่อ ThepExcel-Mfx
    2. จากนั้นเอาไป Paste ใส่ในไฟล์ Excel หรือ Power BI ของตัวเองได้เลย
    ThepExcel-Mfx

    เพียงเท่านี้คุณก็จะสามารถใช้ฟังก์ชันพิเศษที่ผมเขียนเอาไว้ได้แล้วครับ

    แนะนำฟังก์ชันใน Package ThepExcel-Mfx

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 65

    ให้ตารางข้างบนเป็น Query ที่ชื่อว่า MyTable ซึ่งจะใช้อ้างอิงกับหลายๆ ตัวอย่างนะครับ

    กลุ่มที่อยู่ใน Package Free (ใน Premium ก็มี)

    ThepDatefromText : เปลี่ยนข้อความ ให้เป็นวันที่ แถมเลื่อนปีได้

    =ThepDatefromText(DateText as text,DateFormat,optional offsetYear as number)as date
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 66
    สามารถกำหนดได้ให้อ่าน format แบบไหน โดยมันจะสนใจแค่การเรียงของตัวเลขเท่านั้น ไม่สนตัวคั่น
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 67
    สามารถเลื่อนปีให้น้อยลง 543 ปีได้ เพื่อแก้ปัญหาการกรอก พ.ศ. <-> ค.ศ.

    ThepExtractNumber : ดึงเฉพาะตัวเลขจากข้อความ เลือกได้ว่าจะเอาจุดกับ space หรือไม่

    =ThepExtractNumber(OriginalText as text,optional Keepdot as logical, optional Keepspace as logical) as text
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 68
    เก็บเฉพาะตัวเลข ถ้าไม่ระบุอะไรเพิ่ม คือจะเก็บ . และ space ไว้ด้วย

    ThepGetColumnName : ดึงเอาชื่อคอลัมน์จากลำดับตัวเลข

    =ThepGetColumnName(TableName as table,ColNumber as number) as text
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 69
    ดึงค่าว่าคอลัมน์ที่ 3 ของตาราง MyTable ชื่อว่าอะไร

    ThepTrim : Trim แบบเดียวกับ Excel ที่เราคุ้นเคย แต่เลือกอักขระที่จะ Trim ได้ด้วย

    =ThepTrim(OriginalText as text,optional TrimChar as text) as text
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 70
    ถ้าไม่ระบุอะไรก็จะ Trim ข้อความที่เป็น Space ออกไป ทั้งหน้า หลัง และตรงกลางจะเหลือแค่เคาะเดียว
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 71
    แต่ถ้าระบุอักขระตัวไหนก็จะ Trim ตัวนั้นออกแทน space

    ThepGetMultipleListItem : ดึงข้อมูลจาก List หลายๆ ไอเท็มพร้อมกันได้

    =ThepGetMultipleListItem(OriginalList as list,PosIndex as list) as list

    โดยระบุ PosIndex เป็น List ที่เก็บ index ของ item ที่ต้องการไว้ (index ของ list เริ่มต้นที่เลข 0)

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 72
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 73
    สามารถระบุ Index ติดลบเพื่อเอาข้อมูลนับจากหลังสุดได้ (คล้าย Python)

    ThepRenameColumn : เปลี่ยนชื่อคอลัมน์โดยระบุตำแหน่ง (ระบุเป็น list ได้ )

    =ThepRenameColumn(TableName as table,ColNumber as any,NewName as any) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 74
    แบบเปลี่ยนแค่คอลัมน์เดียว
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 75
    แบบเปลี่ยนคอลัมน์ที่ 1 3 และ 4 พร้อมกัน (ถ้าใส่ลำดับคอลัมน์เป็น -1 ก็จะหมายถึงคอลัมน์สุดท้าย)

    ThepGenDateTableFromDate : สร้างตารางจากวันที่เริ่มต้นและสิ้นสุดที่กำหนด

    =ThepGenDateTableFromDate(StartDate as date,EndDate as date) as table

    สามารถกด Invoke ฟังก์ชันแล้วระบุวันที่จากปฏิทินได้เลย

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 76
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 77

    ThepGenDateTableFromText : สร้างตารางจากข้อความวันที่เริ่มต้นและสิ้นสุดที่กำหนด

    =ThepGenDateTableFromText(StartDateText as text,EndDateText as text) as table

    คล้ายๆ กับฟังก์ชันก่อนหน้า แต่คราวนี้ ระบุ Date ให้เป็น text รูปแบบ yyyymmdd ได้เลย

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 78
    สร้างวันที่ตั้งแต่ 1 Jan 2020 ถึง 15 Jan 2020

    ThepReplaceAllError : เปลี่ยน Error ทั้งตารางให้กลายเป็นตัวที่ต้องการ

    =ThepReplaceAllError(TableName as table,optional ReplaceWith as text) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 79
    เดิมมี Error หลายจุด
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 80
    สามารถสั่งให้กลายเป็นค่าอะไรก็ได้ เช่น ค่าว่าง null

    ThepRegExExtract : ดึงข้อความที่ตรงกับ RegEx Pattern ที่ระบุออกมา

    ThepRegExMatchCount : นับได้ว่าเจอผลลัพธ์ตาม Pattern กี่ชุด (ไม่สนใจ capturing Group)

    ThepRegExReplace : แทนที่ข้อความที่ตรงกับ Pattern ที่ระบุ

    ThepOneHot : แปลงคอลัมน์ที่มีหลายๆ item เป็น Category ให้กลายเป็นคอลัมน์ที่มีค่าเป็น 1 กับ 0 (One Hot Encoding)

    =ThepOneHot(TableName as table,TargetColumnName as text) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 81

    ThepThaiNumbertoArabic : แปลงเลขไทยเป็นเลขอารบิก

    = ThepThaiNumbertoArabic(OriginalText as text) as text
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 82

    กลุ่มที่อยู่ใน Package Version Premium เท่านั้น

    ThepReplaceTextList : แทนค่าในข้อความโดยใส่ค่าทีเดียว

    หลายๆ ตัวโดยใช้ List ได้

    =ThepReplaceTextList(OriginalText as text,OldTextList as list,NewTextList as list) as text 
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 83

    ThepFlattenHeader : เปลี่ยนหัวตารางหลายชั้นให้กลายเป็นชั้นเดียว แถมใส่ตัวคั่นได้

    =ThepFlattenHeader(TableName as table,NumRowHeader as number,optional Seperator as text, optional reverse as logical) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 84
    หัวตาราง 2 ชั้น ช่างช้ำใจ…
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 85
    ถ้าไม่ระบุตัวคั่นก็จะต่อกันเลย และลำดับจะย้อนกลับจากหัวตารางล่างไปบนให้ด้วย
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 86
    สามารถใส่ตัวคั่น เช่น – และระบุเป็น false เพื่อไม่ต้องกลับลำดับบนล่างของหัวตาราง

    ThepGetColumn : ดึงเอาคอลัมน์จากลำดับตัวเลขเริ่มและสิ้นสุด

    =ThepGetColumn(TableName as table,StartColumnNum as number,optional EndColumnNum as number) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 87
    เอาเฉพาะคอลัมน์ 2 ถึง 4 จากตาราง MyTable มาได้เลย

    ThepMoveColumn : ย้ายคอลัมน์แบบไม่อ้างอิงชื่อคอลัมน์อื่นที่ไม่เกี่ยวข้อง

    =ThepMoveColumn(TableName as table,ColumnName as any,optional MoveFirst as logical) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 88
    ย้ายคอลัมน์ลูกค้ามาซ้ายสุดของตาราง โดยสูตรไม่อ้างอิงชื่อคอลัมน์อื่น
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 89
    ย้ายคอลัมน์ลูกค้ากับสินค้า (ระบุเป็น List ได้) ไปขวาสุดของตาราง ( ระบุ MoveFirst เป็น false) โดยสูตรไม่อ้างอิงชื่อคอลัมน์อื่น

    ThepExpandAllColumn : แตกคอลัมน์ออกมาจากทุกตารางแบบไม่ฝังชื่อคอลัมน์ กำหนด prefix ได้

    =ThepExpandAllColumn(TableName as table,optional prefix as text) as table

    ตารางที่จะ Expand ออกมาจะต้องอยู่ที่คอลัมน์ที่ชื่อว่า Data เท่านั้น เช่น มีข้อมูลก่อนจะ Expand ดังนี้

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 90

    เราสามารถเรียกฟังก์ชันมา Expand คอลัมน์ทั้งหมดได้ (โดยมันจะ Scan คอลัมน์ทั้งหมดใหม่เสมอ) ดังนี้

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 91

    หรือเราจะระบุ Prefix ของคอลัมน์ที่ Expand ออกมาก็ได้ เช่น

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 92

    ThepMergeTableOffsetRow : ดึงข้อมูล 2 ตารางมาประกบกัน สามารถเหลื่อมแถวได้

    =ThepMergeTableOffsetRow(TableName1 as table,TableName2 as table, RowOffset as number) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 93
    สั่งให้ Merge กับตารางตัวเอง โดยเลื่อนแถว -1 คือ เอาข้อมูลของแถวก่อนหน้ามาได้

    ThepSearchColumn : เลือกเอาเฉพาะคอลัมน์ที่มีคำที่กำหนด แถมปะเพิ่มคอลัมน์ที่ต้องการได้

    =ThepSearchColumn(TableName as table,FindColText as text,optional MatchType as text,optional ColtoKeep as list) as table

    ตัว parameter MatchType สามารถระบุ option เป็นข้อความได้ดังนี้

    • contain : หาที่มีคำนั้นอยู่ข้างใน (ค่า Default กรณีไม่ระบุ MatchType)
    • start: หาที่มีคำนั้นนำหน้า
    • end : หาที่มีคำนั้นลงท้าย
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 94
    หาคอลัมน์ที่มีคำว่า ชิ้น อยู่
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 95
    หาคอลัมน์ที่มีคำว่าชิ้น และปะคอลัมน์ TXID กับ สินค้าเข้าไปด้วย

    ThepRegExExtractAll : ดึงผลลัพธ์ทุกตัวทุก SubGroup ออกมาเป็น List

    เทคนิคที่ทำให้ใช้สูตรของผมได้ง่ายขึ้น (ไม่จำเป็นต้องทำก็ได้)

    ปกติแล้ว การ Copy Paste Query ข้ามไฟล์ ถ้า Code ใน Query นั้นๆ ดันไม่ได้เริ่มด้วย let…in… ตัวโปรแกรมจะใส่ Code let…in…เพิ่มมาให้เองอัตโนมัติ

    ซึ่งมีข้อเสียคือ เวลาเรียกใช้ฟังก์ชันใน Formula Bar มันจะมองไม่เห็น ToolTips เช่นในรูป (แต่ถ้ากด Invoke Function จะเห็นปกตินะ)

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 96

    ซึ่งถ้าอยากให้ตอนเขียนสูตรใน Formula Bar เห็น Tooltips ตามปกติตามรูปข้างล่าง ก็มีอยู่ 3 way ที่จะแนะนำ คือ

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 97
    • Way 1: ให้เอาไฟล์ของผม copy paste เป็นไฟล์ตั้งต้นของคุณเวลาจะเขียน Query ไปเลย (โดยไม่ต้องใช้การ copy query)
    • Way 2 : เปิด Advanced Editor แล้ว copy M code จากไฟล์ผมไป Paste ใน Blank Query ของคุณตรงๆ
    • Way 3 : ใช้การ Copy Query นี่แหละ แต่ให้เข้า Advanced Editor แล้วเอาคำว่า let…in… source ที่ มาครอบอยู่ออกไปซะครับ

    เดี๋ยวขออธิบาย Way3 เพิ่มเติม… เช่น สูตรในฟังก์ชัน ThepTrim หลังจาก Copy Query มาจะเป็นแบบนี้ (มีสีแดงๆ เพิ่มมาโดยอัตโนมัติ ให้คุณเอามันออกไปซะ)

    ดู code แบบนี้ได้ใน View -> Advanced Editor นะครับ

    let
         Source = (OriginalText as text,optional TrimChar as text) as text=>
     let
         TrimCharFix = if TrimChar is null then " " else TrimChar,
         SplitStep = Text.Split(OriginalText,TrimCharFix),
         RemoveBlankStep = List.Select(SplitStep, each _ <> ""),
         CombineStep = Text.Combine(RemoveBlankStep,TrimCharFix)
     in
     CombineStep
     in
         Source

    พอเอาสีแดงๆ ออกไปจะเหลือเป็นแบบนี้ ซึ่งจะใช้สูตรได้ดีขึ้น

    (OriginalText as text,optional TrimChar as text) as text=>
     let
         TrimCharFix = if TrimChar is null then " " else TrimChar,
         SplitStep = Text.Split(OriginalText,TrimCharFix),
         RemoveBlankStep = List.Select(SplitStep, each _ <> ""),
         CombineStep = Text.Combine(RemoveBlankStep,TrimCharFix)
     in
     CombineStep
  • เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ

    Power Query นั้นมาพร้อมกับเครื่องมือสำเร็จรูปที่ช่วยให้เราจัดการข้อมูลได้ง่ายขึ้นมาก แต่ก็ยังมีอีกหลายสถานการณ์ที่เครื่องมือสำเร็จรูปไม่ตอบโจทย์เรา 100% ซึ่งจะต้องมีการแก้สูตร M Code ให้ทำงานได้ตรงใจเรามากขึ้น (แต่อย่าเพิ่งตกใจ มันไม่ได้ยากขนาดนั้น) และในบทความนี้ผมจะแนะนำ Tips การแก้สูตรเล็กๆ น้อยๆ แต่ช่วยให้งานเราสำเร็จได้ดีขึ้นมหาศาลเลย

    เทคนิคการ Filter แบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่

    ปกติแล้วการ Filter ใน Power Query จะสนใจพิมพ์เล็กพิมพ์ใหญ่ด้วย (จริงๆ ก็กับทุกฟังก์ชันนั่นใน Power Query นั่นแหละ) ดังนั้นถ้าเราไม่ต้องการให้มันสนใจเรื่องนี้ก็มีทางแก้อยู่ 2 แนวทาง นั่นก็บังคับให้ทุกตัวเป็นตัวพิมพ์แบบเดียวกันก่อน (เช่นทำเป็น lowercase) แล้วค่อย filter ด้วยเงื่อนไขตัวพิมพ์เล็กใน step ถัดไป ซึ่งคงเป็นวิธีที่หลายคนรู้อยู่แล้ว(มั๊ง)

    ในส่วนนี้ผมจะนำเสนออีกวิธีนึง นั่นก็คือการแก้ให้แปลงพิมพ์เล็กเพิมพ์ใหญ่ในเงื่อนไขของการเปรียบเทียบของสูตรไปเลย เช่น

    Filter ปกติจะได้สูตรนี้

    = Table.SelectRows(Source, each ([Data] = "aa"))

    ถ้าเราต้องการ aa แบบไม่สนพิมพ์เล็กพิมพ์ใหญ่ เราสามารถสั่งให้ lowercase ในสูตรไปเลยก็ได้เป็นดังนี้

    = Table.SelectRows(Source, each (Text.Lower([Data]) = "aa"))
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 98

    ซึ่งมีข้อดีกว่าเดิมคือเราไม่สูญเสียความพิมพ์เล็กพิมพ์ใหญ่ของข้อมูลนั้นๆ ไปนั่นเอง

    เทคนิคการ Remove Duplicates ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่

    ปกติแล้ว Remove Duplicates ใน Power Query จะสนใจพิมพ์เล็กพิมพ์ใหญ่ด้วย เช่น แบบนี้

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 99

    เราจะแก้ไขสูตร M Code เล็กน้อย จากแบบนี้

    = Table.Distinct(MyData, {"Name"})

    เป็นแบบนี้ นั่นคือเพิ่มเงื่อนไขใน equationCriteria ให้เป็น Comparer.OrdinalIgnoreCase มันจะไม่สนเรื่องพิมพ์เล็กพิมพ์ใหญ่ทันที

    = Table.Distinct(MyData, {"Name",Comparer.OrdinalIgnoreCase})
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 100

    เทคนิคการ Split ข้อมูลโดยใช้ Delimiter หลายแบบ

    เทคนิคต่อไปเป็นการทำให้การ Split ของเราเจ๋งยิ่งขึ้น จากเดิมที่ Split ได้ด้วย Delimiter แค่ตัวเดียว คราวนี้เราจะสามารถ Split ด้วยทีละหลายๆ Delimiter ได้ด้วยการแก้ M Code เพียงเล็กน้อยเท่านั้น และใช้ได้กับการ Split เป็นหลายคอลัมน์หรือหลาย Row ก็ได้ เช่น

    ข้อมูลผมอยากจะแบ่งด้วย , ; | คำว่า “และ”

    ตอนแรกให้กด Split ตามปกติไปก่อน

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 101

    แล้วจะได้สูตรประมาณนี้

    = Table.SplitColumn(Source, "ผู้เล่น", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Team.1", "Team.2"})

    ถ้าสูตรมี {“Team.1”, “Team.2”} แปลว่ามันจะ Split ออกมาแค่ 2 คอลัมน์เท่านั้น ถ้าเราไม่ต้องการให้จำกัดแค่ 2 คอลัมน์ ก็ให้ลบ {“Team.1”, “Team.2”} (รวมถึง comma ข้างหน้า) ออกไปเลย เป็นแบบนี้ มันจะ Dynamic ตามจำนวนข้อมูลได้

    = Table.SplitColumn(Source, "ผู้เล่น", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv))

    จากนั้นลบ Changed Type ทิ้งไป เพราะเรายังทำไม่เสร็จ

    ทีนี้มาถึง Step สำคัญ คือ ให้แก้ฟังก์ชันจาก

    SplitTextByDelimiter(",", QuoteStyle.Csv)

    เป็นแบบนี้ โดยใส่ Delimiter เป็น List ของ Delimiter mี่ต้องการได้เลย

    SplitTextByAnyDelimiter({"delimiter1","delimiter2","delimiter3"}, QuoteStyle.Csv)

    เช่นใน Case นี้ใส่ได้ดังนี้

    SplitTextByAnyDelimiter({",",";","|","และ"}, QuoteStyle.Csv)

    สรุปแล้วสูตรเราจะกลายเป็นแบบนี้

    = Table.SplitColumn(Source, "ผู้เล่น", Splitter.SplitTextByAnyDelimiter({",",";","|","และ"}, QuoteStyle.Csv))

    และเราก็จะได้ผลลัพธ์ตามต้องการ

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 102

    ซึ่งเราก็นำไปใช้กับกรณี Split into Rows ได้เช่นกัน

    จาก Code ที่มัน Gen มาให้แบบนี้ (ซึ่งซับซ้อน ช่างมันไปก่อน) เราจะสนใจแค่การแก้ตรง SplitTextByDelimiter เหมือนเดิม

    = Table.ExpandListColumn(Table.TransformColumns(Source, {{"ผู้เล่น", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ผู้เล่น")

    สรุปแล้วให้ แก้ Code กลายเป็นแบบนี้

    = Table.ExpandListColumn(Table.TransformColumns(Source, {{"ผู้เล่น", Splitter.SplitTextByAnyDelimiter({",",";","|","และ"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ผู้เล่น")
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 103

    เทคนิค Group by แล้วรวมข้อความที่เป็น Text คั่นด้วย Delimiter

    สมมติเรามีข้อมูลดังนี้

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 104

    เราจะ Group by ตามผู้ขาย แต่อยากได้รายชื่อสินค้าเอามารวมแล้วคั่นด้วยเครื่องหมาย / จะทำยังไง?

    เราสามารถใช้การ Group ที่เรียกว่า All Rows มาช่วยได้ เช่น

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 105

    จากนั้นจะได้ผลลัพธ์แบบนี้

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 106

    concept คือเหมือนเดิม ว่า _ คืออ้างอิงตารางทั้งตารางที่ผ่านการ Group มาแล้ว ดังนั้นผมสามารถแก้สูตรให้ย่อๆ เหลือแค่นี้ก็ได้ ซึ่งก็จะได้ผลลัพธ์เหมือนเดิมเลย

    = Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each _})
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 107

    จากนั้นผมจะอ้างอิงไปที่คอลัมน์สินค้า ซึ่งทำได้โดยการเขียนว่า _[ชื่อคอลัมน์] เช่น

    = Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each _[สินค้า]})

    ผลลัพธ์จะกลายเป็น List

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 108

    หากต้องการทำให้ได้ List แบบไม่ซ้ำกัน ก็ใช้ List.Distinct มาช่วย

    = Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each List.Distinct(_[สินค้า])})
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 109

    พอเป็นแบบนี้เราจะสามารถกด Expand Column แล้วเลือก Extract Values ได้เลย ซึ่งมันจะเรียกใช้ฟังก์ชัน Text.Combine เพื่อรวมข้อมูลให้

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 110

    ผลลัพธ์จะกลายเป็นแบบนี้

    = Table.TransformColumns(#"Grouped Rows", {"all", each Text.Combine(List.Transform(_, Text.From), "/"), type text})
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 111

    เทคนิคการ Group by แล้วนับค่าในคอลัมน์แบบไม่ซ้ำ

    จาก Data ในเทคนิคที่แล้ว หากเราจะสั่ง Group by ผู้ขาย และจะนับจำนวนลูกค้าแบบไม่ซ้ำกัน เราจะพบว่าไม่สามารถใช้ Count Distinct Rows ใน Group by เพื่อเลือกคอลัมน์ลูกค้าได้สาเหตุเป็นเพราะ Count Distinct Rows ใน Group by เอาไว้นับจำนวนบรรทัดแบบไม่ซ้ำ (โดยดูทั้งบรรทัดเท่านั้น)

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 112

    แล้วถ้าเราจะนับแบบไม่ซ้ำ โดยนับเฉพาะคอลัมน์ลูกค้าจะทำยังไง?

    ทางแก้ก็ทำได้ 2 แนวทาง

    แนวทางอันแรกคือใช้ All Rows แล้วอ้างอิงไปที่คอลัมน์ที่ต้องการนับ เช่น ลูกค้า แล้วใช้ List.Distinct ครอบแบบตัวอย่างที่แล้วเพื่อให้ได้รายการแบบไม่ซ้ำ แต่ตอนจบให้ครอบด้วย List.Count เพื่อนับจำนวน เช่น

    = Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each List.Count(List.Distinct(_[ลูกค้า]))})

    อีกแนวทางคือ เราจะเริ่มจากการใช้ Count Distinct Rows แบบผิดๆ ตาม UI ไปก่อน แล้วจะไปแก้สูตรอีกที ดังนั้น ok ไปเลย จะได้แบบนี้

    = Table.Group(#"Changed Type", {"ผู้ขาย"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 113

    ตรงนี้แปลว่าให้นับจำนวนแถว ของตารางที่ถูกทำให้ Distinct ซึ่ง _ คือตารางภายใต้ group ของผู้ขายทั้งตาราง (เหมือนกับตัวอย่างที่แล้ว)

    Table.RowCount(Table.Distinct(_)) 

    เราจะต้องแก้ให้มันมอง item ในคอลัมน์ลูกค้าของตารางนั้นแบบไม่ซ้ำ จึงต้องแก้สูตรเป็น List.Distinct แล้วอ้างอิงไปที่คอลัมน์ลูกค้า โดยใช้ _[ชื่อคอลัมน์] ดังนี้

    Table.RowCount(List.Distinct(_[ลูกค้า]))

    สรุปแล้วแก้สูตรเป็นดังนี้ก็จะใช้ได้แล้ว

    = Table.Group(#"Changed Type", {"ผู้ขาย"}, {{"Count", each Table.RowCount(List.Distinct(_[ลูกค้า])), Int64.Type}})
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 114

    เทคนิคการ Trim แบบให้ทำให้ช่องว่างตรงกลางเหลือแค่ 1 เคาะ

    หลายคนอาจไม่ได้สังเกต ว่า Trim ใน Power Query นั้น จะตัดข้อมูลได้แค่ด้านหน้ากับด้านหลังเท่านั้น ไม่สามารถจะเอาข้อมูลช่องว่างตรงกลางออกไปได้ (ต่างจาก TRIM ใน Excel)

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 115

    ดังนั้นถ้าอยากให้ใน Power Query มัน Trim ได้แบบเดียวกับใน Excel จะต้องมีการแก้ Code ดังนี้

    ให้ใส่ Custom Column แล้วใส่สูตรดังนี้

    Text.Combine(List.Select(Text.Split([Original]," "),each _<>"")

    หลักการคือ

    • Text.Split จะแยกข้อมูลออกจากกันด้วย Delimiter ที่กำหนด คือ ” “
    • List.Select ใช้เพื่อคัดเลือกเอาข้อมูลใน List ให้เหลือเฉพาะตัวที่ไม่ใช่ช่องว่าง (ตรง each _<>”” โดยที่ _ คือข้อมูลใน List แต่ละตัว)
    • Text.Combine ใช้เพื่อรวมข้อมูลใน List เข้าเป็นข้อความเดียวกัน คั่นด้วย Delimiter ที่กำหนด (แบบในตัวอย่างเรื่อง Group By)
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 116

    เทคนิคการแก้ชื่อคอลัมน์แบบอ้างอิงตำแหน่งแทนชื่อ

    สมมติว่าเรามีข้อมูลอยู่ แล้วต้องการจะแก้ชื่อคอลัมน์ใน Power Query

    ปกติแล้วเราจะกด Double Click เพื่อแก้ชื่อไปเลย เช่น ผมแก้คอลัมน์แรก เป็น BillNumber

    = Table.RenameColumns(Source,{{"TXID", "BillNumber"}})
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 117

    สูตรมันจะบันทุกว่าให้เปลี่ยนชื่อคอลัมน์จาก TXID เป็น BillNumber

    ซึ่งก็ดีนะ แต่จะมีปัญหาทันที ถ้าชื่อคอลัมน์ในต้นฉบับเปลี่ยนไปเป็นอันอื่นที่ไม่ใช่ TXID

    หากเรามั่นใจว่าจะเปลี่ยนชื่อคอลัมน์แรกเสมอ (ให้เป็น TXID) ก็สามารถใช้ฟังก์ชัน Table.ColumnNames มาช่วยได้ ซึ่งมันจะได้ชื่อคอลัมน์ทั้งหมดในตารางที่อ้างอิงออกมาเป็น List เช่น ถ้าอยากได้ชื่อคอลัมน์ของตาราง Source ก็ทำแบบนี้

    = Table.ColumnNames(Source)
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 118

    ถ้าเราจะอ้างอิงไปที่ item แรกใน List สามารถอ้างอิงด้วยเลข index ในรูปแบบ ชื่อlist{index} โดย index เริ่มที่เลข 0

    แปลว่า item แรกคือ

    = Table.ColumnNames(Source){0}
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 119

    ดังนั้นเอาไปแทนในสูตรเดิม = Table.RenameColumns(Source,{{“TXID”, “BillNumber”}}) จะได้แบบนี้ ซึ่งจะเปลี่ยนจากการ Hard Code คำว่า TXID ลงไปในสูตร ให้กลายเป็นไปหาชื่อคอลัมน์แรกสุดมาแทนนั่นเอง

    = Table.RenameColumns(Source,{{Table.ColumnNames(Source){0}, "BillNumber"}})
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 120

    เทคนิครวมข้อมูลทุก Sheet ใน Excel (ข้อมูลไม่เป็น Table)

    การรวมข้อมูลใน Excel หลายๆ ตารางเข้าด้วยกัน หากว่าข้อมูลแต่ละตารางเป็น Table อยู่แล้วจะรวมได้ง่ายมาก ใน Navigation แค่อ้างอิงไปที่ตัวไฟล์ Excel แล้ว Filter ให้เหลือเฉพาะ Table ที่ต้องการแล้วกด Expand Table ออกมาก็จบเลย รายละเอียดลองดูในคลิปนี้

    แต่ถ้าข้อมูลแต่ละตารางไม่ใช่ Table มันจะรวมตรงๆ ไม่ได้ เพราะ Data แต่ละอันยังไม่ได้รับการ Promote หัวตาราง จึงยังค้างเป็นคำว่า Column1, Column2, Column3…

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 121

    ซึ่งพอสั่ง Expand ออกมาแล้วเอาข้อมูลแต่ละตารางมา Append กัน จะทำให้มีปัญหาหัวตารางกลายเป็น Data ซ้ำไปซ้ำมา ซึ่งต้องมานั่ง Filter ทิ้งอีก (และถ้าแต่ละตารางเรียงคอลัมน์ไม่เหมือนกันก็จะมีปัญหาทันที)

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 122

    วิธีแก้ง่ายๆ คือ ก่อนจะถึงขั้นตอน Expand ในขั้นตอนแรกสุดที่ใช้ฟังก์ชัน Excel.Workbook อ่านค่าในไฟล์ Excel ให้แก้ input ที่ชื่อว่า useHeaders จาก null เป็น true ซะ เช่น

    = Excel.Workbook(File.Contents("D:\ThepExcel\Fin\Pandora\B\PowerQuery V2\PQ Data Support\MonthlySales2020-sheets-Easy.xlsx"), null, true)

    เป็น

    = Excel.Workbook(File.Contents("D:\ThepExcel\Fin\Pandora\B\PowerQuery V2\PQ Data Support\MonthlySales2020-sheets-Easy.xlsx"), true, true)
    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 123

    เพียงเท่านี้ มันจะทำการ Promote Header แต่ละตารางก่อนแล้ว

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 124

    ทำให้เวลา Expand ออกมาไม่มีปัญหาแม้ว่าจะเป็น Sheet ครับ

    เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ 125

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

    = Table.ExpandTableColumn(#"Removed Other Columns", "Data", List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_))))

    จบแล้ว

    สำหรับเทคนิค Power Query ในบทความนี้ก็ขอจบเพียงเท่านี้ ใครอยากรู้เทคนิคในการทำอะไรอีกก็สามารถบอกได้เลยนะครับ

    ใครที่สนใจเรียนรู้เรื่อง Power Query ผมมีเรื่องนี้ด้วยครับ

    Excel Power Up 2021 : พลังแห่งข้อมูล สร้างได้ด้วย Power Query

  • Lookup ข้อมูลที่มี Comma ด้วย Power Query

    Lookup ข้อมูลที่มี Comma ด้วย Power Query

    คลิปนี้ผมจะมาสอนวิธี Lookup ข้อมูลโดยที่ตารางหลักดันมีเครื่องหมาย Comma ตั่นแต่ละ Item อยู่ ซึ่งจะเห็นว่าจะ Lookup ตรงๆ ไม่ได้เนอะ แต่เราจะใช้ #PowerQuery ช่วยทำได้ครับ

    หลักการในการทำ คือ

    1. ทำการ Split by Delimiter ข้อมูลออกมาเป็นหลายๆ แถว (Split into Rows)
    2. Merge ข้อมูลที่ต้องการมาจากอีกตาราง
    3. Group By ข้อมูลกลับมาแบบ All Rows
    4. เขียนสูตร Table.Column เพื่อดึงเอาเฉพาะคอลัมน์ที่ต้องการออกมาเป็น List
    5. Extract Values หรือ Text.Combine เพื่อรวม List เข้าด้วยกันเป้นช่องเดียวให้คั่นด้วย Comma
  • คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4:  เทคนิค M Code ที่ใช้บ่อย

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย

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

    เทคนิค M Code #1: อยากเก็บ Text ไว้เฉพาะอักขระที่กำหนด

    วิธีนึงที่ใช้ได้ดีคือใช้ฟังก์ชัน Text.Select มาช่วย ซึ่งมีวิธีการใช้งานดังนี้

    Text.Select(text as nullable text, selectChars as any) as nullable text
    • text คือ ข้อความ Original
    • selectChars สามารถใส่เป็น text ธรรมดาได้ แต่ปกติเราจะใส่เป็น List ที่มีอักขระที่อยากจะเอาไว้ซะมากกว่า

    ยกตัวอย่างเช่น ข้อความของผมเป็นรายชื่อหนังดังนี้ แล้วผมใช้ Text.Select ให้เก็บเฉพาะตัว “a” ไว้

    ผมสามารถกด Add Custom Column แล้วใส่สูตรดังนี้

    =Text.Select([Movie Name],"a")
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 126

    อย่าลืมว่าทุกอย่างใน Power Query เป็น Case-Sensitive ทั้งหมด ถ้าอยากได้ “A” ด้วย ผมสามารถใส่เป็น List ได้ดังนี้

    =Text.Select([Movie Name],{"a","A"})
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 127

    หรือจะใช้วิธีแปลงข้อมูลให้เป็น Lower Case ก่อนส่งค่าไปให้กับ Text.Select ทำงานต่อก็ได้ เช่น

    Text.Select(Text.Lower([Movie Name]),{"a"})
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 128

    ถ้าเราอยากให้เหลือเฉพาะตัวอักษร a-z หรือ A-Z หรือ space ก็สามารถทำแบบนี้ได้

    = Text.Select([Movie Name],{"a".."z"}&{"A".."Z"}&{" "})
    เทคนิค M Code ที่ใช้บ่อย

    แต่ถ้าจะเอาตัวเลขด้วยเราเขียนตรงๆ ด้วยการเพิ่ม {0..9}ไม่ได้ มันจะขึ้น Error มาเตือนว่า Expression.Error: We cannot convert the value 0 to type Text. (เพราะเราจะเชื่อมด้วย & ซึ่งต้องเป็น Text)

    งั้นเราก็ใส่ {“0”..”9″} แทนก็ได้ 555

    = Text.Select([Movie Name],{"a".."z"}&{"A".."Z"}&{" "}&{"0".."9"})

    ดังนั้นจะเห็นว่าอักขระนอกเหนือจากที่เรากำหนด มันจะหายไปหมดเลย

    สรุป M Code

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rank", Int64.Type}, {"Movie Name", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Movie Name],{"a".."z"}&{"A".."Z"}&{" "}&{"0".."9"}))
    in
        #"Added Custom"

    เทคนิค M Code #2: กำจัดช่องว่างส่วนเกินระหว่างคำ

    จะเห็นว่าการเปลี่ยน Star Wars: Episode VII – The Force Awakens ไปเป็น Star Wars: Episode VII The Force Awakens นั้น ระหว่าง VII กับ The มี space 2 ที ซึ่งมันอาจจะมี Space มากกว่านี้ในคำอื่นอีกก็ได้ ดังนั้นเราจะ replace คำธรรมดาๆ ไม่ได้ และเราก็ตัด space ทั้งหมดทิ้งไม่ได้ด้วยเช่นกัน

    หลายๆ คนอาจจะนึกถึงฟังก์ชัน Trim แต่สำหรับใน Power Query แล้ว Trim จะตัดแค่ช่องว่างหัวท้ายเท่านั้น ไม่ตัดตรงกลางเหมือนกับ TRIM ใน Excel

    ดังนั้นก็ถึงเวลาประยุกต์ใช้ความรู้หลายๆ อันมารวมกันแล้ว นั่นคือ

    1. เราจะ Split ข้อความออกจากกันด้วย Space ให้ออกมาเป็น List
    2. เราจะกำจัด item ที่เป็นช่องว่างออก
    3. เราจะ Combine item กลับมารวมกันใหม่ด้วย delimiter ที่เป็นช่องว่างอีกที

    ขั้นตอนที่ 1: Split ข้อความออกจากกันด้วย Space ให้ออกมาเป็น List ด้วย Text.Split

    Text.Split( [Custom] ," ")
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 129

    ขั้นตอนที่ 2 : กำจัด item ที่เป็นช่องว่างออก โดยใช้ List.Select มาช่วย

    List.Select(  Text.Split([Custom]," ")  , each _ <>"")
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 130

    ขั้นตอนที่ 3 : Combine item กลับมารวมกันใหม่ด้วย delimiter ที่เป็นช่องว่าง ด้วย Text.Combine

    Text.Combine( List.Select(Text.Split([Custom]," "), each _ <>"") ," ")
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 131

    เท่านี้เราก็จะได้ผลลัพธ์เหมือนกันใช้ TRIM ใน Excel แล้ว

    ซึ่งแน่นอนว่าเราสามารถเอาไปสร้างเป็นฟังก์ชันไว้ใช้ได้ด้วย เผื่อใช้ในที่อื่นอีก

    เช่น สร้าง Blank Query แล้วใส่ Code นี้ลงไป จากนั้นตั้งชื่อว่า CustomTrimFX

    (txt as text) as text =>
    Text.Combine( List.Select(Text.Split(txt," "), each _ <>"") ," ")

    คราวนี้เราก็จะเรียกใช้ได้ง่ายๆ เลย เช่น เอาไปใช้ผ่าน Invoke Custom Function หรือ จะเรียกใน Custom Column หรือ จะพิมพ์สูตรเองก็ยังได้

    = Table.AddColumn(#"Added Custom", "CustomTrim", each CustomTrimFX([Custom]) )

    ก็จะได้ผลลัพธ์เหมือนกับที่ทำข้างบนทั้งหมด

    สรุป M Code

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rank", Int64.Type}, {"Movie Name", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Movie Name],{"a".."z"}&{"A".."Z"}&{" "}&{"0".."9"})),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "CustomTrim", each  CustomTrimFX([Custom]) )
    in
        #"Added Custom1"

    เทคนิค M Code #3: ทำให้ Table เหลือ Column เฉพาะที่มีข้อความตรงกับเงื่อนไขที่กำหนด

    สมมติผมมี Data ที่มีหัวตารางเป็น Actual กับ Target แต่ละเดือนแบบนี้

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 132

    เราต้องการให้เหลือแค่คอลัมน์ที่เป็น Actual เท่านั้น ซึ่งถ้านั่งเลือกเองก็จะยุ่งยาก และไม่ Dynamic (เผื่อมีอีกหลายตารางที่ต้องทำแบบนี้)

    คัดเลือกคอลัมน์ที่ต้องการด้วย Table.SelectColumns

    ดังนั้นเราจะใช้ Concept ของ List มาช่วย โดยเราสามารถคัดเลือกคอลัมน์ที่ต้องการด้วยวิธีแบบนี้ได้

    Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as table

    เช่นถ้าจะเอาให้เหลือแค่ Actual-202001 กับ Actual-202002 ก็สามารถเขียนได้ว่า

    =Table.SelectColumns(Source, {"Actual-202001","Actual-202002"} )

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

    สร้าง List ชื่อคอลัมน์ที่ต้องการ

    ซึ่งเราสามารถทำได้โดยการใช้ Table.ColumnNames ดึงหัวตารางออกมา เช่น

    Table.ColumnNames(table as table) as list
    =Table.ColumnNames(Source)

    แล้วเอา List.FindText มาช่วยคัดกรอง เช่น ตั้งชื่อตัวแปร MyHeader ด้วยสูตรนี้

    MyHeader= List.FindText(Table.ColumnNames(Source),"Actual")

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

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 133

    หรือจะเจาะจงว่าชื่อคอลัมน์ต้องขึ้นต้นด้วย Actual ก็ต้องใช้ List.Select มาช่วย คู่กับ Text.StartsWith แบบนี้

    MyHeader= List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Actual") )

    พอเราได้ MyHeader แล้วก็เอาไปใส่ใน Table.SelectColumns ได้

    =Table.SelectColumns(Source, MyHeader )

    แต่ถ้าทำแค่นี้มันก็จะไม่มีคอลัมน์ Product ออกมาด้วย งั้นอย่าลืมใส่เพิ่มลงไป เช่น

    =Table.SelectColumns(Source, {"Product"} & MyHeader )

    หรือจะให้เอาคอลัมน์แรกมาแบบจะเป็นชื่ออะไรก็ได้

    ก็ใช้ Table.ColumnNames(Source){0} เพื่อเอา item แรกมา

    สรุปก็เลยเขียนแบบนี้แทน

    = Table.SelectColumns(Source, {Table.ColumnNames(Source){0}} & MyHeader )

    สรุป M Code

    let
        Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
        MyHeader = List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Actual") ),
        Result = Table.SelectColumns(Source, {Table.ColumnNames(Source){0}} & MyHeader )
    in
        Result

    เทคนิค M Code #4: แก้ Data Type ของแต่ละคอลัมน์แบบ Dynamic

    หากเราลองเลือก 2 คอลัมน์แรกของตาราง แล้วกด Detect Data Type ดู จะเห็น Code แบบนี้

    = Table.TransformColumnTypes(Result,{{"Productxxx", type text}, {"Actual-202001", Int64.Type}})

    แสดงว่าเราสามารถใช้ฟังก์ชัน Table.TransformColumnTypes มาช่วยจัดการประเภทข้อมูลในตารางได้

    Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table

    จากการเทียบ code แสดงว่า เจ้า typeTransformations เนี่ย จะต้องใส่เป็น List แบบเดียวกับที่ผมทำสีแดงไว้ นั่นคือเป็น List ซ้อนอยู่ใน List อีกที โดยที่ List ข้างใน เป็น List ของแต่ละคอลัมน์ที่ต้องการ Transform คู่กับ type ที่ต้องการจะแปลง

    {  {"Productxxx", type text}  ,  {"Actual-202001", Int64.Type}  }

    วิธีที่ 1 : ใช้ List.Zip มาช่วย

    ซึ่งวิธีนึงที่ทำได้และเราได้เรียนรู้ฟังก์ชันนั้นไปแล้วในบทที่แล้วก็คือ ใช้ List.Zip มาช่วยนั่นเอง

    List.Zip(lists as list) as list
     
    ซึ่งในที่นี้ lists ต้องใส่เป็น {     ListColumnName , ListDataType      }  เพื่อให้เหมือนกับสิ่งที่อยากได้ข้างบน

    เรามาสร้างทีละตัวละกัน

    ListColumnName={Table.ColumnNames(Source){0}} & MyHeader
    // ตัวนี้ง่ายๆ ตรงไปตรงมาเนอะ
    ListDataType = {type text} & List.Repeat( {Int64.Type}, List.Count(ListColumnName)-1 )
    // ตัวนี้บังคับให้คอลัมน์แรกเป็น text เสมอ นอกนั้นให้สร้าง List ของ Int64.Type ขึ้นมาเท่ากับจำนวนคอลัมน์ที่เหลือ ด้วยการ Repeat item ใน List เป็นจำนวน ListColumnName -1 ครั้ง

    ซึ่งพอมาใช้ใน List.Zip จะเป็นแบบนี้

    List.Zip({ ListColumnName,ListDataType } )

    เอาไปใส่ใน Table.TransformColumnTypes อีกที

    = Table.TransformColumnTypes(Result, List.Zip({ ListColumnName,ListDataType })  )

    สรุป M Code

    let
        Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
        MyHeader = List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Actual") ),
        Result = Table.SelectColumns(Source, {Table.ColumnNames(Source){0}} & MyHeader ),
        ListColumnName={Table.ColumnNames(Source){0}} & MyHeader,
        
        // ตัวนี้บังคับให้คอลัมน์แรกเป็น text เสมอ นอกนั้นให้สร้าง List ของ Int64.Type ขึ้นมาเท่ากับจำนวน ListColumnName -1
        ListDataType = {type text} & List.Repeat( {Int64.Type}, List.Count(ListColumnName)-1 ),
        FinalResult= Table.TransformColumnTypes(Result,List.Zip({ ListColumnName,ListDataType } ))
    in
        FinalResult
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 134

    วิธีที่ 2 : ใช้ List.Transform มาช่วย

    ส่วนของ typeTransformations นั้น นอกจากใช้ List.Zip มาช่วยแล้ว ยังใช้อีก List.Transform มาช่วยได้ด้วย ซึ่งตัว List.Transform นั้นจริงๆ เป็นตัวที่ยืดหยุ่นสุดๆ เลย เพราะ transform สามารถใส่เป็นฟังก์ชันได้ ดังนี้ (เรายังใช้ ListColumnName มาเก็บชื่อคอลัมน์อยู่นะ)

    List.Transform(list as list, transform as function) as list

    ถ้าจะบังคับให้ทุกคอลัมน์เป็น text จะง่ายสุดเลยกับวิธีนี้

    List.Transform(ListColumnName, each {_, type text} )
    // แปลว่า ให้แปลง list แต่ละตัวเป็น list ที่คู่กับคำว่า type text โดยเอา item ไปใส่แทนคำว่า _

    จากนั้นเราเอาไปใส่ใน Table.TransformColumnTypes ดังนี้

    Table.TransformColumnTypes(Result, List.Transform(ListColumnName, each {_, type text} ) )

    ถ้าจะบังคับคอลัมน์ที่อยู่ใน MyHeader เป็น Int64.Type นอกนั้นเป็น Text

    List คอลัมน์ที่ตรงกับ MyHeader นั้นก็คือ MyHeader ได้เลย อันนี้ไม่ยาก…

    ส่วน List ที่ไม่ตรงกับ MyHeader อันนี้เราใช้ List.Difference มาช่วยได้ เช่น

    List.Difference(list1 as list, list2 as list, optional equationCriteria as any) as list

    เช่น

    NotActualHeader = List.Difference(ListColumnName, MyHeader)

    จากนั้นเราก็เรียกใช้ Table.TransformColumnTypes 2 รอบ สำหรับทั้ง 2 กรณี ก็จบแล้ว

    สรุป M Code

    let
        Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
        MyHeader = List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Actual") ),
        Result = Table.SelectColumns(Source, {Table.ColumnNames(Source){0}} & MyHeader ),
        ListColumnName={Table.ColumnNames(Source){0}} & MyHeader,
        //list ตัวที่หัวตารางที่ไม่ได้ขึ้นต้นด้วยคำว่า actual
        NotActualHeader = List.Difference(ListColumnName, MyHeader),
        //transform หัวตารางที่ไม่ได้ขึ้นต้นด้วยคำว่า actual เป็น text
        Header1= Table.TransformColumnTypes(Result,List.Transform(NotActualHeader, each {_, type text} ) ),
        //transform หัวตารางที่ขึ้นต้นด้วยคำว่า actual เป็น Int64.Type
        Header2= Table.TransformColumnTypes(Header1,List.Transform(MyHeader, each {_, Int64.Type} ) )
    in
        Header2

    หรือจะใช้ if … then … else … มาช่วยเขียนรวบไปเลยแบบนี้ก็ยังได้

    let
        Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
        MyHeader = List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Actual") ),
        Result = Table.SelectColumns(Source, {Table.ColumnNames(Source){0}} & MyHeader ),
        ListColumnName={Table.ColumnNames(Source){0}} & MyHeader,
        //transform หัวตารางที่ขึ้นต้นด้วยคำว่า actual เป็น Int64.Type นอกนั้นเป็น Text ด้วย if...then...else
        Header= Table.TransformColumnTypes(Result,
            List.Transform(ListColumnName, each 
                {_, if Text.StartsWith(_,"Actual") then Int64.Type else type text} ) )
    in
        Header

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

    นี่ต้นฉบับ

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 135

    นี่ผลลัพธ์

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย 136

    เทคนิค M Code #5: Expand คอลัมน์จากทุก Table ให้มารวมกันแบบ Dynamic

    จะ Expand แบบ Dynamic ได้ เราต้องสร้าง List ของคอลัมน์ที่ต้องการ Expand ให้ได้ เพื่อเอาไปใส่ใน Table.ExpandTableColumn

    = Table.ExpandTableColumn(#"Removed Other Columns", "Data", List ของชื่อคอลัมน์)

    หลักการคือ เราจะใช้สูตร List.Transform โดยใช้ Table.ColumnNames(_) กับทุกๆ Table เพื่อให้แต่ละ Table คืนค่ามาเป็น List ของหัวตารางใน Table นั้นๆ จากนั้นใช้ List.Union มารวม item ทุกตัวเข้าด้วยกัน ซึ่งสามารถเขียนสูตรได้ว่า

    List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_)))

    สรุปแล้วสูตรโดยรวมเป็นดังนี้

    = Table.ExpandTableColumn(#"Removed Other Columns", "Data", List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_)))   )

    เรื่องนี้ผมเคยเขียนบทความไว้โดยละเอียดแล้ว ลองไปอ่านได้ใน

    ตอนนี้เพื่อนๆ ก็น่าจะได้เทคนิคการใช้ M Code จัดการปัญหาที่น่าปวดหัวไปได้หลายอันแล้ว ยังไงก็ลองเอาไปใช้ดูได้นะครับ ใช้ได้ผลเป็นยังไงก็บอกกันบ้างล่ะ

    ตอนต่อไป

    ตอนต่อไปจะเป็นเรื่องวิธีการทำให้ Power Query มันรันเร็วขึ้น ขอตัวไปศึกษาใน blog ของผู้เชี่ยวชาญ Power Query อย่าง Chris Webb ก่อน นะครับ ใครอยากรู้วิธีทำอะไรด้วย Power Query สามารถ comment บอกได้เลยนะครับ ^^

    สารบัญซีรีส์ M Code

  • คัมภีร์สรุป M Code ใน Power Query ตอนที่ 3:  เจาะลึกเรื่องของ List

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 3: เจาะลึกเรื่องของ List

    ในตอนนี้เราจะมาเรียนรู้เรื่องเกี่ยวกับข้อมูลประเภท List กันให้ลึกซึ้งมากขึ้นครับ ซึ่งเดี๋ยวเพื่อนๆ จะได้เห็นว่า พอเราเริ่มเข้าใจการทำงานของฟังก์ชัน List ต่างๆ ในตอนนี้แล้ว ฟังก์ชันกลุ่มอื่นหลายๆ ตัวก็จะมีชื่อคล้ายๆ กันนี่แหละ ทำให้เราจะพอเดาการใช้งานได้ไปด้วย ยิงปืนนัดเดียวได้นกสองตัวเลย!

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

    List เป็นองค์ประกอบสำคัญในหลายๆ ฟังก์ชัน

    เวลาเราไปดูฟังก์ชันที่มีความสามารถเจ๋งๆ ทำเรื่องที่เราต้องการทีไร มักจะมี input หรือ output เป็น List ทุกทีสิน่า!

    เวลาใช้ฟังก์ชัน Text.Split ก็ได้ผลเป็น List

    Text.Split(text as text, separator as text) as list

    เวลาจะรวม Text หลายๆ อันเป็นก้อนเดียว ด้วย Text.Combine ก็ต้องใช้ List

    Text.Combine(texts as list, optional separator as nullable text) as text

    เวลาดึงข้อมูลหัวตารางจาก Table มาจัดการต่อ ก็ออกมาเป็น List

    Table.ColumnNames(table as table) as list

    การจะจัดการกับประเภทข้อมูลของหัวตาราง Table ก็ต้องใช้ List

    Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table
    

    การจะจัดการกับ Text ให้เหลือเฉพาะอักขระที่อยากได้ ก็ต้องใช้ List เป็นส่วนประกอบ

    Text.Select(text as nullable text, selectChars as any) as nullable text // selectChars สามารถใส่เป็น List ได้ว่าให้เก็บอักขระไหนเอาไว้บ้าง (ในชีวิตจริง ใครจะไปใส่ตัวเดียว)

    อยากจะ Filter ข้อมูลในคอลัมน์ของตาราง ให้เป็น Data เฉพาะรายการที่กำหนด

    แบบนี้ก็สามารถใช้ List.Contains มาช่วยเป็นเงื่อนไขในการ Filter ของ Table.SelectRowsได้ (เดี๋ยวมาดูตัวอย่างกัน) ดังนั้นจะเห็นว่า List นั้นเป็นอะไรที่มีประโยชน์มากๆ และน่าเรียนรู้สุดๆ

    ซึ่งเดี๋ยวเราจะมาเรียนเรื่องเหล่านี้กันในบทความต่อไป แต่ในตอนนี้เราจะมาปูพื้นฐานเรื่อง List กันให้แน่นขึ้นก่อน

    ทบทวนสิ่งสำคัญเกี่ยวกับ List

    มีหลายเรื่องที่เราได้เรียนรู้เกี่ยวกับ List ไปแล้ว เดี๋ยวผมจะทวนให้เร็วๆ พร้อมกับตัวอย่างที่เยอะขึ้น

    สร้าง List ต่อเนื่องด้วย .. (จุดจุด)

    {1..100} // สร้าง List เลข 1-100
    {"a".."z"} // สร้าง List a-z
    {"A".."Z"} // สร้าง List A-Z
    {"a".."e","x".."z"} // สร้าง List a-e ต่อด้วย x-z

    มีตัวที่น่าสนใจ คือ

    {"A".."z"} // สร้าง List A-z แต่ได้อักขระ 6 ตัวที่อยู่ระหว่าง Z กับ a มาด้วย ซึ่งตัวพิมพ์ใหญ่มาก่อนพิมพ์เล็กนะ
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 3: เจาะลึกเรื่องของ List 137

    ถ้าเป็นภาษาไทย ผมแนะนำให้สร้าง {“ก”..”๙”} ไปเลย ดีกว่า {“ก”..”ฮ”} ที่ขาดพวกสระไป ทำให้ดูไม่จืดเลยครับ
    // ใช้ ก – เลข 9 ไทย ไปเลย จะได้สระมาด้วยครบถ้วนครับ

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 3: เจาะลึกเรื่องของ List 138

    รวม List เข้าด้วยกันด้วย &

    = {"a".."d"}&{1..5}&{"Sira","Ekabut"}
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 3: เจาะลึกเรื่องของ List 139

    อ้างอิงข้อมูลใน List ด้วย ชื่อlist{index}

    สมมติผมสร้าง List ชื่อ SampleList ให้มีค่า {1,2,-5,8,4,2,”a”,”b”,”A”}

    SampleList{0} // จะได้เลข 1
    SampleList{1}  // จะได้เลข 2
    SampleList{0..2}  // แบบนี้มันไม่ยอมนะ เราต้องใช้ฟังก์ชันมาช่วยแทนครับ

    ตัวอย่างวิธีเขียน M Code ใน Advanced Editor ให้สามารถแก้ใน Formula Bar ได้ง่ายๆ

    let 
        SampleList =  {1,2,-5,8,4,2,"a","b","A"},
        Result=SampleList{0}
    in
        Result

    ถ้าทำแบบนี้เราจะสามารถไปแก้สูตรใน Formula Bar ได้เลย

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 3: เจาะลึกเรื่องของ List 140

    ฟังก์ชัน List ที่น่าจะใช้บ่อย

    นอกจากคำสั่งพื้นฐานเหล่านี้แล้ว Power Query ก็มีฟังก์ชันเกี่ยวกับ Listให้เลือกอีกมหาศาลเลย แต่ตัวที่ผมคิดว่าสำคัญมีดังนี้

    List.Count : นับจำนวน item ใน List

    List.Count(list as list) as number
    
    =List.Count(SampleList)  // ได้เลข 9  แปลว่ามี 9 item (ซึ่งมี index 0-8)
    =List.Count({1,2,-5,8,4,2,"a","b","A"})  // เดี๋ยวผมจะเขียนแบบ Hardcode ค่าแบบนี้เลย เพื่อนๆ จะได้ไม่ต้องไปหาว่า SampleList คืออะไรเนอะ

    List.Sort : เรียงลำดับข้อมูลใน List

    List.Sort(list as list, optional comparisonCriteria as any) as list
    
    //comparisonCriteria สามารถใส่เป็น Order.DescendingOrder.Ascending ได้ 
    //ถ้าไม่ใส่คือ ascending คือเรียงน้อยไปมาก
    
    = List.Sort({1,2,-5,8,4,2,"a","b","A"}) // ได้ {-5,1,2,2,4,8,"A","a","b"}
    = List.Sort({1,2,-5,8,4,2,"a","b","A"},Order.Descending) // ได้ {"b","a","A",8,4,2,2,1,-5}

    List.Max : เอาค่ามากสุด ใน List

    List.Max(list as list, optional default as any, optional comparisonCriteria as any, optional includeNulls as nullable logical) as any
    
    =List.Max({1,2,-5,8,4,2,"a","b","A"})   // ได้ "b" (ได้ผลเหมือนเอาตัวแรกสุดหลังจาก Sort มากไปน้อย)

    List.Transform : ดัดแปลงข้อมูลแต่ละ item ใน List

    List.Transform(list as list, transform as function) as list
    
    =List.Transform({1,2,-5,8,4,2} , each _ *10) // ได้ {10,20,-50,80,40,20}  โดยที่ _ แทน item แต่ละตัว
    =List.Transform({1,2,-5,8,4,2} , each Number.ToText(_)) // เปลี่ยนจากเลขเป็น text ได้ {"1","2","-5","8","4","2"}

    List.Distinct : กำจัดตัวซ้ำใน List ออกให้เหลือแต่ตัวไม่ซ้ำ

    List.Distinct(list as list, optional equationCriteria as any) as list
    
    = List.Distinct({1,2,-5,8,4,2,"a","b","A"}) // ได้ {1,2,-5,8,4,"a","b","A"}   เลข 2 ตัวที่อยู่อันหลังหายไป
    = List.Distinct({1,2,-5,8,4,2,"a","b","A"},Comparer.OrdinalIgnoreCase) // ปรับให้เป็น Non Case Sensitive ได้ {1,2,-5,8,4,"a","b"} 2 หลัง กับ A หายไป เพราะ A ถือว่าซ้ำกับ a ถ้า IgnoreCase แล้ว

    ฟังก์ชันที่ใช้คัดเลือกข้อมูลบางส่วนจาก List

    List.Select : คัดกรองให้เหลือเฉพาะ item ที่ตรงตามเงื่อนไข

    List.Select(list as list, selection as function) as list
    
    =List.Select({1,2,-5,8,4,2}, each _ > 3)   // ได้ {8,4} เพราะว่า _ แทน item แต่ละตัว 

    List.FindText : คัดมาเฉพาะ item ที่ contain หรือมี text ที่ต้องการอยู่

    List.FindText(list as list, text as text) as list
    
    = List.FindText({"batman","superman","thor","ironman"},"man")   // ได้ {"batman","superman","ironman"}

    List.Intersect : เลือกเฉพาะส่วนที่ซ้ำกันในทุก List ที่ระบุ

    List.Intersect(lists as list, optional equationCriteria as any) as list
    // lists ต้องใส่ List ใน List อีกชั้นนึง
    
    =List.Intersect({ {1,2,-5,8,4,2,"a","b","A"} , {1..9}   })   // ได้ {1,2,8,4}
    =List.Intersect({ {1,2,-5,8,4,2,"a","b","A"} , {1..9},{3..15}  }) // ได้ {8,4}
    =List.Intersect({ {1,2,-5,8,4,2,"a","b","A"} , {"a".."z"} }) // ได้ {"a","b"}
    =List.Intersect({ {1,2,-5,8,4,2,"a","b","A"} , {"A".."Z"} }) // ได้ {"A"}
    =List.Intersect({ {1,2,-5,8,4,2,"a","b","A"} , {"A".."Z"} },Comparer.OrdinalIgnoreCase) // ได้ {"a","b"}

    List.Union : เอา List มารวมกัน (ให้ผลเหมือน list1 รวม list2 ที่หัก List.Intersect(list1,list2) )

    List.Union(lists as list, optional equationCriteria as any) as list
    // lists ต้องใส่ List ใน List อีกชั้นนึง
    
    =List.Union({ {1,2,-5,8,4,2,"a","b","A"} , {1..9} })   // ได้ {1,2,-5,8,4,2,"a","b","A",3,5,6,7,9}

    List.Difference : เลือกเอาข้อมูลใน list1 ตั้งแล้วลบข้อมูลจากใน list2 ออกไป (ตัวซ้ำอาจยังอยู่)

    List.Difference(list1 as list, list2 as list, optional equationCriteria as any) as list
    
    = List.Difference({1,2,-5,8,4,2,"a","b","A"} , {1..9})      // ได้ { -5, 2,"a","b","A" }  ซึ่งมี 2 โผล่มาเพราะมีซ้ำ
    = List.Difference({1,2,-5,8,4,"a","b","A"} , {1..9})     // ได้ { -5, "a","b","A" }
    = List.Difference({1,2,-5,8,4,2,"a","b","A"} , {1..9,2,2,2,2})     // ได้ { -5, "a","b","A" }

    List.Range : ดึงข้อมูล จาก List ด้วยลำดับ item ที่ต้องการ

    List.Range(list as list, offset as number, optional count as nullable number) as list
    
    List.Range(ชื่อlist, index เริ่มต้น, จะเอากี่ตัว ถ้าไม่ใส่คือเอาหมด) as list
    =List.Range({1,2,-5,8,4,2,"a","b","A"} , 2, 3)    // จะได้ {-5,8,4}  เพราะ -5 คือ index ที่ 2

    List.ReplaceRange : แทนที่ข้อมูลจาก List ในลำดับ item ที่ต้องการ ด้วยข้อมูลอีก List นึง

    List.ReplaceRange(list as list, index as number, count as number, replaceWith as list) as list
    
    =List.ReplaceRange({1,2,-5,8,4,2,"a","b","A"} , 2, 3,{9,8,7,6}) // จะได้ {1,2,9,8,7,6,2,"a","b","A"}
    =List.ReplaceRange({1,2,-5,8,4,2,"a","b","A"}, 2, 3,{}) // จะได้ {1,2,2,"a","b","A"} ซึ่ง {-5,8,4} ถูกแทนด้วยว่าง
    =List.ReplaceRange({1,2,-5,8,4,2,"a","b","A"}, 6, 99,{}) // จะ error เพราะใส่เผื่อมากไปไม่ได้
    = List.ReplaceRange({1,2,-5,8,4,2,"a","b","A"}, 6, List.Count(SampleList)-6,{}) // จะได้ {1,2,-5,8,4,2}

    ฟังก์ชันที่เอาไว้ตรวจสอบข้อมูลใน List

    List.PositionOfAny : หาตำแหน่งของข้อมูลที่ต้องการ ว่าอยู่เป็น item ที่เท่าไหร่ใน List (เอาตัวแรกที่เจอ)

    List.PositionOfAny(list as list, values as list, optional occurrence as nullable number, optional equationCriteria as any) as any
    
    =List.PositionOfAny({1,2,-5,8,4,2,"a","b","A"},{99,8,"b"})  //ได้ 3 เพราะเจอ 8 ที่ index 3

    List.ContainsAny : ใช้เช็คว่าใน List มีข้อมูล value (list) ที่กำหนดอยู่หรือไม่ โดยให้ผลเป็น true/false

    List.ContainsAny(list as list, values as list, optional equationCriteria as any) as logical
    
    =List.ContainsAny({1,2,-5,8,4,2,"a","b","A"}, {4})   // ได้ true
    =List.ContainsAny({1,2,-5,8,4,2,"a","b","A"}, {6})   // ได้ false
    =List.ContainsAny({1,2,-5,8,4,2,"a","b","A"}, {6,4})  // ได้ true

    ฟังก์ชันที่เอาไว้สร้าง List ใหม่จากตัวเดิม

    List.Repeat : สร้าง List แบบเดิมขึ้นหลายๆ รอบ

    List.Repeat(list as list, count as number) as list
    
    =List.Repeat({"a","b","A"} , 2)  // ได้ {"a","b","A","a","b","A"}

    List.Zip : รวบ item ในตำแหน่งเดียวกันของทุก List เข้าด้วยกันเป็น List ซ้อนใน List

    List.Zip(lists as list) as list
    
    lists นั้นต้องใส่เป็น List ซ้อน List
    = List.Zip(   {   {1,2,-5,8,4,2,"a","b","A"} ,{1..5}   }  ) 
    // จะได้ { {1,1} , {2,2} ,{-5,3} ,{8,4} ,{4,5} , {2,null} ,{"a",null} , {"b",null} , {"A",null} }

    ฟังก์ชัน List แบบ Advance

    List.Generate : สร้าง List ขึ้นมาด้วยเงื่อนไขที่ต้องการ

    List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list

    สังเกตได้ว่า input ทุกตัวเป็นฟังก์ชันหมดเลย แว๊ก!! ตัวนี้เป็นตัวที่เริ่มยากจริงๆ แล้ว ซึ่งเราจะใช้ each และ _ เป็นตัวอ้างอิง

    • initial : ค่าเริ่มต้น ซึ่งจะเป็น value ตัวแรกของ List
      • มักจะใส่ในรูปแบบของ ()=> ค่าที่ต้องการ
    • condition : เงื่อนไขในการสร้าง List ซึ่งจะสร้างไปเรื่อยๆ จนกว่าเงื่อนไขนี้จะเป็นเท็จแล้วจะหยุดสร้าง
    • next : วิธีการเปลี่ยนค่าเมื่อต้องการเปลี่ยนเป็น value ตัวถัดไป
    • selector : จะแสดงผลลัพธ์แต่ละตัวออกมาในลักษณะไหน

    ตัวอย่างแบบง่าย

    = List.Generate(()=>2, each _ <= 5, each _ + 1)
    //จะสร้าง List ที่มีค่าเริ่มต้นที่ 2 และเพิ่มทีละ 1 ไปเรื่อยๆ จนสุดท้ายค่าต้องไม่เกิน 5 นั่นคือ ได้ค่า {2,3,4,5}
    = List.Generate(()=>10, each _ < 15, each _ + 2)
    //จะสร้าง List ที่มีค่าเริ่มที่ 10 และเพิ่มทีละ 2 ไปเรื่อยๆ จนสุดท้ายค่าต้องน้อยกว่า 15 นั่นคือ ได้ค่า {10,12,14}
    = List.Generate(()=>10, each _ > 0, each _ - 1)
    //จะสร้าง List ที่มีค่าเริ่มที่ 10 และลดลงทีละ 1 ไปเรื่อยๆ จนสุดท้ายค่าต้องมากกว่า 0 นั่นคือ ได้ค่า { 10,9,8 จนถึง..1}
    = List.Generate(()=>10, each _ > 0, each _ - 1, each _*10)
    //จะสร้าง List ที่มีค่าเริ่มที่ 10 และลดลงทีละ 1 ไปเรื่อยๆ จนสุดท้ายค่าต้องมากกว่า 0 นั่นคือ ได้ค่า 10,9,8 จนถึง..1 สุดท้ายใน selector บอกให้เอาแต่ละค่าไปคูณ 10 ก่อนแสดงผล ดังนั้นจะได้เป็น {100,90,80,...10}

    ตัวอย่างแบบซับซ้อน (โดยให้ item แต่ละรายการเป็น Record)

    = List.Generate(()=>[a=2,b=6,c=4], each [b] <= 20, each [a=[a] + 1,b=a*5])
    • ค่าเริ่มต้นเป็นการสร้าง Record ที่มี 3 Field คือ a b c โดย a=2,b=6,c=4
    • จากนั้นลำดับถัดไปให้ ค่าใน a เท่ากับค่า a เดิม+1 และ b=a*5 (สังเกตว่าไม่ได้เกี่ยวกับ b เริ่มต้นเลยก็ได้ และ c ก็หายไปได้เลย)
    • ให้สร้าง List ไปเรื่อยๆ ตราบใดที่ b ไม่เกิน 20

    พอกด ok จะได้ผลลัพธ์แบบนี้ ซึ่งออกมาเป็น List 3 item ที่แต่ละอันเป็น Record (เพราะถ้าสร้างตัวที่ 4 จะทำให้ b เกิน 20 ซึ่งผิดเงื่อนไข)

    { [a=2,b=6,c=4] , [a=3,b=15] , [a=4,b=20] }

    ทีนี้หากเราระบุในส่วน selector เพิ่มว่าต้องการแค่ Field b ดังนี้

    = List.Generate(()=>[a=2,b=6,c=4], each [b] <= 20, each [a=[a] + 1,b=a*5], each [b]) 

    ผลลัพธ์ในแต่ละรายการจะถูก selector เลือกว่าให้แสดงค่าใน Field b เท่านั้น จะได้เป็นแบบนี้

    { 6, 15, 20 }

    ตัวอย่าง : การสร้างเลข Fibonacci ด้วย List.Generate

    เลข Fibonacci คือเลขที่เริ่มต้นด้วย 0 กับ 1 แล้วบวกกันกลายเป็นตัวถัดไป จากนั้นให้เอาผลลัพธ์บวกกับตัวก่อนหน้าไปเรื่อยๆ ก็จะได้ลำดับ Fibonacci เช่น 0,1,2,3,5,8,13,… ไปเรื่อยๆ

    เราจะสามารถสร้าง list ของ Fibonacci ที่ไม่เกิน 50 ดังกล่าวได้ด้วย M Code ดังนี้

    = List.Generate(
        ()=>[Previous=0, Current=1],
        each [Previous] + [Current] <= 50,
        each [
            Previous=[Current],
            Current=[Previous] + [Current]        
        ],
        each [Previous] + [Current])

    อธิบายแต่ละส่วน

    • initial : ค่าเริ่มต้น เป็น Record ที่มี 2 Field คือ Previous=0, Current=1
    • condition : คือค่าใน Field Previous ปัจจุบัน รวมกับ Current ปัจจุบัน ต้องไม่เกิน 50
    • next : ให้ Previousใหม่ คือ Current เดิม และ Current ใหม่ คือ Field Previous เดิม รวมกับ Current เดิม
    • selector : แสดงผลลัพธ์ออกมาเป็น Field Previous ปัจจุบัน รวมกับ Current ปัจจุบัน

    จะได้ออกมาเป็น list ที่มีค่าดังนี้

    {1,2,3,5,8,13,21,34}
    
    step1 : Previous=0 , Current=1 , selector แสดง Previous + Current =0+1=1
    step2 : Prev =Curเดิม =1 , Cur =Prevเดิม+Curเดิม = 0+1=1 , selector แสดง Prev+ Cur =1+1=2
    step3 : Prev =Curเดิม =1 , Cur =Prevเดิม+Curเดิม = 1+1=2 , selector แสดง Prev+ Cur =1+2=3
    step4 : Prev =Curเดิม =2 , Cur =Prevเดิม+Curเดิม = 1+2=3 , selector แสดง Prev+ Cur =2+3=5
    step5 : Prev =Curเดิม =3 , Cur =Prevเดิม+Curเดิม = 2+3=5 , selector แสดง Prev+ Cur =3+5=8
    step6 : Prev =Curเดิม =5 , Cur =Prevเดิม+Curเดิม = 3+5=8 , selector แสดง Prev+ Cur =5+8=13
    step7 : Prev =Curเดิม =8 , Cur =Prevเดิม+Curเดิม = 5+8=13 , selector แสดง Prev+ Cur =8+13=21
    step8 : Prev =Curเดิม =13 , Cur =Prevเดิม+Curเดิม = 8+13=21 , selector แสดง Prev+ Cur =13+21=34
    step8 : Prev =Curเดิม =13 , Cur =Prevเดิม+Curเดิม = 8+13=21 , selector แสดง Prev+ Cur =13+21=34
    step8 : Prev =Curเดิม =21 , Cur =Prevเดิม+Curเดิม = 13+21=34 , selector แสดง Prev+ Cur =21+34=55

    ซึ่งเลขจะจบแค่ 34 เพราะว่าตัวถัดไปจะเป็น 55 ซึ่งเกิน 50 แล้ว ทำให้ condition เป็นเท็จครับ

    List.Accumulate : รับค่าจาก List ที่กำหนด แล้วทำการคำนวณสะสมค่าใหม่เข้าไปในผลลัพธ์เดิมได้

    List.Accumulate(list as list, seed as any, accumulator as function) as any
    • list คือ list ซึ่งมีข้อมูลที่เราต้องการจะทำการสะสมค่า (Accumulate)
    • seed คือ ค่าตั้งต้น
    • accumulator คือ ฟังก์ชันที่จะใช้กับการสะสมค่า
    =List.Accumulate({1, 2, 3, 4}, 0, (state, current) => state + current)

    เรามี list ที่จะทำการสะสม คือเลข 1-4

    seed คือ ค่าเริ่มต้นใส่เป็น 0

    accumulator คือ ฟังก์ชันที่รับ input 2 ตัว (ชื่ออะไรก็ได้) มาคำนวณตาม expression

    • ซึ่ง input ตัวแรก (ในที่นี้คือ state) จะมีค่าเริ่มต้นเท่ากับ seed ซึ่งคือ 0
    • ส่วน input ตัวที่สอง (ในที่นี้คือ current) จะมีตัวชี้ไปที่ list แต่ละรายการ ซึ่งค่าเริ่มต้นเท่ากับ list ตัวแรกสุด นั่นคือเลข 1
    • expression คือ state+current ก็จะได้ 0+1 =1 ซึ่งค่านี้จะถูกสะสมกลับไปยังค่า state อีกครั้ง

    ต่อไป…

    • state+current ก็จะได้ 1+2 =3 ซึ่งค่านี้จะถูกสะสมกลับไปยังค่า state อีกครั้ง
    • state+current ก็จะได้ 3+3 =6 ซึ่งค่านี้จะถูกสะสมกลับไปยังค่า state อีกครั้ง
    • สุดท้าย…
      state+current ก็จะได้ 6+4 =10 ซึ่งค่านี้จะถูกสะสมกลับไปยังค่า state อีกครั้ง และจบการทำงาน

    ผลลัพธ์จะออกมาได้ 10 ทันที (เอาทุกค่ามาบวกกัน)

    ตัวนี้ผมมีตัวอย่างในหนังสือละเอียดกว่านี้ครับ เช่น ใช้ Replace ข้อความสะสมไปเรื่อยๆ

    สรุปจุดต่างระหว่าง List.Generate กับ List.Accumulate

    ผมขอสรุปประเด็นสำคัญของทั้งสองฟังก์ชันไว้ดังนี้

    ประเด็นList.GenerateList.Accumulate
    จุดประสงค์สร้าง List ขึ้นมา โดยกำหนดเงื่อนไขได้
    item แต่ละตัวของ List จะเป็นอะไรก็ได้
    สะสมค่าโดยใช้ List มาช่วย ผลลัพธ์จะออกมาเป็น value อะไรก็ได้
    เงื่อนไขการวน Loopทำซ้ำจนกว่าเงื่อนไขใน Condition จะไม่จริง จะเลิกทำทันทีทำซ้ำเท่ากับจำนวนค่าที่มีใน List เสมอ หยุดกลางคันไม่ได้
    คล้ายๆ กับอะไรในการเขียนโปรแกรมในภาษาอื่นๆDo While LoopFor Loop

    ตอนต่อไป

    ต่อไปจะเป็นการรวมเทคนิค M Code ที่ใช้บ่อย โดยจะมีการประยุกต์ใช้ฟังก์ชันเจ๋งๆ ในการจัดการเรื่องต่างๆ เช่น คัดเลือกอักขระที่ต้องการ การจัดการหัวตารางแบบ Dynamic เป็นต้น

    สารบัญซีรีส์ M Code

  • คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1

    ซีรีส์นี้จะเป็นการเอาเนื้อหา M Code Language ของ Power Query บางส่วนในหนังสือ Excel Power Up และการเรียนรู้เพิ่มเติมของผมหลังจากนั้นมาผสมผสานกัน เนื้อหาจึงมีทั้งส่วนที่เหมือนและแตกต่างกับในหนังสือด้วยนะครับ อีกอย่างในหนังสืออาจ Copy Code ลำบาก ผมเลยเขียนเป็นบทความไว้ให้เลยดีกว่า จะได้ลอง Copy Paste สูตรไปเล่นได้ง่ายๆ ด้วย

    เอาเป็นว่าใครอยากจะลงลึกเรื่อง M Code ก็อ่านซีรีส์นี้ได้เลย เพราะผมอยากให้มีคนไทยใช้เครื่องมือนี้เป็นเยอะๆ ครับ อีกอย่างมันใช้ได้ทั้งใน Excel และ Power BI เลย จึงมีประโยชน์มากๆ

    และที่สำคัญ ในซีรีส์นี้ผมจะมาอัปเดทเนื้อหาเพิ่มให้เรื่อยๆ ด้วยนะ ติดตามไปกันได้ยาวๆ เลยครับ

    M Language คืออะไร?

    M Language เป็นภาษาในรูปแบบฟังก์ชัน (Functional Language ตรงข้ามกับอีก concept คือ Object-oriented Programming) ที่ออกแบบมาเพื่อจัดการและดัดแปลงข้อมูลซึ่งเป็นภาษาของ Power Query โดยเฉพาะ ซึ่งปกติเราจะใช้เครื่องมือมาตรฐานใน Power Query Editor สร้าง M Code ออกมาให้เรา แล้วเราค่อยดัดแปลงมันอีกที แต่มาถึงตอนนี้ เราจะมาทำความเข้าใจ M Code ให้ลึกซึ้งมากขึ้นเพื่อที่ให้สามารถใช้มันจัดการปัญหาที่ซับซ้อนมากขึ้นได้ โดยเฉพาะการเขียน Custom Function ที่ทรงพลังมากๆ

    เราจะค่อยๆ ทำความเข้าใจทีละส่วน เริ่มจากพื้นฐานกันครับ

    ก่อนอื่นคงต้องทบทวนความจำซักนิดนะครับ ว่า M Language เป็นภาษาที่สนใจเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ หรือที่เรียกว่า่ Case Sensitive พูดง่ายๆ คือ ถ้าเขียนชื่อฟังก์ชันอะไรผิดแค่เรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ มันก็จะ Error ทันทีเลย (แม้แต่คำว่า let กับ in ก็ต้องตัวพิมพ์เล็กให้ถูกต้อง)

    Tips : เพื่อให้การเขียน Code ของเราสะดวกสบายที่สุด ผมแนะนำว่าให้คุณฝึกใน Excel365 หรือ Power BI ครับ เพราะมันจะมี Intellisence มาช่วย Guide การเขียนให้เราด้วย

    Let Expression

    เวลาเราใช้เครื่องมือมาตรฐานสั่งคำสั่งต่างๆ มันจะมีคำว่า let … in… โผล่มาโดยอัตโนมัติ ในรูปแบบ คือ

    let
    	ชื่อตัวแปร1=expression1(สูตร),
    	ชื่อตัวแปร2=expression2(สูตร),
    	ชื่อตัวแปร3=expression3(สูตร)
    in
    	ชื่อตัวแปรที่ต้องการแสดงผลลัพธ์

    ยกตัวอย่างเช่น

    let
    	Step1=1+2,
    	Step2=Step1*2
    in
    	Step2
    //แบบนี้ผลลัพธ์จะได้ 6

    ภายใน Let คือ การตั้งชื่อตัวแปรให้สูตร (Expression) โดยที่เราสามารถตั้งชื่อตัวแปร (Variable) กี่ตัวก็ได้ตามต้องการ โดยคั่นด้วยเครื่องหมาย comma เช่นในตัวข้างบน จริงๆ ก็คือเขียนในบรรทัดเดียวกันก็ได้แบบนี้ (และนี่คือสาเหตุว่าทำไมหลัง let ตัวสุดท้ายไม่มี comma เพราะมันใช้คั่นการตั้งชื่อตัวแปร)

    let   ตัวแปร1=สูตร1,   ตัวแปร2=สูตร2,   ตัวแปร3=สูตร3

    และชื่อตัวแปรแต่ละอันก็จะไปโผล่เป็นชื่อ Step ใน Power Query Editor ด้วยนะ

    หลัง in คือ สิ่งที่มันจะทำการคำนวณเพื่อแสดงเป็นผลลัพธ์ เวลามันทำงานมันจะดูว่า “หลัง in เราต้องการผลลัพธ์อะไร” มันจะดูเองว่าจะเอาค่ามาจากไหน

    • ในตัวอย่างข้างบน มันจะรู้ว่าเราต้องการค่าในตัวแปร Step2
    • มันก็จะไปดูว่า Step2 ต้องใช้ค่าจากตัวแปร Step1 ไปคูณ2
    • จากนั้นมันก็จะไปดูว่า Step1 เกิดจากเอาค่า 1+2 ได้ 3
    • แล้ว Step2 จะเอา 3*2 ได้ 6

    Variable และ Expression

    จากในรูปแบบของ M Code จะพบว่า

    • สิ่งที่พิมพ์อยู่ก่อนเครื่องหมาย = คือ Variable หรือ ชื่อตัวแปร
    • สิ่งที่เราพิมพ์หลังเครื่องหมาย = คือ Expression หรือ สูตร

    ซึ่ง Expression จะถูกคำนวณออกมาได้สิ่งที่เรียกว่า Value หรือ ค่าผลลัพธ์ ซึ่งมีได้หลายประเภท

    เรามาลงรายละเอียดแต่ละตัวกันครับ

    Variable (ชื่อตัวแปร)

    ถ้าจะใช้ชื่อตัวแปรแบบอ่านง่ายที่สุด ต้องมีลักษณะดังนี้

    1. ต้องขึ้นต้นด้วยตัวอักษร หรือ _
    2. ห้ามมีช่องว่างเว้นวรรค คีย์เวิร์ดที่สงวนไว้ หรือ สัญลักษณ์ประหลาด

    ถ้าจะผิดกฎดังกล่าว ต้องใส่ชื่อตัวแปรแบบนี้  #”ชื่อตัวแปรนั่นคือ ใส่ชื่อไว้ในเครื่องหมายคำพูด แล้วขึ้นต้นด้วย Hashtag (ถ้าสังเกตเวลาใช้เครื่องมือ UI มาตรฐาน มันจะตั้งชื่อตัวแปรให้เราแบบนี้แหละ)

    let
    	Step1=1+2,
    	#"Step 2"=Step1 *2,
    	Step3=#"Step 2" /3
    in
    	Step3

    ดังนั้น ถ้าไม่อยากให้ชื่อตัวแปรใน M Code ของเรามี # ประหลาดๆ แบบนี้ ก็ให้ตั้งชื่อแบบไม่ต้องเว้นวรรคนะครับ

    Expression (สูตร)

    คล้ายๆ การเขียนสูตรใน Excel ที่สามารถเป็นส่วนผสมกันระหว่าง Value ประเภทต่างๆ, Operator รวมถึง ฟังก์ชันต่างๆ ทั้ง Standard Function ที่มีมาให้อยู่แล้ว และ Custom Function ที่เราเขียนขึ้นเองได้ด้วย

    ซึ่งผลลัพธ์ของสูตรก็จะให้ค่าออกมาเป็น Value เช่นกัน (เหมือน Excel นั่นแหละ) แต่สิ่งที่แตกต่างจาก Excel ปกติคือ Value ของ Power Query สามารถออกมาเป็นค่าเดี่ยวๆ หรือมาเป็นโครงสร้างก็ได้ เช่น List, Record, ตาราง

     ประเภทของ Value (ผลลัพธ์)

    ประเภทของ Value ใน Power Query มีดังนี้

    ประเภทรูปแบบ
    (ระวังตัวพิมพ์เล็กพิมพ์ใหญ่)
    ตัวอย่าง
    Null (ค่าว่าง)nullnull
    Logical (ตรรกะ)true falsetrue false
    Number (ตัวเลข)ตัวเลข0 1 -3 8.5 4.9e-5
    Time (เวลา)#time(hour, minute, second)#time(06,15,00)
    Date (วันที่)#date(year, month, day)#date(2019,07,21)
    DateTime (วันที่คู่กับเวลา)#datetime(year, month, day, hour, minute, second)#datetime(2019,07,21, 06,15,00)
    DateTimeZone (วันที่คู่กับเวลา แบบมี TimeZone)#datetimezone( year, month, day, hour, minute, second, offset-hours, offset-minutes)#datetimezone(2019,07,21, 06,15,00, 07,00)
    Duration (ระยะเวลา)#duration(day,hour,minute,second)#duration(1,2,30,0)
    Text (ข้อความ)“ข้อความในคำพูด”“hello” “สวัสดี”
    Binary ไฟล์/ข้อมูล Binary#binary(“AQID”)
    List (รายการข้อมูล
    มักใช้กับข้อมูล 1 คอลัมน์)
    {value1,value2,value3}{1, 2, 3}
    Record (ข้อมูล 1 แถว)[field1=value1,field2=value2][ A = 1, B = 2 ]
    Table#table( List ของชื่อหัวตาราง , List ของข้อมูล)
    โดยข้อมูลแต่ละแถวเป็น List อีกทีนึง
    #table({“A”,”B”},{{1,2},{3,4}})
    Function(input1,input2,…) => expression ของฟังก์ชันนั้นๆ(x) => x + 1
    Type type { number }
    type table [ A = any, B = text ]

    เดี๋ยวเราจะมาลงรายละเอียดข้อมูลแต่ละประเภทอีกทีครับ

    Operator ต่างๆ ที่ใช้บ่อยๆ

    ใน Power Query นั้น Operator ต่างๆ จะทำงานได้เฉพาะกับ Data Type ที่เหมาะสมเท่านั้น เช่น เราไม่สามารถเอาข้อความที่มีหน้าตาเหมือนตัวเลขไปทำการบวกลบกันได้ ซึ่งต่างจาก Excel และ DAX ที่จะพยายาม Convert ประเภทข้อมูลให้เราโดยอัตโนมัติ

    ประเภทเครื่องหมายความหมายตัวอย่าง
    คำนวณ+บวก3+2
     ลบ3-2
     *คูณ3*2
     /หาร3/2
    ตัวเชื่อม( )ให้คำนวณในวงเล็บก่อน(3+2)*5
     &เชื่อมข้อมูล“Big”&”Cat”
    เปรียบเทียบ=เท่ากับ3=2
     <> ไม่เท่ากับ3<>2
     มากกว่า3>2
     น้อยกว่า3<2
     >=มากกว่าหรือเท่ากับ3>=2
     <=น้อยกว่าหรือเท่ากับ3<=2
    ตรรกะandและx>3 and x<=10
     orหรือx<3 or x>10
     notกลับจริงเป็นเท็จ
    เท็จเป็นจริง
    not (x>3)

    เรื่องที่ควรรู้ของ M Code

    Comment

    //ถ้าจะ Comment บรรทัดเดียว ทำแบบนี้ (ใส่เครื่องหมาย // นำหน้า)
    /*
    ถ้าจะ Comment 
    หลายบรรทัด
    ทำแบบนี้ (ใส่ใน /* ….. */ )
    */

    Code จะเรียงยังไงก็ได้!!

    เนื่องจากมันจะไปหาเองว่าเราต้องการค่าใน Step2 แล้วมันจะไปดูต่อว่าต้องการค่าใน Step1 ดังนั้นเราจะเรียง Code ยังไงก็ได้ครับ เพราะจริงๆ มันก็เป็นแค่ลำดับของการตั้งชื่อตัวแปรเท่านั้นแหละ

    let
    	Step2=Step1*2,
    Step1=1+2
    	
    in
    	Step2

    Return ค่าอะไรออกมาก็ได้

    ซึ่งเราจะ Return ค่าอะไรออกมาหลัง In ก็ได้ เช่น ถึงจะเขียน Step2 มาแล้ว แต่เรา Return ค่า Step1 ก็ได้

    let
    	Step1=1+2,
    	Step2=Step1*2
    in
    	Step1

    โดยที่หากเราเขียนแบบนี้ Power Query จะหาทางเองว่าต้องใช้ตัวไหนบ้างในการรัน แล้วมันจะไม่รันตัวที่ไม่จำเป็นด้วย! ซึ่งในตัวอย่างข้างบนมันจะดูว่า สุดท้ายแล้วต้องการผลลัพธ์จากตัวแปร Step1 ซึ่งคำนวณจาก เอาค่า 1+2 ดังนั้นมันจะไม่เสียเวลาไปคำนวณ Step2 เลยครับ หรือถ้าเอาแบบเห็นชัดๆ เลย คือ

    let
    	Step1=1+2,
    	Step2=Step1*2
    in
    	4+5

    แบบนี้ผลลัพธ์จะได้ 9 ซึ่งเกิดจาก 4+5 เลย โดยไม่เกี่ยวกับ Step1 และ Step2 ด้วยซ้ำครับ !!

    let in ซ้อน let in ได้อีก

    let
    	Step1=1+2,
    	Step2=Step1+3
    	Step3=
    		let
    			Step1 = 100
    			Step2 = Step1*2
    		in
    			Step2
    in
    	Step3

    แบบนี้จะได้ผลลัพธ์ออกมาเป็น 200 เนื่องจาก Step3 สุดท้ายจะไปอ่านค่า Step2 ที่อยู่ใน Let ชั้นในว่า

    Step2 = Step1*2 // ซึ่งจะได้ Step2 = 100*2 ซึ่งจะได้ 200

    ดังนั้นจะเห็นว่าเราสามารถตั้งชื่อ Step ซ้ำกันได้ ถ้าอยู่ใน let คนละ scope กันครับ

    ค่า Value ที่เป็น Output ของ Expression

    ใน Power Query มีได้หลายแบบ ทั้งที่ให้ค่าเดี่ยวๆ และ ค่าเป็นโครงสร้าง ดังนี้

    Primitive Value : ข้อมูลพื้นฐานตัวเดียวเดี่ยวๆ

    พวกนี้เราได้เรียนรู้กันไปแล้วในตารางข้างบนเนอะ

    • Null (ค่าว่าง)
    • Logical (ตรรกะ)
    • Number (ตัวเลข)
    • Time (เวลา)
    • Date (วันที่)
    • DateTime (วันที่คู่กับเวลา)
    • DateTimeZone (วันที่คู่กับเวลา แบบมี TimeZone)
    • Duration (ระยะเวลา)
    • Text (ข้อความ)

    Structured Values : ข้อมูลที่ประกอบกันเป็นโครงสร้าง

    List : รายการข้อมูล

    เป็นการเอาข้อมูล Primitive มาเรียงกัน อยู่ในเครื่องหมาย { } คั่นด้วย comma

    มักถูกใช้เป็นข้อมูล 1 คอลัมน์ใน Table ที่มีหลายๆ ค่า

    รูปแบบ

    {value1, value2, value3 }

    ตัวอย่าง

    • {1, 2, 3} // list ตัวเลข 1 2 และ 3
    • { 1..5 } // มีค่าเท่ากับ { 1, 2, 3, 4, 5 }
    • {“d”..”g”} // มีค่าเท่ากับ { “d”, “e”, “f”, “g”}
    • {“D”..”G”} // มีค่าเท่ากับ { “D”, “E”, “F”, “G”}
    • {100, true, “A”}
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 141

    รวม List ได้ด้วย &

    { 1, 2 } & {3, 4 } // { 1, 2, 3, 4 }
     { 1, 2 } & { 3, 2} // { 1, 2, 3, 2 }
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 142

    อ้างอิงข้อมูลใน List ด้วย {เลข index}

    โดยที่ เลข index จะเริ่มต้นด้วยเลข 0 (itemแรก คือลำดับที่ 0 นะ ระวังให้ดี!!)

    SampleList = { 10, 20, 30 }
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 143

    ลองคลิ๊กขวาแล้ว Drill Down ที่ 20 ดู จะได้ออกมาเป็น

    SampleList{1} // จะได้ 20  และสังเกตว่าถ้าจะอ้างอิง item ลำดับที่ 2 เลข index จะเป็นเลข 1 นะ
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 144

    Record : เป็นกลุ่มของ Field

    คำว่า Field หมายถึงข้อมูลที่เป็นคู่กันระหว่าง name และ value

    มักถูกใช้เป็นข้อมูล 1 แถวใน Table ที่มีหลายๆ คอลัมน์

    รูปแบบ

    [ FieldName1=value1, FieldName2=value2, FieldName3=value3 ]

    โดยที่ชื่อ Field จะเป็นอะไรก็ได้ เป็นตัวหนังสือ ตัวเลข หรือเว้นวรรคก็ได้ ไม่ต้องใส่เครื่องหมายคำพูดด้วย

    ตัวอย่าง

    [ A = 100, B = 200, C = 300 ]
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 145

    รวม Record ได้ด้วย &

    [ A = 1 ] & [ B = 2 ] // [ A = 1, B = 2]

    ถ้าชื่อ field ซ้ำ จะยึดตัวหลังสุด

    [ A = 1, B=2 ] & [ A = 10 ] // [ A = 10, B=2 ]
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 146

    การอ้างอิงข้อมูลใน Record ด้วย [ชื่อ Field]

    SampleRecord = [ First Name = "Sira", Last Name = "Ekabut", Height = 172, Weight = 67 ]
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 147

    ลองคลิ๊กขวาแล้ว Drill Down ที่คำว่า Sira ใน First Name ดู จะออกมาเป็น

    SampleRecord[First Name]    // จะได้ "Sira"
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 148

    Table : ตาราง

    คือข้อมูลที่ถูกจัดให้สามารถมีหลายคอลัมน์ และสามารถมีหลายแถวได้ โดยแต่ละคอลัมน์จะสามารถอ้างอิงได้ด้วยชื่อคอลัมน์

    รูปแบบ (แบบพื้นฐาน)

    #table( set ของชื่อหัวตาราง , set ของข้อมูลแต่ละแถวอยู่ภายใน  { } คั่นด้วย comma )
    #table( {"col1","col2"} , { { r1c1value, r1c2value} , { r2c1value, r2c2value},...   } )

    ตัวอย่าง

    SampleTable = #table( {"A", "B"}, { {1, 2}, {3, 4}, {5, 6} } )

    หรือจะเขียนเป็นแบบนี้ก็จะดูคล้ายตารางมากขึ้น

    SampleTable = #table( 
    {"A", "B"}, 
    { 
    {1, 2}, 
    {3, 4}, 
    {5, 6} 
    } )

    ซึ่งจะได้ผลลัพธ์แบบนี้ครับ

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 149

    การอ้างอิงข้อมูลใน Table

    ถ้าอ้างอิงด้วย table[ ชื่อ Field ] (แบบไม่ต้องมีเครื่องหมายคำพูด)  จะได้ผลลัพธ์เป็น List ที่เป็นรายการของ Field นั้นๆ (มักใช้กับการอ้างอิงข้อมูลใน 1 คอลัมน์ในตาราง) เช่น =SampleTable[B] จะได้ List ข้อมูลในคอลัมน์ B ออกมา

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 150

    ถ้าอ้างอิงด้วย table{เลข index} จะได้ผลลัพธ์ออกมาเป็น Record ซึ่งเป็นข้อมูลใน 1 แถวของตาราง เช่น =SampleTable{1} จะได้ Record แถวที่ 2 ของ Table ออกมา (อย่าลืมว่า Index แถวแรก เริ่มที่เลข 0)

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 151

    ถ้าอ้างอิงด้วยทั้ง {index} และ [ชื่อ Field] ซ้อนกัน ก็จะได้ Item นั้นๆ ออกมาเลย (เพราะระบุทั้งแถวทั้งคอลัมน์แล้ว) เช่น ลองกด Drill Down ดูที่เลข 4 จากตอนที่เป็น Table จะได้แบบนี้

    = SampleTable{1}[B] // หมายถึงเอาแถวที่สอง (index1 คือแถว 2) และ เอา Field ชื่อ B
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 152

    ซึ่งเราสามารถอ้างอิง Field ก่อนแถวก็ได้เช่นกัน เช่น = SampleTable[B]{1}

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 153

    นอกจากนี้เรายังสามารถอ้างอิงจาก Value ใน Field ที่กำหนด เพื่อให้ได้ Record ออกมาได้ด้วย
    ด้วยรูปแบบ {[ชื่อ Field = ค่าที่ต้องการ]} เช่น

    = SampleTable{[A=3]}

    จะได้ Record ที่ Field A มีค่าเท่ากับเลข 3

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 154

    อย่างไรก็ตาม วิธีอ้างอิงจาก Value จะใช้ได้ก็ต่อเมื่อมี Value นั้นๆ แค่ตัวเดียวใน Table เท่านั้น หากมีค่าซ้ำกันมันจะให้ผลเป็น Error ทันที

    Tips : หากเรากด Drill Down ลงไปในช่องที่อยู่ในคอลัมน์ที่เคยสั่ง Remove Duplicates เอาไว้ก่อน จะทำให้ Power Query รู้ว่า Field นั้นมีค่าไม่ซ้ำกันแน่นอน สูตรที่ออกมาจะเป็นลักษณะของการอ้างอิงจาก Value ใน Field ที่กำหนด ได้โดยอัตโนมัติ

    เช่น ตารางของผมเคยกด Remove Duplicates คอลัมน์ TXID มาก่อน จากนั้น ผมกด Drill Down ไปที่ Field ราคาต่อชิ้น ในแถวเดียวกับ TXID ที่มีค่าเป็น TX00004 จะได้ผลลัพธ์ออกมาดังนี้

    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 155

    การ Convert ประเภทข้อมูลที่ใช้บ่อย

    อย่างที่บอกไปแล้วว่า เวลาจะใช้ Operator หรือฟังก์ชันต่างๆ ใน M Code เรื่องของ Data Type เป็นเรื่องที่ค่อนข้างซีเรียส ดังนั้นเราจะต้องมีการ Convert ประเภทข้อมูลให้ถูกต้องก่อนที่จะทำการคำนวณด้วย

    ซึ่งการ Convert ประเภทข้อมูลใน M Code เราจะใช้ฟังก์ชันมาช่วยครับ วิธีการจำทั่วไป คือ จะแปลงเป็นอะไรให้ขึ้นต้นด้วยประเภทนั้น แล้วตามด้วย .From ได้เลย

    จากเป็นวิธีการตัวอย่าง
    ใดๆNumberNumber.From(value as any) as number=Number.From(“0123”)
    =123

    =Number.From(#date(1900,01,01)) =2
    สังเกต : วันแรกที่ Power Query แทนด้วยเลข 1 คือวันที่ 31 Dec ค.ศ.1899 นะครับ ซึ่งไม่ตรงกับ Excel ซึ่งเลข 1 คือวันที่ 1 Jan ค.ศ. 1900)
    ใดๆTextText.From(value as any) as text=Text.From(123)
    =”123″
    ใดๆLogicLogical.From(value as any) as logical= Logical.From(1)
    = Logical.From(“True”)
    = true
    ใดๆDateDate.From(value as any, optional culture as nullable text) as nullable date=Date.From(1)
    =31/12 ค.ศ.1899 หรือ พ.ศ. 2442

    =Date.From(“21 July 2019″,”en-GB”)
    =21/7 ค.ศ.2019 หรือ พ.ศ. 2562
    ใดๆDateTimeDateTime.From(value as any, optional culture as nullable text) as nullable datetime=DateTime.From(“21 July 2019 13:30″,”en-GB”)
    =21/7 ค.ศ.2019 หรือ พ.ศ. 2562 เวลา 13:30 น.

    Tips: ถ้าจะทำเป็นจำนวนเต็ม แล้วจะใช้ Int64 ต้องระวัง เพราะถ้าใช้ Int64 มีการปัดตัวเลขตามหลักคณิตศาสตร์ด้วย

    =Int64.From(1234.789)
    =1235

    ถ้าจะปัดเศษทิ้งไปเลย แนะนำให้ใช้ RoundDown แทน

    = Number.RoundDown(1234.789,0)
    =1234

    สารบัญซีรีส์ M Code

    แหล่งอ้างอิง M Code เจ๋งๆ

    Website

    YouTube

  • วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query

    วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query

    ปกติเวลาเราสั่งให้ Power Query รวมทุกไฟล์ใน Folder เดียวกัน แม้มันจะเอาข้อมูลรวมกันได้ แต่ก็อาจจะมีปัญหาเรื่องชื่อคอลัมน์ได้ในอนาคต

    สาเหตุเพราะ มันมีการระบุชื่อคอลัมน์ที่แตกออกมาอย่างชัดเจนในสูตร M Code ทำให้ หากในอนาคตมีคอลัมน์เพิ่มขึ้นหรือลดลง ก็จะมีปัญหาทันที (อย่างเช่นที่ผมบอกไว้ในหนังสือ Excel Power Up ว่า เมื่อไหร่ก็ตามที่มีการระบุชื่อคอลัมน์ในสูตร M Code นั้น จะอันตรายต่อการ Refresh ทันที)

    ลองทำดู

    โหลดไฟล์ประกอบได้ที่นี่

    ตัวอย่างเช่น ผมสั่งรวมข้อมูลจาก Folder ที่ชื่อ MyFolder และสร้าง Custom Column ดังนี้ เพื่อดึงข้อมูลจาก Excel ด้วยสูตรว่า

    =Excel.Workbook([Content],true)
    วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query 156

    ผมแตกคอลัมน์ Custom ออกมาจะได้ดังนี้

    วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query 157

    เดี๋ยวผมลบคอลัมน์ที่ไม่ใช้ทิ้งก่อน โดยจะเก็บแค่ ชื่อไฟล์ ชื่อ Item และ Data ไว้ โดยเลือกที่จะเอา แล้วสั่ง Remove Other Columns ซะ

    วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query 158

    ปัญหาเรื่องชื่อคอลัมน์ เกิดขึ้นตอน Expand Data

    พอเรากด Expand Data ออกมา จะพบว่าสูตรที่ได้มีการระบุชื่อคอลัมน์ออกมาโต้งๆ เลย ว่า

    = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"}, {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"})
    วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query 159

    ถ้าเป็นแบบนี้ หากในข้อมูลต้นฉบับบางคอลัมน์หายไป หรือมีคอลัมน์เพิ่มเข้ามา จะมีปัญหาทันที!!

    ลองเพิ่ม/ลดคอลัมน์ดู

    จะพบว่าถ้าเพิ่มคอลัมน์ เช่น ยอดขาย มันจะไม่ถูดดึงมาด้วย (เพราะสูตรไม่ได้ระบุคอลัมน์นั้น)

    ถ้าลบคอลัมน์ซักอันนึง เช่น ผมเอาวิธีการชำระเงินออกออกจากทุกไฟล์ มันก็จะยังมีคอลัมน์ค้างอยู่เป็น null อย่างงั้นเลย (เพราะสูตรมีการระบุคอลัมน์นั้น)

    วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query 160

    ถ้าปล่อยให้เป็นแบบนี้ การอัปเดทข้อมูลจะยากเกินไป

    แก้ปัญหาโดยแก้ M Code เล็กน้อย

    เราสามารถทำให้ชื่อ Column มีความ Dynamic ยืดได้หนดได้ โดยต้องแก้บางส่วนของ Code เดิมอันนี้

    = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"}, {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"})

    จะเห็นว่า สีแดง มันคือ List ของชื่อคอลัมน์ทั้งหมด

    = Table.ExpandTableColumn(#"Removed Other Columns", "Data", List ของชื่อคอลัมน์)

    ซึ่ง List ของชื่อคอลัมน์เกิดจากการรวมชื่อคอลัมน์ทุก Table ดังนี้

    List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_)))

    หลักการคือ เราจะใช้สูตร List.Transform โดยใช้ Table.ColumnNames(_) กับทุกๆ Table เพื่อให้แต่ละ Table คืนค่ามาเป็น List ของหัวตารางใน Table นั้นๆ จากนั้นใช้ List.Union มารวม item ทุกตัวเข้าด้วยกัน

    ใครอยากรู้ว่าทำไมสูตรนี้ถึงได้ผล สามารถอ่านได้ที่นี่ https://www.thepexcel.com/m-code-power-query-03-list/

    สรุปแล้วเราจะแก้สูตรเป็นดังนี้

    = Table.ExpandTableColumn(#"Removed Other Columns", "Data", List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_))))

    เราก็จะได้ Column ที่ Expand ได้แบบ Dynamic สุดๆ เจ๋งป่ะ!

    วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query 161

    ผมมั่นใจว่าถ้าเพื่อนๆ ใช้วิธีที่ผมบอกนี้ ตอนอัปเดทข้อมูลชีวิตจะง่ายขึ้นเยอะเลยครับ

    สุดท้าย อย่าเผลอไปกด Auto Detect Data Type นะครับ เดี๋ยวมันก็จะระบุชื่อคอลัมน์อีก ให้ทำเฉพาะคอลัมน์ที่จำเป็นเท่านั้น เช่น ตัวเลข วันที่ เวลา เป็นต้น