How to make an Excel Array Spill when referencing another Array

Подписаться 90 тыс.

Просмотров 2,3 тыс.

50% 1

Using a combination of DROP, REDUCE, LAMBDA and VSTACK we can get the result we need.
Use this sparingly, if you try to run it on 10,000 rows it will go very slowly.
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Mark's blog post
exceloffthegrid.com/spill-mul...
Download my demo file
aasolutions.sharepoint.com/:f...
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

Хобби

Опубликовано:

28 май 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 32

@IvanCortinas_ES Месяц назад

Excellent technique Wyn!! Thank you.

@AccessAnalytic Месяц назад

Cheers Iván

@ClaudioCP Месяц назад

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!

@AccessAnalytic Месяц назад

Cheers Claudio, glad you enjoyed it

@yvesvanderzeypen272 Месяц назад

Your tip is fantastic. Thank you and well done.

@AccessAnalytic Месяц назад

Thank you

@700997372mp Месяц назад

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 ) ) ) )

@AccessAnalytic Месяц назад

Nice, or =REDUCE({"ID","Colour"},E4#,LAMBDA(_Previous,_Each, VSTACK(_Previous, CHOOSECOLS(TEXTSPLIT(_Each," "),2,4))))

@user-xx5et3fj7b Месяц назад

Great! Thank you!

@AccessAnalytic Месяц назад

You’re welcome.

@kebincui Месяц назад

Awesome, as always. Thanks Wyn

@zzota Месяц назад

That's really useful, thanks Wyn.

@AccessAnalytic Месяц назад

You’re welcome

@stevereed5776 Месяц назад

It would also be handy if Microsoft allowed you to use MAP or BYROW

@AccessAnalytic Месяц назад

I’ve not looked into MAP but it seems array of arrays is a big engineering challenge

@tranvinhthinh8825 Месяц назад

Great!!!❤

@barttitulaerexcelbart9400 Месяц назад

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 Месяц назад

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.

@rubenmunozverdu7528 Месяц назад

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?

@AccessAnalytic Месяц назад

I’ve not seen anyone doing this with SCAN

@iankr Месяц назад

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#)?

@AccessAnalytic Месяц назад

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

@iankr Месяц назад

@@AccessAnalytic Many thanks, Wyn.

@tamersalem7542 Месяц назад

=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))

@AccessAnalytic Месяц назад

What's the purpose of the HSTACK?

@tamersalem7542 Месяц назад

@@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?

@AccessAnalytic Месяц назад

@@tamersalem7542 - which equations is it merging? Leaving it out achieves the same result

@tamersalem7542 Месяц назад

@@AccessAnalytic Combine the equations that were used in your example together to give the result once after working twice