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]

 

[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:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]column1[/TD]
[TD]column2[/TD]
[TD]column3[/TD]
[TD]column4[/TD]
[TD]column5[/TD]
[TD]column6[/TD]
[/TR]
[TR]
[TD]monkey[/TD]
[TD]1[/TD]
[TD]girl
[/TD]
[TD]sky[/TD]
[TD]3[/TD]
[TD]Pro[/TD]
[/TR]
[TR]
[TD]elephant
[/TD]
[TD]2
[/TD]
[TD]girl
[/TD]
[TD]sky
[/TD]
[TD]6[/TD]
[TD]Newbie
[/TD]
[/TR]
[TR]
[TD]dino
[/TD]
[TD]1[/TD]
[TD]girl[/TD]
[TD]star[/TD]
[TD]4[/TD]
[TD]Pro[/TD]
[/TR]
[TR]
[TD]elephant
[/TD]
[TD]3
[/TD]
[TD]girl
[/TD]
[TD]star
[/TD]
[TD]7
[/TD]
[TD]Newbie
[/TD]
[/TR]
</tbody>[/TABLE]


Validation:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]column1
[/TD]
[TD]column3[/TD]
[TD]column4
[/TD]
[TD]column6[/TD]
[/TR]
[TR]
[TD]monkey
[/TD]
[TD]girl
[/TD]
[TD]sky
[/TD]
[TD]Pro
[/TD]
[/TR]
[TR]
[TD]dino
[/TD]
[TD]girl
[/TD]
[TD]star[/TD]
[TD]Newbie
[/TD]
[/TR]
[TR]
[TD]elephant
[/TD]
[TD]girl
[/TD]
[TD]star
[/TD]
[TD]Newbie
[/TD]
[/TR]
</tbody>[/TABLE]


Output:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]column1[/TD]
[TD]column2[/TD]
[TD]column3[/TD]
[TD]column4
[/TD]
[TD]column5[/TD]
[TD]column6[/TD]
[TD]Checking
[/TD]
[/TR]
[TR]
[TD]monkey
[/TD]
[TD]1[/TD]
[TD]girl[/TD]
[TD]sky[/TD]
[TD]3[/TD]
[TD]Pro[/TD]
[TD]true
[/TD]
[/TR]
[TR]
[TD]elephant[/TD]
[TD]2[/TD]
[TD]girl
[/TD]
[TD]sky
[/TD]
[TD]6[/TD]
[TD]Newbie[/TD]
[TD]false
[/TD]
[/TR]
[TR]
[TD]dino
[/TD]
[TD]1[/TD]
[TD]girl[/TD]
[TD]star[/TD]
[TD]4[/TD]
[TD]Pro[/TD]
[TD]false
[/TD]
[/TR]
[TR]
[TD]elephant
[/TD]
[TD]3
[/TD]
[TD]girl
[/TD]
[TD]star
[/TD]
[TD]7
[/TD]
[TD]Newbie
[/TD]
[TD]true
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!