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>

 

[Ken Puls]

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]

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

<tbody>
</tbody>

And a list of allowed values would be:

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

<tbody>
</tbody>

So the result ideally should looks like:

column1	column2	column3	column4	column5	column6	Checking	Problem
monkey	1	girl	sky	3	Pro	true
elephant	2	girl___	s ky	6	Newbie	false	column3, column4
dino	1	girl	star	4	Pro	false	column1

<tbody>
</tbody>

 

[Ken Puls]

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]

Thank you, Ken!

Very clear!

I'll think how to optimize it with functions.

 

[ChrisWebb]

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]

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

 

[billszysz]

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]

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]

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.