Forum Discussion
Unable to use power query to combine files in folder synced from SharePoint due to system file
I have a library synced locally so that I can analyze portions of the file contents based on a query. Currently there are around 5,000 files in the library, and there will be closer to 10,000 by the end of this week.
I'm unable to use Power Query to combine the files, then edit them, because of a system file. This seems to be a new issue as this is something I've done successfully in the past. I'm wondering if there's a work around. I cannot use SharePoint folder for the data source as there are far too many files on the site, and there's not an option to filter to only the files in this specific library.
How do I work around this system file issue? If I try to combine & transform or combine & load to, nothing happens. If I try to do either of them, it just returns to this screen.
How do I work around this system file issue?
When you query a folder path you get something like:
where the [Attributes] column contains a Record per file. Each Record has a number of Fields, one of them being 'System'. The latter tells you if the file is marked as a System file
To filter out System files you can do:
let Source = Folder.Files("To_Be_Replaced_With_Path_To_Folder"), FilteredOutSystemFiles = Table.SelectRows(Source, each [Attributes]?[System]? <> true) in FilteredOutSystemFilessimilarly with Hidden files (if a file is open when you query a folder its name starts with ~$ and it's marked as Hidden:
to get rid of Hidden files:
let Source = Folder.Files("To_Be_Replaced_With_Path_To_Folder"), FilteredOutHiddenFiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true) in FilteredOutHiddenFilesTo filter out System + Hidden files:
let Source = Folder.Files("To_Be_Replaced_With_Path_To_Folder"), FilteredOutSystemAndHiddenFiles = Table.SelectRows(Source, each ([Attributes]?[System]? <> true) and ([Attributes]?[Hidden]? <> true) ) in FilteredOutSystemAndHiddenFiles
5 Replies
How do I work around this system file issue?
When you query a folder path you get something like:
where the [Attributes] column contains a Record per file. Each Record has a number of Fields, one of them being 'System'. The latter tells you if the file is marked as a System file
To filter out System files you can do:
let Source = Folder.Files("To_Be_Replaced_With_Path_To_Folder"), FilteredOutSystemFiles = Table.SelectRows(Source, each [Attributes]?[System]? <> true) in FilteredOutSystemFilessimilarly with Hidden files (if a file is open when you query a folder its name starts with ~$ and it's marked as Hidden:
to get rid of Hidden files:
let Source = Folder.Files("To_Be_Replaced_With_Path_To_Folder"), FilteredOutHiddenFiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true) in FilteredOutHiddenFilesTo filter out System + Hidden files:
let Source = Folder.Files("To_Be_Replaced_With_Path_To_Folder"), FilteredOutSystemAndHiddenFiles = Table.SelectRows(Source, each ([Attributes]?[System]? <> true) and ([Attributes]?[Hidden]? <> true) ) in FilteredOutSystemAndHiddenFilesLorenzo that did it! Thank you! It seems so obvious now.
- You're welcome + glad you could to adapt the sample(s) to your context & Thanks for providing feedback (helps people who Search)
- You probably have tried filtering out the system file, if not, right click the cell and choose DOES NOT EQUAL. Alternatively in the Extension column, filter to only the file extension you wish to keep, for eg. xlsx. If this doesn’t resolve it, you might need to do the filtering higher up in the path.
- Thanks, unfortunately I cannot even get to the part where I would be able to select a filter.
