How to refresh Power BI model from Excel file having updates and that was edited in query editor ?

Hi Michael,

Your Problem

You have:
- An Excel file that keeps getting new data or changes.
- You loaded it into Power BI.
- Then you used Power Query to clean it (remove top rows, rename columns, etc.).
- But now refresh doesn’t work when Excel file updates because the structure isn’t exactly the same.

 Goal

You want to refresh data automatically when the Excel file is updated — without doing manual steps in Power Query again and again.

Solution: Make Your Query Dynamic — Clean Once, Refresh Anytime

Here’s how you can solve it step-by-step with an example:

Example: Excel Sheet (SalesData)

You have an Excel sheet like this:
| A             | B             | C       |
|---------------|---------------|---------|
| Company XYZ   | Monthly Report|         |
| Date          | Product       | Sales   |
| 2024-01-01    | Apple         | 100     |
| 2024-01-02    | Banana        | 200     |
...

You only want the data from row 3 onwards, and only Date, Product, Sales columns.

Steps

1. Load Excel File to Power BI

Home > Get Data > Excel
Select your sheet (e.g., SalesData)

2. Clean the Data in Power Query (One-Time Setup)

In Power Query:
1. Remove top rows (e.g., first 2 rows)
2. Rename columns if needed
3. Keep only the needed columns (Date, Product, Sales)
4. Promote first row to headers
5. Close & Apply

Important: Don't use fixed column names or positions that may change later. Use steps like 'Keep Columns' by name so Power BI doesn’t break when structure changes slightly.

3. Refresh Easily Anytime

Now when your Excel file updates (new rows added, old ones changed):
- Just go to Power BI and click Refresh.
- It will automatically clean using the same Power Query steps you already set up.

Common Mistake to Avoid

Don’t reload the file or redo Power Query steps. That breaks the connection.

 Extra Tip (If Structure Often Changes)

If the structure changes too much, then yes — you can:
- Create a new sheet in Excel (e.g., CleanData)
- Use formulas like =FILTER() or =QUERY() to copy only the clean part
- Connect Power BI to CleanData sheet

This keeps the structure consistent for Power BI.

Clean your data once in Power Query and make it dynamic. After that, Power BI will keep using those same cleaning steps on the updated Excel file — no manual edits needed.