How to create an access granted/ Request access column?

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.