Excel Power Query Basics: Split Columns, Merge, and Append in 10 Minutes

Use Excel Power Query basics to clean messy CSV files with just a few clicks. Complete column splitting, trimming, data type assignment, Append, Merge, Group By, and Pivot operations primarily with mouse clicks, then automatically reprocess the data with a refresh next time.

Quick Fix: Turn a Messy CSV into a Clean Table in 10 Minutes

1. Import the file through Data > Get Data > From Text/CSV, then click Transform Data.
2. Use Transform > Split Column to split by pipes or commas, then apply Trim.
3. Set data types (whole number/date), then remove errors and filter the data.
4. Use Home > Close & Load to load it into a table. Next time, simply click Refresh.

Why Power Query?

• A click-based pipeline with excellent reproducibility and maintainability
• Append = vertical combination; Merge = horizontal join
• Fast performance on large datasets with Query Folding

Practical Example

Two Sample CSV Files (August/September)

order_id, order_date , customer , phone , item | sku , qty , revenue , note
1001,2025-08-30 , Kim , 010-1234-5678 , T-Shirt | T001 , 2 , 38000 , first-time
1002,2025-08-30 , Lee , (+82)10 5678 1234 , Pants | B014 , 1 , 35000 , 
1003,2025-08-31 , Park , 010.4321.0000 , Shoes | S101 , 1 , 120000 , urgent

order_id, order_date , customer , phone , item | sku , qty , revenue , note
2001,2025-09-01 , Kim , +82 (10) 7777 8888 , Shoes | S201 , 1 , 140000 , 
2002,2025-09-02 , Choi , 82-10-7777-9999 , Jacket | O010 , 1 , 159000 , return?

Append, Split, and Set Data Types

Append the files by combining a folder, then split item | sku, apply Trim, and change the data types.

Merge, Group By, and Pivot

Add customer segments with Merge, use Group By for daily totals, and create a category matrix with Pivot.

M Code Example

let
  Source = Folder.Files("C:Dataorders"),
  Filtered = Table.SelectRows(Source, each Text.EndsWith([Extension], ".csv")),
  Combine = Csv.Document(Filtered{0}[Content],[Delimiter=",",Encoding=65001,QuoteStyle=QuoteStyle.None]),
  Header = Table.PromoteHeaders(Combine,[PromoteAllScalars=true]),
  Split = Table.SplitColumn(Header,"item | sku",Splitter.SplitTextByDelimiter("|",QuoteStyle.Csv),{"item","sku"}),
  Trimmed = Table.TransformColumns(Split,{{"customer",Text.Trim,type text},{"item",Text.Trim,type text},{"sku",Text.Trim,type text}}),
  Types = Table.TransformColumnTypes(Trimmed,{{"order_id",Int64.Type},{"order_date",type date},{"qty",Int64.Type},{"revenue",Int64.Type}}),
  PhoneDigits = Table.AddColumn(Types,"phone_digits", each Text.Select([phone],{"0".."9"}), type text),
  Grouped = Table.Group(PhoneDigits, {"order_date"}, {{"Revenue", each List.Sum([revenue]), Int64.Type},{"Orders", each List.Sum([qty]), Int64.Type}})
in
  Grouped

Alternatives, Notes, and Checklist

• Power Query is stronger than formulas for large datasets and multi-file combinations; use dynamic arrays alongside it for immediate analysis within worksheets.
• Trim and set data types for key columns before using Merge.
• For folder combinations, add only new files and refresh.

Troubleshooting

Conclusion

With Excel Power Query basics alone, you can automate recurring monthly tasks. The next article covers advanced techniques such as automated folder processing, parameters, and performance optimization.