Power Query: Search for variable within defined list

Cubelife

New Member
Joined
Feb 21, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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 IDAccepted 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.

NameQuestion IDAnswer
JimQ1
1​
HankQ1
6​
SteveQ2
0​
CindyQ2
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.

1610464674384.png




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.

1610465818271.png
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
Here is a way. I named the Key table 'tbl_Key' and the answers table 'tbl_Answers'.

Book5
ABCDEFGHIJKL
1Question IDAccepted ValuesNameQuestion IDAnswerNameQuestion IDAnswerValid Answer
2Q11JimQ11JimQ11TRUE
3Q12HankQ16HankQ16FALSE
4Q13SteveQ20SteveQ20TRUE
5Q14CindyQ22CindyQ22FALSE
6Q2-2
7Q2-1
8Q20
9Q21
Sheet1


tbl_Key Code:
Power Query:
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:
Power Query:
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:
Power Query:
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
 
Solution

Cubelife

New Member
Joined
Feb 21, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Here is a way. I named the Key table 'tbl_Key' and the answers table 'tbl_Answers'.

Book5
ABCDEFGHIJKL
1Question IDAccepted ValuesNameQuestion IDAnswerNameQuestion IDAnswerValid Answer
2Q11JimQ11JimQ11TRUE
3Q12HankQ16HankQ16FALSE
4Q13SteveQ20SteveQ20TRUE
5Q14CindyQ22CindyQ22FALSE
6Q2-2
7Q2-1
8Q20
9Q21
Sheet1


tbl_Key Code:
Power Query:
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:
Power Query:
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:
Power Query:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,420
Messages
5,624,679
Members
416,039
Latest member
Aber05

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top