Ravindra BagaleCourses & study guides

9. Get Data from Folder: Monthly Bank Statements Walkthrough

9.3 Filter Out Hidden, Temporary and Unwanted Files

By Last updated:

He bagha: when a file is open in Excel, a hidden temporary file such as ~$Jan.xlsx appears in the folder. Someone may also save a notes.txt there.

Steps in Power BI

  1. Extension filter arrow › keep only .xlsx (tip: use Transform › Format › lowercase first, so .XLSX is also kept).
  2. Name filter › Text Filters › Does Not Begin With… › ~$.
  3. Attributes column › click the expand icon › tick Hidden › OK › filter Hidden = FALSE.
  4. Rename this step Only statement files.
Source   = Folder.Files(FolderPath),
Lower    = Table.TransformColumns(Source, {{"Extension", Text.Lower, type text}}),
Filtered = Table.SelectRows(Lower, each [Extension] = ".xlsx"
                and not Text.StartsWith([Name], "~$")
                and [Attributes]?[Hidden]? <> true)

Ravindra Bagale's Tip

Mitrano, dhyan dya: combine Files often breaks karan of hidden or temporary files such as ~$Feb.xlsx (created while a file is open in Excel) or Thumbs.db. Filter on Extension (.xlsx or .csv) and remove file names starting with "~$" before combining. Also check the Hidden attribute in the Attributes record. Punha ekda karun bagha, mag pudhe jaa.