ETL Options with files loop (powerquery or free?)

Hi icassiem,

You can do the looping in Power Query regardless of timestamps.

The query below will:

• Connect to a SharePoint site
• Find all CSV files
• Keep only files where the file name contains one of the keywords
• Combine all matching files into one table
• Add the source file name and source folder for audit purposes

Example

let
    SiteUrl = "https://yourtenant.sharepoint.com/sites/yoursite",

    Keywords = {"mature", "efficiency", "interna"},

    Source =
        SharePoint.Files(
            SiteUrl,
            [ApiVersion = 15]
        ),

    FilterCsv =
        Table.SelectRows(
            Source,
            each Text.Lower([Extension]) = ".csv"
        ),

    FilterKeywords =
        Table.SelectRows(
            FilterCsv,
            each
                List.AnyTrue(
                    List.Transform(
                        Keywords,
                        (k) => Text.Contains(Text.Lower([Name]), Text.Lower(k))
                    )
                )
        ),

    RemoveHiddenFiles =
        Table.SelectRows(
            FilterKeywords,
            each [Attributes]?[Hidden]? <> true
        ),

    TransformCsv =
        (FileContent as binary, FileName as text, FolderPath as text) as table =>
            let
                Csv =
                    Csv.Document(
                        FileContent,
                        [
                            Delimiter = ",",
                            Encoding = 65001,
                            QuoteStyle = QuoteStyle.Csv
                        ]
                    ),

                PromotedHeaders =
                    Table.PromoteHeaders(
                        Csv,
                        [PromoteAllScalars = true]
                    ),

                AddSourceFile =
                    Table.AddColumn(
                        PromotedHeaders,
                        "SourceFile",
                        each FileName,
                        type text
                    ),

                AddSourceFolder =
                    Table.AddColumn(
                        AddSourceFile,
                        "SourceFolder",
                        each FolderPath,
                        type text
                    )
            in
                AddSourceFolder,

    AddData =
        Table.AddColumn(
            RemoveHiddenFiles,
            "Data",
            each TransformCsv([Content], [Name], [Folder Path])
        ),

    Combined =
        if Table.RowCount(AddData) = 0
        then #table({}, {})
        else Table.Combine(AddData[Data])
in
    Combined

The important part is this:

Keywords = {"mature", "efficiency", "interna"}

This means the query will include files where the file name contains any of those words.

Examples that would be picked up:

202607_clientA_mature_export.csv
clientB_efficiency_202607.csv
timestamp_clientC_interna.csv

If you want separate outputs, for example one table for Mature and another for Efficiency, then create one query per keyword. But if all files should be appended into one table, the query above works.

Important: all files combined in the same query should have the same structure/columns.

And yes, you can definitly do this with powershell or Python, if you are want to do it that way, just ask we will figure out something.
And sorry if i misunderstand the problem above with PowerQuery looping.