SharePoint Online List column with multiple person values

[Anonymous]

Hi 

I have a report that's built off of a SP list. In this list there's a column [IT Resources]. This is being populated by using the Org's address book. And this filed also contains multiple values. 

Currently the PBI report has a manually maintained a table of User Names and User IDs that's to be used for the report. I am trying to get away from maintaining this manual table, and start using the data that's coming directly from the SP. 

Following the instructions from this link I have extracted the [IT Resources] names by expanding the [FieldVAluesAsText] - 

- https://whitepages.unlimitedviz.com/2018/01/using-power-bi-to-report-on-person-fields-in-sharepoint/ 

Then I have also expanded the [IT ResourcesID] column by Extract Values. 

I need both these fields. 

I end up with 

The ID's in column [IT ResourceId] are in sequence to the names in [FieldValuesAsText.IT_x0020_Resources]. 

How do I go about transposing these 2 columns so the right ID lines up with the right name? Once I get rid of the 1st column [ID] I want to end up with a unique list of users and their IDs. 

I've tried spliting, say the [FieldValuesAsText.IT_x0020_Resources] column, and then Unpivoting these columns. But then how do I treat the [IT ResourceId] to line up with these new rows without duplicating. 

Hoping someone has a (better) solution. 

Thank you

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXK0drJ2BtEFBTmp1k6JeUBo7ZxYVJRfohSrE61kBJKDyYNFjIE8F2tXEJmfbu2ak1qQkZgHVBwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ResourcesID = _t, ResourcesNames = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Transform(List.Zip({Text.Split([ResourcesID], ";"), Text.Split([ResourcesNames], ";")}), each [ResID = _{0}, ResName = _{1}])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ResourcesID", "ResourcesNames"}),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"ResID", "ResName"}, {"ResID", "ResName"})
in
    #"Expanded Custom1"

Pat

[Anonymous]

@mahoneypat this WORKED!! I just used the "#Added custom" part which worked perfectly. 

Thank you! 

[Vera_33]

Hi @Anonymous 

If I understand it correctly, you need something like this? id vs its value without duplicates? And the original two columns are List? then you can try this way, then you can Expand to New Rows, and split them by delimiters

Table.AddColumn(yourPreviousStep, "Custom", each List.Transform( List.Zip({[yourIDColumn],[yourValueColumn]}),each Text.Combine(_,"=")))

IT Resourcesid	FieldValuesAsText
30	a
1573	b
1721	c
…	…
86	d

[Anonymous]

Hi @Vera_33 

Thank you for the suggestion.

I have tried this. 

But now I am getting this error