There is always a new trick to learn, I have found you learn them when you need to do something specific. Thank heaven for UA-cam creators who nurse you through the steps you need to take, can save a lot of time flicking back and forth through the documentation.
Many thanks, Bill. I've searched online for that Suat Ozgur book you advertise there, but I can't find it. Do you know where it's available? Been looking for a book on M that goes being the ribbon UI.
OK, second attempt (the YT police jettisoned my first comment): Hey Mr. Excel, here's a little improvement on your query: In your GroupBy step (Table.Group) just replace "each _" in the aggregation line by: "each Table.Combine({_,#table({},{{}})})". That way you don't need to do the extra step of transforming a column to add the blank line. In PQ-M you can always amend the formulas that are generated by the interface. PS: thanks for mentioning me, really appreciate it.🙂
@@josh_excel Correct! 🙂 (and you can even create a completely empty table like this, but adding that to the bottom of another table does absolutely nothing - as one might expect. :-)
PQ is king!! For fun a lambda to solve it, deals with tables or arrays (headers or no headers): ar: any array or table [h]: headers argument, omitted when array has no headers, 1 if table or array with headers =LAMBDA(ar,[h], LET( a, IF(h, DROP(ar, 1), ar), c, SEQUENCE(, COLUMNS(a)), s, SORT(a), t, TAKE(s, , 1), u, UNIQUE(t), r, REDUCE("",u, LAMBDA(v,i, LET( x, XMATCH(i, t),y, XMATCH(i, t, , -1), VSTACK(v, INDEX(s, SEQUENCE(y - x + 1, , x), c), "")))), d, DROP(DROP(IFNA(r, ""), 1), -1), IF(h, VSTACK(TAKE(ar, 1), d), d) ) )
@@josh_excel You're very welcome!! Here is a function for you with this versatility added. GROUPINS(ar,[ai],[h]) ar: any array or table with headers or not [ai]: array insert, any array, if omitted, a blank row will be inserted, if not, the entire array ai will be inserted [h]: headers argument, omitted when array has no headers, 1 if table or array with headers =LAMBDA(ar, [ai], [h], LET( a, IF(h, DROP(ar, 1), ar), b, IF(ISOMITTED(ai), "", ai), c, SEQUENCE(, COLUMNS(a)), s, SORT(a), t, TAKE(s, , 1), u, UNIQUE(t), r, REDUCE("", u, LAMBDA(v, i, LET(x, XMATCH(i, t), y, XMATCH(i, t, , -1), VSTACK(v, INDEX(s, SEQUENCE(y - x + 1, , x), c), b)))), d, DROP(IFNA(r, ""), 1), e, IF(AND(TAKE(d, -1) = ""), DROP(d, -1), d), IF(h, VSTACK(TAKE(ar, 1), e), e) ) ) To insert only "End" only, (not blank row) call: =GROUPINS(array,"End") - to insert "End" and a blank row call: =GROUPINS(array,{"End";""}) - to insert a row with 2 cols followed by a blank row call; and the array has headers: =GROUPINS(array,{"Group","End";"",""},1) or: =GROUPINS(array,VSTACK({"Group","End"},""),1) Hope that helps. Also, if you want to group by any column, other than first one, can be done easily, let me know. ✌😉
There is always a new trick to learn, I have found you learn them when you need to do something specific. Thank heaven for UA-cam creators who nurse you through the steps you need to take, can save a lot of time flicking back and forth through the documentation.
Awesome, thanks Mr. Excel!
We were all apprentices at one point.... I am still at that point.... Thanks Mr.Excel
Can I pre-order Suat's book? Can't wait to get my hands on it!
From Sri Lanka 🇱🇰🇱🇰🇱🇰
Thanks Mr Excel!!!
Hey, pretty cool. I was looking for a Dynamic Array approach because mine was ugly. But maybe the client is fine with using Power Query.
Many thanks, Bill. I've searched online for that Suat Ozgur book you advertise there, but I can't find it. Do you know where it's available? Been looking for a book on M that goes being the ribbon UI.
OK, second attempt (the YT police jettisoned my first comment):
Hey Mr. Excel, here's a little improvement on your query:
In your GroupBy step (Table.Group) just replace "each _" in the aggregation line by: "each Table.Combine({_,#table({},{{}})})".
That way you don't need to do the extra step of transforming a column to add the blank line.
In PQ-M you can always amend the formulas that are generated by the interface.
PS: thanks for mentioning me, really appreciate it.🙂
I like it. Here is a table with with two blank rows:
= #table({},{{},{}})
@@josh_excel Correct! 🙂
(and you can even create a completely empty table like this, but adding that to the bottom of another table does absolutely nothing - as one might expect. :-)
@@josh_excel woah that was much easier! Do you know how I could add the blank row above the first difference instead of below?
@@andrewdo9319 Just reverse the order:
each Table.Combine({#table({},{{}}),_})
Can also use ampersand:
each _ & #table({},{{}})
Awesome sir
PQ is king!! For fun a lambda to solve it, deals with tables or arrays (headers or no headers):
ar: any array or table
[h]: headers argument, omitted when array has no headers, 1 if table or array with headers
=LAMBDA(ar,[h],
LET(
a, IF(h, DROP(ar, 1), ar),
c, SEQUENCE(, COLUMNS(a)),
s, SORT(a), t, TAKE(s, , 1), u, UNIQUE(t),
r, REDUCE("",u,
LAMBDA(v,i,
LET(
x, XMATCH(i, t),y, XMATCH(i, t, , -1),
VSTACK(v, INDEX(s, SEQUENCE(y - x + 1, , x), c), "")))),
d, DROP(DROP(IFNA(r, ""), 1), -1),
IF(h, VSTACK(TAKE(ar, 1), d), d)
)
)
Thanks! This will come in handy. How would it change if I wanted to add the word "End" after each group then a blank space?
@@josh_excel You're very welcome!! Here is a function for you with this versatility added.
GROUPINS(ar,[ai],[h])
ar: any array or table with headers or not
[ai]: array insert, any array, if omitted, a blank row will be inserted, if not, the entire array ai will be inserted
[h]: headers argument, omitted when array has no headers, 1 if table or array with headers
=LAMBDA(ar, [ai], [h],
LET(
a, IF(h, DROP(ar, 1), ar),
b, IF(ISOMITTED(ai), "", ai),
c, SEQUENCE(, COLUMNS(a)),
s, SORT(a),
t, TAKE(s, , 1),
u, UNIQUE(t),
r, REDUCE("", u, LAMBDA(v, i, LET(x, XMATCH(i, t), y, XMATCH(i, t, , -1), VSTACK(v, INDEX(s, SEQUENCE(y - x + 1, , x), c), b)))),
d, DROP(IFNA(r, ""), 1),
e, IF(AND(TAKE(d, -1) = ""), DROP(d, -1), d),
IF(h, VSTACK(TAKE(ar, 1), e), e)
)
)
To insert only "End" only, (not blank row) call:
=GROUPINS(array,"End")
- to insert "End" and a blank row call:
=GROUPINS(array,{"End";""})
- to insert a row with 2 cols followed by a blank row call; and the array has headers:
=GROUPINS(array,{"Group","End";"",""},1) or:
=GROUPINS(array,VSTACK({"Group","End"},""),1)
Hope that helps. Also, if you want to group by any column, other than first one, can be done easily, let me know. ✌😉
@@Excelambda Hello. I tried to do it for any column and I did not get it. Could please show how to do it?
This is crazy
Thanks Mr Excel!