REPORTING FIELD NOTES · 01
Prepare your weekly Excel report for Power BI.
Start with a stable source table, a clear definition of each row and agreed KPI calculations. Then confirm how Power BI will reach the updated files. These decisions give an automated report something reliable to repeat.
1. Separate the input from the report.
Keep the workbook your team already uses. Identify the underlying records that feed its summaries and charts. Put those records in a table with one header row and a consistent type of value in each column. Keep subtotals and grand totals outside that input table so the same value is not counted twice.
For a suitable range, choose Home → Format as Table, check the selected range and headers, then give the table a meaningful name such as Jobs. Agree the required column names before the build.
Microsoft’s Excel-to-Power-BI preparation guide (PDF) shows the table and import steps.
2. Say exactly what one row means.
In this example, one row represents one job. The job reference identifies it, the received date places it in a reporting week, and status records its position at the reporting cut-off.
| Job reference | Received date | Status |
|---|---|---|
| MV001 | Completed | |
| MV002 | Open | |
| MV003 | Completed | |
| MV004 | Open |
Four jobs received. Two completed. Two open.
Completion rate for these jobs: 2 ÷ 4 × 100 = 50%.
If a second note for MV001 creates another row, counting rows gives five. Establish whether those rows are job records or a history of events before deciding how to count jobs. Deleting repeated job references could remove valid history.
3. Write the calculation in plain English.
The example answers: “What share of the jobs received this week is complete at the cut-off?” Counting jobs completed during the week is a different measure and requires a completion date.
For every KPI, agree the date used, the included statuses, the denominator and the treatment of blanks or cancelled records. Keep one vocabulary for statuses. Record exceptions rather than quietly treating missing values as zero.
Check column types in Power Query: identifiers may need text to preserve leading zeroes, dates need an agreed interpretation, and numeric measures need suitable number types. Microsoft explains why column data types matter.
4. Agree the update routine.
Record where the source is kept, who updates it and when it is ready. Decide whether the same file is updated or a new file arrives each week. Renaming columns, moving a workbook or changing its layout can affect the connection and transformations.
Putting a file in SharePoint or OneDrive is one part of the setup. Scheduled refresh also needs the correct source connection, permissions, credentials and applicable licences. A source that Power BI cannot reach directly can require a gateway. Confirm these dependencies before promising an unattended refresh.
Use Microsoft’s Power BI refresh guidance when checking the environment.
5. Prepare a small review pack.
- The current report and the decisions it supports.
- A representative, anonymised source sample, including common exceptions.
- The KPI definitions and expected totals for one reporting period.
- The source owner, update routine and intended report users.
Agree a suitable transfer method before sharing business data. Keep passwords and confidential records out of the first enquiry. A source review helps confirm what the build can include and what needs resolving first.