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

[norpriest]

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.

 

[vletm]

norpriest
Change those names to same as You've named those before.
... Double click sheet tab and clear endings.
What is Your point?

 

[norpriest]

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.

 

[vletm]

norpriest
If this is something Power Query specific question,
then this thread should be opened to Power BI, Power Query and Power Pivot.
WIth basic Excel ...
You could try to have all those files open and one-by-one modify needed name.
After that, do refresh.
( I do not have/use Power Query ).

 

[pecoflyer]

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.

I don't think so https://trumpexcel.com/combine-data-from-multiple-workbooks/

 

[norpriest]

I don't think so https://trumpexcel.com/combine-data-from-multiple-workbooks/

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.

 

[GraH - Guido]

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.

 

[norpriest]

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.

 

[GraH - Guido]

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.

 

[GraH - Guido]

The table definition (could actually be a list {"ID", "Name"}, but wanted to give you this little extra.

let
    Source =
        #table(
        type table
            [
                #"ID"=number,
                #"Name"=text
            ],
        {
        }
        )
    ,Headers = Table.ColumnNames(Source)
in
    Headers

The "working 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