Ravindra BagaleCourses & study guides मराठी Track your progress

9. Get Data from Folder: Monthly Bank Statements Walkthrough

9.3 Filter Out Hidden, Temporary and Unwanted Files

By Last updated:

Start with a problem

Ruhi had Feb.xlsx open in Excel while she refreshed. The folder suddenly showed ~$Feb.xlsx, and the combine failed with a format error.

Explain simply

Before combining, keep only real statement files: the right extension, no names starting with ~$, and no hidden files.

Show the data

Example folder list (Feb.xlsx is open in Excel, and Mar was saved with an upper-case extension):

Name Extension Hidden
Feb.xlsx .xlsx FALSE
Jan.xlsx .xlsx FALSE
Mar.xlsx .XLSX FALSE
~$Feb.xlsx .xlsx TRUE
notes.txt .txt FALSE
README.txt .txt FALSE

About this data: This table uses columns: Name, Extension, Hidden. This table uses columns: Name, Extension. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: bank_statements/Jan.xlsx, bank_statements/Feb.xlsx, bank_statements/Mar.xlsx, bank_statements/notes.txt (source: src/practice/).

Demonstrate

  1. Select Extension › Transform › Format › lowercase.

    Your result should look like this:

    Extension
    .xlsx, .xlsx, .xlsx, .xlsx, .txt, .txt

  2. Extension filter arrow › keep only .xlsx.

    Your result should look like this:

    Name
    Feb.xlsx, Jan.xlsx, Mar.xlsx, ~$Feb.xlsx

  3. Name filter › Text Filters › Does Not Begin With… › ~$.

    Your result should look like this:

    Name
    Feb.xlsx, Jan.xlsx, Mar.xlsx

  4. Attributes › expand icon › tick Hidden › filter Hidden = FALSE (a second safety net).

  5. Right-click the last step › Rename › Only statement files.

Show the result

Your result should look like this (fictional sample):

Name Extension
Feb.xlsx .xlsx
Jan.xlsx .xlsx
Mar.xlsx .xlsx

Explain what happened

The filters removed 3 of the 6 rows: two .txt files (Extension filter) and the Excel lock file (Name filter). The combine will now open only real statements.

Explore further

  • Lock files appear only while a workbook is open, so the error looks random. Filter them anyway.
  • Thumbs.db and desktop.ini are hidden system files; the Hidden filter catches them.

Detailed reference for this lesson:

Look: 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

Friends, pay attention: Combine Files often breaks because 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. Try it once more, then move on.

Fix common mistakes

Wrong result Reason Correction
Mar.xlsx disappears after filtering The .xlsx filter is case-sensitive and Mar had .XLSX Lowercase Extension first
Combine error: external table is not in the expected format A ~$ lock file was combined Filter Name does not begin with ~$
Error: field 'Hidden' not found Attributes not expanded Expand Attributes › Hidden, or use [Attributes]?[Hidden]? in M

Practise independently

Practice task

A colleague drops Feb (copy).xlsx and budget.pdf into the folder. Which filter removes each file, and which one still gets through?

Hints
  • Check each file against the extension rule and the ~$ rule.
Solution

budget.pdf is removed by the Extension filter. Feb (copy).xlsx passes every filter, so February would be counted twice (spend ₹12,870 becomes ₹25,740). Fix: a naming rule (only Mmm.xlsx or 2026-02.xlsx) with a matching Name filter, or a separate drafts folder.

Think like a professional

Filters protect you from known junk. Duplicate copies are a process problem: write the folder rule in the report's README and show a file count on a check page.

Ravi Sir's Cards for this lesson

Filter out hidden, temporary and unwanted files. One wrong file in the folder can break the combine step or add junk rows.Revision card

मराठी cards