Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
matg
New Member

Linking Excel files - Relative? Find and Replace

I have a Power Bi file that is linked to 10's of Excel files which are stored in the cloud and synced locally.

 

The file is edited by myself and a colleague. Everytime I open it I have to check the data sources are valid and if its been edited by my colleague relink all the Excel files which is time consuming and laborious. Of course once I save it and my collegaue then wants to edit it he has to go through the same proces.

 

Is there a way to either make the paths to the files relative rather than absolute if not is there a way to do a find and replace on the data source paths?

 

Thanks

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

My suggestion is to use a parameter based on a list with possible filepaths.

In this video I show an example in which files are read from a folder as determined by a parameter.

This example uses the "Combine Binaries" functionality that was fully changed with the November 2016 update of Power BI Desktop.

In the video I adjust "Sample Binary" so it will always dynamically point to the first file in the folder, as explained in this idea

 

Maybe you just read individual Excel files, but the concept remains the same: create a parameter and a list with possible filepaths, and replace all hard coded file paths with the parameter.

Specializing in Power Query Formula Language (M)

View solution in original post

1 REPLY 1
MarcelBeug
Community Champion
Community Champion

My suggestion is to use a parameter based on a list with possible filepaths.

In this video I show an example in which files are read from a folder as determined by a parameter.

This example uses the "Combine Binaries" functionality that was fully changed with the November 2016 update of Power BI Desktop.

In the video I adjust "Sample Binary" so it will always dynamically point to the first file in the folder, as explained in this idea

 

Maybe you just read individual Excel files, but the concept remains the same: create a parameter and a list with possible filepaths, and replace all hard coded file paths with the parameter.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.