First Power Query UGH ....SharePoint: append files within most recent folder

[dmj120]

I am having the worst time with this. Tried ChatGPT and Copilot, which just confused me and screwed things royally.

Trying to stop live links between several shared files, I am trying to create a query that will consolidate everything. Can someone provide guidance on queries in SharePoint, and help revising the code?

My Project Overview
First append most recent files: On OneDrive, in the "path" at the top My Files > Pricing Files > -- JJ ONLY --General > Expenditures > RL Reports, which contains folders (labeling eg 2025-0219), and within each of these are xlsx files all having "Expenditures" in the file name. I will update all folders with -- JJ ONLY -- in them, this is just while I'm redoing things for our team.
Second perform average calculations --haven't tried coding, just an outline
Third merge other info from other files (1-5 columns from four other files)


First Step: This is where I've landed on appending the files

let
    // Define SharePoint site URL
    SharePointSite = "https://renovo10-my.sharepoint.com/personal/my_email_com",
    
    // Connect to SharePoint and get all files
    Source = SharePoint.Files(SharePointSite, [ApiVersion = 15]),

    // Filter for the "Expenditures" folder within RL Reports
    FilteredPath = Table.SelectRows(Source, each Text.Contains([Folder Path], "Pricing Files/-- JJ ONLY --General/Expenditures/RL Reports/")),

    // Extract the folder names based on the naming pattern (YYYY-MMDD)
    ExtractedFolderNames = Table.AddColumn(FilteredPath, "Folder Name", each Text.AfterDelimiter([Folder Path], "Expenditures/RL Reports/"), type text),
    UniqueFolders = Table.Distinct(Table.SelectColumns(ExtractedFolderNames, {"Folder Name"})),

    // Convert folder names to dates for sorting
    AddedDateColumn = Table.AddColumn(UniqueFolders, "Folder Date", each try Date.FromText(Text.Middle([Folder Name], 0, 4) & "-" & Text.Middle([Folder Name], 5, 2) & "-" & Text.Middle([Folder Name], 7, 2)) otherwise null, type date),

    // Remove invalid folder names
    ValidFolders = Table.SelectRows(AddedDateColumn, each [Folder Date] <> null),

    // Sort folders in descending order and get the most recent folder
    SortedFolders = Table.Sort(ValidFolders, {{"Folder Date", Order.Descending}}),
    LatestFolder = Table.FirstN(SortedFolders, 1),

    // Get the most recent folder name
    LatestFolderName = LatestFolder[Folder Name]{0},
    
    // Filter files in the most recent folder, ensuring only Excel files with "Expenditures" in the name are included
    FilteredFiles = Table.SelectRows(FilteredPath, each Text.Contains([Folder Path], LatestFolderName) and Text.Contains([Name], "Expenditures") and Text.EndsWith([Name], ".xlsx")),

    // Retrieve the binary content of the files
    ImportedFiles = Table.AddColumn(FilteredFiles, "ExcelData", each Excel.Workbook([Content], true)),

    // Expand the Excel files to get sheet data and rename conflicting columns
    ExpandedSheets = Table.ExpandTableColumn(ImportedFiles, "ExcelData", {"Name", "Data"}),
    RenamedColumns = Table.TransformColumns(ExpandedSheets, {{"Name", each "Sheet Name", type text}}),

    // Expand sheet data into table format
    ExpandedData = Table.ExpandTableColumn(RenamedColumns, "Data", List.Union(List.Transform(RenamedColumns[Data], each if _ is null then {} else Table.ColumnNames(_))))
in
    ExpandedData