Hi all - really hope someone can point me in the right direction. Is there any way I can use Power Query to return a custom property across multiple workbooks stored on SharePoint?
I manage a SharePoint site with around 50 libraries, each containing approx 20-30 Excel files which are peridodically updated by various colleagues. I started using Power Query to create a live library of files (essentially to keep a check on additions/deletions etc), but that dataset is used to provide a golden source of files to create monthly MI (a script runs to open each file individually and scrapes a column of data). The process takes hours!!
The files are due to be replaced next month, and I wanted to explore using custom properties to speed up the MI run in future. The replacement files each have around 15 custom properties linked to a named ranges within the respective files, but I cannot figure out how to use Power Query to pull those values.
It feels like the answer should be so simple but this one really has me stumped.
Any help would be greatly appreciated - thanks!
Bookmarks