Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 co...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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?

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

  • PhilC's avatar
    PhilC
    Resolver I
    7 years ago

    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"

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi PhilC , the data is original (except for the emoticons parsed by this forum). 

      What you are referring to is the exact reason I need help :)

      When a row starts with a value not equal to ERROR, then the last value in the row is the error message.

      When a row starts with value ERROR, then the last value is not available, and the error message is in the following lines until a new line appears starting with starting value in ([space]WARN, ERROR, FATAL).

      It seems I cannot attach a txt file here so here is a new example, sliglty edited to make it shorter:

      FATAL [CPS queue thread 0] [2019-06-25 00:30:52,426] - (com.energyict.nl.cps.mail:checkBouncyCastleProvider) - Can't find BC provider - adding
 WARN [EDINE queue thread 3] [2019-06-25 00:50:11,005] - (nl.sogeti.delta.pluggable.export.export) - Er zijn geen meterstand op te vragen uit het TM
 WARN [EDINE queue thread 3] [2019-06-25 01:50:29,938] - (nl.sogeti.delta.pluggable.export.export) - Er zijn geen meterstand op te vragen uit het TM
ERROR [FTP queue thread 3] [2019-06-25 02:41:49,751] - Connection timed out: connect
java.net.ConnectException: Connection timed out: connect
	at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
	at java.lang.Thread.run(Thread.java:748)
 WARN [FTP queue thread 3] [2019-06-25 02:41:49,767] - (com.energyict.mdw.exportimpl.logFailure) - Export handler error : java.net.ConnectException: Connection timed out
ERROR [FTP queue thread 2] [2019-06-25 02:41:52,184] - java.net.ConnectException: Connection timed out: connect
java.lang.RuntimeException: java.net.ConnectException: Connection timed out: connect
	at com.energyict.mdw.exportimpl.SFtpActionStrategy$SftpClient.connectAndLogIn(SFtpActionStrategy.java:206)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.ConnectException: Connection timed out: connect
	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
	... 17 more
 WARN [FTP queue thread 2] [2019-06-25 02:41:52,184] - (com.energyict.mdw.exportimpl.logFailure) - Export handler error : Connection timed out: connect

       

      Also attached a screenshot of this text in Notepad++ including the indicators for Carriage Return, Line Feed, Spaces and Tabs.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I have a functional idea how to do this but I'm not skilled enough in M to write script for this.

        "Break up into seperate columns" is not specified because it should not be the problem here.

         

        IF SUBSTRING(line, 1,5) in ([space]WARN, FATAL) THEN read entire line until carriage return line break, and then break up in seperate columns 

         

        IF SUBSTRING(line, 1,5) IN (ERROR) then remove every carriage return line break for the current line and all following lines until a new line starts with SUBSTRING(line, 1,5) in ([space]WARN, FATAL), and then break up in seperate columns

         

        I found options in M to replace/remove carriage raturn / line breaks but this seems to work only after importing, and it should be done before importing / at the importing stage.