Power Query Intermediate Lesson 8: Project – ERP Register to HSN-wise GST Summary

📎 This article includes 3 downloadable practice files ↓

📘 Power Query Intermediate Course · Lesson 8 of 8

ERP exports often contain revised invoices: the same invoice number with Rev 1 and Rev 2. Summing everything double-counts them. This project produces a clean HSN summary from such a register.

Steps

1. Import erp-register.csv and set types (Rev whole number, Date, Taxable, GST Rate).
2. Latest revision only: Group By Invoice with Table.Max(_, "Rev") (lesson 4), expand.
3. Add GST = Number.Round([Taxable] * [GST Rate], 2).
4. Group By HSN and GST Rate: count of invoices, sum of Taxable, sum of GST.

Latest = Table.Group(Source, {"Invoice"}, {{"Row", each Table.Max(_, "Rev"), type record}}),
Rows   = Table.ExpandRecordColumn(Latest, "Row", {"Rev", "Date", "HSN", "Taxable", "GST Rate"}),
Tax    = Table.AddColumn(Rows, "GST", each Number.Round([Taxable] * [GST Rate], 2), type number),
Summary = Table.Group(Tax, {"HSN", "GST Rate"},
    {{"Invoices", each Table.RowCount(_), Int64.Type},
     {"Taxable",  each List.Sum([Taxable]), type number},
     {"GST",      each List.Sum([GST]), type number}})

The register has 30 rows but only 25 invoices; the summary correctly counts 25.

Practice

Unzip the source files (e.g. to D:\PQ\). Try to build the query with the ribbon first, then compare with the M solution and the expected result. Every solution was run by Excel’s own Power Query engine before publishing.