The challenge posted by u/k012sxaQ captures a frustration that many spreadsheet users know well: moving from local folders to cloud-based data sources feels like learning a new language. The M code for pulling a single Excel file from SharePoint is straightforward enough. But when the goal shifts to reading every file inside a folder, without first syncing everything to a local drive, the path forward becomes murky. This is exactly where the gap between traditional spreadsheet thinking and modern data workflows becomes visible.
What the user is asking for is not complex in concept. SharePoint folders are web-accessible containers, and Power Query has the tools to navigate them. The missing piece is understanding how to translate a local folder reference into a SharePoint API path. The `Folder.Files` function works beautifully when pointed at a local directory. The equivalent for SharePoint requires building a URL that the `SharePoint.Contents` or `Web.Contents` functions can authenticate against. It is a shift in mindset: stop thinking of folders as file system paths and start thinking of them as web endpoints with structured metadata. The solution exists, but it demands that users abandon the mental model they have relied on for years.
This is where we see a real opportunity. The user in this case already knows how to write M code and understands the basic structure of web requests. They are not starting from zero. What they need is a bridge, a clear, practical example that maps the familiar `Folder.Files` pattern onto SharePoint's RESTful structure. The answer lies in using `SharePoint.Files` or constructing a URL that includes the folder's relative path within the site. Once that connection is made, the same filtering, combining, and transformation logic they already use for local files applies directly. The cognitive leap is smaller than it first appears.
Our take is straightforward: the future of spreadsheet work is not about abandoning Power Query or M code. It is about rethinking where data lives and how we reach it. Users who master this SharePoint-to-Power Query connection gain the ability to automate data pulls from shared team folders, project repositories, and departmental archives without manual downloads or local copies. That is a tangible productivity win. The user who asked this question is already closer to the answer than they realize, they just need a single working example to close the gap. That is the kind of practical guidance that transforms frustration into capability.