Power Query - Get the Excel Spreadsheet File Name-CodePudding

I am looking for a M-Function that returns the currently opened Excel Spreadsheet Filename without the directory and extension.

i.e. If the spreadsheet that is opened is located here:
C:\HPCLMHSTLI_930.XLSX

I would like:
HPCLMHSTLI_930

Note: I got this working using a Custom M Function that reads a Settings Table which has two Cells defined with the following:
=MID(CELL("filename"),SEARCH("[",CELL("filename")) 1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
=LEFT(B3,(SEARCH(".",B3)-1))

So I am NOT looking for this solution. This solution just seems like a lot of work and that there should be a more elegant M-Language function that would return the currently opened spreadsheet filename.

CodePudding user response：

I didn't quite follow how you got the path - I guess that is Cell("fileName"), but with a bit of fiddling with M Code, I came up with the following:

let
    x = "C:\HPCLMHSTLI_930.XLSX",
    y=Text.AfterDelimiter(x,"\"),
    z=Text.BeforeDelimiter(y,".")
in
    z

This seems to do the trick. Since a file name can have more than 1 period in it, this may be too simple, but maybe this can work if your filenames are simple enough.

But this can be improved by wrapping this in a function in the following way:

let
  ParseFileName = (x) => 
    let 
      y=Text.AfterDelimiter(x,"\"),
      z=Text.BeforeDelimiter(y,".")
    in z
in
  ParseFileName

and then call that using something like this:

let
    Source = ParseFileName("C:\HPCLMHSTLI_930.XLSX")
in
    Source

CodePudding user response：

I found two more possible answers.

This first is a bit more sophisticated than the one above. In this case, we grab the substring starting after the first "\" and the last "."

let
    x = "C:\HPCLMHSTLI_930.XLSX",
    y = Text.PositionOf(x,"\", Occurrence.First),
    z = Text.PositionOf(x,".", Occurrence.Last),
    a = Text.Middle(x,y 1,z-y-1)
in
    a

There probably should be if statements in case one of these characters are not found.

I found one final solution that could also work in the simpler cases.

let
    x = "C:\HPCLMHSTLI_930.XLSX",
    z = Text.BetweenDelimiters(x,"\",".")
in
    z

M gives us a bunch of choices on this.