An Excel file Power BI can read every month
Reliable reporting starts with a consistent source. Organize Excel columns, identifiers and monthly files so each new period does not require another manual cleanup.
What does one row represent?
Start by defining what a row means: one invoice line, one payment or a monthly target. Do not mix these levels. If an invoice has three product lines, repeating and summing its full amount on every line would overstate the total.
Keep one header row and explicit fields such as date, document ID, product ID, quantity and amount. Put merged cells and presentation subtotals on a separate display sheet.
Give identifiers and data types explicit rules
A product code such as 0017 should not become 17 if leading zeros are meaningful. Store identifiers as text, quantities as numbers and dates in a consistent form. A name is not a durable substitute for an ID.
For example, a branch name with a trailing space can look identical to the clean version. Apply a defined cleaning rule and surface unknown identifiers in an exception list instead of silently dropping their rows.
A new month should not mean a new schema
If each month has its own file, preserve column names and structure. Power Query supports combining similarly structured files. Define which files belong in the import: copies, temporary exports and older revisions can create duplicates.
A workbook may have separate January and February columns. For analysis, a date column and an amount column can be easier to maintain, with new periods adding rows rather than changing the schema.
Test with the next file
A successful first import is not enough. Add the next file, a new product and a missing value. Reconcile the row count, total and exception list. Microsoft’s Power Query guidance emphasizes appropriate data types and clearly organized transformation steps.
Keep a short completion guide and name its owner. If your team manually combines files every month, discuss two consecutive sample periods with DataStudio through an agreed secure channel. The repeated work becomes easier to identify.