Group tranposed data

Occasional Contributor

Hello,

 

I'm having difficulty 'grouping' the transposed data - so as attached, if the account in colum A matches/is the same, rather than creating separate transposed colums, all amounts will appear under the matching account. thanks

LousQuinn_1-1670579924122.png

 

 

6 Replies

Hi @LousQuinn 

 

With Power Query (sample attached):

 

Sans titre.png

Hi @LousQuinn 

 

A 365 alternative assuming you have functions HSTACK & VSTACK:

 

Sans titre.png

 

In I3:

=LET(
  Header,   TRANSPOSE(UNIQUE(TBL_Source[Account])),
  StackAmt, REDUCE(0,Header,
    LAMBDA(seed,acct, HSTACK(seed, FILTER(TBL_Source[Amount],TBL_Source[Account]=acct)))
  ),
  Clean,    IFNA(DROP(StackAmt,,1),""),
  VSTACK(Header, Clean)
)
Thanks for both you answers
You're welcome. If any of them does what you expect please mark it as solution to help those who search this site - Thanks & nice day...
Ok will test both solutions asap. thanks again

@LousQuinn 

Another 365 option

Sans titre.png

in I9:

=LET(
  JoinAmts, LAMBDA(Amounts,Accounts,account,
      TEXTJOIN(";",,FILTER(Amounts,Accounts=account))
  ),
  Accts, TRANSPOSE(UNIQUE(TBL_Source[Account])),
  StackedValues, REDUCE("",Accts,
    LAMBDA(seed,acct, VSTACK(seed,JoinAmts(TBL_Source[Amount],TBL_Source[Account],acct)))
  ),
  VSTACK(
    Accts,
    TRANSPOSE(IFNA(NUMBERVALUE(TEXTSPLIT(TEXTJOIN("|",TRUE,StackedValues),";","|")),""))
  )
)