This puzzle looks a lot like day 1. We have a list of numbers and need to do some operations that will return some big numbers. Let’s try to DAX out them. If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions. Part 1 We have a list of numbers of each the first 25 are a preamble. After this, any number can be calculated as a sum of any pair of numbers in that interval.
In day 8 we find a some weird infinite loop on a handheld device. To fix this problem we are asked to create a program that goes trough a stack of instructions. nop +0 acc +1 jmp +4 acc +3 jmp -3 acc -99 acc +1 jmp -4 acc +6 And after we find where the infinite loop is located we replace the bad command with the correct instruction. If you want to follow along please download AdventOfCode2020.
Day 7: Handy Haversacks is the hardest puzzle to solve using only Power BI until now. It took me a lot of time to find a viable and fast solution. I have first tried to solve this using DAX PATH but had issues with the different levels of recursion. It was possible in the real data input for the same bag to be child of different bags in different levels. Only after a deep dive on using the List.
Time to check for some custom declarations forms. In the first part we will check the distinct number of questions anyone on each group answered “yes”, and on the second part the questions where everyone gave a positive response. If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions. Part 1 The input for this problem is very similar to the passports we have seen on day 4.
This problem seems a lot of work at first but in reality it’s just binary number conversion to decimal. Picking up the provided example: FBFBBFF RLR If we replace the lower half [F, L] by 0, and the upper half [B, R] by 1 we get: 0101100 101 Doing the conversions: \begin{equation} \begin{aligned} (0101100)_2 &= 0*2^6 + 1*2^5 + 0*2^4 + 1*2^3 + 1*2^2 + 0*2^1 + 0*2^0 \\\ &= 32 + 8 + 4 \\\ &= 44 \\\ \\\ (101)_2 &= 1*2^2 + 0*2^1 + 1*2^0 \\\ &= 4 + 1 \\\ &= 5 \end{aligned} \end{equation}
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.
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: ..##....... #...#...#.. .#....#..#. ..#.#...#.# .#...##..#. ..#.##..... .#.#.#....# .#........# #.##...#... #...##....#
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.
This will be the first post of a series where I hope to be able to solve the problems from the Advent of Code using only Power BI. This event, sadly, only came to my attention this year. It started in 2015 and it’s a lot of fun. In these posts I will talk about my thinking and strategy to try and solve these problems. Without further ado let’s get started:
⚠ WARNING: The content of this post has been made deprecated due to the availability of the official Google Sheets connector and you should use that feature to import the data. ⚠ Check the official announcement on the link below: https://powerbi.microsoft.com/en-us/blog/power-bi-november-2021-feature-summary/#post-17912-_Toc87473946 This post is just for future reference In the beggining I’ve been tasked to find a way of exporting data from Google Spreadsheets for a client. I’ve started researching for an already working solution and got to the following links:
