Import Dynamic Workbook

Belebala

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
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

New member
Joined
Mar 23, 2016
Messages
153
Reaction score
0
Points
0
Location
Nottingham, UK
Website
www.instantpages.ltd.uk
Excel Version(s)
Office 365 Monthly update
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

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
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?

2019-01-29_8-59-22.jpg2019-01-29_8-59-34.jpg
 

Belebala

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
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.
2019-01-30_7-36-07.jpg
 

Belebala

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
Sure. That would be very helpful if you could review it.

Thank you.
 

Attachments

  • Dynamic Query Test.xlsx
    16.4 KB · Views: 14

Paul_Christie

New member
Joined
Mar 23, 2016
Messages
153
Reaction score
0
Points
0
Location
Nottingham, UK
Website
www.instantpages.ltd.uk
Excel Version(s)
Office 365 Monthly update
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

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
My time zone is GTM-6.

Thanks a bunch for looking into this for me.
 

Belebala

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
I got a question, why my query fnGetParameter display the table icon not function icon? Did I do something wrong?

2019-01-30_12-38-27.jpg
 

Paul_Christie

New member
Joined
Mar 23, 2016
Messages
153
Reaction score
0
Points
0
Location
Nottingham, UK
Website
www.instantpages.ltd.uk
Excel Version(s)
Office 365 Monthly update
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

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
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),
 

Attachments

  • Book1.xlsx
    16.4 KB · Views: 9
  • Dynamic File Test.xlsx
    17.8 KB · Views: 6

Belebala

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
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?
 

Belebala

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
My query is saved under the Dynamic File Test and the data is coming fom the Book1 workbook.
 

Attachments

  • Dynamic File Test.xlsx
    17.8 KB · Views: 9
  • Book1.xlsx
    16.4 KB · Views: 6

Belebala

New member
Joined
Jan 28, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
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

Member
Joined
Aug 22, 2016
Messages
51
Reaction score
0
Points
6
Location
Greenville, SC
Excel Version(s)
Excel 2016
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),



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


  1. Next, place these formulas in each of these named ranges


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


  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.


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


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


  1. 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]),
 
Top