Extract Special Character String /*

[JonPT]

Hi Power BI Desktop Forum,

Newbie here who just joined and hoping you can assist me? Extensive searching and trial and error in PBI Desktop but no luck. Here's my scenario...

I'm trying to extract an exact special character string /* from column ALL_VALUES similar to below. Is this possible in Power BI Desktop?

ALL_VALUES

/*
/value1/*;/*

/*;/value1/*

/value1*;/value2*;value3/*;value4/*;/*

/*value1*;/*value2*/*;*value3/*;value4/*;/*

Extraction Required Description

Extract only the exact special character string /*

IF there is no text before or after /*

AND if it is between delimiters ;

FROM ALL_VALUES to a new column WILDCARD_ONLY

Extraction Required

ALL_VALUES

/*                     # Extract /*

/value1/*;/*     # Extract last /* after delimiter ;

/*;/value1/*     # Extract first /* before delimiter ;

/value1*;/value2*;/*;value3/*;value4/* # Extract middle /* between delimiters ;

/*value1*;/*value2*/*;*value3/*;value4/*;/* # Extract last /* after delimiter ;

Example Output

WILDCARD_ONLY

/*           # /* extracted

/*           # Last /* extracted

/*           # First /* extracted

/*           # Middle /* extracted

/*           # Last /* extracted

 
(everything after # are comments to explain the requirement per line)

Any assistance would be greatly appreciated

Thank you

Regards

JonPT

[rajendraongole1 · Accepted solution]

Hi @JonPT - In power query editor custom column window, you can use Text.Split and List.Select functions to isolate the exact /* string that meets your criteria.If you need to extract based on more complex rules (like excluding /* if it’s part of a larger string), you might need to add more conditions in the List.Select function

let
// Split the string by semicolon
SplitValues = Text.Split([ALL_VALUES], ";"),
// Select only entries that are exactly '/*'
FilteredValues = List.Select(SplitValues, each Text.Trim(_) = "/*")
in
if List.Count(FilteredValues) > 0 then
Text.Combine(FilteredValues, ";")
else
null

Hope this works in your scenerio

[JonPT]

Many thanks @rajendraongole1. That worked perfectly. Really appreciate your super quick response.

[Kedar_Pande]

DAX Logic to Extract /*

WILDCARD_ONLY = 
VAR StringToSearch = 'YourTable'[ALL_VALUES]
VAR Pattern = "/*"
VAR Delimiter = ";"
VAR PositionFirst = SEARCH(Pattern, StringToSearch, 1, 0)
VAR PositionLast = SEARCH(Pattern, StringToSearch, LEN(StringToSearch) - LEN(Pattern), 0)
VAR ExtractedFirst = IF(AND(MID(StringToSearch, PositionFirst - 1, 1) = Delimiter || PositionFirst = 1, MID(StringToSearch, PositionFirst + 2, 1) = Delimiter || LEN(StringToSearch) = PositionFirst + 1), Pattern, BLANK())
VAR ExtractedLast = IF(AND(MID(StringToSearch, PositionLast - 1, 1) = Delimiter || PositionLast = 1, MID(StringToSearch, PositionLast + 2, 1) = Delimiter || LEN(StringToSearch) = PositionLast + 1), Pattern, BLANK())
RETURN 
IF(
NOT(ISBLANK(ExtractedFirst)),
ExtractedFirst,
IF(NOT(ISBLANK(ExtractedLast)), ExtractedLast, BLANK())
)

Let me know if you need further adjustments or clarification!

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande 

[JonPT]

Thank you @Kedar_Pande for your detailed DAX response. @rajendraongole1 solution worked for me as well which I marked as the solution. But I will definitely keep your DAX solution in my back pocket for potential future scenarios.