replacing data without getting additional tables

This seems like a really easy problem but I am struggling.

I've created visuals on Power BI Desktop that are linked to Excel files on Sharepoint. I want to recreate these visuals but replace the data with a new Excel file from Sharepoint (essentially all clients want the same visuals but have different data files). I've successfully linked my first Power BI desktop file to the data on Sharepoint and now I would like to replace this data with my new data (but maintain the visuals I've already created).

Each time I try to replace the data I end up with a 2nd copy of all the excel tabs (ex: Outcomes and Outcomes (2) vs just a new Outcomes table with the refreshed data). I am certain there has to be a way to "replace" these and not just add duplicate values. Is there an easy way to do this?

Need Help

v-ssriganesh
5 months ago
Hi eliseglines,
Thank you for posting your query in the Microsoft Fabric Community Forum. Also, thanks to cengizhanarslan & grazitti_sapna for the earlier guidance.

Regarding the error in your screenshot: “OLE DB or ODBC Error: Type mismatch (DISP_E_TYPEMISMATCH)” This usually indicates that some values in the new Excel file do not match the data types expected by the existing Power Query steps. Even if the columns were not renamed, this can happen if the new data contains:

Text values in columns that previously contained numbers or dates

Blank or mixed values in numeric/date columns

Formatting differences introduced when rows were removed

Since the issue appears while loading tables like Outcomes, Treatment, and Cost Savings, it would be helpful to review whether any values in those columns differ in type from the original dataset.

Check the new Excel file for mixed data types or unexpected values in those columns, as that can trigger the type mismatch error during refresh.

Best regards,
Ganesh Singamshetty.

5 Replies

grazitti_sapna
Super User
5 months ago
Hi eliseglines,

if you want to replace just the file name and both the files have exact same no. of columns and exact same names, You can just change the source table name within same query.

Hit the gear icon next to the source in applied steps

and then just select the source table name, this is just an example of excel but you can just change the source in table selection step in your version for sharepoint

Alternatively, go to advance editor and change the location and file name in there.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
cengizhanarslan
Super User
5 months ago
Step 1) Open Power Query

Go to: Home → Transform Data

This opens Power Query Editor.

Step 2) Select the existing table

In the left panel, select the query (for example Outcomes).

Then in Applied Steps, click the Source step.

Step 3) Change the file path

Click the gear icon next to the Source step.

Replace the SharePoint / Excel file path with the new file location.

As long as the sheet names and structure are the same, all visuals will continue working.

Step 4) Apply changes

Click:

Close & Apply
Power BI will refresh the model using the new file, while keeping all visuals, relationships, and measures intact.
eliseglines
Frequent Visitor
5 months ago
Thank you! This makes sense. I'm trying to change the data (same columns, different raw data) but I am getting the following error message:
Any idea? My coworker deleted some rows of duplicate data but said other than that, no columns were changed. I don't recognize any of these error messages.
v-ssriganesh
Community Support
5 months ago
Hi eliseglines,
Thank you for posting your query in the Microsoft Fabric Community Forum. Also, thanks to cengizhanarslan & grazitti_sapna for the earlier guidance.

Regarding the error in your screenshot: “OLE DB or ODBC Error: Type mismatch (DISP_E_TYPEMISMATCH)” This usually indicates that some values in the new Excel file do not match the data types expected by the existing Power Query steps. Even if the columns were not renamed, this can happen if the new data contains:

Text values in columns that previously contained numbers or dates

Blank or mixed values in numeric/date columns

Formatting differences introduced when rows were removed

Since the issue appears while loading tables like Outcomes, Treatment, and Cost Savings, it would be helpful to review whether any values in those columns differ in type from the original dataset.

Check the new Excel file for mixed data types or unexpected values in those columns, as that can trigger the type mismatch error during refresh.

Best regards,
Ganesh Singamshetty.
v-sgandrathi
Community Support
4 months ago
Hi eliseglines,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

Thank you.