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
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