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 val...
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
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 |
Hi Anonymous
Thank you for the suggestion.
I have tried this.
But now I am getting this error