• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I change sheet names that have the same keyword?

norpriest

New Member
For example,

File_A.xlsx has a sheet called "incentive_productA"
File_B.xlsx has a sheet called "Incentive_productB"
File_C.xlsx has a sheet called "Incentive_productC"

I want to change all the sheet names to just "Incentive"
By the way the uppercase and lowercase of the keyword might be different.

How can I do that? Thank you.
 
Last edited:
norpriest
Change those names to same as You've named those before.
... Double click sheet tab and clear endings.
What is Your point?

I tried to automatically append those sheets in Power Query.
In order to make them append automatically, those sheets need to have the exact same name.

So I tried to find a way to change those sheets names at once.
 
79387
That's great, thanks.
But it seems like if the sheet names aren't the same then it'll still throw error anyway.
And most of the files I compiled from my colleagues have inconsistent sheet names even though the structures inside are the same.

But that can be done by asking them to fill out the same sheet names though.
For Power Query, I think at least there must be some matching keywords for the system to look for, either it's file name, sheet name, or table name.
 
Last edited:
I tried to automatically append those sheets in Power Query.
In order to make them append automatically, those sheets need to have the exact same name.

So I tried to find a way to change those sheets names at once.
You do not need to do that. Instead inside Power Query normalize the sheetnames. Upper, lower or proper case them, clean & trim. Then filter with contains a certain string. To avoid these extra steps, you can add a column fnamee filter and use Text.Contains( [column] ,"incentive", Comparer.OrdinalIgnoreCase). Filter on true in the next step and delete the column. Best performance comes with a custom function, but that might be a bit to advanced already.
If the sheets have a consistent content, you can check for the column headers and if you have a match, then keep the sheet else filter it out.
 
You do not need to do that. Instead inside Power Query normalize the sheetnames. Upper, lower or proper case them, clean & trim. Then filter with contains a certain string. To avoid these extra steps, you can add a column fnamee filter and use Text.Contains( [column] ,"incentive", Comparer.OrdinalIgnoreCase). Filter on true in the next step and delete the column. Best performance comes with a custom function, but that might be a bit to advanced already.
If the sheets have a consistent content, you can check for the column headers and if you have a match, then keep the sheet else filter it out.

Hi GraH,
Thank you for suggestion.

For example, The attached files are what I want to append.
Says they might have different file names and different sheet names, but they have same structures as you can see columns "id" and "name".

Can we still append them when file names and sheet names are different?
Thank you.
 

Attachments

  • header.xlsx
    8.5 KB · Views: 1
  • Book22.xlsx
    7.9 KB · Views: 1
I would say yes.
At work for the moment and on my cell phone, so I can't view your files. If I have time I'll take a look this evening.
 
The table definition (could actually be a list {"ID", "Name"}, but wanted to give you this little extra.
Code:
let
    Source =
        #table(
        type table
            [
                #"ID"=number,
                #"Name"=text
            ],
        {
        }
        )
    ,Headers = Table.ColumnNames(Source)
in
    Headers
The "working code"
Code:
let
    Source = Folder.Files("G:\Uploads\norpriest"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    ContentAsTable = Table.TransformColumns(#"Removed Other Columns",{{"Content", Excel.Workbook, type table}}),
    ExpandContent = Table.ExpandTableColumn(ContentAsTable, "Content", {"Data"}, {"Data"}),
    PromoteHeaders = Table.TransformColumns(ExpandContent,{{"Data", Table.PromoteHeaders, type table}}),
    CheckStdTable = Table.AddColumn(PromoteHeaders, "IsStdTable", each List.IsEmpty(
List.Difference(
          TableDef, Table.ColumnNames([Data])
        , Comparer.OrdinalIgnoreCase
        )
)),
    FilterStdTable = Table.SelectRows(CheckStdTable, each ([IsStdTable] = true)),
    KeepData = Table.SelectColumns(FilterStdTable,{"Data"}),
    SetStdTitles = Table.AddColumn(KeepData, "StdTable", each Table.RenameColumns([Data],
    List.Zip({
        Table.ColumnNames([Data])
        ,TableDef
    })
)),
    RemoveData = Table.RemoveColumns(SetStdTitles,{"Data"}),
    Expand_StdTable = Table.ExpandTableColumn(RemoveData, "StdTable", TableDef, TableDef)
in
    Expand_StdTable
 

Attachments

  • norpriest_48165.xlsx
    17.2 KB · Views: 0
Back
Top