Forum Discussion
Extract Special Character String /*
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
nullHope this works in your scenerio
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
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.
