Forum Discussion
Extract Special Character String /*
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
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
4 Replies
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
Many thanks rajendraongole1. That worked perfectly. Really appreciate your super quick response.
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-pandeThank 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.
