Power Bi for Hybrid Azure & 365

[siil-itman]

As the title suggests, I'm working with a hybrid 365 setup. For a number of reasons, we are still working with an on-premise AD and I'm extracting all sorts of info our of our AD and on-premise systems into Power Bi to give me all sorts of useful info.

What I would like to do is to be able to query 365 for licenses so I can show the unlicensed accounts in our AD but I can't work out how to connect to and query 365. Has anyone else done this or can give me a clue how?

While I'm currently working from Power Bi Desktop, i'm moving my data and reports into app.powerbi.com. I've setup a gateway server and am currently creating dataflows and datasets that auto refresh to stop people nagging me for the latest data. It would be preferable that the solution to get the 365 data is something I can automate?

Thanks in advance

[siil-itman]

@v-rzhou-msft Thanks for the links, I cracked it in the end thanks to those.

I've did it in powerbi online as I need the scheduled refresh. I ended up setting up a new dataflow using OData as the data source. Once I pointed it at reports.office.com/pbi/v1.0/%tennant id% and used "organisational credentials", it gave me access to the data I needed in the UserState table. 

[v-rzhou-msft · Accepted solution]

Hi @siil-itman ,

You can connect to Office 365 Adoption content pack:

Office 365 Adoption content pack

Customize the Office 365 Adoption content pack

Power BI desktop provide exchange online connector for us to get data:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.