Y'all, someone pointed out that the create file action should be outside the loop - they are TOTALLY right, WHOOPS. Put it under the loop instead of in the loop. 😂 Also, I realize the resolution is hard to read, sorry! I put all of the code in the video description if you're having trouble seeing it in the recording. I now use UI zoom on newer recordings. :)
I guess you may need to give a short update on how and why the Flow takes so long to fetch the data; for me, it is taking over 1-2 hours, but I am cancelling the flow. Or even better, Action 'Parse_JSON' failed after 4h. Interestingly, I see JSON export and how it is getting bigger, but it doesn't end. Something is not correct, I assume =)
@@mirrrvelll5164 uhhh, yeah that’s strange! Are you in a large org? I wouldn’t use this if you work somewhere with like 10k employees for instance, usually they have a more robust process for it. It’s weird that the parse is the part taking a long time, usually it’s the for-each loops
@@mirrrvelll5164 I haven't tried it with that many so it's hard to say, but that sounds semi-reasonable. It might be an issue with your filter if it's that step that's taking a long time and not the loop. PowerShell can also get group membership data in a more scalable way, or Logic Apps has virtually the same UI as Power Automate but also scales better.
As someone coming to PowerAutomate from a Systems Admin background I love the way you present these things in my language. Very easy to understand, information dense presentation with useful tools that I can apply to a range of automation tasks.
Thanks for this awesome idea on how to get the group details into Power BI. One remark though: It seems you're recreating the files again and again while you're iterating through the groups. I think the compose action and the create file actions should be moved outside the loop, right?
Hi Christine, this was very useful, thank you. List Groups returned me over 6k results and when I used the "Filter rows" (e.g. startswith(displayName,'PMO') ), I could retrieve the groups applicable to my requirement. These groups are used in Project Online (PWA) & the PWA interface did not have an export feature to provide me with group members/owners. With your technique it's possible and allows us to review and manage group memberships.
This is great thanks for the video. 2 questions: 1. How can i just grab Entra and M365 roles only and do the same process? 2. I have thousandgs of groups (inherited from someone else) so the process takes hours. ANy way to filter it do extract certain groups based on certain criteria like the group name contain certain text etc.
Super useful, Thanks for the video. I have large number of groups in the organization > 25000. Is it possible to retrieve all those M365 groups that are public?
I think so, but you'd have to use the Graph API instead of the connector - that'll let you put a filter in the query. It looks like from the output on this page that there's a property for "visibility" that shows Public as a value, so I thiiiiink you could probably use that on your "get" call following the filter pattern they use in the other examples, but I've not tried it. learn.microsoft.com/en-us/graph/api/group-list?view=graph-rest-1.0&tabs=http If you do and it works let me know :)
I was trying to do the sme but for members in a team channel, but I wasn't able to igure it out, I tried with and http request but it returns errors as my app does not have the required permissions, ChannelMember.Read.All. DO you know a way to get the members of Channel?
Why did you do this with power automate in the middle, instead of directly via MS Graph queries inside PowerBI only? Thinking the method in the video means you've got priviledged data sitting in SharePoint in raw format in your JSONs
Querying APIs directly in PBI is a pain, particularly when you get into pagination… if I were to do it anywhere serious it’d be a real ETL tool to put it in a database table, but this isn’t a data engineering channel. SharePoint is as secure as the site you store it in… I would not put this in a public site obviously, but I think it’d be fine in an IT team site or similar. In the grand scheme of things user and group membership is not particularly sensitive in comparison to what is regularly stored in SP in everyday use.
Quite interesting video, but very hard to see. There where you defined "Upn", "grouped", and so on. Could you tell me the exact format/syntax? { "upn": "UPN", } `? I am running this part where "https" comes in, but it is taking over 10 minutes and got an error: "Flow run timed out". I have a lot of users (like 3-4k) and maybe around 200 groups together, which is true. I adjusted the pagination, but it still takes time. Is that normal? Thanks!
I would expect it to take over 10 minutes for that many, the for-each loops take a long time. You might try setting the degrees of parallelism to 1 in the settings on the loops - slowing it down actually speeds it up because it keeps you from getting throttled. Are you looking for the append to members array text? That one is this: { "upn": "@{items('Apply_to_each_group_member')?['userPrincipalName']}", "userId": "@{items('Apply_to_each_group_member')?['id']}", "groupId": "@{items('Apply_to_each')?['id']}", "userRole": "Member" } Append to owners array is this: { "upn": "@{items('Get_owners_for_each_group')?['userPrincipalName']}", "userId": "@{items('Get_owners_for_each_group')?['id']}", "groupId": "@{items('Apply_to_each')?['id']}", "userRole": "Owner" } The compose step after that is this: [ @{variables('groupMembers')}, @{variables('groupOwners')} ]
@@bi-ome Thanks for your support. I managed to get it to work after 58 minutes of running! It gave an error. =) However, interestingly, I got my export. Both are in SP (I can use data in Power BI), and one file shows me over 11k rows even though I put a limit of 1000. How is that possible? This is an error: (all the steps are in green..) ActionFailed. An action failed. No dependent actions succeeded. p.s If I use parallelism of 1, I am getting just some users and groups, which is wrong.
This is great! Thanks. Unfortunately my flow fails as there's too many groups/members in my environment. Is there a way to get members from only one specific group?
Oh totally, there’s a separate action for that - I think it’s called “list group members” and it has you select a specific group. If you search for “Groups” there’s a whole set of actions to do things with specific groups. :)
Assuming I have last modified date in the table, how can I list all Teams/Groups not modified in last 6 months, 1 year, etc.? I'm not very good with DAX and not sure what formula to use for this.
I would just drop the modified date into the filter pane or a slicer - there's a relative date option there that will let you do last x days/months/years. You'd only need DAX if you were trying to measure something related to the modified date (like counting stale groups).
I know, sorry :| I have the code snippets scattered through comment responses, I'll just pull them together into the video description to consolidate--
Thank you for this video. I'm trying to use this workflow to get group members from a security group in Entra. I'm hoping this will work. I think I added the Append to array variable groupMembers the way you described, but I'm not sure since I can't see the detail in the video. I keep getting a error message that says, Enter a valid JSON. Is this correct? { "upn":"@{item()?['userPrincipalName']}", "userid":"@{item()?['id']}", "groupid"@{item()?['id']}", "userrole": "Member" } Any ideas? Well, I answered my own question when I pasted my action here. I missed a : and " on groupid.
Hi Christine thank you for your work. I am facing an issue, after I ran the first test, the HTTP request step only returns odata.context and value lines. Here is no result like: odata.type, id, businessphones etc. and the flow runs around 10 minutes every time. Could you please let me know how can I fix this issue?
There’s not a direct query source for this that I’m aware of. You could schedule this for once a day though, I can’t see a reason that you’d need it fresher than that in normal use.
User permissions are in the Power BI Admin API: learn.microsoft.com/en-us/rest/api/power-bi/admin/users-get-user-artifact-access-as-admin The PBI admin API is on my to-do list for sure, it’s just a matter of time - been short on that lately. 😅
the send an http action fails for with "internal server error". I can use same query in graph explorer and get owners but not via send http action of O365 group. The user creating flow is Teams and power platform admin, not sure why this isn't working.
Hmm, I'm not sure offhand why that would be. I vaguely recall testing this with a non-privileged test account so I don't think it's permissions. Is the HTTP connector blocked perhaps? Or double check the code, usually when I get internal errors with these it's because I forgot a comma or a bracket or something.
@@bi-ome appreciate your reply to my comment. I figured out I had enclosed Group ID in the graph query within curly braces, after hours of trial removing those worked. Wish the error was more meaningful! In my case I'm fetching Group IDs from SPO list as I need to only report on a handful of Team members. I liked the way you're reporting the result of flow in P BI, really cool :)
I think you could probably use the HTTP action to call the Graph API to get this. I can't find an example in the documentation, but this thread has an example where they get groups for a specific user that would probably work. You'd use this on the MS Forms trigger for when a response is created and pull the submitter's username from the trigger (you might need to get their user ID from the "get user profile" first, I can't recall if the email will work directly). edit - forgot the link: stackoverflow.com/questions/73048102/how-to-receive-list-of-groups-that-user-is-member-of-in-powerapp
I am trying to adapt your tutorial and loop through members of one particular big group (the first one) to get their emails and then send a http request to graph api where I query users/mail/transitivemberof/ a second group that starts with a name in order to return the createddatetime when that user has been added to the second group. And for the life of me I cannot figure out how to return a json with a user email, created date time (date when an user was added to second group) and display name of that group. Can you please advise how to return that?
I don't think you can query the timestamp that a user was added to a group with this - I think you'd have to get that from the audit log activity data. My guess is the created datetime for a user is the date the user was created. If you're trying to get group membership though, I would query to get the group ID with the "starts with" first, then do a second query to get the members of that group ID instead of trying to do both in the same query.
Is there a way to make the query to run through a specific list of groups? instead through all of them? even with pagination, my organisation has way too much groups
Yes, you can either just put a list of the group IDs in an array and loop over those if you know which you want, or you can use filters on a Graph API query to get a list of specific groups on some criteria: learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http (then use the resulting group IDs in your loop).
I think you'd have to query the audit logs for that. I found a thread on it here but there's not a ton of info on this. The audit log history only goes back so far, too, so you'd have to store it when you query to build a history: stackoverflow.com/questions/76235207/how-can-i-find-when-a-user-was-added-to-an-azure-ad-group-using-the-microsoft-g
Hi Christine. I am facing the same issue as another was having, trying to get the schema from the HTTP request step it only returns odata.context and value [ ]. I was using the new design but I toggled that off and I'm still getting the same result. What am I doing wrong?
From the Apply to each List Group members it seems as though the first Group Id is blank, would this cause the HTTP request to return only the odata.context and value?
Did you possible paste in the content as the example and not the body output? There are a couple of boxes in the flow history for the step, make sure to copy the right one. The schema I used was this, in case you want to just roll with that: { "type": "object", "properties": { "value": { "type": "array", "items": { "type": "object", "properties": { "id": { "type": "string" }, "displayName": { "type": [ "string", "null" ] }, "givenName": { "type": "string" }, "surname": { "type": "string" }, "userPrincipalName": { "type": "string" }, "@odata.type": { "type": "string" } }, "required": [ "@odata.type", "id", "displayName", "givenName", "mail", "surname", "userPrincipalName" ] } }, "@odata.context": { "type": "string" } } }
Sorry, I've started using UI zoom the last couple months for that reason! 😅 { "userId": "@{items('Apply_to_each')?['Id']}", "upn": "@{items('Apply_to_each')?['UserPrincipalName']}", "managerUpn": "@{outputs('Get_manager_(V2)')?['body/userPrincipalName']}", "displayName": "@{items('Apply_to_each')?['DisplayName']}", "department": "@{items('Apply_to_each')?['Department']}" }
Here you go, this is to get owners - replace the bracket bit with your dynamic content: graph.microsoft.com/v1.0/groups//owners edit... TH-cam does not like the hyperlink formatting, but you get the idea 🤨
It helps if you set the video quality, sometimes it will default to low. I have since started using UI scaling but I can’t retroactively fix without re-recording
Y'all, someone pointed out that the create file action should be outside the loop - they are TOTALLY right, WHOOPS. Put it under the loop instead of in the loop. 😂 Also, I realize the resolution is hard to read, sorry! I put all of the code in the video description if you're having trouble seeing it in the recording. I now use UI zoom on newer recordings. :)
I guess you may need to give a short update on how and why the Flow takes so long to fetch the data; for me, it is taking over 1-2 hours, but I am cancelling the flow. Or even better, Action 'Parse_JSON' failed after 4h. Interestingly, I see JSON export and how it is getting bigger, but it doesn't end.
Something is not correct, I assume =)
@@mirrrvelll5164 uhhh, yeah that’s strange! Are you in a large org? I wouldn’t use this if you work somewhere with like 10k employees for instance, usually they have a more robust process for it. It’s weird that the parse is the part taking a long time, usually it’s the for-each loops
@@bi-ome Well, it is not 10k but around 4k (not bigger). And like 200 groups.
@@mirrrvelll5164 I haven't tried it with that many so it's hard to say, but that sounds semi-reasonable. It might be an issue with your filter if it's that step that's taking a long time and not the loop. PowerShell can also get group membership data in a more scalable way, or Logic Apps has virtually the same UI as Power Automate but also scales better.
I've got over 6000 groups. It takes hours for me.
As someone coming to PowerAutomate from a Systems Admin background I love the way you present these things in my language. Very easy to understand, information dense presentation with useful tools that I can apply to a range of automation tasks.
😄 Perhaps it’s that I’m also a former sys admin!
Great video, but the resolution is too high --> hard to read what is happening
Noted, thanks :)
Thanks for the video Christine, this was really useful :)
Very impressive work!
This is great, until I get to the 15:00 mark...I cannot find "body" in the list groups content.
If you're in modern UI, switch to classic. It's not in modern yet. Also make sure to search for it, it's not there unless you search. Lol.
Thanks, great solution and its working a treat !
Thanks for this awesome idea on how to get the group details into Power BI. One remark though: It seems you're recreating the files again and again while you're iterating through the groups. I think the compose action and the create file actions should be moved outside the loop, right?
You're totally right, good catch >.
Brilliant! Thanks Christine 🙂
Hi Christine, this was very useful, thank you. List Groups returned me over 6k results and when I used the "Filter rows" (e.g. startswith(displayName,'PMO') ), I could retrieve the groups applicable to my requirement. These groups are used in Project Online (PWA) & the PWA interface did not have an export feature to provide me with group members/owners. With your technique it's possible and allows us to review and manage group memberships.
There is also a link in the platform where users can go to manage groups they own: myaccount.microsoft.com/groups/groups-i-own - FYI!
This is great thanks for the video. 2 questions:
1. How can i just grab Entra and M365 roles only and do the same process?
2. I have thousandgs of groups (inherited from someone else) so the process takes hours. ANy way to filter it do extract certain groups based on certain criteria like the group name contain certain text etc.
You’d need to use the Graph API, it’ll let you get extra fields and filter the query more precisely. MS has a documentation page with syntax-
Super useful, Thanks for the video. I have large number of groups in the organization > 25000. Is it possible to retrieve all those M365 groups that are public?
I think so, but you'd have to use the Graph API instead of the connector - that'll let you put a filter in the query. It looks like from the output on this page that there's a property for "visibility" that shows Public as a value, so I thiiiiink you could probably use that on your "get" call following the filter pattern they use in the other examples, but I've not tried it. learn.microsoft.com/en-us/graph/api/group-list?view=graph-rest-1.0&tabs=http
If you do and it works let me know :)
I was trying to do the sme but for members in a team channel, but I wasn't able to igure it out, I tried with and http request but it returns errors as my app does not have the required permissions, ChannelMember.Read.All. DO you know a way to get the members of Channel?
Can you not grant your app permissions if you’re already working with an app? Usually getting the app in their first place is the hurdle
Why did you do this with power automate in the middle, instead of directly via MS Graph queries inside PowerBI only?
Thinking the method in the video means you've got priviledged data sitting in SharePoint in raw format in your JSONs
Querying APIs directly in PBI is a pain, particularly when you get into pagination… if I were to do it anywhere serious it’d be a real ETL tool to put it in a database table, but this isn’t a data engineering channel.
SharePoint is as secure as the site you store it in… I would not put this in a public site obviously, but I think it’d be fine in an IT team site or similar. In the grand scheme of things user and group membership is not particularly sensitive in comparison to what is regularly stored in SP in everyday use.
Quite interesting video, but very hard to see.
There where you defined "Upn", "grouped", and so on. Could you tell me the exact format/syntax?
{
"upn": "UPN",
} `?
I am running this part where "https" comes in, but it is taking over 10 minutes and got an error: "Flow run timed out".
I have a lot of users (like 3-4k) and maybe around 200 groups together, which is true. I adjusted the pagination, but it still takes time. Is that normal?
Thanks!
I would expect it to take over 10 minutes for that many, the for-each loops take a long time. You might try setting the degrees of parallelism to 1 in the settings on the loops - slowing it down actually speeds it up because it keeps you from getting throttled.
Are you looking for the append to members array text? That one is this:
{
"upn": "@{items('Apply_to_each_group_member')?['userPrincipalName']}",
"userId": "@{items('Apply_to_each_group_member')?['id']}",
"groupId": "@{items('Apply_to_each')?['id']}",
"userRole": "Member"
}
Append to owners array is this:
{
"upn": "@{items('Get_owners_for_each_group')?['userPrincipalName']}",
"userId": "@{items('Get_owners_for_each_group')?['id']}",
"groupId": "@{items('Apply_to_each')?['id']}",
"userRole": "Owner"
}
The compose step after that is this:
[
@{variables('groupMembers')},
@{variables('groupOwners')}
]
@@bi-ome Thanks for your support. I managed to get it to work after 58 minutes of running! It gave an error. =) However, interestingly, I got my export. Both are in SP (I can use data in Power BI), and one file shows me over 11k rows even though I put a limit of 1000. How is that possible?
This is an error: (all the steps are in green..)
ActionFailed. An action failed. No dependent actions succeeded.
p.s If I use parallelism of 1, I am getting just some users and groups, which is wrong.
This is great! Thanks. Unfortunately my flow fails as there's too many groups/members in my environment. Is there a way to get members from only one specific group?
Oh totally, there’s a separate action for that - I think it’s called “list group members” and it has you select a specific group. If you search for “Groups” there’s a whole set of actions to do things with specific groups. :)
Assuming I have last modified date in the table, how can I list all Teams/Groups not modified in last 6 months, 1 year, etc.?
I'm not very good with DAX and not sure what formula to use for this.
I would just drop the modified date into the filter pane or a slicer - there's a relative date option there that will let you do last x days/months/years. You'd only need DAX if you were trying to measure something related to the modified date (like counting stale groups).
Brilliant work, really helpful but is there a document with steps as the fonts are very very small unable to see anything
I know, sorry :| I have the code snippets scattered through comment responses, I'll just pull them together into the video description to consolidate--
Thank you for this video. I'm trying to use this workflow to get group members from a security group in Entra. I'm hoping this will work. I think I added the Append to array variable groupMembers the way you described, but I'm not sure since I can't see the detail in the video. I keep getting a error message that says, Enter a valid JSON. Is this correct? {
"upn":"@{item()?['userPrincipalName']}",
"userid":"@{item()?['id']}",
"groupid"@{item()?['id']}",
"userrole": "Member"
}
Any ideas? Well, I answered my own question when I pasted my action here. I missed a : and " on groupid.
It's always the typos with the hand-typing JSON! :)
Hi Christine thank you for your work. I am facing an issue, after I ran the first test, the HTTP request step only returns odata.context and value lines.
Here is no result like: odata.type, id, businessphones etc. and the flow runs around 10 minutes every time. Could you please let me know how can I fix this issue?
Are you in the new UI? Try toggling it off if you are, it's missing a lot of the dynamic content cards.
@@bi-ome Christine, thank you for you response. No, I am using the old UI. Is there any additional step I need to do before create this flow?
How to build a direct-query report and get the Group membership on-demand ?
There’s not a direct query source for this that I’m aware of. You could schedule this for once a day though, I can’t see a reason that you’d need it fresher than that in normal use.
Hi Christine, I was thinking to get list of users from Power BI workspaces, is there any way to do that? can you make a video for it?
User permissions are in the Power BI Admin API: learn.microsoft.com/en-us/rest/api/power-bi/admin/users-get-user-artifact-access-as-admin
The PBI admin API is on my to-do list for sure, it’s just a matter of time - been short on that lately. 😅
the send an http action fails for with "internal server error".
I can use same query in graph explorer and get owners but not via send http action of O365 group.
The user creating flow is Teams and power platform admin, not sure why this isn't working.
Hmm, I'm not sure offhand why that would be. I vaguely recall testing this with a non-privileged test account so I don't think it's permissions. Is the HTTP connector blocked perhaps? Or double check the code, usually when I get internal errors with these it's because I forgot a comma or a bracket or something.
@@bi-ome appreciate your reply to my comment.
I figured out I had enclosed Group ID in the graph query within curly braces, after hours of trial removing those worked. Wish the error was more meaningful!
In my case I'm fetching Group IDs from SPO list as I need to only report on a handful of Team members.
I liked the way you're reporting the result of flow in P BI, really cool :)
Is there a way to use a forms as trigger and to get the list of the groups that owned by the responder on forms.
I think you could probably use the HTTP action to call the Graph API to get this. I can't find an example in the documentation, but this thread has an example where they get groups for a specific user that would probably work. You'd use this on the MS Forms trigger for when a response is created and pull the submitter's username from the trigger (you might need to get their user ID from the "get user profile" first, I can't recall if the email will work directly).
edit - forgot the link: stackoverflow.com/questions/73048102/how-to-receive-list-of-groups-that-user-is-member-of-in-powerapp
I am trying to adapt your tutorial and loop through members of one particular big group (the first one) to get their emails and then send a http request to graph api where I query users/mail/transitivemberof/ a second group that starts with a name in order to return the createddatetime when that user has been added to the second group.
And for the life of me I cannot figure out how to return a json with a user email, created date time (date when an user was added to second group) and display name of that group.
Can you please advise how to return that?
I don't think you can query the timestamp that a user was added to a group with this - I think you'd have to get that from the audit log activity data. My guess is the created datetime for a user is the date the user was created. If you're trying to get group membership though, I would query to get the group ID with the "starts with" first, then do a second query to get the members of that group ID instead of trying to do both in the same query.
Is there a way to make the query to run through a specific list of groups? instead through all of them? even with pagination, my organisation has way too much groups
Yes, you can either just put a list of the group IDs in an array and loop over those if you know which you want, or you can use filters on a Graph API query to get a list of specific groups on some criteria: learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http (then use the resulting group IDs in your loop).
@@bi-ome thank you! And do you know maybe a way to get a date of when user was added to the group?
I think you'd have to query the audit logs for that. I found a thread on it here but there's not a ton of info on this. The audit log history only goes back so far, too, so you'd have to store it when you query to build a history: stackoverflow.com/questions/76235207/how-can-i-find-when-a-user-was-added-to-an-azure-ad-group-using-the-microsoft-g
@@bi-ome and do you know maybe how to query GRAPH API through “run query against a dataset” with just one column with emails?
Hi Christine. I am facing the same issue as another was having, trying to get the schema from the HTTP request step it only returns odata.context and value [ ]. I was using the new design but I toggled that off and I'm still getting the same result. What am I doing wrong?
From the Apply to each List Group members it seems as though the first Group Id is blank, would this cause the HTTP request to return only the odata.context and value?
Did you possible paste in the content as the example and not the body output? There are a couple of boxes in the flow history for the step, make sure to copy the right one. The schema I used was this, in case you want to just roll with that:
{
"type": "object",
"properties": {
"value": {
"type": "array",
"items": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"displayName": {
"type": [
"string",
"null"
]
},
"givenName": {
"type": "string"
},
"surname": {
"type": "string"
},
"userPrincipalName": {
"type": "string"
},
"@odata.type": {
"type": "string"
}
},
"required": [
"@odata.type",
"id",
"displayName",
"givenName",
"mail",
"surname",
"userPrincipalName"
]
}
},
"@odata.context": {
"type": "string"
}
}
}
@@evealsdorf7402 Possibly - definitely feed it an example with data in it! I am not sure how it could have a blank group id, that's strange.
Append to array Variable step is throwing an error "Enter a valid JSON"
Usually this means you have a typo somewhere in there - extra or missing comma, curly bracket, or quotation
can someone paste the Append to array Variable values step?-
can see due to high resolution
tks
Sorry, I've started using UI zoom the last couple months for that reason! 😅
{
"userId": "@{items('Apply_to_each')?['Id']}",
"upn": "@{items('Apply_to_each')?['UserPrincipalName']}",
"managerUpn": "@{outputs('Get_manager_(V2)')?['body/userPrincipalName']}",
"displayName": "@{items('Apply_to_each')?['DisplayName']}",
"department": "@{items('Apply_to_each')?['Department']}"
}
What is the https action's URI? can you please post it here?
Here you go, this is to get owners - replace the bracket bit with your dynamic content: graph.microsoft.com/v1.0/groups//owners
edit... TH-cam does not like the hyperlink formatting, but you get the idea 🤨
Great, thank you!
seriously, can you guys see the text on the screen?
It helps if you set the video quality, sometimes it will default to low. I have since started using UI scaling but I can’t retroactively fix without re-recording
@@bi-ome It's set to 1080p but still can't make out a lot of the text
So good, explained so well, but holy fk MS needs a better way. Thanks
🤣