Forum Discussion
How to LOOKUP values in a list?
Hi,
I have two tables, one named "rooms" and another named "users". Rooms has a column named members, which is a list of user IDs. The Users table has an ID column and a Name column.
I've mapped rooms.members to users.id, and ensured it's active.
I'd like to know how to display a new column in the Rooms table that shows all of the user names represented by the IDs in the members column.
I've tried RELATED like this after creating a one-to-many relationship between rooms.members and users.id, but it doesn't seem to accept lists.
Hi Anonymous ,
We can use the following steps to meet your requirement:
1. split members column to rows
2. merge with the user table and expand the name column as User.name
3. group by the table as all rows
4. add two custom columns:
Text.Combine(Table.ToList(Table.SelectColumns([Data],{"Users.name"})),",")Text.Combine(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns([Data],{"members"}),{{"members", type text}})),",")All the queries are here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUXJz9Al2BdJA5AjEhmZKsTrRShZIMoZA7ASmQTKWaDLOcBkjFE2GOkYGQNoFiIEMsDyyVmMdEx1jCx1zHUMDHUOQORBNSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, is_archived = _t, members = _t, name = _t, owner = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"is_archived", type logical}, {"members", type text}, {"name", type text}, {"owner", Int64.Type}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"members", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "members"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"members", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"members"}, Users, {"id"}, "Users", JoinKind.LeftOuter), #"Expanded Users" = Table.ExpandTableColumn(#"Merged Queries", "Users", {"name"}, {"Users.name"}), #"Grouped Rows" = Table.Group(#"Expanded Users", {"id", "is_archived", "name", "owner"}, {{"Data", each _, type table [id=number, is_archived=logical, members=number, name=text, owner=number, Users.name=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "UserName", each Text.Combine(Table.ToList(Table.SelectColumns([Data],{"Users.name"})),",")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "MemberID", each Text.Combine(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns([Data],{"members"}),{{"members", type text}})),",")) in #"Added Custom1"
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared.
Best regards,
4 Replies
- parry2k
Super User
Anonymous how does your raw data looks like? It is not clear how your room table data looks like. Can you post some data?
parry2k ,
Thanks for the quick reply. I imported the data from two JSON files, and then split the columns to get what you see here. I've actually tried two versions of the rooms table, one with the rooms.members values split out into a comma-separated list, and one kept as a set of lists. I've included both here so you can get a look at the data.
Rooms table with lists:
Expanded rooms table:
Users table:
I'd like to have a new column in the rooms table where each cell has a list of names based on the IDs in rooms.members, which are looked up in users.id.
Let me know if any other info would be helpful.
Thanks
v-lid-msftCommunity Support
Hi Anonymous ,
We can use the following steps to meet your requirement:
1. split members column to rows
2. merge with the user table and expand the name column as User.name
3. group by the table as all rows
4. add two custom columns:
Text.Combine(Table.ToList(Table.SelectColumns([Data],{"Users.name"})),",")Text.Combine(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns([Data],{"members"}),{{"members", type text}})),",")All the queries are here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUXJz9Al2BdJA5AjEhmZKsTrRShZIMoZA7ASmQTKWaDLOcBkjFE2GOkYGQNoFiIEMsDyyVmMdEx1jCx1zHUMDHUOQORBNSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, is_archived = _t, members = _t, name = _t, owner = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"is_archived", type logical}, {"members", type text}, {"name", type text}, {"owner", Int64.Type}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"members", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "members"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"members", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"members"}, Users, {"id"}, "Users", JoinKind.LeftOuter), #"Expanded Users" = Table.ExpandTableColumn(#"Merged Queries", "Users", {"name"}, {"Users.name"}), #"Grouped Rows" = Table.Group(#"Expanded Users", {"id", "is_archived", "name", "owner"}, {{"Data", each _, type table [id=number, is_archived=logical, members=number, name=text, owner=number, Users.name=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "UserName", each Text.Combine(Table.ToList(Table.SelectColumns([Data],{"Users.name"})),",")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "MemberID", each Text.Combine(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns([Data],{"members"}),{{"members", type text}})),",")) in #"Added Custom1"
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared.
Best regards,
