Use a function within a group by with some conditions

[SaaM]

Hi All,

I am trying to use a function within the group by. I did manage to make it work so far.

My original fake table ( my real table contains 15000 rows and 60 columns):

Item	Serial Number	Conditional	Index
A	null	2	1
A	ET12	3	2
A	ET13	3	3
B	BT15	5	4
B	BT16	5	5
C	CTT1	6	6
D	DET1	8	7
D	DET2	8	8

I want to add a column with a result like this:

Column Wanted

ET12-ET13-

ET12-ET13-

ET13-

BT15-BT16-

BT16-

CTT1-

DET1-DET2-

DET2-

I did first a group by item (AllRows), then i got this table:

Item	AllRows
A	[Table]
B	[Table]
C	[Table]
D	[Table]

and for i.e the table for item A is look like this:

Item	Serial Number	Conditional	Index
A	null	2	1
A	ET12	3	2
A	ET13	3	3

I want to apply my function for each item's table but did not manage to.

I already use this solution:

https://community.powerbi.com/t5/Power-Query/Concatenate-column-with-condition-Recursive-way-or-any-...

But it took hours to get the result.

Kind regards

Saam

[Anonymous · Accepted solution]

Since the logic you want to apply to the table is not at all clear, I went to see the answers to the linked post and I used them to propose this solution. Check if it's right for you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIGYiMgVlCK1YGImEBFFQxBACFuCpUzAgEUURA2BgGwqBNInaEhmDSAmwwSNTM1MYZLGqELQymIIc5AFlg3hHB2dlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, column = _t, index = _t, #"Serial number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"column", Int64.Type}, {"index", Int64.Type}, {"Serial number", type text}}),
    #"Raggruppate righe" = Table.Group(#"Changed Type", {"Item"}, {{"all", each Table.AddColumn(_, "Custom", (r)=> if r[column] > r[index] then fn(_, r[Item]) else 0
)}}),
    fn = (tbl as table,colval as text) => Text.Combine( Table.SelectRows(tbl, each [Item] = colval)[Serial number], "-")
in
    fn

che può essere semplificato e reso molto più efficiente nel seguente modo:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIGYiMgVlCK1YGImEBFFQxBACFuCpUzAgEUURA2BgGwqBNInaEhmDSAmwwSNTM1MYZLGqELQymIIc5AFlg3hHB2dlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, column = _t, index = _t, #"Serial number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"column", Int64.Type}, {"index", Int64.Type}, {"Serial number", type text}}),
    #"Raggruppate righe" = Table.Group(#"Changed Type", {"Item"}, {{"all", each Table.AddColumn(_, "Custom", (r)=> if r[column] > r[index] then Text.Combine( _[Serial number], "-") else 0
)}})
in
    #"Raggruppate righe"