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:

Tanks

Cars

Planes

Bikes

Apples

<tbody>
</tbody>

And I know that Allowed values for that column only:

Tanks

Cars

Planes

Bikes

<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

Tanks	true
Cars	true
Planes	true
Bikes	true
Apples	false

<tbody>
</tbody>

 

[Mer333]

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]

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

Regards

 

[ChrisWebb]

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]

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
monkey	1	girl	sky	3	Pro
elephant	2	girl	sky	6	Newbie
dino	1	girl	star	4	Pro
elephant	3	girl	star	7	Newbie

<tbody>
</tbody>

Validation:

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

<tbody>
</tbody>

Output:

column1	column2	column3	column4	column5	column6	Checking
monkey	1	girl	sky	3	Pro	true
elephant	2	girl	sky	6	Newbie	false
dino	1	girl	star	4	Pro	false
elephant	3	girl	star	7	Newbie	true

<tbody>
</tbody>

 

[ChrisWebb]

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]

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]

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

 

[ChrisWebb]

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

Chris

 

[Mer333]

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]

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!