none
Power BI extract filename from path in table RRS feed

  • Question


  • In power BI has anyone 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 some file names do not have paths.

    In SQL I could achieve this by 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 AppNa
    • Edited by leviya Tuesday, September 19, 2017 9:11 AM
    Tuesday, September 19, 2017 9:08 AM

Answers

  • Just remembered that the text delimiter functions don't generate an error if the delimiter is not found (the original text is returned in such cases). Therefore, the previous expression can be simplified as:

    Text.AfterDelimiter([Path], "\", {0, RelativePosition.FromEnd})

    Wednesday, September 20, 2017 4:34 PM

All replies

  • You can add a custom column with an expression similar to the following:

    if Text.Contains([Path], "\") then Text.AfterDelimiter([Path], "\", {0, RelativePosition.FromEnd}) else [Path]
    where [Path] is the name of the column containing the file path.

    Tuesday, September 19, 2017 1:42 PM
  • Just remembered that the text delimiter functions don't generate an error if the delimiter is not found (the original text is returned in such cases). Therefore, the previous expression can be simplified as:

    Text.AfterDelimiter([Path], "\", {0, RelativePosition.FromEnd})

    Wednesday, September 20, 2017 4:34 PM