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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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