Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bigfun
Helper I
Helper I

Ideas on trimming a URL based on criteria

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

 

Untitled-1.png

 

2 ACCEPTED SOLUTIONS
DataInsights
Super User
Super User

@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]
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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]
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
DataInsights
Super User
Super User

@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]
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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/"

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]
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.