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?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks! I guess there is a infinite loop problem because with a full production file it just keeps on loading and says the file is gigabytes in size, and eventually my system is out of memory and it crashes :) But I will try to understand your steps and find the problem, it's a good start.

PhilC's avatar
PhilC
Resolver I
7 years ago

Ah, sorry.  Using the Index offset might be the issue on a larger number of rows.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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?

  • PhilC's avatar
    PhilC
    Resolver I
    7 years ago

    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