Wilmar AI Cafe
← Back to menu

Week 3 · Excel: Power Query & VBA

A hands-on session where participants solve the same Excel automation tasks using two approaches: Power Query and AI-assisted VBA.

📅 Fri 31 Jul · 4–5 PM
📍 Wilmar office
🧭 3 hands-on tasks
⏱️ 60 min
Host · Xinran Liu

📦 Class materials

Download these before we start. Keep all three files in the same folder — the Power Query tasks import the two CSVs into the workbook. Save your own copy of the workbook so your queries and macros are kept.

📊Practice workbookxlsx 🧹01 · Messy tradescsv ➕02 · New trades to appendcsv

Menu

Follow along, compare Power Query with AI-assisted VBA, and practice using sample files.

0
Setup

Practice Files

Use a small folder of sample Excel/CSV files so everyone can practice importing, cleaning, appending, and calculating fields.

Folder structure
Week3_Practice_Files/ ├─ 01_Messy_Trades.csv ├─ 02_New_Trades_To_Append.csv └─ Week2_Workbook.xlsx
01_Messy_Trades.csv

This file should include messy trade data for cleaning. Include columns like:

  • Trade ID
  • Trade Date with mixed date formats
  • Product Code such as APAC-BUTTER-2026-001
  • Counterparty with extra spaces or inconsistent casing
  • Quantity stored as text
  • Price stored as text or with currency symbols
02_New_Trades_To_Append.csv

This file should use the same final structure as the cleaned data and contain a few new rows to append into the current workbook.

Keep the files small. The goal is to learn the workflow, not to process a huge dataset.
1
20 min

Import & Clean Data

Import messy trade data, change data types, clean text fields, and split a product code into useful columns.

Power Query approach
  • Go to Data > Get Data > From Text/CSV and select 01_Messy_Trades.csv.
  • Open the file in Power Query.
  • Trim and clean text columns such as Counterparty.
  • Change Trade Date to Date, Quantity to Number, and Price to Decimal Number.
  • Split Product Code by delimiter - into Region, Product, Year, and Sequence.
  • Rename columns clearly and load the result into Excel.
AI-assisted VBA approach

Ask AI to create a macro that imports the CSV, cleans fields, changes data types, and splits Product Code.

Create an Excel VBA macro that imports a CSV file selected by the user. The macro should place the data into a sheet called Cleaned_Trades, trim spaces in text fields, convert Trade Date to date, convert Quantity and Price to numbers, and split Product Code by hyphen into Region, Product, Year, and Sequence columns. Add comments to explain each step.
Compare
  • Power Query is easier to audit through Applied Steps.
  • VBA can be useful when you want buttons, automation, or custom workbook behavior.
2
20 min

Append One File to Current Excel

Import one new trade file from a folder and append it to the current cleaned Excel table.

Power Query approach
  • Load the current cleaned table into Power Query.
  • Import 02_New_Trades_To_Append.csv.
  • Apply the same cleaning steps and column names.
  • Use Append Queries to combine the existing table and new file.
  • Load the appended result back into Excel.
AI-assisted VBA approach

Ask AI to create a macro that lets the user select one file and append its rows to the existing table.

Create an Excel VBA macro that asks the user to select a CSV file. Append the rows from that CSV file to the bottom of the table named Trades_Table in the current workbook. Match columns by header name where possible. Do not duplicate the header row. After appending, format the table and show a message with the number of rows added.
Compare
  • Power Query is better when the same file structure will be refreshed regularly.
  • VBA is useful when users need a simple button to append one file on demand.
3
20 min

Create a Calculated Field

Add a calculated field such as Trade Value, Margin, or Risk Flag using both Power Query and AI-assisted VBA.

Power Query approach
  • Open the cleaned or appended table in Power Query.
  • Choose Add Column > Custom Column.
  • Create Trade Value using Quantity × Price.
  • Optional: create a Risk Flag if Trade Value is above a threshold.
  • Load the final table back into Excel.
Trade Value = [Quantity] * [Price] Risk Flag = if [Trade Value] > 100000 then "Review" else "OK"
AI-assisted VBA approach

Ask AI to create a macro that inserts formulas or calculates values directly in the Excel table.

Create an Excel VBA macro that adds two new columns to the table named Trades_Table. The first column should be Trade Value calculated as Quantity multiplied by Price. The second column should be Risk Flag. If Trade Value is greater than 100000, return Review; otherwise return OK. Format Trade Value as currency and autofit the columns.
Compare
  • Power Query keeps the calculation inside the transformation workflow.
  • VBA can add formulas, formatting, and user-facing automation in the workbook.