SOLVED

Power Query - Remove current file from the query

%3CLINGO-SUB%20id%3D%22lingo-sub-2143391%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Remove%20current%20file%20from%20the%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143391%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20merging%20a%20list%20of%20files.%3C%2FP%3E%3CP%3Eas%20my%20file%20is%20destined%20to%20be%20a%20template.%20It's%20searching%20in%20the%20same%20folder%20as%20the%20working%20workbook%2FQuery%20file.%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20issue%20is%20that%20I%20get%20the%20information%20from%20the%20Query%20file%20included%20too.%20I%20could%20remove%20specifically%20the%20filename%20%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E(well%20I%20should%20be%20able%20to%20find%20how%20to%2C%20even%20if%20I%20have%20to%20use%20the%20button%20and%20not%20code%20...%20ok%20I%20don't%20know%20yet%20but%20..)%3C%2FFONT%3E%2C%20but%20I'd%20prefer%20it%20to%20be%20dynamic%20(%20if%20the%20file%20is%20renamed%2C%20the%20filter%20is%20still%20active).%3C%2FP%3E%3CP%3EHow%20can%20I%20indicate%20the%20workbook%20name%20and%20how%20can%20I%20remove%20it%20(%20can%20I%20add%20in%20my%202nd%20line%20another%20%22each%20%5BName%5D%20%3CEM%3E%3CU%3Esomething%20%3C%2FU%3E%3C%2FEM%3E%26lt%3B%26gt%3B%20true)%22%26nbsp%3B%3C%2FP%3E%3CP%3EPS%20%3A%20In%20the%20same%20time%2C%20can%20I%20add%20another%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Eeach%20%5Bextension%5D%20%3D%20%22.xlsx%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Etoo%20%3F%20(or%20is%20it%20a%20different%20wording%20%3F%20Or%20I%20should%20do%20another%20step%20%3F%20)%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%20%20%20%20Source%20%3D%20Folder.Files(Excel.CurrentWorkbook()%7B%5BName%3D%22Filepath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D)%2C%0A%20%20%20%20%23%22Filtered%20Hidden%20Files1%22%20%3D%20Table.SelectRows(Source%2C%20each%20%5BAttributes%5D%3F%5BHidden%5D%3F%20%26lt%3B%26gt%3B%20true)%2C%0A%20%20%20%20%23%22Invoke%20Custom%20Function1%22%20%3D%20Table.AddColumn(%23%22Filtered%20Hidden%20Files1%22%2C%20%22Transform%20File%20(5)%22%2C%20each%20%23%22Transform%20File%20(5)%22(%5BContent%5D))%2C%0A%20%20%20%20%23%22Renamed%20Columns1%22%20%3D%20Table.RenameColumns(%23%22Invoke%20Custom%20Function1%22%2C%20%7B%22Name%22%2C%20%22Source.Name1%22%7D)%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns1%22%20%3D%20Table.SelectColumns(%23%22Renamed%20Columns1%22%2C%20%7B%22Source.Name1%22%2C%20%22Transform%20File%20(5)%22%7D)%2C%0A%20%20%20%20%23%22Expanded%20Table%20Column1%22%20%3D%20Table.ExpandTableColumn(%23%22Removed%20Other%20Columns1%22%2C%20%22Transform%20File%20(5)%22%2C%20Table.ColumnNames(%23%22Transform%20File%20(5)%22(%23%22Sample%20File%20(5)%22)))%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Expanded%20Table%20Column1%22%2C%7B%7B%22Source.Name1%22%2C%20type%20text%7D%2C%20%7B%22Seq%20No.%22%2C%20Int64.Type%7D%2C%20%7B%22Doc%20No.%22%2C%20type%20text%7D%2C%20%7B%22Doc%20Rev%22%2C%20Int64.Type%7D%2C%20%7B%22Issue%20Purpose%22%2C%20type%20text%7D%2C%20%7B%22Title%22%2C%20type%20text%7D%2C%20%7B%22Client%20Review%20Required%20Date%22%2C%20type%20date%7D%2C%20%7B%22Review%20Class%22%2C%20type%20text%7D%2C%20%7B%22Unit%20No%22%2C%20Int64.Type%7D%2C%20%7B%22PCWBS%22%2C%20Int64.Type%7D%2C%20%7B%22FWBS%22%2C%20type%20text%7D%2C%20%7B%22Client%20Doc%20No.%22%2C%20type%20any%7D%2C%20%7B%22SRC%20FEED%20Reference%20Document%20No%22%2C%20type%20any%7D%2C%20%7B%22Transmittal%20No%22%2C%20type%20text%7D%2C%20%7B%22Sent%20Date%22%2C%20type%20date%7D%2C%20%7B%22Cancel%22%2C%20type%20text%7D%2C%20%7B%22Contractor%20Review%20Result%22%2C%20type%20any%7D%2C%20%7B%22URL%22%2C%20type%20text%7D%2C%20%7B%22Column18%22%2C%20type%20any%7D%7D)%0Ain%0A%20%20%20%20%23%22Changed%20Type%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2143391%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143529%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Remove%20current%20file%20from%20the%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969774%22%20target%3D%22_blank%22%3E%40Lumib%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20simplest%20and%20most%20reliable%20way%20is%20not%20to%20keep%20file%20with%20query%20in%20the%20same%20folder%20to%20which%20you%20connect.%20Or%20that's%20some%20workflow%20business%20logic%20of%20which%20prevent%20to%20do%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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"

 

 

7 Replies

@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?

@Sergei Baklan 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] )

 

 

 

 

 

@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.

Spoiler
 

@Sergei Baklan 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 )

best response confirmed by Lumib (New Contributor)
Solution

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

 

@Lumib 

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.