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"))
ซึ่งมีข้อดีกว่าเดิมคือเราไม่สูญเสียความพิมพ์เล็กพิมพ์ใหญ่ของข้อมูลนั้นๆ ไปนั่นเอง
เทคนิคการ Remove Duplicates ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่
ปกติแล้ว Remove Duplicates ใน Power Query จะสนใจพิมพ์เล็กพิมพ์ใหญ่ด้วย เช่น แบบนี้
เราจะแก้ไขสูตร M Code เล็กน้อย จากแบบนี้
= Table.Distinct(MyData, {"Name"})เป็นแบบนี้ นั่นคือเพิ่มเงื่อนไขใน equationCriteria ให้เป็น Comparer.OrdinalIgnoreCase มันจะไม่สนเรื่องพิมพ์เล็กพิมพ์ใหญ่ทันที
= Table.Distinct(MyData, {"Name",Comparer.OrdinalIgnoreCase})
เทคนิคการ Split ข้อมูลโดยใช้ Delimiter หลายแบบ
เทคนิคต่อไปเป็นการทำให้การ Split ของเราเจ๋งยิ่งขึ้น จากเดิมที่ Split ได้ด้วย Delimiter แค่ตัวเดียว คราวนี้เราจะสามารถ Split ด้วยทีละหลายๆ Delimiter ได้ด้วยการแก้ M Code เพียงเล็กน้อยเท่านั้น และใช้ได้กับการ Split เป็นหลายคอลัมน์หรือหลาย Row ก็ได้ เช่น
ข้อมูลผมอยากจะแบ่งด้วย , ; | คำว่า “และ”
ตอนแรกให้กด Split ตามปกติไปก่อน
แล้วจะได้สูตรประมาณนี้
= 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))และเราก็จะได้ผลลัพธ์ตามต้องการ
ซึ่งเราก็นำไปใช้กับกรณี 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.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each _[สินค้า]})ผลลัพธ์จะกลายเป็น List
หากต้องการทำให้ได้ List แบบไม่ซ้ำกัน ก็ใช้ List.Distinct มาช่วย
= Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each List.Distinct(_[สินค้า])})
พอเป็นแบบนี้เราจะสามารถกด Expand Column แล้วเลือก Extract Values ได้เลย ซึ่งมันจะเรียกใช้ฟังก์ชัน Text.Combine เพื่อรวมข้อมูลให้
ผลลัพธ์จะกลายเป็นแบบนี้
= 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 เอาไว้นับจำนวนบรรทัดแบบไม่ซ้ำ (โดยดูทั้งบรรทัดเท่านั้น)
แล้วถ้าเราจะนับแบบไม่ซ้ำ โดยนับเฉพาะคอลัมน์ลูกค้าจะทำยังไง?
ทางแก้ก็ทำได้ 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}})
ตรงนี้แปลว่าให้นับจำนวนแถว ของตารางที่ถูกทำให้ 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}})
เทคนิคการ Trim แบบให้ทำให้ช่องว่างตรงกลางเหลือแค่ 1 เคาะ
หลายคนอาจไม่ได้สังเกต ว่า Trim ใน Power Query นั้น จะตัดข้อมูลได้แค่ด้านหน้ากับด้านหลังเท่านั้น ไม่สามารถจะเอาข้อมูลช่องว่างตรงกลางออกไปได้ (ต่างจาก TRIM ใน Excel)
ดังนั้นถ้าอยากให้ใน 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)
เทคนิคการแก้ชื่อคอลัมน์แบบอ้างอิงตำแหน่งแทนชื่อ
สมมติว่าเรามีข้อมูลอยู่ แล้วต้องการจะแก้ชื่อคอลัมน์ใน Power Query
ปกติแล้วเราจะกด Double Click เพื่อแก้ชื่อไปเลย เช่น ผมแก้คอลัมน์แรก เป็น BillNumber
= Table.RenameColumns(Source,{{"TXID", "BillNumber"}})
สูตรมันจะบันทุกว่าให้เปลี่ยนชื่อคอลัมน์จาก TXID เป็น BillNumber
ซึ่งก็ดีนะ แต่จะมีปัญหาทันที ถ้าชื่อคอลัมน์ในต้นฉบับเปลี่ยนไปเป็นอันอื่นที่ไม่ใช่ TXID
หากเรามั่นใจว่าจะเปลี่ยนชื่อคอลัมน์แรกเสมอ (ให้เป็น TXID) ก็สามารถใช้ฟังก์ชัน Table.ColumnNames มาช่วยได้ ซึ่งมันจะได้ชื่อคอลัมน์ทั้งหมดในตารางที่อ้างอิงออกมาเป็น List เช่น ถ้าอยากได้ชื่อคอลัมน์ของตาราง Source ก็ทำแบบนี้
= Table.ColumnNames(Source)
ถ้าเราจะอ้างอิงไปที่ item แรกใน List สามารถอ้างอิงด้วยเลข index ในรูปแบบ ชื่อlist{index} โดย index เริ่มที่เลข 0
แปลว่า item แรกคือ
= Table.ColumnNames(Source){0}
ดังนั้นเอาไปแทนในสูตรเดิม = Table.RenameColumns(Source,{{“TXID”, “BillNumber”}}) จะได้แบบนี้ ซึ่งจะเปลี่ยนจากการ Hard Code คำว่า TXID ลงไปในสูตร ให้กลายเป็นไปหาชื่อคอลัมน์แรกสุดมาแทนนั่นเอง
= Table.RenameColumns(Source,{{Table.ColumnNames(Source){0}, "BillNumber"}})
เทคนิครวมข้อมูลทุก Sheet ใน Excel (ข้อมูลไม่เป็น Table)
การรวมข้อมูลใน Excel หลายๆ ตารางเข้าด้วยกัน หากว่าข้อมูลแต่ละตารางเป็น Table อยู่แล้วจะรวมได้ง่ายมาก ใน Navigation แค่อ้างอิงไปที่ตัวไฟล์ Excel แล้ว Filter ให้เหลือเฉพาะ Table ที่ต้องการแล้วกด Expand Table ออกมาก็จบเลย รายละเอียดลองดูในคลิปนี้
แต่ถ้าข้อมูลแต่ละตารางไม่ใช่ Table มันจะรวมตรงๆ ไม่ได้ เพราะ Data แต่ละอันยังไม่ได้รับการ Promote หัวตาราง จึงยังค้างเป็นคำว่า Column1, Column2, Column3…
ซึ่งพอสั่ง Expand ออกมาแล้วเอาข้อมูลแต่ละตารางมา Append กัน จะทำให้มีปัญหาหัวตารางกลายเป็น Data ซ้ำไปซ้ำมา ซึ่งต้องมานั่ง Filter ทิ้งอีก (และถ้าแต่ละตารางเรียงคอลัมน์ไม่เหมือนกันก็จะมีปัญหาทันที)
วิธีแก้ง่ายๆ คือ ก่อนจะถึงขั้นตอน 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)
เพียงเท่านี้ มันจะทำการ Promote Header แต่ละตารางก่อนแล้ว
ทำให้เวลา Expand ออกมาไม่มีปัญหาแม้ว่าจะเป็น Sheet ครับ
อย่างไรก็ตาม ตอน Expand มันจะจดจำชื่อคอลัมน์เอาไว้ด้วย ถ้าในอนาคตมีคอลัมน์เพิ่มมาอีก มันจะไม่ถูก Expand ออกมา ถ้าใครอยากทำให้มันอ่านชื่อคอลัมน์ใหม่ตลอด ให้ทำตามเทคนิคนี้ได้เลย จะมีการแก้สูตรเป็นแบบนี้
= Table.ExpandTableColumn(#"Removed Other Columns", "Data", List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_))))จบแล้ว
สำหรับเทคนิค Power Query ในบทความนี้ก็ขอจบเพียงเท่านี้ ใครอยากรู้เทคนิคในการทำอะไรอีกก็สามารถบอกได้เลยนะครับ
ใครที่สนใจเรียนรู้เรื่อง Power Query ผมมีเรื่องนี้ด้วยครับ
