Forum Discussion
Combine data from multiple rows into a single row
Hi again Serge. I managed to follow the query you set up when I applied it to the full dataset. I have one problem. When I get to the stage where I split the concatenated column where a semicolon exists, it only splits the cell into 2 columns even if there are more than 2 semicolons (more than 2 combinations of person and role). Could you please tell me where the code is forcing the output to 2 columns only?
Jilly , in addition to JKPieterse comment. I assumed reporters are in one cell separated by semicolons. If you'd like to split them on separate cells you shall take care about variable number of persons reported per incident (thus variable number of columns). One option is to define number of columns which is more than any possible maximum.
Another option, to fit number of columns into actual current maximum, requires couple of line of additional M script code:
// Last step of previous version
RemoveUnused = Table.SelectColumns(RenameBack,
{"Incident #", "Persons reporting", "Location of Incident"}),
// Number of semicolons in each record for Persons Reporting
SepsPerRecord = Table.AddColumn(RemoveUnused, "SepsInString",
each Text.Length([Persons reporting])-
Text.Length(Text.Replace([Persons reporting],";","")) ),
// Max fpr all records number of semicolns
MaxSeps = List.Max(SepsPerRecord[SepsInString]),
// Generate list of sequental column names
// from "Reporter 1" to "Reporter <MaxSeps+1>"
ReportersColumnNames = List.Transform({1..MaxSeps+1},
each "Person " & Number.ToText(_)),
// Make standard split and substiture automatically
// generates list with column names on ReportersColumnNames
// Another option is to set number of columns which is
// definetelly more than possible maximum.
SplitReporters = Table.SplitColumn(RemoveUnused,
"Persons reporting",
Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
ReportersColumnNames)
in
SplitReporters