Forum Discussion
Conditionally combining multiple rows into one column
PhilC thank you for your help, I think I solved it using parts of your script.
1. Import file in 1 column
2. Insert new column, with values of original column only if the line starts with (ERROR, WARN, FATAL). So the messages containing multiple lines will not be put into this new column
3. Fill new column down
4. Group by on this new column as the key (it contains a timestamp so thats possible), and the original column as the value, and removing linefeeds in the grouping process:
#"Group" = Table.Group(#"Fill Down", {"Temp"}, {{"Column1", each Text.Combine([Column1],"#(lf)"), type text}})5. This solves the original problem. Now split by delimiters and set column types to finish
PhilC please explain what your thinking process was by adding the index column and the logic that uses this index column? I didn't need this part and this made it slow. Maybe I'm missing something?
Resolver IAh, sorry. Using the Index offset might be the issue on a larger number of rows.
PhilC thank you for your help, I think I solved it using parts of your script.
1. Import file in 1 column
2. Insert new column, with values of original column only if the line starts with (ERROR, WARN, FATAL). So the messages containing multiple lines will not be put into this new column
3. Fill new column down
4. Group by on this new column as the key (it contains a timestamp so thats possible), and the original column as the value, and removing linefeeds in the grouping process:
#"Group" = Table.Group(#"Fill Down", {"Temp"}, {{"Column1", each Text.Combine([Column1],"#(lf)"), type text}})5. This solves the original problem. Now split by delimiters and set column types to finish
PhilC please explain what your thinking process was by adding the index column and the logic that uses this index column? I didn't need this part and this made it slow. Maybe I'm missing something?
- PhilC
Hi Anonymous
Great you found a way to use the code and get it to work for you.
Regarding the INDEX column, it looked like the third row should be in column 5 (from the original data posted), but there was no "-" delimiter to split between column 4 and column 5 after "File Import Failure" (one row 2), I used the index and offset to bring that row up and add in the "-" so I could split on it.
If it is not needed, then great, no need to have complexity if not required.
Cheers