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

dmj120

Active Member
Joined
Jan 5, 2010
Messages
312
Office Version
  1. 365
  2. 2019
  3. 2010
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

Power Query:
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
 

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top