หลายๆ คนน่าจะได้ลองใช้ Power Query ผ่านเครื่องมือบน User Interface ปกติไปจนเป็นแล้ว และหลายคนก็จะเริ่มรู้แล้วว่าเครื่องมือ User Interface ปกตินั้นมันไม่สามารถตอบโจทย์ในงานบางอย่างได้ เรียกได้ว่าเครื่องมือ Power Query ปกตินั้นสามารถตอบโจทย์งานทั่วไป 80% ได้แหละ แต่งานอีก 20% จะต้องมีการยุ่งกับ M Code ถึงจะสามารถทำได้สำเร็จ
แต่ในบทความนี้ผมจะเน้นไปที่ M Code ที่เราจะสามารถนำมาใช้ในงานของเราได้จริงๆ ในกรณีที่พบได้บ่อย เช่น เราสามารถใช้ M Code ใช้แก้ปัญหาเมื่อข้อมูลอยู่ตำแหน่งไม่แน่นอนได้ ทุกคนจะได้เห็นภาพมากขึ้นว่าเราจะเรียนรู้ M Code ไปทำไมกัน??
let
Source = ...,
MyTable = ตารางต้นฉบับที่เตรียมไว้,
MyTable = #"Replaced Value",
AddIndex = Table.AddIndexColumn(MyTable, "Index", 1, 1, Int64.Type),
FilterRows = Table.SelectRows(AddIndex, each ([Column1] = "ผลไม้")),
FruitRow = FilterRows{0}[Index],
Result = Table.Skip(MyTable,FruitRow-1)
in
Result
ได้ผลลัพธ์แบบนี้
วิธีที่ 2 : ใช้ความรู้เรื่อง List มาช่วย
แบบนี้เราสามารถทำให้ข้อมูลใน Column1 เป็น List แล้วใช้ List.PositionOf เพื่อหาตำแหน่งของคำที่ต้องการได้ เช่น
ทำให้คอลัมน์แรกเป็น List ก่อนด้วยการคลิ๊กขวาที่คอลัมน์แรกแล้วกด Drill Down เพื่อให้อ้างอิงข้อมูลคอลัมน์ให้เป็น List
= MyTable[Column1]
จากนั้นใช้ List.PositionOf ครอบเข้าไป ก็จะหาตำแหน่งของผลไม้ ออกมาเป็น index ที่ 3
= List.PositionOf(MyTable[Column1],"ผลไม้")
แล้วเราก็อาจเก็บค่านี้ไว้ในตัวแปร PositionFruit
แล้วเอาไปเรียกใช้ใน Table.Skip ได้เลย เช่น
= Table.Skip(MyTable,PositionFruit)
ก็จะได้ผลลัพธ์ที่ต้องการครับ สรุป M Code ทั้งหมดเป็นแบบนี้
let
Source = ...,
MyTable = ตารางต้นฉบับที่เตรียมไว้,
PositionFruit = List.PositionOf(MyTable[Column1],"ผลไม้"),
Result = Table.Skip(MyTable,PositionFruit)
in
Result
let
Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
ReplaceColumnName=Table.TransformColumnNames(Source,each Text.Replace(_,"-","_"))
in
ReplaceColumnName
ตัวอย่าง 10 : เลือกเฉพาะคอลัมน์ที่มี – อยู่
เราใช้ Table.ColumnNames เพื่อเอาชื่อคอลัมน์ทั้งหมดในตารางออกมาเป็น List
ใช้ List.Select เพื่อทำให้ List เหลือเฉพาะ item ที่ตรงตาม Criteria
ใช้ 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
ก่อนหน้านี้ผมได้เคยเขียนบทความวิธีใช้ Power Query ดึงข้อมูลจาก Web API ไปแล้ว 3 ตอน ถ้าใครยังไม่เคยเข้าไปอ่านก็สามารถเข้าไปอ่านดูก่อนได้นะครับ (ไม่อ่านก็ยังสามารถทำตามในบทความนี้ได้อยู่)
ซึ่งบทความในวันนี้เนื้อหาจะคล้ายกับตอนที่ 2 ของเดิมมากๆ คือเป็น Web API แบบ Post แต่จะเป็นกรณีตัวอย่างการใช้กับ EMS Tracking ของไปรษณีย์ไทยโดยเฉพาะ ซึ่งหลายๆ คนน่าจะอยากใช้งานอยู่ ซึ่งวิธีทำนี้จะใช้ใน Excel หรือ Power BI ก็ได้นะครับ (พอดีมีลูกค้าที่เรียน Power BI กับผมถามมา เลยเอามาเขียนเป็นบทความเผื่อเพื่อนๆ คนอื่นด้วยเลยดีกว่า)
และนี่คือแนวทางจัดการเวลามี each ซ้อนกันหลายชั้นครับ นั้นคือ ให้เปลี่ยน each แต่ละตัวเป็นชื่อ parameter เต็มๆ คู่กับ => แบบไม่ต้องย่อว่า each แบบปกตินั่นเอง
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 ได้ด้วย
ตัวอย่างการใช้งาน
หาแบบ default จะได้ผลลัพธ์ตัวแรก และ capturing group ของมันถ้าใส่ mode เป็น “g” จะหาแบบ global คือหาทุก match (แต่ไม่สนใจ capturing group)หากแบบ gi คือ global + case insensitive คือ ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่ แบบหาทุก matchหากแบบ i คือ case insensitive คือ ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่ ได้ตัวแรกอันเดียว แต่เอา capturing group ด้วยถ้าใส่ mode เป็น “g” จะหาแบบ global คือหาทุก match (แต่ไม่สนใจ capturing group)
(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
(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
ThepRegExExtractAll : ดึงผลลัพธ์ทุกตัวทุก SubGroup ออกมาเป็น List
อย่างไรก็ตาม ตัวที่ท้าทายที่สุดก็คือ การ Extract ข้อมูลทุกตัวที่ตรงกับ Pattern และเอา Capturing Group ด้วย ซึ่งเราใช้ฟังก์ชัน String.MatchAll ของ JavaScript ตรงๆไม่ได้ เพราะ Internet Explorer มันไม่ Support
ใน 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
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
ซึ่งเราก็นำไปใช้กับกรณี 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}}}), "ผู้เล่น")
เทคนิค Group by แล้วรวมข้อความที่เป็น Text คั่นด้วย Delimiter
สมมติเรามีข้อมูลดังนี้
เราจะ Group by ตามผู้ขาย แต่อยากได้รายชื่อสินค้าเอามารวมแล้วคั่นด้วยเครื่องหมาย / จะทำยังไง?
เราสามารถใช้การ Group ที่เรียกว่า All Rows มาช่วยได้ เช่น
จากนั้นจะได้ผลลัพธ์แบบนี้
concept คือเหมือนเดิม ว่า _ คืออ้างอิงตารางทั้งตารางที่ผ่านการ Group มาแล้ว ดังนั้นผมสามารถแก้สูตรให้ย่อๆ เหลือแค่นี้ก็ได้ ซึ่งก็จะได้ผลลัพธ์เหมือนเดิมเลย
= Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each _})
= Table.TransformColumns(#"Grouped Rows", {"all", each Text.Combine(List.Transform(_, Text.From), "/"), type text})
เทคนิคการ Group by แล้วนับค่าในคอลัมน์แบบไม่ซ้ำ
จาก Data ในเทคนิคที่แล้ว หากเราจะสั่ง Group by ผู้ขาย และจะนับจำนวนลูกค้าแบบไม่ซ้ำกัน เราจะพบว่าไม่สามารถใช้ Count Distinct Rows ใน Group by เพื่อเลือกคอลัมน์ลูกค้าได้สาเหตุเป็นเพราะ Count Distinct Rows ใน Group by เอาไว้นับจำนวนบรรทัดแบบไม่ซ้ำ (โดยดูทั้งบรรทัดเท่านั้น)
= Table.Group(#"Changed Type", {"ผู้ขาย"}, {{"Count", each Table.RowCount(List.Distinct(_[ลูกค้า])), Int64.Type}})
เทคนิคการ Trim แบบให้ทำให้ช่องว่างตรงกลางเหลือแค่ 1 เคาะ
หลายคนอาจไม่ได้สังเกต ว่า Trim ใน Power Query นั้น จะตัดข้อมูลได้แค่ด้านหน้ากับด้านหลังเท่านั้น ไม่สามารถจะเอาข้อมูลช่องว่างตรงกลางออกไปได้ (ต่างจาก TRIM ใน Excel)
ดังนั้นถ้าอยากให้ใน Power Query มัน Trim ได้แบบเดียวกับใน Excel จะต้องมีการแก้ Code ดังนี้
บทความนี้จะเป็นการรวมเทคนิค M Code แบบประยุกต์กับการใช้งานจริงที่เราต้องพบเจอบ่อยๆ มาไว้ด้วยกัน จะได้เป็นแหล่งอ้างอิงให้เพื่อนๆ เวลาทำงานด้วยนะ ซึ่งจะเข้าใจการทำงานมันได้อย่าลืมไปอ่านตอนที่แล้วซึ่งเป็นการสรุปวิธีทำงานของฟังก์ชันเกี่ยวกับ List ซะก่อนล่ะ
เทคนิค M Code #1: อยากเก็บ Text ไว้เฉพาะอักขระที่กำหนด
แต่ถ้าจะเอาตัวเลขด้วยเราเขียนตรงๆ ด้วยการเพิ่ม {0..9}ไม่ได้ มันจะขึ้น Error มาเตือนว่า Expression.Error: We cannot convert the value 0 to type Text. (เพราะเราจะเชื่อมด้วย & ซึ่งต้องเป็น Text)
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
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 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} }
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
ในตอนนี้เราจะมาเรียนรู้เรื่องเกี่ยวกับข้อมูลประเภท 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(textsas list, optional separator as nullable text) as text
เวลาดึงข้อมูลหัวตารางจาก Table มาจัดการต่อ ก็ออกมาเป็น List
Table.ColumnNames(table as table) as list
การจะจัดการกับประเภทข้อมูลของหัวตาราง Table ก็ต้องใช้ List
Table.TransformColumnTypes(table as table, typeTransformationsas 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 เฉพาะรายการที่กำหนด
ตัวอย่างวิธีเขียน M Code ใน Advanced Editor ให้สามารถแก้ใน Formula Bar ได้ง่ายๆ
let
SampleList = {1,2,-5,8,4,2,"a","b","A"},
Result=SampleList{0}
in
Result
ถ้าทำแบบนี้เราจะสามารถไปแก้สูตรใน Formula Bar ได้เลย
ฟังก์ชัน 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.Descending, Order.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.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 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
ซีรีส์นี้จะเป็นการเอาเนื้อหา M Code Language ของ Power Query บางส่วนในหนังสือ Excel Power Up และการเรียนรู้เพิ่มเติมของผมหลังจากนั้นมาผสมผสานกัน เนื้อหาจึงมีทั้งส่วนที่เหมือนและแตกต่างกับในหนังสือด้วยนะครับ อีกอย่างในหนังสืออาจ Copy Code ลำบาก ผมเลยเขียนเป็นบทความไว้ให้เลยดีกว่า จะได้ลอง Copy Paste สูตรไปเล่นได้ง่ายๆ ด้วย
เอาเป็นว่าใครอยากจะลงลึกเรื่อง M Code ก็อ่านซีรีส์นี้ได้เลย เพราะผมอยากให้มีคนไทยใช้เครื่องมือนี้เป็นเยอะๆ ครับ อีกอย่างมันใช้ได้ทั้งใน Excel และ Power BI เลย จึงมีประโยชน์มากๆ
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 เราต้องการผลลัพธ์อะไร” มันจะดูเองว่าจะเอาค่ามาจากไหน
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, ตาราง
ถ้าอ้างอิงด้วย table[ ชื่อ Field ] (แบบไม่ต้องมีเครื่องหมายคำพูด) จะได้ผลลัพธ์เป็น List ที่เป็นรายการของ Field นั้นๆ (มักใช้กับการอ้างอิงข้อมูลใน 1 คอลัมน์ในตาราง) เช่น =SampleTable[B] จะได้ List ข้อมูลในคอลัมน์ B ออกมา
ถ้าอ้างอิงด้วย table{เลข index} จะได้ผลลัพธ์ออกมาเป็น Record ซึ่งเป็นข้อมูลใน 1 แถวของตาราง เช่น =SampleTable{1} จะได้ Record แถวที่ 2 ของ Table ออกมา (อย่าลืมว่า Index แถวแรก เริ่มที่เลข 0)
= SampleTable{1}[B] // หมายถึงเอาแถวที่สอง (index1 คือแถว 2) และ เอา Field ชื่อ B
ซึ่งเราสามารถอ้างอิง Field ก่อนแถวก็ได้เช่นกัน เช่น = SampleTable[B]{1}
นอกจากนี้เรายังสามารถอ้างอิงจาก Value ใน Field ที่กำหนด เพื่อให้ได้ Record ออกมาได้ด้วย ด้วยรูปแบบ {[ชื่อ Field = ค่าที่ต้องการ]} เช่น
= SampleTable{[A=3]}
จะได้ Record ที่ Field A มีค่าเท่ากับเลข 3
อย่างไรก็ตาม วิธีอ้างอิงจาก Value จะใช้ได้ก็ต่อเมื่อมี Value นั้นๆ แค่ตัวเดียวใน Table เท่านั้น หากมีค่าซ้ำกันมันจะให้ผลเป็น Error ทันที
Tips : หากเรากด Drill Down ลงไปในช่องที่อยู่ในคอลัมน์ที่เคยสั่ง Remove Duplicates เอาไว้ก่อน จะทำให้ Power Query รู้ว่า Field นั้นมีค่าไม่ซ้ำกันแน่นอน สูตรที่ออกมาจะเป็นลักษณะของการอ้างอิงจาก Value ใน Field ที่กำหนด ได้โดยอัตโนมัติ
เช่น ตารางของผมเคยกด Remove Duplicates คอลัมน์ TXID มาก่อน จากนั้น ผมกด Drill Down ไปที่ Field ราคาต่อชิ้น ในแถวเดียวกับ TXID ที่มีค่าเป็น TX00004 จะได้ผลลัพธ์ออกมาดังนี้
การ Convert ประเภทข้อมูลที่ใช้บ่อย
อย่างที่บอกไปแล้วว่า เวลาจะใช้ Operator หรือฟังก์ชันต่างๆ ใน M Code เรื่องของ Data Type เป็นเรื่องที่ค่อนข้างซีเรียส ดังนั้นเราจะต้องมีการ Convert ประเภทข้อมูลให้ถูกต้องก่อนที่จะทำการคำนวณด้วย
ปกติเวลาเราสั่งให้ Power Query รวมทุกไฟล์ใน Folder เดียวกัน แม้มันจะเอาข้อมูลรวมกันได้ แต่ก็อาจจะมีปัญหาเรื่องชื่อคอลัมน์ได้ในอนาคต
สาเหตุเพราะ มันมีการระบุชื่อคอลัมน์ที่แตกออกมาอย่างชัดเจนในสูตร M Code ทำให้ หากในอนาคตมีคอลัมน์เพิ่มขึ้นหรือลดลง ก็จะมีปัญหาทันที (อย่างเช่นที่ผมบอกไว้ในหนังสือ Excel Power Up ว่า เมื่อไหร่ก็ตามที่มีการระบุชื่อคอลัมน์ในสูตร M Code นั้น จะอันตรายต่อการ Refresh ทันที)
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.