# Power Query: Allowed values



## Mer333 (Dec 10, 2014)

Hi all!

I have a nice idea in my mind. Does anyone know how to check whether value or text in a column contains in an Allowed values list?

To be more specific. 

Imagine that we have a column like:

TanksCarsPlanesBikesApples

<tbody>

</tbody>
And I know that Allowed values for that column only: 
TanksCarsPlanesBikes


<tbody>

</tbody>

So I need a function which would check every value in every row and return true or false into new custom column.

Like

TankstrueCarstruePlanestrueBikestrueApplesfalse

<tbody>

</tbody>


----------



## Ken Puls (Dec 10, 2014)

Here's how I did this.

I created two tables:

Table one is your table with all values (including Apple).  I added a header called Values, and called the table Values.  I then pulled it into Power Query, and just chose to Close and Load, creating a connection only.  (So it shows in the Workbook Queries pane as "load is disabled"

Table two is your table with the acceptable values (no Apple).  I added a header called Allowed, and called the table Allowed.  I then pulled that one into Power Query as well, again choosing to Cloe and Load, creating a connection only.  (I now have two queries showing in the Workbook Queries pane showing "load is disabled".

Next I went to the Power Query tab and clicked "Merge" to create a new query.

I selected Values first, Allowed in the second box.  I then selected each column in their respective windows and clicked OK.
Once in Power Query, I expanded the second column... notice how the last line reads "null"
I added a new column, and used the following formula:  if [NewColumn.Allowed] = null then "False" else "True"

Hope that helps,


----------



## Mer333 (Dec 11, 2014)

Hhhm. I suppose it should be legit. I will try it in a real example which is more complex than two columns. Also to be honest, I'll need to compare a lot of columns at the same time but not the whole table... Not sure yet that it will work...

I also asked Chris Webb about it and he sent me a link to his post on this matter - https://cwebbbi.wordpress.com/2014/01/27/comparing-columns-in-power-query/

But it's still not an answer. Like I wrote to him I  have a huge table with o lot of columns. And for some of them I have a lists of Allowed values. I need to check all rows of those specific columns and see where they don’t match my allowed values. I’m thinking about writing a few functions with lists of allowed values but not sure yet how to do it.

I'll create more specific example:

*column1**column2**column3**column4**column5**column6*monkey1girlsky3Proelephant2girl___s ky6Newbiedino1girlstar4Pro

<tbody>

</tbody>
And a list of allowed values would be:

*column1**column3**column4**column6*monkeygirlskyProelephantboystarNewbie

<tbody>

</tbody>
So the result ideally should looks like:

*column1**column2**column3**column4**column5**column6**Checking**Problem*monkey1girlsky3Protrueelephant2girl___s ky6Newbiefalsecolumn3, column4dino1girlstar4Profalsecolumn1

<tbody>

</tbody>


----------



## Ken Puls (Dec 11, 2014)

Okay, so this isn't the prettiest, and it could be cleaned up a LOT by setting it up using functions, but I just wanted to start with the fact that it can be done.

I've uploaded a sample on my OneDrive:  https://onedrive.live.com/redir?resid=8A224897002E61E8!8683

(Just ignore any version messages if you get them.)


----------



## Mer333 (Dec 12, 2014)

Thank you, Ken! 

Very clear!

I'll think how to optimize it with functions.


----------



## ChrisWebb (Jan 22, 2015)

As requested, here's an Excel workbook that does this using a function: http://1drv.ms/1BKWAWE

Here's the code for the entire query:


```
let
    //Load input table to check
    InputTable = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    //Load validation table to check against
    ValidationTable = Excel.CurrentWorkbook(){[Name="Validation"]}[Content],
    //Define a function to check the input table row by row
    GetDifferences = (CurrentRow) =>
     let
      //Remove any fields from the current row that aren't in the validation table
      RemoveFields = Record.SelectFields(CurrentRow, Table.ColumnNames(ValidationTable), 1),
      //Get a list of field names
      CurrentRowFields = Record.FieldNames(RemoveFields),
      //Get a list of field values
      CurrentRowValues = Record.FieldValues(RemoveFields),
      //Find the number of fields
      NumberOfFields = List.Count(CurrentRowFields),
      //Get a list of numbers from 0 to NumberOfFields-1
      ListOfNumbers = List.Numbers(0,NumberOfFields-1),
      //Find the names of the fields that contain values that aren't in the equivalent column in the validation table
      GetDifferences = List.Transform(ListOfNumbers, each if List.Contains(Table.Column(ValidationTable, CurrentRowFields{_}), CurrentRowValues{_}) then null else CurrentRowFields{_}),
      RemoveNulls = List.RemoveNulls(GetDifferences)
     in
      RemoveNulls,
    //Add a calculated column to call the GetDifferences function
    ListDifferences = Table.AddColumn(InputTable, "ListDifferences", each GetDifferences(_)),
    //Add a calculated column to check if the ListDifferences list is empty
    Checking = Table.AddColumn(ListDifferences, "Checking", each List.IsEmpty(
[ListDifferences])),
    //Declare a function to combine text by comma
    CombineWithComma = Combiner.CombineTextByDelimiter(", "),
    //Get text containing all the non-matching column names
    Problem = Table.AddColumn(Checking, "Problem", each CombineWithComma(
[ListDifferences])),
    //Remove the ListDifferences column
    RemoveList = Table.RemoveColumns(Problem,{"ListDifferences"})
in
    RemoveList
```


----------



## Mer333 (Jan 23, 2015)

Chris, it's simply amazing!!! I would never do it myself... Brilliantly!!! Thank you so much!


----------



## billszysz (Jan 23, 2015)

If you want another one solution...... )
Link below is to my GoogleDrive.
https://drive.google.com/file/d/0B6UlMk8OzUrxYl8tUWw3d1JjSDQ/view?usp=sharing


Regards .... "guy from YT channel"


----------



## billszysz (Jan 23, 2015)

Sorry Chris... it doesn't work properly 
Put i.e "Yes" to G5 (Source table, Sheet1) and refresh Query.
Try my method if you want (post #8 this topic)

Regards


----------



## Mer333 (Jan 23, 2015)

Hi Bill!  Thank you for another good solution!  It really proves how many smart people there are among us!

Though Chris's function seems better because you are able to use it even against 100 columns.

Guys, any ideas how to check columns not separately but row by row?

I mean if I have a validation table where each row represents allowed values, not column.


----------



## Mer333 (Dec 10, 2014)

Hi all!

I have a nice idea in my mind. Does anyone know how to check whether value or text in a column contains in an Allowed values list?

To be more specific. 

Imagine that we have a column like:

TanksCarsPlanesBikesApples

<tbody>

</tbody>
And I know that Allowed values for that column only: 
TanksCarsPlanesBikes


<tbody>

</tbody>

So I need a function which would check every value in every row and return true or false into new custom column.

Like

TankstrueCarstruePlanestrueBikestrueApplesfalse

<tbody>

</tbody>


----------



## Mer333 (Jan 23, 2015)

billszysz said:


> Sorry Chris... it doesn't work properly
> Put i.e "Yes" to G5 (Source table, Sheet1) and refresh Query.
> Try my method if you want (post #8 this topic)
> 
> Regards



Hmm... You are right... Interesting what is wrong there. Something with last column.


----------



## billszysz (Jan 23, 2015)

Sorry Mer333....I am busy now...
I answer later.

Regards


----------



## ChrisWebb (Jan 23, 2015)

Bill -thanks for pointing that out. There was a bug that meant the last column wasn't checked. This is the fixed version:


```
let
    //Load input table to check
    InputTable = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    //Load validation table to check against
    ValidationTable = Excel.CurrentWorkbook(){[Name="Validation"]}[Content],
    //Define a function to check the input table row by row
    GetDifferences = (CurrentRow) =>
     let
      //Remove any fields from the current row that aren't in the validation table
      RemoveFields = Record.SelectFields(CurrentRow, Table.ColumnNames(ValidationTable), 1),
      //Get a list of field names
      CurrentRowFields = Record.FieldNames(RemoveFields),
      //Get a list of field values
      CurrentRowValues = Record.FieldValues(RemoveFields),
      //Find the number of fields
      NumberOfFields = List.Count(CurrentRowFields),
      //Get a list of numbers from 0 to NumberOfFields-1
      ListOfNumbers = List.Numbers(0,NumberOfFields),
      //Find the names of the fields that contain values that aren't in the equivalent column in the validation table
      GetDifferences = List.Transform(ListOfNumbers, each if List.Contains(Table.Column(ValidationTable, CurrentRowFields{_}), CurrentRowValues{_}) then null else CurrentRowFields{_}),
      RemoveNulls = List.RemoveNulls(GetDifferences)
     in
      RemoveNulls,


    //Add a calculated column to call the GetDifferences function
    ListDifferences = Table.AddColumn(InputTable, "ListDifferences", each GetDifferences(_)),
    //Add a calculated column to check if the ListDifferences list is empty
    Checking = Table.AddColumn(ListDifferences, "Checking", each List.IsEmpty(
[ListDifferences])),
    //Declare a function to combine text by comma
    CombineWithComma = Combiner.CombineTextByDelimiter(", "),
    //Get text containing all the non-matching column names
    Problem = Table.AddColumn(Checking, "Problem", each CombineWithComma(
[ListDifferences])),
    //Remove the ListDifferences column
    RemoveList = Table.RemoveColumns(Problem,{"ListDifferences"})
in
    RemoveList
```

The advantage of this approach over the others on this thread, and the reason why it's more complicated, is that it doesn't hard code any column names anywhere - you can add columns to the tables and change column names, and it will still work.

Chris


----------



## Mer333 (Jan 23, 2015)

Excellent Chris! Thanks! 

I'm sure that you know how to do the same but for the whole row (combination of columns).

Example of the idea:

Input:

*column1**column2**column3**column4**column5**column6*monkey1girl
sky3Proelephant
2
girl
sky
6Newbie
dino
1girlstar4Proelephant
3
girl
star
7
Newbie


<tbody>

</tbody>

Validation:

*column1*
*column3**column4*
*column6*monkey
girl
sky
Pro
dino
girl
starNewbie
elephant
girl
star
Newbie


<tbody>

</tbody>

 Output:

*column1**column2**column3**column4*
*column5**column6**Checking*
monkey
1girlsky3Protrue
elephant2girl
sky
6Newbiefalse
dino
1girlstar4Profalse
elephant
3
girl
star
7
Newbie
true


<tbody>

</tbody>


----------



## ChrisWebb (Jan 23, 2015)

Just to be clear - in this case each row in the Validation table represents a valid combination of values, and each row in the input table must contain a combination of values that is present in just one row in the Validation table?


----------



## ChrisWebb (Jan 23, 2015)

I think it might be as easy as this:


```
let
    //Load input table to check
    InputTable = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    //Load validation table to check against
    ValidationTable = Excel.CurrentWorkbook(){[Name="Validation"]}[Content],
    //Check if the Validation table contains a row that matches the current row in Input
    Checking = Table.AddColumn(InputTable, "Checking", each Table.Contains(ValidationTable, Record.SelectFields(_, Table.ColumnNames(ValidationTable), 1)))
in
    Checking
```


----------



## Mer333 (Jan 23, 2015)

Yeah, right. In other words, each row from input table must contain at least one combination from validation table.


----------



## ChrisWebb (Jan 23, 2015)

OK, then in that case the code from my last post should work.

Chris


----------



## Mer333 (Jan 23, 2015)

I don't have an access to my model till Monday but if it matches the logic above then it's it! The idea is to check combination of current row in input table against all combinations from validation table. Thanks in advance! I'll try the code as soon as I will be able to do it and inform about results!


----------



## Mer333 (Jan 28, 2015)

ChrisWebb said:


> OK, then in that case the code from my last post should work.
> 
> Chris


It works perfectly through seems a bit slow but results are exactly as expected.

Thanks Chris!


----------



## Mer333 (Dec 10, 2014)

Hi all!

I have a nice idea in my mind. Does anyone know how to check whether value or text in a column contains in an Allowed values list?

To be more specific. 

Imagine that we have a column like:

TanksCarsPlanesBikesApples

<tbody>

</tbody>
And I know that Allowed values for that column only: 
TanksCarsPlanesBikes


<tbody>

</tbody>

So I need a function which would check every value in every row and return true or false into new custom column.

Like

TankstrueCarstruePlanestrueBikestrueApplesfalse

<tbody>

</tbody>


----------



## ChrisWebb (Jan 28, 2015)

How slow is a bit slow? How many rows are there in each table, and what type of data source are you using? I was wondering if a more 'relational' approach might be better... can you test the two following alternative queries instead please?


```
let
    //Load input table to check
    InputTable = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    //Load validation table to check against
    ValidationTable = Excel.CurrentWorkbook(){[Name="Validation"]}[Content],
    //Join these tables on all columns present in the ValidationTable
    Source = Table.NestedJoin(InputTable,Table.ColumnNames(ValidationTable),ValidationTable,Table.ColumnNames(ValidationTable),"NewColumn"),
    //Does the join return a table with some rows in? If so, then it's a valid row
    IsJoinNotEmpty = Table.AddColumn(Source, "Custom", each not Table.IsEmpty([NewColumn])),
    //Remove the column with the joined table
    RemovedColumns = Table.RemoveColumns(IsJoinNotEmpty ,{"NewColumn"})
in
    RemovedColumns
```


```
let
    //Load input table to check
    InputTable = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    //Load validation table to check against
    ValidationTable = Excel.CurrentWorkbook(){[Name="Validation"]}[Content],
    //Join these tables on all columns present in the ValidationTable
    Source = Table.NestedJoin(InputTable,Table.ColumnNames(ValidationTable),ValidationTable,Table.ColumnNames(ValidationTable),"NewColumn"),
    #"Added Custom" = Table.AddColumn(Source, "RowCount", each Table.RowCount([NewColumn])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Checking", each [RowCount]>0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"NewColumn", "RowCount"})
in
    #"Removed Columns"
```


----------



## Mer333 (Jan 29, 2015)

A bit slow means few seconds.... Unfortunately my data is very little for now. Just 188 rows in input and 310 rows in Validation. Data type - text.

I just tested your new approaches. It's difficult to judge because they all runs in a few seconds but the last one with "each [RowCount]>0" looks a bit faster then others... I'm not sure anyway. I will test it again when my input will be at least a few thousands rows...


----------



## ImkeF (Feb 1, 2015)

You can improve speed by using this code:


```
let
    //Load input table to check
    InputTable_ = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    //Only needed in case validation table has blanks
    InputTable = Table.AddColumn(InputTable_, "NoBlanks", each "1"),
    ValidationTable_ = Excel.CurrentWorkbook(){[Name="Validation"]}[Content],
    //Only need in case validation table has blanks
    ValidationTable = Table.AddColumn(ValidationTable_, "NoBlanks", each "1"),
    Source = Table.NestedJoin(InputTable,Table.ColumnNames(ValidationTable),ValidationTable,Table.ColumnNames(ValidationTable),"NewColumn"),
    //Here comes the speed - no logical check needed
    Expand = Table.ExpandTableColumn(Source, "NewColumn", {List.Last(Table.ColumnNames(ValidationTable))}, {"1"}),
    AddColumn = Table.AddColumn(Expand, "Checking", each if [1] is null then "false" else "true"),
    RemoveColumn = Table.RemoveColumns(AddColumn,{"1", "NoBlanks", "Index"})
in
    RemoveColumn
```

If you want to spot the fields that didn't match, you can apply conditional formatting using formulas like: =ISNA(MATCH(A1;column1;0)).


----------

