Pulling a value from multiline text into a custom column

[Jays_D]

Hi All,

I have a "Comments" box in a Power BI dashboard which has multiple lines of text that get added to it from a Power App.

Each new line is prefixed with date and user name then their text in this format:

30/07/2023 John Smith: <text>
27/07/2023 John Smith: **Passed**
27/07/2023 John Smith: **Checked**

What I want to do is:

If the line contains **Checked** then split out the Date and the users name into speerate columns.

I tried the following to get the date however it seems to only work if the **Checked** is the top line in the multi-line textbox:

Checked Date = IF(CONTAINSSTRINGEXACT(jobs[jobcomments],"**Checked**"),LEFT(jobs[jobcomments],10))

I've also tried a numebr of ways to extract the name, starting from Character 11 to ":" as the delimiter after the users name but can't get it to work.

Any guidance would be greatly appreciated !

[rubayatyasmin · Accepted solution]

@Jays_D Or alternatively create three calculated column, 

//checks if the column has checked value

HasChecked = CONTAINSSTRING(jobs[jobcomments], "**Checked**")

// for date 

Date =
IF(
jobs[HasChecked] = TRUE,
LEFT(jobs[jobcomments],10),
BLANK()
)

//for user name

UserName =
IF(
jobs[HasChecked] = TRUE,
MID(
jobs[jobcomments],
12,
SEARCH(":", jobs[jobcomments], 12, LEN(jobs[jobcomments])) - 12
),
BLANK()
)

[Jays_D]

This has worked brilliantly. I was haalf way there with the first 2 columns (I could do that) I just wasn't sure how to use the delimiter to extract the Username.

Thank you so much, truly aappreciated - I've learned something new today 🙂

[rubayatyasmin]

Hi, @Jays_D 

1. Load your data into Power Query. In Power BI Desktop, go to Home > Edit Queries.

2. With your table loaded into the Power Query Editor, select the 'jobcomments' column. Then go to the Transform tab, and click on the 'Split Column' dropdown. Select 'By Delimiter'.

3. In the 'Split Column by Delimiter' window, select 'Custom' as the delimiter and input a line break (which can be input by pressing Ctrl + J in the text box). Then select 'Split into Rows'. Click 'OK'.

4. Now you have each line as a separate row. You can filter the rows that contain 'Checked'. To do this, select the 'Text Filters' dropdown from the 'jobcomments' column and select 'Contains'. In the dialog box that appears, input 'Checked' and hit 'OK'.

5. With the rows that contain 'Checked' isolated, go back to the 'Split Column' dropdown and select 'By Delimiter'. For delimiter, input a space ' ' and select 'Split at the left-most delimiter'. You'll now have two new columns: one for the date, and one for the rest of the text.

6. Repeat step 5 on the second new column you created, but this time set the delimiter as ':'.

Now you have the data split into separate columns for date, user name, and comments (which includes 'Checked'). Rename the columns as needed. When you're done, click 'Close & Apply' to load this transformed data into Power BI.

This solution will work if your data always follows the same format: Date User: Comment. If your data varies from this format, you'll need to adjust the steps accordingly.