What is the best approach to build a Power BI Report that reads info on all SharePoint Online documents from all site collections in an Office 365 Tenant? I’d like to build a report from all SharePoint libraries in a Office 365 tenant…and would like to know if there’s an approach that could help me on that without having to manually add each library (ex: using search API, etc). I've tried with Search API, but no success...
Reporting on documents across site collections, such as recently added docs, docs with a certain field value, etc., can be done through search. But if you need to generate some sort of statistic on all documents, then you're looking for the brute force method of looping through every site collection, every site, and every library. So first, are you sure you need to do this? What kind of data are you trying to gather about all documents in all site collections?
If you do go the brute force route, check into the REST API or the CSOM. For reporting, you'll need to copy the data you're trying to report on to another store, such as an azure sql database, which you could then use as the basis for your power bi reports.
Alternatively, 3rd party tools like share-gate have a pretty robust reporting tool built in, though that wouldn't use Power BI.