--- title: เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ url: https://www.thepexcel.com/adjust-m-code-power-query/ type: post date: 2020-12-20 updated: 2025-12-22 author: Sira Ekabut categories: [Power Query] tags: [Text.Lower, Table.RenameColumns, power query, Text.Split, Table.SplitColumn, m code, Excel.Workbook, Table.TransformColumns, Table.Group, File.Contents, Table.RowCount, List.Distinct, Table.SelectRows, List.Union, List.Count, Splitter.SplitTextByAnyDelimiter, Table.ColumnNames, Splitter.SplitTextByDelimiter, List.Select, Table.Distinct, List.Transform, Table.ExpandListColumn, Text.Combine, Table.ExpandTableColumn] --- # เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ Power Query นั้นมาพร้อมกับเครื่องมือสำเร็จรูปที่[ช่วยให้เราจัดการข้อมูลได้ง่ายขึ้นมาก](https://www.thepexcel.com/10-power-query-basic-transform/) แต่ก็ยังมีอีกหลายสถานการณ์ที่เครื่องมือสำเร็จรูปไม่ตอบโจทย์เรา 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")) ``` ![1](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-010.png) ซึ่งมีข้อดีกว่าเดิมคือเราไม่สูญเสียความพิมพ์เล็กพิมพ์ใหญ่ของข้อมูลนั้นๆ ไปนั่นเอง ## เทคนิคการ Remove Duplicates ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่ ปกติแล้ว Remove Duplicates ใน Power Query จะสนใจพิมพ์เล็กพิมพ์ใหญ่ด้วย เช่น แบบนี้ ![2](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-015.png) เราจะแก้ไขสูตร M Code เล็กน้อย จากแบบนี้ ``` = Table.Distinct(MyData, {"Name"}) ``` เป็นแบบนี้ นั่นคือเพิ่มเงื่อนไขใน equationCriteria ให้เป็น Comparer.OrdinalIgnoreCase มันจะไม่สนเรื่องพิมพ์เล็กพิมพ์ใหญ่ทันที ``` = Table.Distinct(MyData, {"Name",Comparer.OrdinalIgnoreCase}) ``` ![3](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-016-1024x268.png) ## เทคนิคการ Split ข้อมูลโดยใช้ Delimiter หลายแบบ เทคนิคต่อไปเป็นการทำให้การ Split ของเราเจ๋งยิ่งขึ้น จากเดิมที่ Split ได้ด้วย Delimiter แค่ตัวเดียว คราวนี้เราจะสามารถ Split ด้วยทีละหลายๆ Delimiter ได้ด้วยการแก้ M Code เพียงเล็กน้อยเท่านั้น และใช้ได้กับการ Split เป็นหลายคอลัมน์หรือหลาย Row ก็ได้ เช่น ข้อมูลผมอยากจะแบ่งด้วย , ; | คำว่า “และ” ตอนแรกให้กด Split ตามปกติไปก่อน ![4](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-007-1024x629.png) แล้วจะได้สูตรประมาณนี้ ``` = 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)) ``` และเราก็จะได้ผลลัพธ์ตามต้องการ ![5](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-008-1024x279.png) ซึ่งเราก็นำไปใช้กับกรณี 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}}}), "ผู้เล่น") ``` ![6](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-009-1024x367.png) ## เทคนิค Group by แล้วรวมข้อความที่เป็น Text คั่นด้วย Delimiter สมมติเรามีข้อมูลดังนี้ ![7](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-011-1024x526.png) เราจะ Group by ตามผู้ขาย แต่อยากได้รายชื่อสินค้าเอามารวมแล้วคั่นด้วยเครื่องหมาย / จะทำยังไง? เราสามารถใช้การ Group ที่เรียกว่า All Rows มาช่วยได้ เช่น ![8](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-018-1024x458.png) จากนั้นจะได้ผลลัพธ์แบบนี้ ![9](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-019-1024x607.png) concept คือเหมือนเดิม ว่า _ คืออ้างอิงตารางทั้งตารางที่ผ่านการ Group มาแล้ว ดังนั้นผมสามารถแก้สูตรให้ย่อๆ เหลือแค่นี้ก็ได้ ซึ่งก็จะได้ผลลัพธ์เหมือนเดิมเลย ``` = Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each _}) ``` ![10](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-020.png) จากนั้นผมจะอ้างอิงไปที่คอลัมน์สินค้า ซึ่งทำได้โดยการเขียนว่า _[ชื่อคอลัมน์] เช่น ``` = Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each _[สินค้า]}) ``` ผลลัพธ์จะกลายเป็น List ![11](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-021.png) หากต้องการทำให้ได้ List แบบไม่ซ้ำกัน ก็ใช้ List.Distinct มาช่วย ``` = Table.Group(#"Changed Type", {"ผู้ขาย"}, {"all", each List.Distinct(_[สินค้า])}) ``` ![12](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-022.png) พอเป็นแบบนี้เราจะสามารถกด Expand Column แล้วเลือก Extract Values ได้เลย ซึ่งมันจะเรียกใช้ฟังก์ชัน Text.Combine เพื่อรวมข้อมูลให้ ![13](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-023.png) ผลลัพธ์จะกลายเป็นแบบนี้ ``` = Table.TransformColumns(#"Grouped Rows", {"all", each Text.Combine(List.Transform(_, Text.From), "/"), type text}) ``` ![14](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-024-1024x160.png) ## เทคนิคการ Group by แล้วนับค่าในคอลัมน์แบบไม่ซ้ำ จาก Data ในเทคนิคที่แล้ว หากเราจะสั่ง Group by ผู้ขาย และจะนับจำนวนลูกค้าแบบไม่ซ้ำกัน เราจะพบว่าไม่สามารถใช้ Count Distinct Rows ใน Group by เพื่อเลือกคอลัมน์ลูกค้าได้สาเหตุเป็นเพราะ Count Distinct Rows ใน Group by เอาไว้นับจำนวนบรรทัดแบบไม่ซ้ำ (โดยดูทั้งบรรทัดเท่านั้น) ![15](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-012-1024x467.png) แล้วถ้าเราจะนับแบบไม่ซ้ำ โดยนับเฉพาะคอลัมน์ลูกค้าจะทำยังไง? ### ทางแก้ก็ทำได้ 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}}) ``` ![16](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-013-1024x165.png) ตรงนี้แปลว่าให้นับจำนวนแถว ของตารางที่ถูกทำให้ 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}}) ``` ![17](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-014-1024x161.png) ## เทคนิคการ Trim แบบให้ทำให้ช่องว่างตรงกลางเหลือแค่ 1 เคาะ หลายคนอาจไม่ได้สังเกต ว่า Trim ใน Power Query นั้น จะตัดข้อมูลได้แค่ด้านหน้ากับด้านหลังเท่านั้น ไม่สามารถจะเอาข้อมูลช่องว่างตรงกลางออกไปได้ (ต่างจาก TRIM ใน Excel) ![18](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-029.png) ดังนั้นถ้าอยากให้ใน 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) ![19](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-030-1024x286.png) ## เทคนิคการแก้ชื่อคอลัมน์แบบอ้างอิงตำแหน่งแทนชื่อ สมมติว่าเรามีข้อมูลอยู่ แล้วต้องการจะแก้ชื่อคอลัมน์ใน Power Query ปกติแล้วเราจะกด Double Click เพื่อแก้ชื่อไปเลย เช่น ผมแก้คอลัมน์แรก เป็น BillNumber ``` = Table.RenameColumns(Source,{{"TXID", "BillNumber"}}) ``` ![20](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-025.png) สูตรมันจะบันทุกว่าให้เปลี่ยนชื่อคอลัมน์จาก TXID เป็น BillNumber ซึ่งก็ดีนะ แต่จะมีปัญหาทันที ถ้าชื่อคอลัมน์ในต้นฉบับเปลี่ยนไปเป็นอันอื่นที่ไม่ใช่ TXID หากเรามั่นใจว่าจะเปลี่ยนชื่อคอลัมน์แรกเสมอ (ให้เป็น TXID) ก็สามารถใช้ฟังก์ชัน Table.ColumnNames มาช่วยได้ ซึ่งมันจะได้ชื่อคอลัมน์ทั้งหมดในตารางที่อ้างอิงออกมาเป็น List เช่น ถ้าอยากได้ชื่อคอลัมน์ของตาราง Source ก็ทำแบบนี้ ``` = Table.ColumnNames(Source) ``` ![21](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-026.png) ถ้าเราจะอ้างอิงไปที่ item แรกใน List สามารถอ้างอิงด้วยเลข index ในรูปแบบ ชื่อlist{index} โดย index เริ่มที่เลข 0 แปลว่า item แรกคือ ``` = Table.ColumnNames(Source){0} ``` ![22](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-027.png) ดังนั้นเอาไปแทนในสูตรเดิม = Table.RenameColumns(Source,{{“TXID”, “BillNumber”}}) จะได้แบบนี้ ซึ่งจะเปลี่ยนจากการ Hard Code คำว่า TXID ลงไปในสูตร ให้กลายเป็นไปหาชื่อคอลัมน์แรกสุดมาแทนนั่นเอง ``` = Table.RenameColumns(Source,{{Table.ColumnNames(Source){0}, "BillNumber"}}) ``` ![23](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-028-1024x467.png) ## เทคนิครวมข้อมูลทุก Sheet ใน Excel (ข้อมูลไม่เป็น Table) การรวมข้อมูลใน Excel หลายๆ ตารางเข้าด้วยกัน หากว่าข้อมูลแต่ละตารางเป็น Table อยู่แล้วจะรวมได้ง่ายมาก ใน Navigation แค่อ้างอิงไปที่ตัวไฟล์ Excel แล้ว Filter ให้เหลือเฉพาะ Table ที่ต้องการแล้วกด Expand Table ออกมาก็จบเลย [รายละเอียดลองดูในคลิปนี้](https://youtu.be/7Q910bPPJ8U) แต่ถ้าข้อมูลแต่ละตารางไม่ใช่ Table มันจะรวมตรงๆ ไม่ได้ เพราะ Data แต่ละอันยังไม่ได้รับการ Promote หัวตาราง จึงยังค้างเป็นคำว่า Column1, Column2, Column3… ![24](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-001-1024x486.png) ซึ่งพอสั่ง Expand ออกมาแล้วเอาข้อมูลแต่ละตารางมา Append กัน จะทำให้มีปัญหาหัวตารางกลายเป็น Data ซ้ำไปซ้ำมา ซึ่งต้องมานั่ง Filter ทิ้งอีก (และถ้าแต่ละตารางเรียงคอลัมน์ไม่เหมือนกันก็จะมีปัญหาทันที) ![25](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-002-1024x417.png) วิธีแก้ง่ายๆ คือ ก่อนจะถึงขั้นตอน 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) ``` ![26](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-003-1024x291.png) เพียงเท่านี้ มันจะทำการ Promote Header แต่ละตารางก่อนแล้ว ![27](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-004-1024x600.png) ทำให้เวลา Expand ออกมาไม่มีปัญหาแม้ว่าจะเป็น Sheet ครับ ![28](https://www.thepexcel.com/wp-content/uploads/2020/12/PQ-InterM-005-1024x437.png) อย่างไรก็ตาม ตอน Expand มันจะจดจำชื่อคอลัมน์เอาไว้ด้วย ถ้าในอนาคตมีคอลัมน์เพิ่มมาอีก มันจะไม่ถูก Expand ออกมา [ถ้าใครอยากทำให้มันอ่านชื่อคอลัมน์ใหม่ตลอด ให้ทำตามเทคนิคนี้](https://www.thepexcel.com/dynamic-expand-column-power-query/)ได้เลย จะมีการแก้สูตรเป็นแบบนี้ ``` = 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 --- _Source: [https://www.thepexcel.com/adjust-m-code-power-query/](https://www.thepexcel.com/adjust-m-code-power-query/)_