CODEWITHSUNDEEP
excelsharepointpowerquery
Christopher Gürke
Christopher Gürke

Reputation: 3

Data Source Error when sharing Excel Power Query file with others

I have an Excel file that combines Excel templates from a sharepoint folder via Power Query. On my computer this works wonderfully. However, as soon as I make the Excel file available to other colleagues, they can no longer refresh the data. After some tests I found out that in Power Query the sample file does not point to the right place. Is there a way how I can parameterize the part and fix the sample file that way?

Upvotes: 0

Views: 548

Answers (1)

horseyride
horseyride

Reputation: 21318

In excel, pick a cell and give it a range name, like NameVariable

Enter your filepath like C:\temp\samplefile.xlsx in that named range

Then in powerquery, in home ... advanced editor ... add a formula that refers to that range name, similar to this:

Location = Excel.CurrentWorkbook(){[Name="NameVariable"]}[Content]{0}[Column1],

and change any hard coded references to the filename to use Location instead

As an example, change

let Source = Excel.Workbook(File.Contents("C:\temp\samplefile.xlsx"), null, true),

to be

let Location = Excel.CurrentWorkbook(){[Name="NameVariable"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(Location), null, true),

or if the range just had location instead of full filepath, then

Source = Excel.Workbook(File.Contents(Location&"samplefile.xlsx"), null, true),

Upvotes: 1

Related Questions