CODEWITHSUNDEEP
powerbi
ACL
ACL

Reputation: 11

Power BI extract filename from path in table

In Power BI does anyone have any idea how I can extract a filename from a path in a table column without using SQL? DAX does not seem to have reverse searches. Also please note that some file names do not have paths.

In SQL I could achieve this with the following:

CASE
        WHEN @FullPath = 'TRUE' OR CHARINDEX('\', dbo.Usage.App) = 0 THEN 
          UPPER(dbo.Usage.App)
        ELSE
          UPPER (RIGHT(dbo.Usage.App, CHARINDEX('\', REVERSE(dbo.Usage.App)) -1))
    END
    AS AppName

Upvotes: 1

Views: 3668

Answers (2)

ACL
ACL

Reputation: 11

You can try this:

if(SEARCH("\", [App],1,-1)=-1, [App], RIGHT([App],LEN([App])-SEARCH("@",SUBSTITUTE([App],"\","@",LEN([App])-LEN(SUBSTITUTE([App],"\",""))))))

Upvotes: -1

Marc Pincince
Marc Pincince

Reputation: 5202

Assuming your fullpath is in Column1, I believe this DAX based solution would work:

Create a new column with...

=RIGHT([Column1],LEN([Column1])-SEARCH("@",SUBSTITUTE([Column1],"\","@",LEN([Column1])-LEN(SUBSTITUTE([Column1],"\","")))))

I found this solution here.

Here's a screenclip that might help too:

enter image description here

Be sure to replace Column1 with the name of your column.

Upvotes: 3

Related Questions