Use a function within a group by with some conditions

Question

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):ItemSerial NumberConditionalIndexAnull21AET1232AET1333BBT1554BBT1655CCTT166DDET187DDET288I want to add a column with a result like this:Column WantedET12-ET13-ET12-ET13-ET13-BT15-BT16-BT16-CTT1-DET1-DET2-DET2-I did first a group by item (AllRows), then i got this table:ItemAllRowsA[Table]B[Table]C[Table]D[Table]and for i.e the table for item A is look like this:&nbsp;ItemSerial NumberConditionalIndexAnull21AET1232AET1333&nbsp;I want to apply my function for each item's table but did not manage to.&nbsp;I already use this solution:https://community.powerbi.com/t5/Power-Query/Concatenate-column-with-condition-Recursive-way-or-any-other/m-p/1677277#M51094But it took hours to get the result.&nbsp;Kind regards&nbsp;Saam&nbsp;&nbsp;&nbsp;

anonymous · Accepted Answer

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.
&nbsp;
&nbsp;
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)=&gt; if r[column] &gt; r[index] then fn(_, r[Item]) else 0
)}}),
    fn = (tbl as table,colval as text) =&gt; Text.Combine( Table.SelectRows(tbl, each [Item] = colval)[Serial number], "-")
in
    fn
&nbsp;
&nbsp;
che può essere semplificato e reso molto più efficiente nel seguente modo:
&nbsp;
&nbsp;
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)=&gt; if r[column] &gt; r[index] then Text.Combine( _[Serial number], "-") else 0
)}})
in
    #"Raggruppate righe"
&nbsp;

Forum Discussion

Use a function within a group by with some conditions

1 Reply

Related content

IF Function - 2 Conditions,

IF function with multiple conditions

Using negation condition in switch function

Filter Conditions in swtich() function

DATESYTD function

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

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