Advent of Code 2020 - Day 3

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

Now we need to check for trees in paths. The interesting bit is that the path can grow ad infinitum to the right.

Check the details at https://adventofcode.com/2020/day/3

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

Part 1

How do we avoid repeating the pattern? Looking at the provided sample we have:

..##.......
#...#...#..
.#....#..#.
..#.#...#.#
.#...##..#.
..#.##.....
.#.#.#....#
.#........#
#.##...#...
#...##....#
.#..#...#.#

The interesting part begins after line 3, where we are going to need to get positions that are over the length of the row. The value that we wish for row 4 is 1, which is the value of the remainder of dividing 12 by the lenth of the track which is 11. Adding a new column with the remainder we can crosscheck with our data and everything seems to work:

Row Column Value MOD(Column,Length)
0 0 • 0
1 3 • 3
2 6 # 6
3 9 • 9
4 12 # 1
5 15 • 4
6 18 # 7
7 21 # 10
8 24 # 2
9 27 # 5

I’ve loaded the file without changes, except for the added index to help us with the calculations.

To recreate this logic I’ve opted to use DAX but it could also be made in Power Query using a similar aproach.

Where’s my measure:

Day 3 - Part 1 =
VAR _trees =
    ADDCOLUMNS (
        'Day 3',
        "Trees?",
            MID (
                'Day 3'[Toboggan Trajectory],
                MOD ( 3 * 'Day 3'[Index], LEN ( 'Day 3'[Toboggan Trajectory] ) ) + 1,
                1
            )
    )
RETURN
    COUNTROWS ( FILTER ( _trees, [Trees?] = "#" ) )

We create a table variable over our data with an extra column called [Trees?]. This column will contain the character we need to check for that row using the function MID(). To get the correct value we multiply the [Index] by 3, the number of characters we want to advance horizontally and then calculate the remainder over the length of the row.

The last part is just counting the number of rows where we found an #.

Part 2

To complete this puzzle we now need to check the remaining slopes.

  • Right 1, down 1.
  • Right 3, down 1. (This is the slope you already checked.)
  • Right 5, down 1.
  • Right 7, down 1.
  • Right 1, down 2.

Get the number of trees for each one and multiply them with each other.

Because it’s not possible to create a custom DAX function we need to repeat the code I’ve made in the previous part for each of the slopes, replacing the numbers accordingly. The only tricky part was jumping two lines in the last slope:

Day 3 - Part 2 =
VAR N_3_1 = [Day 3 - Part 1]
VAR _trees_1_1 =
    ADDCOLUMNS (
        'Day 3',
        "Trees?",
            MID (
                'Day 3'[Toboggan Trajectory],
                MOD ( 'Day 3'[Index], LEN ( 'Day 3'[Toboggan Trajectory] ) ) + 1,
                1
            )
    )
VAR N_1_1 =
    COUNTROWS ( FILTER ( _trees_1_1, [Trees?] = "#" ) )
VAR _trees_5_1 =
    ADDCOLUMNS (
        'Day 3',
        "Trees?",
            MID (
                'Day 3'[Toboggan Trajectory],
                MOD ( 5 * 'Day 3'[Index], LEN ( 'Day 3'[Toboggan Trajectory] ) ) + 1,
                1
            )
    )
VAR N_5_1 =
    COUNTROWS ( FILTER ( _trees_5_1, [Trees?] = "#" ) )
VAR _trees_7_1 =
    ADDCOLUMNS (
        'Day 3',
        "Trees?",
            MID (
                'Day 3'[Toboggan Trajectory],
                MOD ( 7 * 'Day 3'[Index], LEN ( 'Day 3'[Toboggan Trajectory] ) ) + 1,
                1
            )
    )
VAR N_7_1 =
    COUNTROWS ( FILTER ( _trees_7_1, [Trees?] = "#" ) )
VAR _trees_1_2 =
    ADDCOLUMNS (
        'Day 3',
        "Trees?",
            MID (
                'Day 3'[Toboggan Trajectory],
                MOD ( 'Day 3'[Index], LEN ( 'Day 3'[Toboggan Trajectory] ) ) + 1,
                1
            )
    )
VAR N_1_2 =
    COUNTROWS ( FILTER ( _trees_1_2, [Trees?] = "#" && MOD ( [Index], 2 ) = 0 ) )
RETURN
    N_1_2 * N_1_1 * N_3_1 * N_5_1 * N_7_1

Conclusion

While I chose DAX to solve this problem we could as easily just use Power Query to do it. Just have in mind that in this case, the implementation would almost be the same, but for most cases, choosing one or the other will imply if you spend a few minutes or some hours to get the results you want.

Have fun!!

 Share!