I really enjoyed this technique to "force" the spill using the Reduce function along with the Lambda inside. Excellent job Wyn! In addition to learning a lot from your videos I also admire your enthusiasm and cool Aussie accent to boot!
great video, you could do away with using drop and actually reference the table heading in the function too, just a thought = REDUCE( TRANSPOSE(TableHeadings[Headings]), I3#, LAMBDA(_prev,_each, VSTACK( _prev, CHOOSECOLS( TEXTSPLIT(_each, " "), 2, 3 ) ) ) )
Many thanks, Wyn. I'm just slightly confused about the workings of the LAMBDA function. Where are you defining what _previous and _each represent? Are they taken to be the first two arguments of the LAMBDA itself (i.e. the "" and the E4#)?
Yes the initial value “” is picked up by the LAMBDA _previous and then the _each is the current value of the array E4# support.microsoft.com/en-us/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb
My doubt is the usual about scan/reduce. Reduce returns usually last element of array and scan returns the whole array. So maybe if you use scan instead of reduce you wouldn't need to vstack?
Wyn, this is a very handy function. As MVP: how difficult is it for Microsoft to add this to their in build functions library? I had rather prefer this than this silly "PERCENTAGE"....😉
@@AccessAnalytic First, thank you for your interest and kind response. The purpose is to merge the two equations together. Is there an alternative solution available for that?
Excellent technique Wyn!! Thank you.
Cheers Iván
I really enjoyed this technique to "force" the spill using the Reduce function along with the Lambda inside. Excellent job Wyn! In addition to learning a lot from your videos I also admire your enthusiasm and cool Aussie accent to boot!
Cheers Claudio, glad you enjoyed it
Your tip is fantastic. Thank you and well done.
Thank you
Awesome, as always. Thanks Wyn
That's really useful, thanks Wyn.
You’re welcome
Great! Thank you!
You’re welcome.
great video, you could do away with using drop and actually reference the table heading in the function too, just a thought
=
REDUCE(
TRANSPOSE(TableHeadings[Headings]),
I3#,
LAMBDA(_prev,_each,
VSTACK(
_prev,
CHOOSECOLS(
TEXTSPLIT(_each, " "),
2,
3
)
)
)
)
Nice, or
=REDUCE({"ID","Colour"},E4#,LAMBDA(_Previous,_Each, VSTACK(_Previous, CHOOSECOLS(TEXTSPLIT(_Each," "),2,4))))
Many thanks, Wyn. I'm just slightly confused about the workings of the LAMBDA function. Where are you defining what _previous and _each represent? Are they taken to be the first two arguments of the LAMBDA itself (i.e. the "" and the E4#)?
Yes the initial value “” is picked up by the LAMBDA _previous and then the _each is the current value of the array E4#
support.microsoft.com/en-us/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb
@@AccessAnalytic Many thanks, Wyn.
Great!!!❤
It would also be handy if Microsoft allowed you to use MAP or BYROW
I’ve not looked into MAP but it seems array of arrays is a big engineering challenge
My doubt is the usual about scan/reduce. Reduce returns usually last element of array and scan returns the whole array. So maybe if you use scan instead of reduce you wouldn't need to vstack?
I’ve not seen anyone doing this with SCAN
Wyn, this is a very handy function. As MVP: how difficult is it for Microsoft to add this to their in build functions library? I had rather prefer this than this silly "PERCENTAGE"....😉
It appears to be highly complex to allow arrays of arrays but they are aware and maybe one day it will be possible to work natively.
=LET(o,FILTER(B3:B6,C3:C6=D2),
p,DROP(REDUCE("",o,LAMBDA(k,x,VSTACK(k,CHOOSECOLS(TEXTSPLIT(x," "),2,4)))),1),HSTACK(p))
What's the purpose of the HSTACK?
@@AccessAnalytic First, thank you for your interest and kind response. The purpose is to merge the two equations together. Is there an alternative solution available for that?
@@tamersalem7542 - which equations is it merging? Leaving it out achieves the same result
@@AccessAnalytic Combine the equations that were used in your example together to give the result once after working twice