I wanted to develop dynamic file path for txt file but it keeps giving me error on the “filecontents” step below:
let
Source = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content],
FolderPath = Source{0}[FolderPath header],
Sourcev2 = Excel.CurrentWorkbook(){[Name="FilePath_P2C"]}[Content],
FilePath = Sourcev2{0}[FilePath_Plant to Company header],
FullPath = FolderPath & FilePath,
fileContents = File.Contents(FullPath),
lines = Lines.FromBinary(fileContents, null, null, 1252),
table = Table.FromColumns({lines})
in
table
The error is
DataFormat.Error: The supplied file path must be a valid absolute path.
The file path is correct when I debugged it, Im not sure how to solve this.
Wei Ni is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Excel: Getting the Absolute Path of the current excel file
In Excel create a cell with this formula.
= TEXTBEFORE(@CELL("filename",A1), "[" )
That gives you the absolute path ( the folder )
If want just the filename, it’s in this formula:
= TEXTBEFORE(
TEXTAFTER(@CELL("filename",A1), "[" ),
"]"
)
Power Query Full Path
What is the value of FullPath ? Something is not exact.
You might be missing the folder delimiter ? Try this
FullPath = FolderPath & "" & FilePath,
Another option if you know the folder, but not necessarily the filename:
= Folder.Contents( FolderPath )
You can find the newest txt in a folder.
let Source = Folder.Contents( FolderPath ),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([Extension] = ".txt")),
#"Newest txt file" = Table.First(
Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}) )
in
#"Newest txt file"
You could filter the base name with a case-insensitive Text.Contains match