Power Query: Allowed values

[Mer333]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Tanks[/TD]
[/TR]
[TR]
[TD]Cars[/TD]
[/TR]
[TR]
[TD]Planes[/TD]
[/TR]
[TR]
[TD]Bikes[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[/TR]
</tbody>[/TABLE]

And I know that Allowed values for that column only: [TABLE="class: grid, width: 500"]

<tbody>[TR]
[TD]Tanks[/TD]
[/TR]
[TR]
[TD]Cars[/TD]
[/TR]
[TR]
[TD]Planes[/TD]
[/TR]
[TR]
[TD]Bikes[/TD]
[/TR]
</tbody>
[/TABLE]

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

Like
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Tanks[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]Cars[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]Planes[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]Bikes[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]false[/TD]
[/TR]
</tbody>[/TABLE]

 

[ChrisWebb]

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]

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]

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)).