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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
MeganM11
New Member

Using parameter as data source

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.

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

Hi @MeganM11 ,

 

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

vcgaomsft_0-1725342798542.png

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
Impactful Individual
Impactful Individual

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

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.