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