powerbi report - lookup teams/group details in graph

[Oskar]

Hi

I am trying to build a report that include a live lookup via graph to get a groups details and group members/owners by graph.

I am able to write M code in a power query to retrive a group and its members/owners to a table.

But i am not able to figure out how to pass a variable of a groupid from my report (selected value from a slicer or table) and pass it to power query and ingest dynamicly to a graph call.

i want my report to do a lookup in graph to get details about a group.

maybe graph in power query is not the correct way, i am open to any suggestions how to set this up.

i have also tried to setup a power automate flow

where it writes the result in a json file that i ingest to my dataset.

but then i cant figure out how to update that specific dataset direct after the flow have run so i get the latest data in my json file.

i have not been able to figure out if power automate just can send a json result back to my report to get an direct answer on a flow to my visuals

[Oskar]

thank you.

the first option to have a powershell or power automate flow to retrive all teams and all members to a datasource and use that in my powerbi. that could work but the powerbi report will only have the latest data retrieved and a job like that for a large tenant will be bit overwhelming.

second option to trigger a flow from my powerbi could work. but i am having issues retrieving the data back to my powerbi report.

add rows to a dataset could work... but i get no options in the connector...

i have tried to setup a json file and that works but it will not work if multiple ppl use the report at once.

since it update one file and update the dataset... so i guess it would effect all viewers.

maybe option one is still the best and most robust solution even if the flow/powershell will be heavy to run on 20000 teams and get all members and owners.

[tharunkumarRTK]

"Add Rows to a dataset" expects a push dataset in your workspace. If you do not have a push dataset then it will not show up. Also like you mentioned if multiple users are consuming the report at the same time then this is not a ideal solution.

I would suggest you to go with option 1. Because, in my personal experience, I created a powerhsell script and ran it against a tenant having 700K groups. Each run takes 4 to 5 hours to complete. So 20K groups should not a problem in my opinon. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Oskar]

What do you think about power automate? I am more comfortable with a flow than powershell. Do you think a flow can work for 20000 groups, create a csv wit group details, then create a csv with all members and owners for the groups. Schedule it to run every day? 

[tharunkumarRTK]

@Oskar 

You might have used Graph APIs as datasource and retrieved AAD groups info using web connector in Power query.

You cannot pass the group id parameter dynamically to the power query, because web connector does not support dynamic m query parameters.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

I would suggest two solutions:

1. Build an end to end solution to retrieve all the groups data in your tenant. You can write a powershell script and run it on a schedule basis. Save the output in any storage location and read the output into a dataflow. Connect to the dataflow from power bi desktop.

2. With power atuomate, you can capture the user selection of group id and can run the graph api. Then you need to update the records in the dataset, for this either you can add an action which will automatically refresh your power bi dataset. 

   or you can use power bi push dataset which can dynamically ingest the data into power bi datasets from power automate flow.

https://www.sqlbi.com/articles/implementing-real-time-updates-in-power-bi-using-push-datasets-instea...

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!