Another great article from Ben Gribaudo on #PowerQuery and #MLang , this time exploring the nuance of the global environment in a custom connector. If you want to understand M better, his series is essential reading.

https://bengribaudo.com/blog/2022/12/23/7130/power-query-m-primer-part25-extending-the-global-environment

#PowerBI

Day 11 of #AdventOfCode in #PowerBI

Today is the first day where the crux is an insight not in the problem statement. In this case modulo arithmetic keeps us from overflowing bigints and doubles.

Also, a good exercise in comparing two different factorings of a problem, because my first approach did not scale.

Follow code and progress: https://www.antifound.com/posts/advent-of-code-2022/#day-11-monkey-in-the-middle

https://adventofcode.com/2022/day/11

#PowerQuery #MLang

Day 10 of #AdventOfCode in #PowerBI gives us another accumulation of states in a list!

Today we play the part of a CRT display. Once again, a good exercise in list manipulation with a couple wrinkles around state representation and index bases.

Follow along and check out my code: https://www.antifound.com/posts/advent-of-code-2022/#day-10-cathode-ray-tube

https://adventofcode.com/2022/day/10

#PowerQuery #MLang

Day 9 of #AdventOfCode in #PowerBI.

Today's challenge is another two-dimensional grid and accumulation of state. It ends up looking like the game Snake.

A good example of changing the cardinality of a fact table between the two halves.

Follow solutions and code: https://www.antifound.com/posts/advent-of-code-2022/#day-9-rope-bridge

https://adventofcode.com/2022/day/9

#MLang #DAXLang #PowerQuery

Day 7 of #AdventOfCode in #PowerBI

1. Much more involved and stateful parsing of the input. This lets us stretch our legs on writing #MLang functions to build a solution.
2. Realistic and useful representation of a filesystem if you ever need this in PBI -- a good example of an unwound hierarchy for #DAXLang.
3. Found a severe memory leak in M Intellisense.

Follow along and find solutions on my blog: https://www.antifound.com/posts/advent-of-code-2022/#day-7-filesystems

https://adventofcode.com/2022/day/7

https://community.powerbi.com/t5/Issues/Memory-leak-when-using-PQ-advanced-editor/idi-p/2954784#M88901

#PowerQuery

Day 6 of #AdventOfCode in #PowerBI is very straightforward. It is a good example of basic string munging in #MLang.

Follow progress and solutions on my blog: https://www.antifound.com/posts/advent-of-code-2022/#day-6-tuning-trouble

https://adventofcode.com/2022/day/6

#PowerQuery

Today's #AdventOfCode in #PowerBI was the most fun I have had in #MLang in quite some time!

It provides a great example of functional patterns for iterative processing. The hard parts are defining a good state representation and a single step function. With these, everything else goes swimmingly.

Also a good example of how to handle ASCII art in code.

Follow along with my progress and find solutions: https://www.antifound.com/posts/advent-of-code-2022/

https://adventofcode.com/2022/day/5

#PowerQuery #ETL

Day 4 of #AdventOfCode in #PowerBI.

Another day, another set problem. Today’s pushes you to a sparse set representation, capturing just upper and lower bounds of contiguous sets of numbers. You could do it with dense sets, too, but that would feel too much like yesterday’s!

Follow commentary and solutions on my blog: https://www.antifound.com/posts/advent-of-code-2022/

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

#PowerQuery #MLang

Day 3 of #AdventOfCode in #PowerBI.

All about grouping and set operations (and vindication for 0-indexing) in #PowerQuery / #MLang and #DAXLang.

Good exercise in preserving valuable source information through #ETL as well.

Take a look and follow along on my blog (code and solutions available): https://www.antifound.com/posts/advent-of-code-2022/

https://adventofcode.com/2022/day/3

Today's #AdventOfCode in #PowerBI proves the value of a good lookup or reference table in preparing data. Rather than calculate rock-paper-scissors scores, we can just store the 9 possible plays between two opponents and their scores. No code to understand RPS, just a join and a sum.

Follow along on my blog with link to my solution file and code https://www.antifound.com/posts/advent-of-code-2022/

https://adventofcode.com/2022/day/2

#PowerQuery #MLang #ETL

I am doing this year’s Advent of Code in #PowerBI with #PowerQuery #MLang and #DAXLang

Follow along with my progress at https://www.antifound.com/posts/advent-of-code-2022/

Today’s (and future) solutions can be found in my AOC pbix file. It is a good example of dealing with source data that is grouped positionally and delimited with blank data rows. https://www.antifound.com/assets/aoc2022.pbix

#AdventOfCode https://adventofcode.com/2022/day/1

Fun calendar request from a client: week numbers for Friday-Thursday weeks.

1. Start withThursdays only (week-ends)
2. Group by year
3. Add index column to per-year grouped table
4. Ungroup
5. Backfill all dates

The important insight is not to start with all dates and try to figure out where they belong, but to start only with the concept of weeks and add the rest of the dates in a later step after numbering the weeks.

Code: https://paste.sr.ht/~greggyb/86c53d6a01d0dd15508945a383c999ec9d96c9d2

#PowerQuery #PowerBI #MLang #etl