SOLVED

Unable to use power query to combine files in folder synced from SharePoint due to system file

Copper Contributor

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.

Beckie_White_0-1718045587635.png

 

 

5 Replies
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.
best response confirmed by Beckie_White (Copper Contributor)
Solution

@Beckie_White 

 

How do I work around this system file issue?

When you query a folder path you get something like:

Sample.png

 

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
    FilteredOutSystemFiles

 

similarly with Hidden files (if a file is open when you query a folder its name starts with ~$ and it's marked as Hidden:

Sample2.png

 

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
    FilteredOutHiddenFiles

 

To 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

 

@Lorenzo 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)
1 best response

Accepted Solutions
best response confirmed by Beckie_White (Copper Contributor)
Solution

@Beckie_White 

 

How do I work around this system file issue?

When you query a folder path you get something like:

Sample.png

 

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
    FilteredOutSystemFiles

 

similarly with Hidden files (if a file is open when you query a folder its name starts with ~$ and it's marked as Hidden:

Sample2.png

 

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
    FilteredOutHiddenFiles

 

To 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

 

View solution in original post