Forum Discussion
Is it Possible to Find a Match between a Column in a Table and a Cell in Another Table?
Hello,
I'm trying to use Power Query to Perform a Look up or a search between the values in a column from a Table to the values in cells from another Table.
For Example.
Here is table 1. It list the states I need to ID in cell values from table 2.
| STATE |
| NC |
| VA |
| SC |
| GA |
| FL |
Here is table 2. It shows a pathway of States traveled. I am trying to find a way to output if any STATE in Table 1 is within any cell value/Row in column STATES TRAVELED in Table 2 then Output "YES"
| EMPLOYEE | STATES TRAVELED | (Output) |
| John | AK, WA, NV | No |
| Paul | AK, NC, NV, GA | Yes |
| Sam | NC, NC, FL, VA | Yes |
| Amy | SC, NC, FL | Yes |
| Kate | TX, AZ, NM | No |
Is this possible?
THANK YOU FOR YOUR HELP!
As a general rule, powerbi prefers columnar data rather than, for example, comma-separated lists within a field.
Anyway, here goes
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXL01lEId9RR8AtTitWJVgpILM2Bivo5g0R1FNwdwTLBiblACbAgELv56CiEQSQccyuBEsFwCbCgd2JJKlA0JEJHwTEKKOOrFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [EMPLOYEE = _t, #"STATES TRAVELED" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPLOYEE", type text}, {"STATES TRAVELED", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "STATES TRAVELED", "STATES TRAVELED - Copy"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"STATES TRAVELED", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "STATES TRAVELED"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"STATES TRAVELED", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"STATES TRAVELED", Text.Trim, type text}}), #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each if (List.Contains(TableStates[STATE], [STATES TRAVELED] )) then "Yes" else "No"), #"Grouped Rows" = Table.Group(#"Added Custom", {"EMPLOYEE", "STATES TRAVELED - Copy"}, {{"Visited Any", each List.Max([Custom]), type text}}) in #"Grouped Rows"
1 Reply
- HotChilli
Community Champion
As a general rule, powerbi prefers columnar data rather than, for example, comma-separated lists within a field.
Anyway, here goes
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXL01lEId9RR8AtTitWJVgpILM2Bivo5g0R1FNwdwTLBiblACbAgELv56CiEQSQccyuBEsFwCbCgd2JJKlA0JEJHwTEKKOOrFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [EMPLOYEE = _t, #"STATES TRAVELED" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPLOYEE", type text}, {"STATES TRAVELED", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "STATES TRAVELED", "STATES TRAVELED - Copy"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"STATES TRAVELED", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "STATES TRAVELED"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"STATES TRAVELED", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"STATES TRAVELED", Text.Trim, type text}}), #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each if (List.Contains(TableStates[STATE], [STATES TRAVELED] )) then "Yes" else "No"), #"Grouped Rows" = Table.Group(#"Added Custom", {"EMPLOYEE", "STATES TRAVELED - Copy"}, {{"Visited Any", each List.Max([Custom]), type text}}) in #"Grouped Rows"