M code for the equivalent of of Left (string, len(string)-11)

How to remove the last X characters from a string in a column?

I am trying to generate a list of user-friendly file names that are used in the generation of a Power Query table. However when reading the list of m text commands, I did not see a straightforward approach for doing the the equivalent of:

 Text.left(#"previous table"{"Filename'}, Text.length(#"previous table"{"Filename'} - 11)

(based on my knowledge of Excel).

Since I saw my filenames have variable length to them but knew I always wanted to eliminate the last 11 characters, I thought maybe I could use Text.reverse(#"previous table"{"Filename'}) and then do a split at position 11 and just keep the right side of the split and then do another reversal. However I don't know m syntax well enough yet and all I got was an error when I tried to do Text.Reverse

My m code:

let
    Source = Forecasting,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Source.Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Source.Name", "Submitted Reports"}}),
    Custom1 = Text.Reverse(#"Renamed Columns"{{"Submitted Reports"}})
in
    Custom1

Example data when we get to the Renamed Columns step:

Submitted Reports
Bug Bunny 241008923443.xlsm
Daffy Duck 241130234521.xlsm
Wyle E. Cayote 240909124312.xlsm

Desired results:

Submitted Reports
Bug Bunny 241008
Daffy Duck 241130
Wyle E. Cayote 240909

Solution

Remove the file extension (if exists) and the last six characters:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // Remove ".xlsm" if it exists
    RemoveExtension = Table.TransformColumns(Source, {{"Submitted Reports", each if Text.EndsWith(_, ".xlsm") then Text.RemoveRange(_, Text.Length(_) - 5, 5) else _}}),
    CleanFilenames = Table.TransformColumns(RemoveExtension, {{"Submitted Reports", each Text.RemoveRange(_, Text.Length(_) - 5, 5)}})
in
    CleanFilenames

If you want to remove the last five characters knowing what they are (".xlsm"):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    CleanFilenames = Table.TransformColumns(Source, {{"Submitted Reports", each if Text.EndsWith(_, ".xlsm") then Text.RemoveRange(_, Text.Length(_) - 5, 5) else _}})
in
    CleanFilenames

or if you want to remove a four character file extenstion:

CleanFilenames = Table.TransformColumns(Source, {{"Submitted Reports", each if Text.Middle(_, Text.Length(_) - 5, 1) = "." then Text.RemoveRange(_, Text.Length(_) - 5, 5) else _}})

or just remove the last five characters:

CleanFilenames = Table.TransformColumns(Source, {{"Submitted Reports", each Text.RemoveRange(_, Text.Length(_) - 5, 5)}})