Belebala

2019-01-28, 04:33 PM

Hello,

I'm new to power query and would like some help on import workbook with dynamic file location.

I have a an excel file with power query saved under W:\Excel\Jan and the source file is saved under W:\Excel\Jan\Source. I copy the same file to another location for the next month (W:\Excel\Feb) from a new source file W:\Excel\Feb\Source.

I don't want to change my source from my queries every month, is there anyway to have a dynamic source location?

I have googled and it suggested to create a parameter table with the value of the source location =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) and create a ParameterValue query of = Table.TransformColumnTypes(Source,{{"ParameterValue", type text}}). Now I'm stuck. In my query, I changed my source to the following but it is giving me an error or "The important ParameterQuery matches no exports":

Let

Source = Excel.Workbook(File.Contents(ParameterQuery[ParameterValue]{0})),

I am not a tech savy and have no idea how it should work. Much appreciated if anyone could help.

Thanks.

Belebala

Paul_Christie

2019-01-29, 02:01 PM

There are several ways to do what you want, the following is the method that Ken Puls uses in his excellent book M is for (Data) Monkey

In the workbook where you have your PowerQuery, create an Excel Table, name it 'ParameterTable' with two columns 'Parameter' and 'Value' and add a parameter in the first row of the table.

Enter FilePath in the first row of the Parameter column and enter a value path name (e:\pq) in the first row of the value column.

Create a blank query and select 'View' and 'Advanced Editor'

Past the following

// fnGetParameter function written by Ken Puls, FCPA, FCMA, MS MVP (Excel)

// Original source can be found at the following URL:

// http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

 

// To use the function you must have a table called "Parameters" in your workbook

// The header row must contain a "Parameter" and a "Value" column (with those names)

 

// Data rows should list the paramter name in the first column and the value of that

// parameter in the second column

 

// Provided you save this query under the name "fnGetParameter", you can then reference

// it from other queries as follows:

//

// =fnGetParameter("your_parameter_name")

 

// One useful sample to return the current folder:

// Parameter Name: File Path

// Parameter Value: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

// Call as "fnGetParamater("File Path")

 

(ParameterName as text) =>

let

ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],

ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),

Value=

if Table.IsEmpty(ParamRow)=true

then null

else Record.Field(ParamRow{0},"Value")

in

Value

 

save and name the query as fnGetParameter in your existing query edit the existing source line so it looks like

 

Source = Folder.Files(fnGetParameter("FilePath")),

 

You can now use the table created above as a 'variable' data source.

 

Paul Christie

Belebala

2019-01-29, 03:01 PM

Hi Paul Christie,

 

After I replaced the code of fnGetParameter, it asked me to invoke my function and the function return null. Is it normal?

 

88278828

Paul_Christie

2019-01-30, 10:36 AM

If you enter 'FilePath' and then press the Invoke button it should return the value you have for that parameter in the table.

 

Paul

Belebala

2019-01-30, 01:39 PM

Ok, I have invoke the File Path as a parameter. Now I get an error when I tried to add a custom column of File Path in MyTable query. I'm sorry for so many questions.

8829

Paul_Christie

2019-01-30, 01:48 PM

Is it possible for you to post the workbook so I can look at it?

Belebala

2019-01-30, 02:16 PM

Sure. That would be very helpful if you could review it.

 

Thank you.

Paul_Christie

2019-01-30, 02:37 PM

The fnGetParameter bit works perfectly it's the next bit that is causing you the problem. I'll see what I can do with one of my examples. What's your location from a time zone point of view relative to the UK?

Belebala

2019-01-30, 04:50 PM

My time zone is GTM-6.

 

Thanks a bunch for looking into this for me.

Paul_Christie

2019-01-30, 06:18 PM

I've attached a full example using anonomised data

 

8832

 

Hope this helps

Belebala

2019-01-30, 06:39 PM

I got a question, why my query fnGetParameter display the table icon not function icon? Did I do something wrong?

 

8833

Paul_Christie

2019-01-30, 06:50 PM

Without being able to watch what you were doing I can't be sure. What I did was create a blank query, pasted the function code in, named the function as fnGetParameter and saved it as connection only.

 

Paul

Belebala

2019-01-30, 06:59 PM

I think I got ParameterTable query working. How do I replace the source file if my import file is an excel workbook?

 

I tried this, but it said token identifier expected.

Source = Excel.Workbook(fnGetParameter("FilePath")&fnGetParameter("FileName")),null, true),

Paul_Christie

2019-01-30, 08:13 PM

The source line needs to look like this

 

Source = Excel.Workbook(File.Contents(fnGetParameter("FilePath")&fnGetParameter("FileName")), null, true),

 

Paul

Belebala

2019-01-30, 08:20 PM

Now I get the error of :

Expression.Error: We cannot convert the value "D:\PowerQuery..." to type Function.

Details:

Value=D:\PowerQuery\

Type=Type

 

Any idea?

Paul_Christie

2019-01-31, 04:23 PM

Is it possible to post the workbook with the query and the workbook with the data so i can see both ends of what is going wrong?

 

Paul

Belebala

2019-01-31, 04:41 PM

My query is saved under the Dynamic File Test and the data is coming fom the Book1 workbook.

Paul_Christie

2019-01-31, 06:17 PM

It looks like it had something to do with how you created fnGetParameter

 

88438844

 

Have you got Ken Puls book M is for (Data) Monkey. It really is worth every penny it will cost you.

 

Paul

Belebala

2019-01-31, 06:36 PM

I will definitely grab Ken Puls' book.

 

I get it now, if I invoked the fnGetParameter query, it will turn into the query table icon. If I don't invoke it, it will work.

 

Thank you very much Paul for solving my problem. I appreciate your effort!

 

Belebala

El Cid

2019-03-27, 03:01 PM

let

path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],

filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],

address = Text.Combine({path,filename}, ""),

Source = Excel.Workbook(File.Contents(address), null, true),

 

 

 

 

First you make two named ranges in a separate worksheet tab I name PARAM: PATH and FNAME using two separate cells, one right after the other.

 

 

 

 

Next, place these formulas in each of these named ranges

 

 

 

 

In PATH and put this formula (which gets the path of the current spreadsheet) in it:

 

 

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

 

 

 

FNAME and put this formula (to get the name of the current spreadsheet) in it if you are pulling data from it to use in your current spreadsheet.

 

 

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

 

OR

 

Just type in the name of the file you want to use.

 

 

 

Then, to transfer it to Power Query as a constants, you insert these lines in the PQ editor:

 

 

path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],

filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],

 

It loads the named ranges as a (single value) table and gets the first row ({0}) of Column1, i.e., the path and file name of the current sheet. This way, you can load the file using a relative address and you "current file" can change names.

 

 

 

Now you assign the variable "address" to the actual name of your import file combined with the "path" added in step 2.

 

 

address = Text.Combine({path,filename}, ""),

 

OR, if you did not use FNAME

 

address = Text.Combine({path,"QB QIP.csv"}, ""), //Note that “path” will be the path to the current spreadsheet. If the files are in the same folder, then their paths are “path” as well

 

 

 

This final steps brings it all together and will import the desired file

 

 

Excel File: Source = Excel.Workbook(File.Contents(address), null, true),

CSV File: Source = Csv.Document(File.Contents(address),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),

El Cid

2019-03-27, 03:46 PM

I prefer the Range Naming method.

 

let

path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],

filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],

address = Text.Combine({path,filename}, ""),

Source = Excel.Workbook(File.Contents(address), null, true),

 

 

 

 

PARAM tab: First set up a worksheet tab named PARAM or whatever name you like that will contain the range names where you do not want to dump your data when and if you do dump your PQ report to a sheet.

RANGE NAMES: In this tab-sheet named PARAM, or whatever, setup two named ranges: PATH and FNAME using two separate cells. Place them right after each other cell A1 and then cell A2.

FORMULAS for NAMED RANGES: Next, place these formulas in each of these named ranges

 

 

 

a. PATH - put this formula (which gets the path of the current spreadsheet) in it:

 

 

 

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

 

 

 

b. FNAME - put this formula (to get the name of the current spreadsheet) in it if you are pulling data from it to use in your current spreadsheet.

 

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

 

OR

 

Just type in the full name of the file you want to use, including the file name extension.

 

 

 

4. Insert these lines in the PQ editor to bring them into your query:

 

path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],

filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],

 

It loads the named ranges as a (single value) table and gets the first row ({0}) of Column1, i.e., the path and file name of the current sheet. This way, you can load the file using a relative address and you "current file" can change names.

 

5. "address" variable: Now you assign the variable "address" to the actual name of your import file combined with the "path" added in steps 2-3.

 

address = Text.Combine({path,filename}, ""),

 

OR, if you did not use FNAME

 

address = Text.Combine({path,"PICKME.csv"}, ""),

 

//Note that “path” will be the path to the current spreadsheet. If they are in a different location you'll have to override the formula used in step 3.

 

6. This final steps brings it all together and will import the desired file

 

Excel File: Source = Excel.Workbook(File.Contents(address), null, true),

 

CSV File: Source = Csv.Document(File.Contents(address),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),

 

Examples:

 

//CSVFILE_NEW.csv

let

path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],

filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],

address = Text.Combine({path,filename}, ""),

Source = Csv.Document(File.Contents(address),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])

in

Source

 

//EXCEL FILE.xlsx

let

path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],

filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],

address = Text.Combine({path,filename}, ""),

Source = Excel.Workbook(File.Contents(address), null, true)

in

Source

Reference: Thanks to VITO post here https://powerpivotpro.com/2015/10/share-and-refresh-power-query-with-local-links/#comment-235526