web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / modify excel file (bus...
Power Automate
Answered

modify excel file (business) header names with a flow

(0) ShareShare
ReportReport
Posted on by freddy65 18

Hi All,

I have an excel file that gets uploaded automatically and saved to a SharePoint site (teams). The column names will include customer data and and data for each month of the financial Year.. Jul-23, Aug-23.... Jun-24. I am trying to find a way to dynamically remove the years from the table headers so its just Jul, Aug, Sept, Oct...etc..

 

 

id	Customer Description	Customer State	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24

to
															
id	Customer Description	Customer State	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun

 

 


Is something like this possible with Power Automate?


Categories:
General topics
  • Nived_Nambiar Profile Picture
    Nived_Nambiar 18,138 Super User 2026 Season 1 on at

    Hi @freddy65 

     

    Run office script which can help to remove the year component from table headers

     

    Use this office script for this purpose

    function main(workbook: ExcelScript.Workbook) {
 // Get the active worksheet
 let sheet = workbook.getActiveWorksheet();

 // Get all tables on the active worksheet
 let tables = sheet.getTables();

 // Define a regex pattern to match "month-year"
 const pattern = /^(January|February|March|April|May|June|July|August|September|October|November|December)-\d{4}$/i;

 // Loop through each table
 tables.forEach(table => {
 // Get the table headers
 let headers = table.getHeaderRowRange().getValues()[0];

 // Loop through each header and modify if it matches the pattern
 let newHeaders = headers.map(header => {
 if (pattern.test(header)) {
 // Split the header by '-'
 let [month, year] = header.split('-');
 return month; // Return the month part only
 }
 return header; // Return the original header if no match
 });

 // Set the modified headers back to the table
 table.getHeaderRowRange().setValues([newHeaders]);
 });
}

     

    Use run script to run the office script as well in Power Automate.

    Nived_Nambiar_0-1718590969978.png

     

    Note that- due to some changes as per your requirement - there may be some changes in office script needed.

     

    Thanks & Regards,

    Nived N 🚀

    LinkedIn: Nived N's LinkedIn
    YouTube: Nived N's YouTube Channel
    Blog: Nived Nambiar's Blogs

    🔍 Found my answer helpful? Please consider marking it as the solution!
    Your appreciation keeps me motivated. Thank you! 🙌

     

  • freddy65 Profile Picture
    freddy65 18 on at

    Thank you for the quick response, as the file will be overridden over and over automatically I am unable to use a script which is why I was hoping for a different solution.  Thanks again

  • Nived_Nambiar Profile Picture
    Nived_Nambiar 18,138 Super User 2026 Season 1 on at

    Hi @freddy65 

     

    You can select script from run script- i think it won't get overridden all the time when file is overridden as it is stored separately.

     

    Thanks & Regards,

    Nived N 🚀

    LinkedIn: Nived N's LinkedIn
    YouTube: Nived N's YouTube Channel
    Blog: Nived Nambiar's Blogs

    🔍 Found my answer helpful? Please consider marking it as the solution!
    Your appreciation keeps me motivated. Thank you! 🙌

     

  • freddy65 Profile Picture
    freddy65 18 on at

    Hi @Nived_Nambiar 

    I am giving this a go as I do not see any other solution. I have added the script to the excel file as my first step to then test on another file however I am getting the following errors.. Do I need to change the formatting of some of the header columns?

    freddy65_0-1718600485140.png

     

  • Nived_Nambiar Profile Picture
    Nived_Nambiar 18,138 Super User 2026 Season 1 on at

    Hi @freddy65 

     

    Yesterday i was not able to try the script in an excel file due to some issues. I have updated the script like below and i have tested it , it is working fine.

     

    function main(workbook: ExcelScript.Workbook, sheetname:string, tablename:string) {
 // Get the active worksheet
 let sheet = workbook.getWorksheet(sheetname);

 // Get all tables on the active worksheet
 let table = sheet.getTable(tablename);

 // Define a regex pattern to match "month-year"
 const pattern = /^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sept|Oct|Nov|Dec)-\d{4}$/i;
	let headers: string[] = table.getHeaderRowRange().getValues()[0] as string[];
	let newHeaders = headers.map(header => {
 if (pattern.test(header)) {
 // Split the header by '-'
 let [month, year] = header.split('-');
 return month; // Return the month part only
 }
 return header; // Return the original header if no match
 });
	table.getHeaderRowRange().setValues([newHeaders]);

}

     

    Here you have to pass 2 parameters- one is sheet name and other is table name where the data to changed is present.

     

    Thanks & Regards,

    Nived N 🚀

    LinkedIn: Nived N's LinkedIn
    YouTube: Nived N's YouTube Channel
    Blog: Nived Nambiar's Blogs

    🔍 Found my answer helpful? Please consider marking it as the solution!
    Your appreciation keeps me motivated. Thank you! 🙌

     

  • freddy65 Profile Picture
    freddy65 18 on at

    Hi @Nived_Nambiar 

     

    Thanks for getting back to me, much appreciated. I tried the new script in Excel and put both parameters in the code and the code runs but does not produce the results. Is there a way to test the output as I am new to Type script? Thanks again for the assist! 

    I made the following changes:

let sheet = workbook.getWorksheet("MM Budget");
...
 let table = sheet.getTable("Data");
  • Nived_Nambiar Profile Picture
    Nived_Nambiar 18,138 Super User 2026 Season 1 on at

    Hi @freddy65 

     

    did you hardcoded that in code?- I have made the code to take from parameters the values - sheet name and table name.

     

    Could you share how script looks finally in your case ?

     

    Thanks & Regards,

    Nived N 🚀

    LinkedIn: Nived N's LinkedIn
    YouTube: Nived N's YouTube Channel
    Blog: Nived Nambiar's Blogs

    🔍 Found my answer helpful? Please consider marking it as the solution!
    Your appreciation keeps me motivated. Thank you! 🙌

     

  • freddy65 Profile Picture
    freddy65 18 on at

    Hi @Nived_Nambiar 

    I made no other changes to your code. When I used your code it seems it can not find the sheetname or tablename so I thought I would hard code this in. With your code directly in Excel, It comes up with the following message, when I hardcode the sheet name then I get the same error with the tablename. The only thing I have not mentioned about my headers is that there are many more columns before the Month-Year columns but I don't see this as an issue.

    Line 3: Workbook getWorksheet: The argument is invalid or missing or has an incorrect format.
  • freddy65 Profile Picture
    freddy65 18 on at

    Apologies, I see what you did with the parameters and it did do something using Automate but it changed all years to 2024?

    From:
Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24

To:
Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24

     

  • Verified answer
    Nived_Nambiar Profile Picture
    Nived_Nambiar 18,138 Super User 2026 Season 1 on at

    Hi @freddy65 

     

    Got it where the issue was. Actually I have assumed columns would be in format- Jan-2024, Feb-2024 etc. so the code was done in that way. Now it can work with any year formats, but assuming month format would be of MMM

     

    Try this script

    function main(workbook: ExcelScript.Workbook, sheetname:string, tablename:string) {
 // Get the active worksheet
 let sheet = workbook.getWorksheet(sheetname);

 // Get all tables on the active worksheet
 let table = sheet.getTable(tablename);

 // Define a regex pattern to match "month-year"
 const pattern = /^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-\d+$/i;
	let headers: string[] = table.getHeaderRowRange().getValues()[0] as string[];
	let newHeaders = headers.map(header => {
 if (pattern.test(header)) {
 // Split the header by '-'
 let [month, year] = header.split('-');
 return month; // Return the month part only
 }
 return header; // Return the original header if no match
 });
	table.getHeaderRowRange().setValues([newHeaders]);

}

     

    See how it worked

    before running the script-

    Nived_Nambiar_0-1718777374230.png

     

    After running script-

    Nived_Nambiar_1-1718777391290.png

     

    Hope it helps !

    Thanks & Regards,

    Nived N 🚀

    LinkedIn: Nived N's LinkedIn
    YouTube: Nived N's YouTube Channel
    Blog: Nived Nambiar's Blogs

    🔍 Found my answer helpful? Please consider marking it as the solution!
    Your appreciation keeps me motivated. Thank you! 🙌

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
11manish Profile Picture

11manish 233 Super User 2026 Season 2

#2
David_MA Profile Picture

David_MA 227 Super User 2026 Season 2

#3
Mohsin Ali Profile Picture

Mohsin Ali 178

Last 30 days Overall leaderboard

Featured topics

Known issue with browser automation in Power Automate for Desktop on Google Chrome 146 and 147
Share your ideas and struggles with building Power Automate flows!
Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow
Start and wait for an approval action
Restore a deleted flow
List of actions cheat sheet for Power Automate Desktop
Welcome to the Power Automate forum!

Product updates

Microsoft Power Platform Community release plans