Forum Discussion
How to create an access granted/ Request access column?
Hello there,
I am working on a Power BI Tenant dashboard. We want to create a dashboard and publish on power bi service, This dashboard will serve users to check for which workspace do they have access. I have Workspaces field, Access field and Access request field. For the Access fiels I want to make it dynamic using the user who loged in to the service.
| Workspaces | Access | Access Request |
| A | Granted | Click here to request Access |
| B | Request | Click here to request Access |
| C | Granted | Click here to request Access |
I want to create something like the above table. I have a username and Email fields on my user table.
Can someone help me on this please?
Hi Anonymous ,
You can use this API to get all the workspaces in your organization and their members. Like this.
GET https://api.powerbi.com/v1.0/myorg/admin/groups?$filter=state eq 'Active'&$expand=users&$top=5000
- First you need to register an application in Azure AD and then use these information to get an access token.
Register an Azure AD application to use with Power BI
() => let body = "grant_type=password&&resource=https://analysis.windows.net/powerbi/api&&response_type=code&&client_id=xxxxx&&client_secret=xxxx&&username=xxxx.com&&password=xxxx", Data=Json.Document(Web.Contents("https://login.microsoftonline.com/common/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])), access_token = Data[access_token] in access_token- Then use the access token to get the workspaces and users.
let Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/groups?$filter=state eq 'Active'&$expand=users&$top=5000", [Headers=[Authorization="Bearer "& GetAccessToken() ]])), #"Converted to Table" = Record.ToTable(Source), #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "value")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}), #"Expanded Value" = Table.ExpandListColumn(#"Removed Columns", "Value"), #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "hasWorkspaceLevelSettings ", "name", "users"}, {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "hasWorkspaceLevelSettings ", "name", "users"}), #"Expanded users" = Table.ExpandListColumn(#"Expanded Value1", "users"), #"Expanded users1" = Table.ExpandRecordColumn(#"Expanded users", "users", {"emailAddress", "groupUserAccessRight", "displayName", "identifier", "principalType"}, {"emailAddress", "groupUserAccessRight", "displayName", "identifier", "principalType"}), #"Added Custom" = Table.AddColumn(#"Expanded users1", "Workspace Link", each "https://app.powerbi.com/groups/" & [id] & "/list") in #"Added Custom"- Then set up dynamic RLS, publish to Service, and add users/groups to the RLS role.
Dynamic Row Level Security with Power BI Made Simple
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- First you need to register an application in Azure AD and then use these information to get an access token.
1 Reply
Hi Anonymous ,
You can use this API to get all the workspaces in your organization and their members. Like this.
GET https://api.powerbi.com/v1.0/myorg/admin/groups?$filter=state eq 'Active'&$expand=users&$top=5000
- First you need to register an application in Azure AD and then use these information to get an access token.
Register an Azure AD application to use with Power BI
() => let body = "grant_type=password&&resource=https://analysis.windows.net/powerbi/api&&response_type=code&&client_id=xxxxx&&client_secret=xxxx&&username=xxxx.com&&password=xxxx", Data=Json.Document(Web.Contents("https://login.microsoftonline.com/common/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])), access_token = Data[access_token] in access_token- Then use the access token to get the workspaces and users.
let Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/groups?$filter=state eq 'Active'&$expand=users&$top=5000", [Headers=[Authorization="Bearer "& GetAccessToken() ]])), #"Converted to Table" = Record.ToTable(Source), #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "value")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}), #"Expanded Value" = Table.ExpandListColumn(#"Removed Columns", "Value"), #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "hasWorkspaceLevelSettings ", "name", "users"}, {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "hasWorkspaceLevelSettings ", "name", "users"}), #"Expanded users" = Table.ExpandListColumn(#"Expanded Value1", "users"), #"Expanded users1" = Table.ExpandRecordColumn(#"Expanded users", "users", {"emailAddress", "groupUserAccessRight", "displayName", "identifier", "principalType"}, {"emailAddress", "groupUserAccessRight", "displayName", "identifier", "principalType"}), #"Added Custom" = Table.AddColumn(#"Expanded users1", "Workspace Link", each "https://app.powerbi.com/groups/" & [id] & "/list") in #"Added Custom"- Then set up dynamic RLS, publish to Service, and add users/groups to the RLS role.
Dynamic Row Level Security with Power BI Made Simple
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- First you need to register an application in Azure AD and then use these information to get an access token.