Forum Discussion
Transform several rows into one muli-line cell
PeterBartholomew1 Impressive as ever, though funny that you mention PowerQuery as a last resort, where I would instinctively go for PQ to begin with and choose complex formulae only if I have to. Attaching a file with both PQ and your formula (slightly altered to work with the structured table in my example).
It's probably something that is influenced as much by the comfort zone of the programmer as the technical demands of the problem. I do like PQ for ETL tasks but not so much when the data is already loaded in Excel.
You mention complicated formulae but once I have to switch to the Advanced Editor in PQ, it doesn't feel very 'low code'. I did take a quick look at creating a PQ solution but made a rookie error of Grouping first, which left me the task of trying to merge the rows within each embedded table.
I approve of your conversion to structured references, though occasionally I add a further layer of indirection by introducing a concise defined name to refer to a long structured reference. What I don't use is A1-style referencing. Having described it as "an abomination born of idleness that has no place in any computational environment", I can't help feeling that there would be an element of hypocrisy for me to then proceed to employ it!
That's never ended discussion what to use: formulas vs PQ vs DAX & data model. Or formulas & PQ & DAX with data model. From my point of view that's the only what makes the decision - how critical is the using of Refresh All.
The rest is solvable and mostly personal preferences. PQ has rich library could be used from UI. On the other hand DAX has lot of patterns and I believe it'll be lambda-based libraries and/or patterns.
