SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-840293%22%20slang%3D%22en-US%22%3EUsing%20the%20value%20of%20a%20cell%20as%20a%20source%20of%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840293%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20have%20the%20value%20of%20a%20cell%20in%20my%20worksheet%20as%20a%20source%20of%20a%20query%20(it%20contain%20the%20address%20of%20another%20excel%20file).%20After%20hour%20of%20trying%20I%20didn't%20make%20it.%20Does%20anyone%20have%20an%20idea%20on%20how%20to%20do%20it%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-840293%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841186%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20value%20of%20a%20cell%20as%20a%20source%20of%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404501%22%20target%3D%22_blank%22%3E%40natansansh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20make%20a%20cell%20as%20a%20Query%20Parameter%20for%20File%20Path%2C%20follow%20these%20steps...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Select%20the%20cell%20with%20File%20Path%20and%20in%20the%20Name%20Box%2C%20type%20FilePath.%20This%20will%20create%20a%20Named%20Range%20called%20FilePath%20which%20refers%20to%20the%20selected%20cell%20which%20contains%20the%20File%20Path.%3C%2FP%3E%3CP%3ERefer%20to%20the%20following%20image...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20752px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130467i186ADE8602FBE6B2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Named%20Range.jpg%22%20title%3D%22Named%20Range.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20this%20case%20the%20path%20assumes%20that%20there%20is%20a%20file%20called%20SampleFile.xlsx%20saved%20at%20my%20Desktop.%3C%2FP%3E%3CP%3EDownload%20the%20attached%20file%20SampleFile.xlsx%2C%20save%20it%20to%20the%20desired%20location%20and%20mention%20it's%20path%20in%20Cell%20B1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Assuming%20you%20already%20have%20a%20working%20query%2C%20you%20only%20need%20to%20pass%20the%20File%20Path%20from%20the%20cell%20in%20the%20query%20so%20that%20the%20query%20reads%20the%20data%20from%20the%20mentioned%20file%20only.%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20called%20Dynamic%20File%20Path%20From%20Cell_Query%20Parameter.xlsx%20with%20already%20working%20query%20that%20write%20the%20data%20on%20Sheet2%20from%20a%20simple%20Excel%20file%20called%20SampleFile.xlsx%20as%20per%20the%20example%20showed%20in%20the%20file.%3C%2FP%3E%3CP%3EBefore%20replacing%20the%20File%20Path%20in%20the%20working%20query%2C%20the%20query%20looks%20like%20as%20shown%20below...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.Workbook(File.Contents(%22C%3A%5CUsers%5Csktneer%5CDesktop%5CSampleFile.xlsx%22)%2C%20null%2C%20true)%2C%0A%20%20%20%20Sheet1_Sheet%20%3D%20Source%7B%5BItem%3D%22Sheet1%22%2CKind%3D%22Sheet%22%5D%7D%5BData%5D%2C%0A%20%20%20%20%23%22Promoted%20Headers%22%20%3D%20Table.PromoteHeaders(Sheet1_Sheet%2C%20%5BPromoteAllScalars%3Dtrue%5D)%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Promoted%20Headers%22%2C%7B%7B%22Region%22%2C%20type%20text%7D%2C%20%7B%22Sale%22%2C%20Int64.Type%7D%7D)%0Ain%0A%20%20%20%20%23%22Changed%20Type%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20after%20replacing%20the%20file%20path%20in%20the%20above%20string%20with%20a%20parameter%20which%20reads%20the%20file%20path%20from%20the%20cell%2C%20the%20query%20would%20look%20like%20as%20shown%20below...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20FilePath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22FilePath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%0A%20%20%20%20Source%20%3D%20Excel.Workbook(File.Contents(FilePath)%2C%20null%2C%20true)%2C%0A%20%20%20%20Sheet1_Sheet%20%3D%20Source%7B%5BItem%3D%22Sheet1%22%2CKind%3D%22Sheet%22%5D%7D%5BData%5D%2C%0A%20%20%20%20%23%22Promoted%20Headers%22%20%3D%20Table.PromoteHeaders(Sheet1_Sheet%2C%20%5BPromoteAllScalars%3Dtrue%5D)%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Promoted%20Headers%22%2C%7B%7B%22Region%22%2C%20type%20text%7D%2C%20%7B%22Sale%22%2C%20Int64.Type%7D%7D)%0Ain%0A%20%20%20%20%23%22Changed%20Type%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EPay%20attention%20to%20the%20following%20two%20lines%20from%20the%20above%20query%20to%20know%20what%20is%20to%20be%20changed%20in%20the%20original%20query...%3C%2FP%3E%3CP%3E%3CSTRONG%3EFilePath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22FilePath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%3C%2FSTRONG%3E%2C%3CBR%20%2F%3ESource%20%3D%20Excel.Workbook(File.Contents(%3CSTRONG%3EFilePath%3C%2FSTRONG%3E)%2C%20null%2C%20true)%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20both%20the%20file%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
natansansh
Occasional Visitor

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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies