GSTR 2B reconciliation in Power Query

Under Section 16(2)(aa) of the CGST Act, input tax credit is restricted to invoices communicated to the recipient in Form GSTR-2B. When claiming eligible credit in Table 4(A)(5) of Form GSTR-3B, any excess claim over the auto-generated statement triggers scrutiny under Rule 88D and intimation in Form DRC-01C. For teams managing hundreds of vendor vouchers in Excel, standard spreadsheet formulas often break down due to formatting differences, making Power Query a practical alternative.

If you are setting up your overall matching routine, review our GSTR-2B reconciliation and ITC matching guide for standard audit categorisation rules.

Understanding the data structure before building queries

The standard GSTR-2B Excel workbook downloaded from the GST portal contains separate tables including B2B, B2BA, CDNR, and CDNRA per official GSTN return guidelines. In contrast, your accounting software (such as Tally, Zoho Books, or SAP) exports a single purchase register containing inward invoices, debit notes, and credit notes in one consolidated table.

Directly joining these two sources without preparation causes join errors. Before building the merge query, both datasets must be reshaped into identical structures with clean matching keys.

Step-by-step Power Query merge workflow

To establish a clean reconciliation model, follow this four-step sequence in Microsoft Excel:

1. Import both data sources

1. Open Excel, navigate to Data → Get Data → From File → From Excel Workbook, and select your downloaded GSTR-2B file.
2. In the Navigator window, select the B2B sheet and click Transform Data to open the Power Query Editor.
3. Click New Source → File → Excel Workbook to load your internal Purchase Register.

2. Standardise data types and clean text

1. In both queries, select the supplier GSTIN column and click Transform → Format → Uppercase and Trim. This eliminates accidental trailing spaces entered during voucher creation.
2. Change the data type of the Invoice Number column to Text. Click Replace Current on the type detection step.
3. Clean special characters: use Transform → Replace Values to remove forward slashes, hyphens, or blank spaces if your internal naming conventions differ from your vendors.
4. Ensure tax columns (CGST, SGST, IGST, and Taxable Value) are set to Decimal Number. Round each tax column to two decimal places using Transform → Rounding → Round.

3. Create a unique matching key

A single invoice number is not unique across suppliers. Two distinct vendors can both issue invoice number 101. Therefore, you must construct a composite key combining the GSTIN and the invoice number.

1. Select the GSTIN column, hold Ctrl, and select the cleaned Invoice Number column.
2. Navigate to Add Column → Merge Columns.
3. Choose a separator such as a hyphen or hash character and name the new column MatchKey.
4. Repeat this exact step in both the GSTR-2B query and the Purchase Register query.

4. Merge queries to identify gaps

1. From the Home tab, click Merge Queries → Merge Queries as New.
2. Select Purchase Register as the primary table and GSTR-2B as the secondary table.
3. Click on MatchKey in both table previews to link them.
4. Select Full Outer (all rows from both) as the Join Kind to see all purchase vouchers alongside all portal records.
5. Expand the joined GSTR-2B columns to display vendor tax amounts and filing status side by side.

Worked example: resolving reconciliation differences

Consider a firm, Apex Engineering, with three purchase vouchers in their October purchase register compared against their October GSTR-2B statement. The figures show three common operational outcomes.

The total input tax credit recorded in books is ₹51,500.00 (calculated as ₹18,000.00 + ₹24,500.00 + ₹9,000.00). However, the GSTR-2B total is ₹27,000.00 (calculated as ₹18,000.00 + ₹9,000.00). Claiming the full ₹51,500.00 in Table 4(A)(5) of Form GSTR-3B creates an excess claim of ₹24,500.00, attracting an intimation under Rule 88D in Form DRC-01C. Power Query isolates the unfiled voucher PUR-102 so the accountant can withhold that claim until the supplier furnishes it in GSTR-1.

Where the Power Query setup breaks down

While Power Query handles recurring spreadsheet joins effectively, several routine business situations can disrupt automated refreshes:

• Folder path dependencies: If the model uses a hardcoded local file path (such as C:\Users\Accountant\Downloads\GSTR_2B.xlsx), moving files or running the sheet on another team member's computer breaks the data source step.
• Multi-sheet consolidation: The GSTR-2B workbook splits transactions into separate sheets for standard invoices (B2B) and amendments (B2BA). If your query imports only the B2B sheet, amendments are omitted, producing false discrepancies.
• Credit notes and negative values: Credit notes in the CDNR table have positive numbers in portal downloads but must be subtracted from inward credit in your net ITC ledger. Omitting a conditional transformation leads to distorted totals.
• Performance bottlenecks: Merging large datasets exceeding 20,000 rows across fuzzy matching logic can cause Excel to freeze or take several minutes to recalculate.

Vendor communication for missing invoices

When your Power Query merge identifies invoices present in your register but omitted from GSTR-2B, send a targeted intimation to your vendor before filing your monthly return:

Frequently asked questions

Last checked: 26 October 2026. Sources: CBIC Tax Information Portal, GSTN Portal Tutorial & Help.