คัมภีร์สรุป 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")

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

=Text.Select([Movie Name],{"a","A"})

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

Text.Select(Text.Lower([Movie Name]),{"a"})

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

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

แต่ถ้าจะเอาตัวเลขด้วยเราเขียนตรงๆ ด้วยการเพิ่ม {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] ," ")

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

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

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

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

เท่านี้เราก็จะได้ผลลัพธ์เหมือนกันใช้ 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 แต่ละเดือนแบบนี้

เราต้องการให้เหลือแค่คอลัมน์ที่เป็น 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 อยู่ ไม่ว่าจะตรงไหนก็ตาม

หรือจะเจาะจงว่าชื่อคอลัมน์ต้องขึ้นต้นด้วย 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

วิธีที่ 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 #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(_)))   )

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

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

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

ตอนต่อไป

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