Forum Discussion

nok's avatar
nok
Advocate II
8 months ago
Solved

Trim the spreadsheet name in "transform file" code

Hi! I have a PowerQuery "transform file" code which is used for append the data from all spreadsheets named "BAN" within the excel files stored in the same folder on my SharePoint. This is my code:

= (Parâmetro3 as binary) => let
Source = Excel.Workbook(Parâmetro3, null, true),
BAN_Sheet = Source{[Item="BAN",Kind="Sheet"]}[Data],
#"Promoted headers" = Table.PromoteHeaders(BAN_Sheet, [PromoteAllScalars=true])
in
#"Promoted headers"

 

This code works well, but I noticed that sometimes the system that does the automatic extraction of these excel files generates a file with a space in the spreadsheet name, resulting in something like "BAN " or " BAN". This causes an error when trying to append the data from all the spreadsheets in this folder because not all of them have the same name "BAN".

 

How can I modify my code to perform a kind of Trim function on the item="BAN" so that even if there is a space somewhere, Power BI can remove/ignore the space and still read the data?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • rohit1991's avatar
    rohit1991
    8 months ago

    Hii nok 

     

    You can fix this by trimming the sheet name before filtering it. Excel.Workbook returns a table with a [Name] column, so instead of matching "BAN" exactly, match the trimmed name. Just change your step to:

    BAN_Sheet = Source{[Item = Text.Trim("BAN"), Kind = "Sheet"]}[Data]

    This ensures that "BAN", "BAN " or " BAN" all resolve to the same sheet, and your append process won’t break due to extra spaces.

     

2 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    8 months ago

    Hii nok 

     

    You can fix this by trimming the sheet name before filtering it. Excel.Workbook returns a table with a [Name] column, so instead of matching "BAN" exactly, match the trimmed name. Just change your step to:

    BAN_Sheet = Source{[Item = Text.Trim("BAN"), Kind = "Sheet"]}[Data]

    This ensures that "BAN", "BAN " or " BAN" all resolve to the same sheet, and your append process won’t break due to extra spaces.

     

  • mnadeemsalam's avatar
    mnadeemsalam
    Frequent Visitor
    8 months ago

    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.