Source from cell value in Power Query

[gelu]

Hello Connoisseurs,

I am trying to get a cell value instead of the absolute address in the code below.

let
Source = Excel.Workbook(File.Contents("C:\AAD\Sheet1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = .....
in
#"Changed Type"


I have imported a table and drilled down so as to get the address "C:\AAD\Sheet1.xlsx" available in Power Query.
Here I am stuck.

Can anyone help?

Thank you,

GT

 

[Alex Blakenburg]

The easiest is if you give the cell a range name.
In the link below the file name and file path are in a different cell each with a range name.
If you are happy to put the Path & Filename in the 1 cell and give the cell the Range Name "FileName" then the code would look like this:
Replace this:

Source = Excel.Workbook(File.Contents("C:\AAD\Sheet1.xlsx"), null, true),

With this:

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

Note: The M Variable Name can be but does not need to be the same name as the Named Range

For further reading:
Change the Power Query source based on a cell value - Excel Off The Grid

 

[gelu]

Thank you Alex, a charm!

 

[Alex Blakenburg]

You're welcome. Glad I could help.