Power Query: Search for variable within defined list

[Cubelife]

Basically, I'm trying to validate that test answers provided are within the defined list of accepted test answer values. This problem is frustratingly simple in theory, but I'm having a difficult time figuring out a solution within Power Query (PQ).

Example:
I have a table of test questions and their multiple choice answer values. I call this the "Key" table.

Question ID	Accepted Values
Q1	1​
Q1	2​
Q1	3​
Q1	4​
Q2	-2​
Q2	-1​
Q2	0​
Q2	1​

I also have a table with the respondent's name, question ID, and answer. I call this the "Data" table.

Name	Question ID	Answer
Jim	Q1	1​
Hank	Q1	6​
Steve	Q2	0​
Cindy	Q2	2​

So far I have two separate queries for each table titled the same as the table; so there's a "Key" and "Data" PQ. My trouble comes when I try to figure out how to validate the responses from "Data" against the "Key". My best guess is it will somehow be solved using concatenated fields (Method 1) or lists (Method 2) of the accepted values from the "Key" table which can then be compared against the answers.

Below is the M code for the "Key" PQ and the "Data" PQ which contains a merge with "Key". My issue with method 1 (lists) is that I can't get a conditional column to read the values within the list. My issue with method 2 is that negative values aren't correctly validated when creating my conditional column.

Method 1: Concatenated fields

Key PQ:
let
Source = Excel.CurrentWorkbook(){[Name="Key"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Question ID", type text}, {"Accepted Values", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Question ID", Text.Trim, type text}}),
#"Added Suffix" = Table.TransformColumns(#"Trimmed Text", {{"Accepted Values", each _ & ", ", type text}}),
#"Grouped Rows" = Table.Group(#"Added Suffix", {"Question ID"}, {{"Accepted Values", each Text.Combine([Accepted Values]), type text}})
in
#"Grouped Rows"


Data PQ:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Answer", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Question ID", Text.Trim, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text",{"Question ID"},Key,{"Question ID"},"Key",JoinKind.LeftOuter),
#"Expanded Key" = Table.ExpandTableColumn(#"Merged Queries", "Key", {"Accepted Values"}, {"Accepted Values"}),
#"Added Custom" = Table.AddColumn(#"Expanded Key", "QA Values", each if Text.Contains([Accepted Values],[Answer]) then 1 else 0)
in
#"Added Custom"


The below screenshot is the end result of the Data query. You can see that rows 1-3 correctly display a 1 or 0. Row 4 should result in a 0 because 2 is not in the list of accepted values. PQ doesn't recognize"-2" and only sees the number 2. I don't know how to get around this. You'll notice there are commas separating the accepted values. That comes from the "Key" query which adds a suffix to the end of each value. I thought that by removing the suffix it would work, but I still had no luck when removing the comma. I also tried using spaces but had the same result.

Method 2: Lists

Key PQ:
let
Source = Excel.CurrentWorkbook(){[Name="Key"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Question ID", type text}, {"Accepted Values", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Question ID", Text.Trim, type text}}),
#"Added Suffix" = Table.TransformColumns(#"Trimmed Text", {{"Accepted Values", each _ & ", ", type text}}),
#"Grouped Rows" = Table.Group(#"Added Suffix", {"Question ID"}, {{"Custom", each Text.Combine([Accepted Values]), type text}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Accepted Values", each {[Custom]})
in
#"Added Custom"

Data PQ:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Answer", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Question ID", Text.Trim, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text",{"Question ID"},Key,{"Question ID"},"Key",JoinKind.LeftOuter),
#"Expanded Key" = Table.ExpandTableColumn(#"Merged Queries", "Key", {"Accepted Values"}, {"Accepted Values"}),
#"Added Custom" = Table.AddColumn(#"Expanded Key", "QA Values", each if List.Contains([Accepted Values],[Answer]) then 1 else 0)
in
#"Added Custom"

The below screenshot is the end result of the Data query. You can see that the column "QA Values" is all 0's which we know to be incorrect. There's something about lists I just don't understand within this context. Even after expanding the results of the List I then am presented with the same issue as Method 1 because the values are no longer in a list format.

 

[lrobbo314]

Here is a way. I named the Key table 'tbl_Key' and the answers table 'tbl_Answers'.

Book5
	A	B	C	D	E	F	G	H	I	J	K	L
1	Question ID	Accepted Values			Name	Question ID	Answer		Name	Question ID	Answer	Valid Answer
2	Q1	1			Jim	Q1	1		Jim	Q1	1	TRUE
3	Q1	2			Hank	Q1	6		Hank	Q1	6	FALSE
4	Q1	3			Steve	Q2	0		Steve	Q2	0	TRUE
5	Q1	4			Cindy	Q2	2		Cindy	Q2	2	FALSE
6	Q2	-2
7	Q2	-1
8	Q2	0
9	Q2	1
Sheet1

tbl_Key Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Key"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Question ID", type text}, {"Accepted Values", Int64.Type}}),
    Group = Table.Group(Type, {"Question ID"}, {{"Count", each List.Transform(_[Accepted Values], Int64.From), type table}})
in
    Group

tbl_Answers Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Question ID", type text}, {"Answer", Int64.Type}})
in
    Type

Merged Table Code:

let
    Source = Table.NestedJoin(tbl_Answers,{"Question ID"},tbl_Key,{"Question ID"},"tbl_Key",JoinKind.LeftOuter),
    ExpandTbl_Key = Table.ExpandTableColumn(Source, "tbl_Key", {"Count"}, {"tbl_Key.Count"}),
    inList = Table.AddColumn(ExpandTbl_Key, "Valid Answer", each List.Contains([tbl_Key.Count],[Answer]))
in
    inList

 

[Cubelife]

Here is a way. I named the Key table 'tbl_Key' and the answers table 'tbl_Answers'.

Book5
	A	B	C	D	E	F	G	H	I	J	K	L
1	Question ID	Accepted Values			Name	Question ID	Answer		Name	Question ID	Answer	Valid Answer
2	Q1	1			Jim	Q1	1		Jim	Q1	1	TRUE
3	Q1	2			Hank	Q1	6		Hank	Q1	6	FALSE
4	Q1	3			Steve	Q2	0		Steve	Q2	0	TRUE
5	Q1	4			Cindy	Q2	2		Cindy	Q2	2	FALSE
6	Q2	-2
7	Q2	-1
8	Q2	0
9	Q2	1
Sheet1

tbl_Key Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Key"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Question ID", type text}, {"Accepted Values", Int64.Type}}),
    Group = Table.Group(Type, {"Question ID"}, {{"Count", each List.Transform(_[Accepted Values], Int64.From), type table}})
in
    Group

tbl_Answers Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Question ID", type text}, {"Answer", Int64.Type}})
in
    Type

Merged Table Code:

let
    Source = Table.NestedJoin(tbl_Answers,{"Question ID"},tbl_Key,{"Question ID"},"tbl_Key",JoinKind.LeftOuter),
    ExpandTbl_Key = Table.ExpandTableColumn(Source, "tbl_Key", {"Count"}, {"tbl_Key.Count"}),
    inList = Table.AddColumn(ExpandTbl_Key, "Valid Answer", each List.Contains([tbl_Key.Count],[Answer]))
in
    inList

LOVELY!!! Thank you for helping, that's going to save me a ton of time. So my problem was I needed to keep my lists as true lists with one value per row. Just takes some "group think" to get to the bottom of problems sometimes, love these forums. Thanks again!