9. Get Data from Folder: Monthly Bank Statements Walkthrough
9.3 Filter Out Hidden, Temporary and Unwanted Files
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
- Extension filter arrow › keep only
.xlsx(tip: use Transform › Format › lowercase first, so.XLSXis also kept). - Name filter › Text Filters › Does Not Begin With… ›
~$. - Attributes column › click the expand icon › tick Hidden › OK › filter Hidden = FALSE.
- 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.
Ravindra Bagale's Tip – मराठी
मित्रांनो, लक्ष द्या: ~$Feb.xlsx (file Excel मध्ये उघडी असताना तयार होणारी) किंवा Thumbs.db सारख्या hidden किंवा temporary files मुळे Combine Files बऱ्याचदा तुटतं. Combine करण्याआधी Extension (.xlsx किंवा .csv) वर filter लावा आणि "~$" ने सुरू होणारी file names काढा. Attributes record मधला Hidden attribute पण तपासा. पुन्हा एकदा करून बघा, मग पुढे जा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, ध्यान दो: ~$Feb.xlsx (file के Excel में खुले रहने पर बनने वाली) या Thumbs.db जैसी hidden या temporary files की वजह से Combine Files अक्सर टूट जाता है. Combine से पहले Extension (.xlsx या .csv) पर filter लगाओ और "~$" से शुरू होने वाले file names हटाओ. Attributes record में Hidden attribute भी check करो. एक बार फिर करके देखो, फिर आगे बढ़ो.