Forum Discussion
Power Query - Remove current file from the query
- Feb 18, 2021
So the final, correct code, finally is this
let /*we need to list the files in the same folder as your workbook 1. Begin by Naming a cell "Filepath" (caution it's case sensitive) 2. In this cell, indicate the following formula : =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1) 3. Indicate the following Source in your query : Source = Folder.Files(Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1]) in Source To remove the current workbook : 1.to get the filename, create a cell "Filename" 2. indicate the following formula : =MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)+1,FIND("]",CELL("filename",$A$1),1)-FIND("[",CELL("filename",$A$1),1)-1) 3. Filter your table : #"Filtered Rows" = Table.SelectRows(Source, each [Name] <> Filename */ Source = Folder.Files(Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1]), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx") and ([Name] <> FilenameQ) and ([Attributes]?[Hidden]? <> true))
SergeiBaklan Yes in fact he principle is that the File with Power Query is to be used across team, in different folder (and it's not always possible to use shared folder, or not the same shared folder, as there are confidentiality rules.
So as you see, my source is dynamic ( it search the folder in wich you add your file with power query).
In fact, I think that perhaps I can use the same system. I used a formula to get dynamically my file name ... now I just need to understand how I can use it as variable ...
I tried with following code, but it indicate that a "token identifier" is expected
Table.SelectRows(Source, each [Name] <> Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1] )
Not exactly the same case but related is here
Power Query Source from Relative Paths - Microsoft Tech Community