Forum Discussion
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 & ApplyImportant: 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.
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.
Hi KhushbooSrivast,
you have understood my problem perfectly :-). In fact I have already done most of the steps you propose but I am failing to make my query dynamic. Power BI simply does not understand what data I want to copy to my model.
My original Excel data looks like this
row 1: relevant column A, relevant column B ... not relevant column A .. relevant column C ...
row 2 - 6: 5 not relevant rows
row 7: copy of row 1
row 8: not relevant row
row 9 to 14: relevant data rows
row 15 + 16: not relevant rows
rows 17 - 70: relevant data rows
My cleaned data in Power BI (query editor) looks like this:
row 1: relevant column A, relevant colum B ... relevant column C ...
rows 2 to 51: relevant data rows
Although the title of the columns "relevant column A" etc. is the same as the column names in row 1 of my Excel sheet, Power BI does not update any data. How can I teach Power BI to take the relevant data from the Excel sheet ? Do I need to do any changes to my Excel file ? Or do I need to do all this in Query editor ?
I would be thankful if you could explain in more detail. Thanks !!
Hi, I just spent another good hour trying to resolve things, no success. I understand that Power BI should upload the original Excel file and perform all the cleaning steps orginally done by me (that I can see in query editor). However, even though I did not do any structural changes to the Excel file after I uploaded it to Power BI, the programm does not update anything. Also, if I edit the Excel file and rename columns in row 1 (my idea was to give row 1 the proper column names), data import does not work at all anymore as that changes the structure of the Excel file and all the power query steps do not work anymore. So I reversed this, arriving at the same situation again.
Connection to the source Excel file is OK, as e. g. when the Excel file is open or One Drive sync. pending Power BI will not open it and report an error message. So I assume the connection still works. Yet I don´t inderstand why Power BI fails to run the query editor steps properly and update the data although the basic structure of the source file was not changed and column and field names (and data types also) stay the same.
I may actually try plan B, cleaned up extra Excel sheet, but if the structure of the Excel sheet needs to be exactly the same as the database I want to have in Power BI, what is the function of the query editor in the first place ?