Forum Discussion
Solved
Power Query - Remove current file from the query
I am merging a list of files. as my file is destined to be a template. My source will be the folder where the working workbook/Query file is saved. However, the issue is that the merged table incl...
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 yeah in fact in my last attempts it was not working because I was indicating
Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1]or
[Filepath]in my code, finally I found something where they were using an intermediate step to transform it as Power Query text, and after they were using the new power query element in the formula and it worked ( but I imagine if I had used the correct way to indicate it from the beginning it would have worked )
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))
In addition, I'd recommend to get filepath by one query, list of files in the folder by another one and do filtering and other transformation in third query with references on two above. Otherwise you could have Firewall error if not to ignore privacy settings.
