Power Query: Formatting with special Line Breaks/Delimiter

[potatodog123]

Hi, I'm trying to import activity logs of a certain application to visualize errors. The logs are not very data-analytics friendly. It is essentially a text file thats formatted like this:

05/15/2022 11:32 AM: Success.
05/15/2022 11:45 AM: ERROR: in process4
   in: line number 4
   in: line number 8
05/15/2022 11:48 AM: Success.
07/15/2022 9:02 AM: Success.
07/15/2022 9:15 AM: ERROR: in process6
   in: line number 102
   in: line number 200
   in: line number 830
07/15/2022 12:49 PM: Success.

etc...
I imported the text file as CSV and formated the dataset in power query, but I'm struggling find a line break/delimiter so that each log (based on time) is 1 entry.

What I'm getting is (using tab as Delimiter)

05/15/2022 11:32 AM: Success.

05/15/2022 11:45 AM: ERROR: in process4

in: line number 4

in: line number 8

05/15/2022 11:48 AM: Success.

I want it to look like:

05/15/2022 11:32 AM: Success.

05/15/2022 11:45 AM: ERROR: in process4    in: line number 4    in: line number 8

05/15/2022 11:48 AM: Success.

[v-luwang-msft · Accepted solution]

Hi @potatodog123 ,

Pls test the below:

Add index:

Then use the below dax to create new column:

test = if(LEFT(test1[Column1],1)=" ",0,test1[Index])

maxindex = CALCULATE(MAX(test1[Index]),FILTER(ALL(test1),test1[test]<>0&&test1[Index]<=EARLIER(test1[Index])))

Final create a new table:

Table = SUMMARIZE (
    'test1',test1[maxindex],
     "newcolumn", CONCATENATEX ( VALUES ( 'test1'[Column1]), [Column1],UNICHAR(10))
)

After research, when we add line break in text directly, Power BI always strips line breaks out of text when it gets loaded into the Data Model.So we need to create visual 😞Please review more details about UNICHAR in this blog.)

Best Regards

Lucien

[v-luwang-msft]

Hi @potatodog123 ,

What is the difference between the results you are currently getting and the results you would like to get? So far based on what you have provided, it seems to me that both are the same.

Best Regards

Lucien

[potatodog123]

Hi, in the example: I'm getting 5 rows of data, I want to have 3 rows in that case.