Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to parse the data in a title field to create a column I can use to join it with another data source. My data looks like this:
Issue ID Issue Description
1234 W.O. 22-929292 needs to be addressed.
1235 22-092828 is top priority
1236 Facilities entered 22-872622
I need my output to be:
Issue ID Issue Description Work Order No
1234 W.O. 22-929292 needs to be addressed. 22-929292
1235 22-092828 is top priority 22-092828
1236 Facilities entered 22-872622 22-872622
I'm new to DAX. I see where I select the Add New Column in Power BI Desktop. That part I understand.
I searched for this issue but most of the examples are not where the text is in a variable position in the field. I tried a few things like FIND, SEARCH and MID. I just kept getting EOF error messages.
Boy do I miss my SQL "Like" command in this application.
Solved! Go to Solution.
Hi Greg, Thanks for the code! I'll let you know if I hit any roadblocks.
@Strongbuck Well, maybe this, PBIX is attached below signature.
Work Order ID =
VAR __Dash = SEARCH("-",[Item Description],,0)
VAR __Space = SEARCH(" ",[Item Description],__Dash,0)
VAR __Chars = IF(__Space <> 0, __Space - __Dash, LEN([Item Description]) - __Dash + 1)
RETURN
MID([Item Description], __Dash - 2, __Chars + 2)
I copied your code into Power BI and ran it. The Work Order ID column was created but it is filled with "#Error". The following error message is being displayed.
An argument of function 'SEARCH' has the wrong data type or has an invalid value.
I opened the pbix field you sent and see that it works beautifully in that file. Of course, the data only included records that contained the pattern the query is lookin for. How do you fix it so it handles data without the pattern.
I tried IFERROR to fix it but that gave me another error message.
User | Count |
---|---|
57 | |
21 | |
19 | |
16 | |
16 |
User | Count |
---|---|
87 | |
77 | |
52 | |
37 | |
21 |