Forum Discussion
Concatenation??
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 🙂
1 Reply
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
