Advent of Code 2020 - Day 4

Power BI / DAX / Power Query / Advent of Code 2020

Day 4 is about passport fields validation. In part 1 we need to check if all the fields exist and in part 2 check if they are valid.

This seems like a job where the heavy lifting should be made by Power Query.

https://adventofcode.com/2020/day/4

If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions.

Part 1

Following this strategy we load the data to our file and we find our first challenge. “Each passport is represented as a sequence of key:value pairs separated by spaces or newlines. Passports are separated by blank lines.”

Figure 1: Initial state

Figure 1: Initial state

This would be a lot easier if we had a passport per line. To achieve that we need to group the related columns together.

Figure 2: Steps in order to group by

Figure 2: Steps in order to group by

  1. Added an index column
  2. Using the index get the value of the previous row to help us know where one passport ends and the other starts
  3. Add a conditional index column filled only where a blank line exists

Now we just need to remove the empty lines on [Column1] and use the Table.FillDown on [Idx] to be ready to group by.

Figure 3: Grouping by

Figure 3: Grouping by

After the group by we just need to create a column for each of the fields using Text.BetweenDelimiters and Splitter.SplitTextByCharacterTransition to get something like this:

Figure 4: Splitting the passports

Figure 4: Splitting the passports

Notice that when a field doesn’t exist it gets filled as an empty string. Based on this knowlegde we can create a column that checks if any of these fields is empty:

not List.ContainsAny(
        {[ecl],[pid],[eyr],[hcl],[byr],[iyr],[hgt Value]},
        {"", null}
    )

The only thing left to solve this first part is to count the TRUE values in this column.

Part 2

To solve part 2 we need to check if those fields are valid:

  • byr (Birth Year) four digits; at least 1920 and at most 2002.
  • iyr (Issue Year) four digits; at least 2010 and at most 2020.
  • eyr (Expiration Year) four digits; at least 2020 and at most 2030.
  • hgt (Height) a number followed by either cm or in:
    • If cm, the number must be at least 150 and at most 193.
    • If in, the number must be at least 59 and at most 76.
  • hcl (Hair Color) a # followed by exactly six characters 0-9 or a-f.
  • ecl (Eye Color) exactly one of: amb blu brn gry grn hzl oth.
  • pid (Passport ID) a nine digit number, including leading zeroes.
  • cid (Country ID) ignored, missing or not.

Check [ecl]

List.ContainsAny({"amb", "blu", "brn", "gry", "grn", "hzl", "oth"}, {[ecl]})

Check [pid]

Text.Length([pid]) = 9 and Value.Is(Value.FromText([pid]), Number.Type))

Check [hgt]

([hgt Scale] = "cm" and [hgt Value] >= 150 and [hgt Value] <= 193) or
([hgt Scale] = "in" and [hgt Value] >= 59 and [hgt Value] <= 76)

To check [hcl] I’ve created a little function:

(input as text) =>
        Text.Length(input) = 7 and
        Text.StartsWith(input, "#") and
        input = Text.Select(input, {"#","a".."f", "0".."9"})

And another function to check the dates [byr], [iyr] and [eyr]:

(input as number, lowerBound as number, upperBound as number) =>
    input >= lowerBound and input <= upperBound

Figure 5: Call Check dates function example

Figure 5: Call Check dates function example


And to end this part all we need is to check if the passport passed all column validations and count the number of trues to have our puzzle result:

List.AllTrue({
    [#"Valid ecl?"], [#"Valid pid?"], [#"Valid eyr?"], [#"Valid hgt?"],
    [#"Valid hcl?"], [#"Valid byr?"], [#"Valid iyr?"]}))

Conclusion

We were able to solve these puzzles without complicated regex expressions and with simple operations.

The most interesting part was the strategy used to group and join the passports on one line only. A few ideas to keep ou our toolbelt for future work.

Have fun!!

 Share!