We are at an toboggan rental shop and we need to help the shopkeeper to validate a bunch of corrupted passwords.
(If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions)
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( [Password],{[Char to Check]},Occurrence.All))
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!!