Advent of Code 2020 - Day 2

We are at an toboggan rental shop and we need to help the shopkeeper to validate a bunch of corrupted passwords.

Part 1

In part one we need to check if a given character occurs between a certain lower and upper bound.

This kind of operation seems to be more suited to be done in Power Query as we are going row by row checking for the validity of the rule. My idea here is to create a column with value of 1 if the password is valid and 0 otherwise.

I’ve loaded the data from the file and using simple operations like SplitTextByDelimiter got to this result:

Now we create a new column that counts the number of occurrences of a character in a string with the following code:

    List.Count(
Text.PositionOfAny(


And to finish the task we only need a conditional column that gives 1 or 0 if the number of ocurrences is between the bounds:

    if [Number Ocurrences] >= [Lower Bound] and
[Number Ocurrences] <= [Upper Bound]
then 1
else 0


The DAX measure that will simply be a SUM() over the 'Is valid?' column:

Part 2

Same strategy but different rules, now we need to check if the character is exactly in only one of the bounds. All other cases are invalid. To do this check we first need to know what character is in each of the positions.

We add two new columns that give us the character with the function:

    Text.At([Password], [Upper Bound]-1)


And another conditional:

    if [Char to Check] = [Char At Lower Bound] and
[Char to Check] <> [Char At Upper Bound]
then 1
else if [Char to Check] = [Char At Upper Bound] and
[Char to Check] <> [Char At Lower Bound]
then 1
else 0


The answer to part two will just be the SUM() of Is valid in new policy?

Conclusion

Day two was not complicated at all. Power Query shines on these kind of transformations of the input.

Have fun!!