Connect Excel to Power BI Without Re-Keying Data

Monday reporting, in many SMEs, is one person exporting three files, copying columns into a master Excel workbook, fixing two formulas and sending a PDF. Two hours every week, and a wrong number now and then. Power BI exists precisely to remove that re-keying. Provided you connect the sources properly, once, and let the refresh do the rest.

Connect Excel to Power BI: the first source, and the trickiest

Almost everyone starts with an Excel file. That is normal: it is where sales targets, quote tracking and the schedule live. The good practice: put that file on SharePoint or OneDrive, never on a laptop or a network share. From Power BI Desktop, you connect to the file through its SharePoint address. Once the report is published to the Power BI service, it refreshes on its own, with no gateway, because the source is already in the cloud.

The trap is the shape of the file. Power BI wants tables: one header row, one row per record, no merged cells, no subtotals in the middle, no tab per month. Convert the range into an Excel table (Insert, then Table) and give it a name. That name is what Power BI will see, and it survives added rows.

Accounting, ERP, CRM, SQL: one method per source

• Accounting software: most online tools offer a scheduled export or an API. At minimum, a monthly export dropped into a SharePoint folder is enough for margin and overdue receivables.
• ERP or management software on a server on your premises: the source is usually a SQL database. Power BI connects to it natively; you need a data gateway installed on a machine that stays on.
• CRM: if it lives in Dataverse (Dynamics 365 or a Power Apps application), the connector is direct. Otherwise, the vendor almost always provides a connector or an export.
• SharePoint lists and Dataverse tables: ideal sources, in the cloud, no gateway, with permissions already managed.

The principle: every source is read where it lives. You do not export in order to re-import.

Scheduled refresh and gateway: what runs without you

Once the report is published, you schedule the refresh in the Power BI service. With a Pro licence, up to eight times a day; with Premium per user, up to 48. For leadership reporting, one refresh overnight and one at noon are enough.

For on-premises sources (SQL, files on a server), Microsoft's on-premises data gateway builds the bridge. It installs in an hour. Two field rules: install it on a server, not on the laptop of someone about to go on holiday; and run it under a service account, not under a person's account. A report that stops refreshing the day someone leaves the company is almost always a gateway or a connection tied to their account.

On licences: Power BI Desktop is free. Publishing and sharing a report takes a Pro licence per person, in the region of fifteen euros per user per month, to be checked against Microsoft's current price list. Count one for every reader, not just for the person who builds, unless your company has Premium or Fabric capacity.

Clean up in Power Query, once and for all

Your sources are never clean. A customer is "DUPONT SA" in the books and "Dupont" in the CRM; a date is stored as text; an amount contains a stray space. Power Query, the editor built into Power BI Desktop, is made for this: you describe the cleanup steps once (rename, set types, merge, filter), and they replay at every refresh. Nobody corrects anything by hand anymore.

The discipline that goes with it: one reference table per key concept. A customer table with a unique identifier, a product table, a calendar. Every source attaches to them. That is what a single source of truth means, and it is what lets you answer "this customer's revenue" with one number, not three.

When you need to tidy the data first

Sometimes the data you want to see does not exist anywhere reliable: it is keyed into a file shared by six people, with competing versions and deleted rows. Connecting Power BI to that file industrialises the mess. In that case, the right order is to tidy first: a SharePoint list for simple tracking, Dataverse as soon as the data is related or exceeds a few thousand rows, and a small Power Apps application for guided entry. Power BI then reads a clean base.

The signal that never lies: if you spend more time cleaning the source in Power Query than building visuals, it is the source that needs to change. The criteria are in Power Apps or Excel: when to make the leap, and it is often the starting point for a fixed-price data entry application.

To decide in which order to connect your sources, the scoping session starts with a map of your data: where it is, who keys it in, how often. The report and its connections are then built at a fixed price, set at scoping. The executive dashboard template shows what the result looks like once the sources are wired up.