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]

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

[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. 

[KhushbooSrivast]

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.