Power Query Source Folder

Hi David,

Yes, if all files are different you may use From Folder connector and filter your files based on its name start. The code looks like

let

pMyFolder = "C:\Test",
pMyFileID = "RG0040",
pMyTable = "Table1",

    Source = Folder.Files(pMyFolder),
    FilterFile = Table.SelectRows(Source, each Text.StartsWith([Name], pMyFileID)),
    KeepContent = Table.SelectColumns(FilterFile,{"Content"}),
    Content = KeepContent{0}[Content],
    GetContent = Excel.Workbook(Content),
    GetTable = Table.SelectRows(GetContent, each ([Name] = pMyTable)),
    RemoveUnused = Table.SelectColumns(GetTable,{"Data"}),
    Expandtable = Table.ExpandTableColumn(RemoveUnused, "Data", {"Date 1", "Date 2", "Value"}, {"Date 1", "Date 2", "Value"})
in
    Expandtable

Better to define first 3 as parameters not to hardcode them within the query. And above query could be fully generated from UI.