Concatenation??

[mitchpj75]

Hi all, 

Just looking for some advice.  Below is my table (screenshot for context)

This is the data format setout for ease of reading

case_id	case_row_id	Date of Call	Call successful?	Date of Call column TO BE CREATED	Call successful column TO BE CREATED
330	0	01/11/2018	No	01/11/2018;02/11/2018	No;Yes
330	1	02/11/2018	Yes	This row would then be deleted	This row would then be deleted
332	0	01/11/2018	Yes	01/11/2018	Yes
333	0	01/11/2018	No	01/11/2018;02/11/2018;06/11/2018	No;No;No
333	1	02/11/2018	No	This row would then be deleted	This row would then be deleted
333	2	06/11/2018	No	This row would then be deleted	This row would then be deleted
336	0	02/11/2018	No	etc
336	1	08/11/2018	No
336	2	14/11/2018	Yes
339	0	01/11/2018	No
339	1	02/11/2018	Yes
340	0	01/11/2018	No
340	1	02/11/2018	No

Whenever there is a unique case id, I need:

 - Date of call values to be merged into a new Date of Call Column

- Call Successful values to be merged into a new Call successful column

Is it best to concatenate?  if so how??

Thanks 🙂

[AlienSx]

Hi, @mitchpj75 

let
    raw_data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY2UNJRAmNDfUNDfSMDQwsgxy9fKVYHJmsIkjVCko1MLYZKG2HTjJA2xmu2MTazUWTBxpthkzWDmYxVrxnMZAtcsiCTDU2wOtoSr6Mt8QaICd7QNMEamiDZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [case_id = _t, case_row_id = _t, #"Date of Call" = _t, #"Call successful?" = _t]),
    Source = Table.TransformColumnTypes(raw_data, {{"Date of Call", type date}}, "en-GB"),
    z = 
        Table.Group(
            Source, {"case_id"}, 
            { {"Date of Call", each Text.Combine(List.Transform([Date of Call], Date.ToText), ";")}, 
            {"Call successful", each Text.Combine([#"Call successful?"], ";")}}
        )
in
    z