Ideas on trimming a URL based on criteria

[bigfun]

I am trying to figure out the best way to go about trimming a column, but only when it meets a certain criteria. And leave the Urls as they are that don't meet the criteria.

I want to rollup all the Urls that contain '/dispatch/calendar/daily/xxxx-xx-xx' into one row in the Report view '/dispatch/calendar/daily/' to get a better sense of how often the daily calendar is used and not the individual days

[DataInsights · Accepted solution]

@bigfun,

Create this calculated column and use it in your visual (use your table name instead of Table1):

sUrl Trimmed =
IF (
    CONTAINSSTRING ( Table1[sUrl], "/dispatch/calendar/daily/" ),
    "/dispatch/calendar/daily/",
    Table1[sUrl]
)

[DataInsights · Accepted solution]

@bigfun,

Create this calculated column and use it in your visual (use your table name instead of Table1):

sUrl Trimmed =
IF (
    CONTAINSSTRING ( Table1[sUrl], "/dispatch/calendar/daily/" ),
    "/dispatch/calendar/daily/",
    Table1[sUrl]
)

[bigfun]

Absolutely wonderful ty so much.... works like a charm.... Also one follow up, I assume it would work if I used it in a nested query too.. if I had other url's that needed a trim like say "marketing/keyword/"

[DataInsights · Accepted solution]

Glad to hear that works. I recommend the SWITCH function for nesting:

sUrl Trimmed =
SWITCH (
    TRUE,
    CONTAINSSTRING ( Table1[sUrl], "/dispatch/calendar/daily/" ), "/dispatch/calendar/daily/",
    CONTAINSSTRING ( Table1[sUrl], "/marketing/keyword/" ), "enter text here",
    Table1[sUrl]
)