Why the layout of an export matters
Many reports start life as an export from an accounting, sales or stock system, which someone then tidies in Excel. That tidying is often where the time goes each month. It is also where small differences creep in, because the steps are done by hand and are rarely written down.
Power BI can load Excel workbooks and CSV files directly. It works best when each file is laid out as a plain table that looks the same every time it is exported.
One table, one header row
The single most helpful change is to keep each sheet or file as one table with one row of column headings at the top. Report-style layouts are easy to read on screen but hard to load reliably.
- Avoid titles, logos or notes above the header row.
- Avoid merged cells, blank spacer columns and subtotal rows inside the data.
- Keep one row per transaction, line or item, not one row per group.
- Keep column names the same from month to month, including spelling and spacing.
Dates, numbers and codes
Most loading problems come from values that look right to a person but are stored inconsistently.
- Store dates as real dates in one format, not as text, and check whether the system exports day-first or month-first dates.
- Store amounts as numbers without currency symbols or thousands separators typed in as text.
- Say whether amounts include or exclude VAT, ideally in the column name.
- Keep customer, product and account codes as codes, and treat codes with leading zeros as text so the zeros are not lost.
Excel or CSV?
Both work. A CSV file holds a single table and no formatting, which makes it predictable to load. An Excel workbook can hold several sheets and is easier for people to check before it is loaded. What matters more is that the same export, with the same layout, is produced the same way every month.
If a file has to be edited by hand before it can be used, it is worth recording what the edit is. That step can often be moved into the reporting itself, so the original export is loaded unchanged.
Keep files in a predictable place
Power BI refreshes from a location, not from whoever saved the file last. Agree one shared folder, a consistent file name or naming pattern, and who is responsible for placing the export there each month. Avoid keeping the only copy on a personal laptop or in an email attachment.
When to move beyond exports
Exports are a reasonable starting point, and some sources will only ever be available that way. Where a system offers a supported connection, such as the Business Central APIs, reading the data directly removes the manual export step. Spreadsheets and direct connections can sit side by side in the same reporting model.