oh wow! You'll be able to use #python from within #excel and #powerquery soon. And that Python install includes #pandas, #matplotlib and #statsmodels https://www.theverge.com/2023/8/22/23841167/microsoft-excel-python-integration-support

Some thoughts on no- and low-code:

Programming is more than code. Low-code means programming in a format other than code. Excel and Logo are programming.

Many tools assume that writing code is the hard part of programming. It isn't, though ironically many of these tools make it harder.

#PowerQuery is an example of low-code done right. GUI generates M code. You can write more when you inevitably need to.

#PowerBI is billed as low-code, but it's a (not too bad) programming environment.

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

@QuantumDot2 @wyn

But #PowerQuery generates more code than it needs here, and the code below removes that. This is the shortest you can get an 'Enter Data' source by removing stuff it does not need, and this is much shorter than my rot26 optimization of my original:

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0tKqpUcM4oyiwuyU0sVkjMS1FIVPBILCioVPBLLVeITE0sUoqNBQA="),Compression.Deflate)),type table[Message=text])

I will admit that by the time I was satisfied with the query it had given me and got it into #PowerBI, I had had several minutes wondering if a language model was going to put me out of a job, but there were enough fiddly bits that needed tweaking that I think I’m safe for now. #ChatGPT likes to make up non-existent stock functions when building #PowerQuery code.

I will admit that by the time I was satisfied with the query it had given me and got it into #PowerBI, I had had several minutes wondering if a language model was going to put me out of a job, but there were enough fiddly bits that needed tweaking that I think I’m safe for now. #ChatGPT likes to make up non-existent stock functions when building #PowerQuery code.

After watching people play with #ChatGPT yesterday during #NormConf, I decided to chat with it about a #PowerQuery function that I’ve been meaning to write — something simple that solves a non-urgent, minor question that someone raised with me a few months ago. I was surprised how quickly I got to what appeared to be working code and the apparent ease that it documented the function. I was even able to coerce it into creating nearly-working documentation metadata for the function.

After watching people play with #ChatGPT yesterday during #NormConf, I decided to chat with it about a #PowerQuery function that I’ve been meaning to write — something simple that solves a non-urgent, minor question that someone raised with me a few months ago. I was surprised how quickly I got to what appeared to be working code and the apparent ease that it documented the function. I was even able to coerce it into creating nearly-working documentation metadata for the function.

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

Haven’t posted #PowerQuery stuff in a bit because I’ve been busy in #RStats for one of my final projects. I’m sure I committed a grave number of statistics-based crimes, but at least I remembered to normalize my data before doing a t-test.

My current PQ project is developing an extension on top of the Table.Profile function to ease the review of untyped data by determining types (via a function and not through PQ’s auto detect option) and characterizing a few other useful feature of each field.

Haven’t posted #PowerQuery stuff in a bit because I’ve been busy in #RStats for one of my final projects. I’m sure I committed a grave number of statistics-based crimes, but at least I remembered to normalize my data before doing a t-test.

My current PQ project is developing an extension on top of the Table.Profile function to ease the review of untyped data by determining types (via a function and not through PQ’s auto detect option) and characterizing a few other useful feature of each field.

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