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 includes elements from the query file too. I could remove specifically the filename (well I should be able to find how to, even if I have to use the button and not code ... ok I don't know yet but ..), but I'd prefer it to be dynamic ( if the file is renamed, the filter is still active).

How can I indicate the workbook name and how can I remove it ( can I add in my 2nd line another "each [Name] something <> true)" 

PS : In the same time, can I add another 

 

each [extension] = ".xlsx"

 

too ? (or is it a different wording ? Or I should do another step ? )

 

    Source = Folder.Files(Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1]),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (5)", each #"Transform File (5)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name1"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name1", "Transform File (5)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (5)", Table.ColumnNames(#"Transform File (5)"(#"Sample File (5)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name1", type text}, {"Seq No.", Int64.Type}, {"Doc No.", type text}, {"Doc Rev", Int64.Type}, {"Issue Purpose", type text}, {"Title", type text}, {"Client Review Required Date", type date}, {"Review Class", type text}, {"Unit No", Int64.Type}, {"PCWBS", Int64.Type}, {"FWBS", type text}, {"Client Doc No.", type any}, {"SRC FEED Reference Document No", type any}, {"Transmittal No", type text}, {"Sent Date", type date}, {"Cancel", type text}, {"Contractor Review Result", type any}, {"URL", type text}, {"Column18", type any}})
in
    #"Changed Type"

 

 

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

     

7 Replies

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

       

       

       

       

       

Resources