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

[McChem]

Hi !

I am new to PBI and want to use it for R+D purposes. I created a nice report that is taking data from a small Excel file (3 sheets with about 50 rows). After loading data into PBI I modified the data using query editor (removed many top rows, some bottom rows, intermediate rows, removed many columns, renamed columns etc.). So the data structure in my PBI model is different from the source Excel file. Report works fine but now comes the refresh / update ... Excel file is updated few times per week with fresh data, some data is overwritten, other data is added in the form of new rows. Data refresh does not work as the data structure is different between source file and PBI model. Online videos suggest to use append or merge functions in query editor but this is impractical (need to do manual work every time I have a small update in the source file 😞 ). 

How do I best solve this problem so I do not need to do any query editor work to refresh the model ? I could e. g. create a copy of the relevant data in additional Excel sheets that copy the data from my source sheets but have the same structure as the PBI model. That would work I guess ? But this way I merely shift the problem to Excel and I need to have duplicate Excel sheets, one for my raw data (that I may edit as I like) and one for automatic data transfer to PBI ? 

Can you advise on a good solution ? Thanks !

Michael

[McChem]

Success ! Actually I found the mistake, typical beginner´s mistake I guess ... In query editor I had removed some 7 of the last rows during initial data cleanup and this step was always performed after data refresh, so new rows added in the original Excel file were removed during the following query routine. I deleted the row removal step and now the update works perfectly. New rows are added automatically and old ones updated. 

Thanks a lot for your support and feedback. As a beginner I may need it again ... :-). 

[McChem]

Thanks a lot for the quick response, I will try so. 

[v-menakakota]

Hi  @McChem  ,

Thanks for reaching out to the Microsoft fabric community forum.

We really appreciate your efforts and for letting us know the update on the issue. Reply back after trying the solution provided by the @Alex_Sawdo  . I would also take a moment to thank  gaya3krishnan86 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

Best Regards, 
Menaka.
Community Support Team

[v-menakakota]

Hi @McChem ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

Thank you. 

[Alex_Sawdo]

Unfortunately, Power BI really likes structured data and can't easily process a source that is constantly changing. So, what you could do is: 

Offload the data you want to a seperate sheet (like you said) to ensure the formatting stays the same for upload.

Potentially add a column with Ignore/Include for each row in your current spreadsheet, and use Power Query to only allow the Include rows
Offload the information into a more stable format, like a database of some capacity. 

I'm sure others may have some better ideas, but these are what I usually end up doing when processing Excel data into Power BI. 

[McChem]

Hi Alex, 

thanks for your feedback. While I believe this could be a solution, it seems more workload as I would have to redo all the connections in my model to the new data source. So I will try the solution proposed below first, but I will keep yours in mind as plan B.  

[KhushbooSrivast · Accepted solution]

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.

[McChem]

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 !!

[McChem]

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 ?