Tag Archives: person or group

Power BI and SharePoint Person or Group Field

Posted on by
Reply

I created a simple Power BI report to pull data from a SharePoint list and quickly ran into a problem with the Person or Group column type.

Error:
Expression.Error: We cannot convert the value “” to type Table.
Details:
Value=
Type=[Type]

In the report, I was trying to expand a Person or Group column, but not all of the field values were populated.

Here is the fix:

let
    Source = SharePoint.Tables("https://taco.sharepoint.com/sites/food", [Implementation="2.0", ViewMode="All"]),
    #"abcc-b8fe-4b23-be01-abc5f2c3320c" = Source{[Id="abcc-b8fe-4b23-be01-abc5f2c3320c"]}[Items],
    #"Expanded Assigned User2" = Table.TransformColumns(#"abcc-b8fe-4b23-be01-abc5f2c3320c", {{"Assigned User", each if Value.Is(_,type list) then _{0} else [title = "not assigned"], type record}} ),
    #"Expanded Assigned Analyst" = Table.ExpandRecordColumn(#"Expanded Assigned User2", "Assigned User", {"title"}, {"Assigned User.title"})
in
    #"Expanded Assigned User"

Person or Group Field Append or Remove a Group

Posted on by
Reply

Using PowerShell I needed to append a group to a Person or Group field in a list.  The same logic should apply for adding or removing a user.

Append Group:



$web = Get-SPweb "http://sharepointed.com/sites/taco/"
$list = $web.lists["Good Tacos"]

$groupName = "Taco Eaters"
$group = $web.SiteGroups[$groupName]
$GroupValue = new-Object Microsoft.SharePoint.SPFieldUserValue($web,$group.id, $group.Name)

foreach ($item in $list.items)
 {
 $groups = $item["GroupsField"]

$groups.Add($GroupValue)

$item["GroupsField"] = $groups
 $item.Update()
 }

Remove Group:



$web = Get-SPweb "http://sharepointed.com/sites/taco/"
$list = $web.lists["Good Tacos"]

$groupName = "Taco Eaters"
$group = $web.SiteGroups[$groupName]
$GroupValue = new-Object Microsoft.SharePoint.SPFieldUserValue($web,$group.id, $group.Name)

foreach ($item in $list.items)
{
$groups = $item["GroupsField"]

<span style="line-height: 1.5em;">$groupRemove = $groups | ? { $_.LookupId -eq $GroupValue.LookupId }</span>

$groups.Remove($groupRemove)

$item["GroupsField"] = $groups
$item.Update()
}