Trim the spreadsheet name in "transform file" code

hi nok 

You can handle this situation by searching dynamically for the sheet whose name trims down to “BAN”, instead of directly referencing Item="BAN".

Your current code expects the sheet name to be exactly “BAN”, so any name like “BAN ” or “ BAN” will break. The idea is to look through all sheets in the workbook, trim their names, and select the one whose trimmed name equals “BAN”.

Below is an updated version of your function using this approach:

(Parâmetro3 as binary) =>
let
    Source = Excel.Workbook(Parâmetro3, null, true),

    // Find the sheet where the trimmed name equals "BAN"
    BAN_Entry =
        List.First(
            List.Select(
                Source,
                each Text.Trim([Item]) = "BAN" and [Kind] = "Sheet"
            )
        ),

    BAN_Sheet = BAN_Entry[Data],

    #"Promoted Headers" = Table.PromoteHeaders(BAN_Sheet, [PromoteAllScalars = true])
in
    #"Promoted Headers"

Explanation of what this code does:

1. Loads the Excel workbook.
2. Loops through all sheets.
3. Applies Text.Trim() to the sheet names.
4. Selects the one where the trimmed name equals "BAN".
5. Returns its data and promotes headers.

This way, it will work for:

• “BAN”
• “BAN ”
• “ BAN”
• any variation with extra spaces

If the file ever contains more than one sheet that trims to “BAN”, it will take the first one, so be sure your files follow the expected pattern.

If you need to handle multiple sheets with “BAN” inside the same file or add error handling, feel free to share a sample file and I can help adjust the function further.

Regards,

Nadeem Salam

If this answers your question, please mark it as a solution so it can help others.