Dynamic Filepath for Excel Power Query Source

[lucausa75]

Dear All,

I have an Excel Power Query I need to make the Source as dynamic based on the value on MyPN!K2 cell as in the below screenshot


to have this line Source = Excel.Workbook(File.Contents("C:\TEST\MyFile\January2024.xlsm"), null, true), becaming Source = Excel.Workbook(File.Contents(MyPN!K2), null, true),

Any way to do it?
Thanks

 

[ExcelToDAX]

You have to give that cell a Named Range. Once you do that, you can do

=Excel.CurrentWorkbook()

and your named range should pop up. You can then bake it into that formula.

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Redirecting

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[sadath]

Path= Excel.CurrentWorkbook(){[Name="MyPN!K2"]}[Content]{0}[DATA SOURCE]
Source = Excel.Workbook(File.Contents(Path), null, true)

 

[lucausa75]

I got this error despite "MyPN" folder exists in my excel file

 

[RoryA]

As said previously, you need to name that cell, not use its address in PQ (that will not work).

 

[lucausa75]

As said previously, you need to name that cell, not use its address in PQ (that will not work).

How can I do this?

 

[RoryA]

Select the cell, then in the name box on the left of the formula bar, type a name for the cell - e.g. FilePath

 

[lucausa75]

Select the cell, then in the name box on the left of the formula bar, type a name for the cell - e.g. FilePath

Thanks for your help but I'm getting the same error as Excel is not recognize the name of the folder:

 

[RoryA]

You need to replace the "MyPN!K2" in that formula with whatever name you used - e.g. "FilePath"