Forum Discussion

Lumib's avatar
Lumib
Copper Contributor
Feb 17, 2021
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...
  • Lumib's avatar
    Lumib
    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))
        

     

Resources