Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jays_D
Advocate I
Advocate I

Pulling a value from multiline text into a custom column

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 !

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
Super User

@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()
)

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

3 REPLIES 3
rubayatyasmin
Super User
Super User

@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()
)

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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
Super User
Super User

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.

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.