Forum Discussion
SharePoint Online List column with multiple person values
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
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
4 Replies
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 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