Most finance analysts in India spend 2 to 3 hours every day copying data from Tally, SAP, or bank portals, deleting empty rows, fixing date formats, and re-applying formulas. Power Query automates this entire sequence without writing a single line of code. The eight steps below are the standard pattern we ship on every Indian finance team engagement - from the smallest CA practice to the largest listed-group FP&A team.
The eight-step Power Query pattern
The pattern is the same for every source. The exact transformations vary by source, but the order of operations is fixed: source → headers → data types → unpivot → filter → calculated columns → merge → load. Skip a step and the next step is fragile. Follow the order and the query is auditable, reproducible, and fast.
01
Get Data from Files or Folders
Navigate to Data → Get Data → From File → From Folder. Point Power Query at your monthly report folder. Every new file dropped into that folder is automatically processed, cleaned, and combined. The folder can be a local path, a OneDrive folder, a SharePoint library, or a Google Drive folder mounted on Windows. The pattern handles 12 files a year or 365 files a year with the same query.
02
Clean and Transform Headers and Data Types
Use the Use First Row as Headers button, filter out blank/null rows, and set column data types explicitly (Date, Currency, Text, Whole Number). This prevents silent SUM errors caused by text-formatted numbers - the most common Power Query mistake in Indian finance teams. Right-click the column header → Change Type → select the right type. Do this for every column.
03
Unpivot Monthly Ledger Columns
If your input file has months spread horizontally across columns (Jan, Feb, Mar), select the fixed columns (Account, Category) and choose Transform → Unpivot Other Columns. This converts wide data into a clean, database-friendly layout ideal for Pivot Tables and Power Pivot. The unpivot step is the single most useful transformation in any Indian finance team's Power Query toolbelt.
04
Remove Duplicates and Filter Bad Rows
Right-click the column → Remove Duplicates on the invoice number or transaction ID. Use the filter dropdown to remove blank rows, header rows that snuck in from a bad export, and total rows. The Remove Top Rows / Remove Bottom Rows buttons handle the 0-indexed header rows that Tally sometimes inserts.
05
Add Calculated Columns
Add Column → Custom Column for any line that the source system does not provide. Common columns in Indian finance: GST component (CGST/SGST/IGST), TDS section (194C/194J/194H), financial year (based on the date), ITC eligibility flag. Each custom column is one M formula - auditable, reproducible, documented.
06
Merge Queries for Reconciliation
Home → Merge Queries → select the second query → choose the join columns → choose the join type (Left Outer for unmatched list, Inner for matched only). The output is a new column with Table values. Expand the columns you need. This is the pattern for GST reconciliation, intercompany matching, and customer-vendor master matching.
07
Load to Data Model or Excel Table
Click Close & Load To... and choose Only Create Connection or Add to Data Model. The raw data stays out of sheet cells, keeping the workbook lightweight and fast. Choose Table if you need the data visible on a sheet; choose Data Model if you need Power Pivot to build a star schema. For a 100K-row dataset, Data Model is the right answer.
08
Refresh and Schedule
The first refresh is manual (Data → Refresh All). Subsequent refreshes can be scheduled: in OneDrive, set the workbook to refresh hourly; in SharePoint, set the data source to refresh on a schedule; in Power BI, the refresh is built in. For a Tally export that arrives every morning at 8am, the refresh happens before the team sits down.
The five patterns Indian finance teams use most
01
Tally XML/JSON to consolidated Table
Export the daybook and the ledger from Tally to XML or JSON, drop in a shared folder, and Power Query From Folder reads every month. Unpivot the month columns, set data types, and load to the data model. The query runs in under 30 seconds for a year of Tally data. The same pattern works for Busy, Wings, and other Indian accounting software.
02
GSTR-2B vs purchase register reconciliation
Download the GSTR-2B JSON from the GST portal, load via Power Query From JSON, expand the invoice line items, and merge with the Tally purchase register on GSTIN + invoice number. The output is a Table with a match/mismatch flag. Conditional formatting turns mismatches red. The reconciliation that took 2 days takes 5 minutes.
03
Bank statement auto-import
Export the bank statement CSV from net banking, drop in the shared folder, Power Query reads the date and amount columns, and parses the description into vendor, type, and category. The output is a Table ready for cash flow analysis. Same pattern for credit card statements, payment gateway reports (Razorpay, Cashfree), and UPI aggregators.
04
Multi-entity trial balance consolidation
Each entity exports its trial balance to a separate file in a sub-folder. Power Query From Folder reads all of them, tags each row with the entity name (from the folder or file name), and consolidates into one Table. For groups with multiple currencies, add a FX rate table and a merge step. The output is the consolidated trial balance, refreshed on demand.
05
Refresh-to-report CFO dashboard
Power Query pulls the source data into the data model, Power Pivot builds a star schema with one fact table and a few dimension tables, the Dashboard tab uses Pivot Tables and Slicers, and the Checks tab uses DAX measures. The CFO clicks Refresh All on Monday morning and the dashboard updates in under a minute. The standard engagement for a senior finance team.
The five most common mistakes
Every senior Excel consultant has made every one of these mistakes at least once. The list below is the one we share with junior analysts on day one of every engagement. The senior is the one who catches them on the mid-build review.
Text-formatted numbers
If the source CSV has currency symbols or thousand separators in the amount column, Power Query loads them as text. The SUM at the end returns 0. The fix: Change Type on every numeric column. Do this in step 2 of the query, before any other transformation.
Headers in row 2
Some Indian systems export with a company name in row 1 and headers in row 2. The fix: Remove Top Rows → 1, then Use First Row as Headers. Do this immediately after the source step.
Date formats that change mid-file
A Tally export might have dates as DD/MM/YYYY in one row and MM/DD/YYYY in another. The fix: parse the date with a custom format (Date.FromText with the right Culture) and validate against a known sample. If the format is genuinely inconsistent, split the file and process each chunk separately.
Merging on the wrong column
A common mistake is to merge on the customer name when the master has a customer ID. The fix: always merge on the ID, not the name. The senior should validate the merge output by spot-checking five rows against the source.
Refreshing a 10M-row query in Excel
If the data is genuinely 10M+ rows, Excel is the wrong tool. The fix: aggregate the data in Power Query (Group By, with SUM/AVG/COUNT) before loading. A 10M-row Table becomes a 10K-row summary. The dashboard refreshes in seconds and the workbook stays under 5MB.
From Power Query to a real BI stack
Power Query is the entry point to a modern Excel BI stack. The full stack is: Power Query (extract and transform) → Data Model (star schema) → Power Pivot (DAX measures) → Pivot Tables and Pivot Charts (visualisation) → Slicers (interaction) → Dashboard tab (one-page summary). The CFO clicks Refresh All on Monday morning and the entire stack updates in under a minute. The stack is what a senior finance team uses; the absence of the stack is what a junior team uses to rebuild the same report every month.
