SOLVED

Using the value of a cell as a source of a query

Copper Contributor

Hi everyone,

 

I'd like to have the value of a cell in my worksheet as a source of a query (it contain the address of another excel file). After hour of trying I didn't make it. Does anyone have an idea on how to do it ?

 

Thanks !

1 Reply
best response confirmed by natansansh (Copper Contributor)
Solution

@natansansh 

To make a cell as a Query Parameter for File Path, follow these steps...

 

1) Select the cell with File Path and in the Name Box, type FilePath. This will create a Named Range called FilePath which refers to the selected cell which contains the File Path.

Refer to the following image...

Named Range.jpg

In this case the path assumes that there is a file called SampleFile.xlsx saved at my Desktop.

Download the attached file SampleFile.xlsx, save it to the desired location and mention it's path in Cell B1.

 

2) Assuming you already have a working query, you only need to pass the File Path from the cell in the query so that the query reads the data from the mentioned file only.

Please find the attached called Dynamic File Path From Cell_Query Parameter.xlsx with already working query that write the data on Sheet2 from a simple Excel file called SampleFile.xlsx as per the example showed in the file.

Before replacing the File Path in the working query, the query looks like as shown below...

let
    Source = Excel.Workbook(File.Contents("C:\Users\sktneer\Desktop\SampleFile.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Sale", Int64.Type}})
in
    #"Changed Type"

 

And after replacing the file path in the above string with a parameter which reads the file path from the cell, the query would look like as shown below...

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Sale", Int64.Type}})
in
    #"Changed Type"

Pay attention to the following two lines from the above query to know what is to be changed in the original query...

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

 

Please find both the file attached.

 

1 best response

Accepted Solutions
best response confirmed by natansansh (Copper Contributor)
Solution

@natansansh 

To make a cell as a Query Parameter for File Path, follow these steps...

 

1) Select the cell with File Path and in the Name Box, type FilePath. This will create a Named Range called FilePath which refers to the selected cell which contains the File Path.

Refer to the following image...

Named Range.jpg

In this case the path assumes that there is a file called SampleFile.xlsx saved at my Desktop.

Download the attached file SampleFile.xlsx, save it to the desired location and mention it's path in Cell B1.

 

2) Assuming you already have a working query, you only need to pass the File Path from the cell in the query so that the query reads the data from the mentioned file only.

Please find the attached called Dynamic File Path From Cell_Query Parameter.xlsx with already working query that write the data on Sheet2 from a simple Excel file called SampleFile.xlsx as per the example showed in the file.

Before replacing the File Path in the working query, the query looks like as shown below...

let
    Source = Excel.Workbook(File.Contents("C:\Users\sktneer\Desktop\SampleFile.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Sale", Int64.Type}})
in
    #"Changed Type"

 

And after replacing the file path in the above string with a parameter which reads the file path from the cell, the query would look like as shown below...

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Sale", Int64.Type}})
in
    #"Changed Type"

Pay attention to the following two lines from the above query to know what is to be changed in the original query...

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

 

Please find both the file attached.

 

View solution in original post