I know, sorry :| I have the code snippets scattered through comment responses, I'll just pull them together into the video description to consolidate--
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).
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.
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.
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.
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.
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?
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. 😅
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. :)
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... RUclips does not like the hyperlink formatting, but you get the idea 🤨
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" } } }
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
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
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
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.
Brilliant! Thanks Christine 🙂
Thanks for the video Christine, this was really useful :)
Very impressive work!
Great video, but the resolution is too high --> hard to read what is happening
Noted, thanks :)
So good, explained so well, but holy fk MS needs a better way. Thanks
🤣
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--
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).
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.
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.
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!
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.
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.
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.
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?
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. 😅
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
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. :)
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... RUclips does not like the hyperlink formatting, but you get the idea 🤨
Great, thank you!
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.
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
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
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?