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...
excel
power bi
  • 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))

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 17, 2021

Lumib 

The simplest and most reliable way is not to keep file with query in the same folder to which you connect. Or that's some workflow business logic of which prevent to do that?

  • Lumib's avatar
    Lumib
    Copper Contributor
    Feb 17, 2021

    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] )

     

     

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 17, 2021

      Lumib 

      When you may add =CELL("filename",$A$1) into any cell of the current workbook. Depends on you keep the file in the cloud or on network share it returns filepath in bit different format. Parse it to exclude sheet name from the path, keep result in named cell and power query this cell using it's name. Filter list of files based on returned filepath.

      • Lumib's avatar
        Lumib
        Copper Contributor
        Feb 18, 2021
        Spoiler
         

        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 )

Resources