Using parameter as data source

[MeganM11]

Hi,

For some reason Power Query does not allow me to use a parameter as a datasource when using the following code:

Source = Excel.Workbook(File.Contents(Model_datasource), null, true)

 Where "Model_datasource" is the parameter referencing a cell in the excel workbook.

In order to get it to work I have to use the following code:

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

Source = Excel.Workbook(File.Contents(#"FilePath"), null, true),

Where "Model_datasource" is the excel named cell.

Why is it not working as a parameter? Is it something it a privacy issue?

Please note that the folderpath is linked to a company sharepoint folder.

[v-cgao-msft]

Hi @MeganM11 ,

Please see the blog below and JM's reply below, I hope it helps.

Change the Power Query source based on a cell value

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[PwerQueryKees]

What is the content of the parameter? It should be a path to the excel file. 

And once you got that, you still have to find the cell in you workbook.

So, first solution gets you an error or en excel file as Source and second solution gets you some content from the workbook containing the query.

This is how it is supposed to work. 

Excel.Workbook and Excel.CurrwntWorkbook return the same type of date, so you should use their results the same...

[MeganM11]

The content of the parameter is an excel file path.

Thanks for the explanation. The weird thing is that the first query using Excel.Workbook and using the parameter directly without having the extra step of creating the FilePath works for my colleague linking to the same excel file.

That's why I am uncertain why the same query does not work for me without the work around.