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
mahoneypat
Microsoft Employee
Microsoft EmployeeHere'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