Forum Discussion
Conditionally combining multiple rows into one column
I have a folder full of files in the format below. I want to load these into PowerBI using Power Query.
See attached picture, I colored every column with its own color.
Loading and splitting the columns that are NOT colored orange are not a problem.
The orange values are an error message. Most of the time these are short string in the same line, but sometimes they contain detailed error logs of mulitple rows.
How can I get the orange colored values into one column, where the multiple row logs will end up in one "cell" or value ?
WARN [Import schedule Elvaco importer] [2019-06-23 08:12:18,620] - (com.energyict.mdw.importimpl.FileImportImpl:process) - MDI202 End Import: severe errors occurred
ERROR [Import schedule Elvaco importer] [2019-06-23 08:12:22,522] - File import failure
com.energyict.delta.mdr.imp.csvconsumption.exception.DeviceChannelCombinationNotFoundException: DEL-DVAL-20: No device with serial-number '00691138' has a channel with 'CHN001' after the last slash ('/') in the external name.
at com.energyict.delta.mdr.imp.csvconsumption.CsvConsumptionDataParser.checkIfRtuWithChannelExistsAndSetChannelInModel(CsvConsumptionDataParser.java:209)
at com.energyict.delta.mdr.imp.csvconsumption.CsvConsumptionDataParser.parseDataLine(CsvConsumptionDataParser.java:133)
at com.energyict.delta.mdr.imp.csvconsumption.CsvConsumptionDataImporter.importReader(CsvConsumptionDataImporter.java:77)
at com.energyict.eisimport.core.AbstractReaderImporter.doImportFile(AbstractReaderImporter.java:48)
at com.energyict.eisimport.core.AbstractImporter.importFile(AbstractImporter.java:93)
at com.energyict.mdw.importimpl.FileImportImpl.doProcess(FileImportImpl.java:236)
at com.energyict.mdw.importimpl.FileImportImpl.access$100(FileImportImpl.java:25)
at com.energyict.mdw.importimpl.FileImportImpl$3.doExecute(FileImportImpl.java:225)
at com.energyict.cpo.Environment.execute(Environment.java:361)
at com.energyict.cpo.Environment.execute(Environment.java:339)
at com.energyict.mdw.importimpl.FileImportImpl.processFile(FileImportImpl.java:232)
at com.energyict.mdw.importimpl.FileImportImpl.process(FileImportImpl.java:200)
at com.energyict.mdw.importimpl.ImportMappingImpl.process(ImportMappingImpl.java:300)
at com.energyict.mdw.importimpl.ImportMappingImpl.execute(ImportMappingImpl.java:248)
at com.energyict.mdw.importimpl.ImportScheduleImpl.doExecute(ImportScheduleImpl.java:270)
at com.energyict.mdw.importimpl.ScheduleImpl.execute(ScheduleImpl.java:79)
at com.energyict.mdw.importimpl.ImportScheduleProcess.doRun(ImportScheduleProcess.java:187)
at com.energyict.mdw.importimpl.ImportScheduleProcess.run(ImportScheduleProcess.java:151)
at java.lang.Thread.run(Thread.java:748)
WARN [Import schedule Elvaco importer] [2019-06-23 08:12:22,569] - (com.energyict.mdw.importimpl.FileImportImpl:process) - MDI201 Import failed: com.energyict.delta.mdr.imp.csvconsumption.exception.DeviceChannelCombinationNotFoundException: DEL-DVAL-20: No device with serial-number '00691138' has a channel with 'CHN001' after the last slash ('/') in the external name.
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?
10 Replies
Hi Anonymous ,
Do you want to get the output below?
If so, we could achieve that in Query Editor. Please refer to the Applied Steps in my attachement.
If you still need help, please share your desired output so that we could help further on it.
Best Regards,
Cherry
Hi v-piga-msft
Thanks for your reply. Unless I understand your solution incorrectly, this is not the output I'm looking for. I attached a screenshot of an Excel table with the preferred output. The first 4 columns are not a problem for me, column5 is.
HI, in the data you provided initially, has it been edited at all or is it exactly as per the original file? I am wondering why for the second record the "com...." is on its own line and not following on from "File import failure"
