Forum Discussion
Advocate IIPulling a value from multiline text into a custom column
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()
)
Community ChampionHi, Jays_D
Load your data into Power Query. In Power BI Desktop, go to Home > Edit Queries.
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'.
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'.
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'.
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.
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.