0
\$\begingroup\$

Background

As discussed here on Stack Overflow, I am comparing a local and a OneDrive version of the same superfolder, in order to identify discrepancies in the sync. As such, I must compare all files and (sub)folders.

I am using Power Query to list the full contents of each superfolder. Since comparison will require various Table.*Join()s, then I must combine all (sub)levels of contents into a single flat table: of the form yielded by Folder.Contents(), but with far more data.

Content Name Extension Date accessed Date Modified Date created Attributes Folder Path
Binary file.ext .ext 7/24/2023 4:21:00 PM 7/1/2023 1:00:00 PM 2/1/2023 9:00:00 AM Record C:\Users\greg\OneDrive - My Company\Superfolder
Table Folder 7/24/2023 4:21:03 PM 6/30/2023 5:00:00 PM 1/1/2023 12:00:00 AM Record C:\Users\greg\OneDrive - My Company\Superfolder
Binary sub_file.ext .ext 7/24/2023 4:21:06 PM 7/1/2023 2:00:00 PM 2/1/2023 10:00:00 AM Record C:\Users\greg\OneDrive - My Company\Superfolder\Folder
Table Subfolder 7/24/2023 4:21:09 PM 6/30/2023 6:00:00 PM 1/1/2023 1:00:00 AM Record C:\Users\greg\OneDrive - My Company\Superfolder\Folder

Observe how the row for a folder contains a table object under the [Content] column, which describes the contents of that folder in identical fashion.

Solutions

With tens of thousands of files, and a deeply nested folder structure, I am seeking the most performant solution. So far, I have developed two recursive approaches:

Solution 1: Query Each Folder

let Folder_FullContents = (
    // The only real input.
    path as text,
    
    // Mimics the signature of Folder.Contents().
    optional options as nullable record
) as table =>
    let
        // Buffer the first level of folder contents, specifically path scalars.
        contents = Table.Buffer(Folder.Contents(path, options)),
        
        // List the full paths to each subfolder.
        subfolders = Table.AddColumn(
            Table.SelectRows(contents, each [Attributes][Directory]),
            "File Path",
            each Text.Combine({[Folder Path], [Name]}),
            type text
        )[File Path],
        
        // Prepend the current contents to those returned from the subfolders.
        result = Table.Combine(List.Combine({
            {contents},
            List.Transform(subfolders, @Folder_FullContents)
        }))
    in result
in Folder_FullContents

Solution 2: Unnest Root Contents

The engine of this solution is Folder_UnnestContents(), which flattens and consolidates the tables nested in the [Content] column yielded by Folder.Contents().

let Folder_UnnestContents = (table as table) as table =>
    let
        // Buffer the table, as recommended on Stack Overflow.
        tbl = Table.Buffer(table),
        
        // Prepend the current contents to [Content] extracted from the subfolders.
        result = Table.Combine(List.Combine({
            {tbl},
            List.Transform(
                Table.SelectRows(tbl, each [Attributes][Directory])[Content],
                @Folder_UnnestContents
            )
        }))
    in result
in Folder_UnnestContents

Then it is simple to implement Folder_FullContents():

let Folder_FullContents = (
    path as text,
    optional options as nullable record
) as table =>
    let
        // Buffer the contents, as recommended on Stack Overflow.
        contents = Table.Buffer(Folder.Contents(path, options)),
        
        // Unnest those contents via the engine.
        result = Folder_UnnestContents(contents)
    in result
in Folder_FullContents

Questions

  1. How should .Buffer()ing really be handled here? I was advised on Stack Overflow to buffer liberally, but upon further discussion, it seems the benefits might not extend to the [Content] column, with its objective (not scalar) data.

  2. Which of these approaches is more performant at scale, especially on deeply nested (sub)folders? My VPN connections are volatile, so I cannot establish a consistent baseline to gauge performance.

  3. Which of them is more stable, especially against modifications made to the file structure while the query is loading?

    I suspect that Solution 2 might prove more robust against modifications to file structure in real time, particularly when the query takes a long time to run. Its single call to Folder.Contents() presumably (?) captures one coherent "snapshot" of the structure; but Solution 1 runs Folder.Contents() on each subfolder, with small window of delay between each. Or does the latter occur anyway, even with Solution 2, whenever a table object is accessed under the [Content] column?

\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.