Hey Power BI friends,
Background: I've been tasked with creating several Power BI reports that more than likely could only utilize the Microsoft Graph (not the Security one) and / or other APIs that require a Bearer or Auth token. One in particular is the licensing details from the Graph (not user delegated) for our organization, but I want to do this natively with the Web.Contents method instead of creating a custom connector, creating a scheduled PS job that outputs data to a CSV, or other workaround to allow it to be supported by other admins with a bit less experience on this end.
I've seen this question asked quite a few times most of what I've read always boils down to needing to create a custom connector for Power BI in VS (which has quite a few limitations and security issues within it). I didn't know if anyone has ever gotten around the generation of a bearer token natively through the Web.Contents function within Power Query. There was formerly a connector for this the Microsoft Graph, but it was phased out. Eventually people tried the OData connector and in 2018 from what I've read people said that it wouldn't work either. I was eventually able to make most of it work natively by using the Web.Contents function and a hardcoded parameters for the Auth token along with this Table.GenerateByPagehttps://docs.microsoft.com/en-us/power-query/samples/trippin/5-paging/readme to navigate through pagining of the results, but I can't schedule this because of the limitation with the auth token component.
I've seen quite a few people mention that they handle OAuth like this: https://docs.microsoft.com/en-us/power-query/samples/github/readme or in the MyGraph sample https://github.com/microsoft/DataConnectors/tree/master/samples/MyGraph they do it similar as well where they embed it, but this would mean an extended period of time to create a connector and if the API changes I have to change the connector and redeploy everywhere. Another option I've seen people do is embed their client secrets as well as credentials in params or within the pbix which just seems like a bad idea from an exposure perspective (This was a great video, but if a user got access to the PBIX then we might encounter an issue: https://www.youtube.com/watch?v=2RZkc_qrV1g).
Does anyone have any other ideas? Thanks!
