Forum Discussion
Transform several rows into one muli-line cell
It all depends on the version of Excel you are using. For example, with the latest versions of Excel 365 the following gives the bare bones of a formula solution that sits in one cell and creates the entire output array in one
= LET(
distinctAlbum, UNIQUE(Album),
details, BYROW(distinctAlbum,
LAMBDA(a,
LET(
t, FILTER(Track&", "&Artist, Album=a),
TEXTJOIN(CHAR(10),,t)
)
)
),
CHOOSE({1,2}, distinctAlbum, details)
)Without Lambda functions, you would work on one album at a time and fill down. Without LET, you nest the formula, without FILTER, the going gets tough. Perhaps FILTERXML.
By the time you get to that point, switching to Power Query would probably be better.
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.
