Combine values of multiple rows in one row and excluding one row based on condition

[Nivas538]

Hi Team,

I got the all the rows into onerow (Comma seperated values) but didn't understand how to exclude islatest=1 condition in the output column

Currently I'm using this M Query:
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Name"}, {{"Count", each _, type table [Alias=text, IsLatest=number, RegEventName=text, Index=number]}}),
#"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"ind",1,1)),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.1", each Table.Column([Count],"EventName")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom4", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),

Output which i am getting : (But need to exclude Islatest event from the output colum(OtherEvents))

Name	Other Events
b	Dec,Mar
b	Dec,Mar
c	Dec
d	Mar
a	Dec,Mar,Nov,May
a	Dec,Mar,Nov,May
a	Dec,Mar,Nov,May
a	Dec,Mar,Nov,May

Can you please help me out?

Input Data:

Input
Name	EventName	Edate	Sdate	Islatest
b	Dec	18-12-2019	12-12-2019	0
b	Mar	17-03-2020	13-03-2020	1
c	Dec	18-12-2019	12-12-2019	1
d	Mar	17-03-2020	13-03-2020	1
a	May	18-05-2020	14-05-2020	1
a	Nov	18-11-2019	11-11-2019	0
a	Mar	17-03-2020	13-03-2020	0
a	Dec	18-12-2019	12-12-2019	0

Required Output:

OutPut
Name	Other Events
b	Dec
b	Dec
c
d
a	Dec,Mar,Nov
a	Dec,Mar,Nov
a	Dec,Mar,Nov
a	Dec,Mar,Nov

Thanks In Advance,

Regards

Sri.

[ImkeF · Accepted solution]

Hi @Nivas538 

you can do without the index like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlLSUXJJTQaShhb6hkb6RgaGliCOERLHQClWB6LSN7EIJGmub2AMlDQyAHGMkTlglclEmAlRmUK0mYlglZUQMw1M4ZImyBy4Sr/8MqjthnALDZE4Bkhm4rcdoZKIUIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, EventName = _t, Edate = _t, Sdate = _t, Islatest = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"EventName", type text}, {"Edate", type date}, {"Sdate", type date}, {"Islatest", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"All", each _}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine(Table.SelectRows([All], (x)=> x[Islatest]=0)[EventName], ", ")),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"Name"}, {"Name.1"})
in
    #"Expanded All"