Column name change dynamically on monthly bases

[SunnyJoshi]

Hello ,

is there any way to make a flow which dynamically changes column name. For example 

current column names are as below

col A : 01-01-2023

col B : 01-02-2023

col C : 01-03-2023

col D : 01-04-2023

 i want to chemge them dynamically from next month it will create as below

col A : 01-02-2023

col B : 01-03-2023

col C : 01-04-2023

col D : 01-05-2023

and also clear the values in new column which is col D.

Can anyone help me?

[lbendlin]

Unpivot your data so your columns become rows. That way you always have the same column structure.

[VJR]

You can make use of the Excel formulas for this, either write the formulas manually within the Excel (easy way) or write it via PAD (not needed).

- The first cell will have today's date formula. If you don't want to show this, then place it in some far away cell like Z1 invisible to others.

- Formula on next column B1

- Cell C1 will have formula as =EOMONTH(B1;0)+1

- Cell D1 will have formula as =EOMONTH(C1;0)+1 and so on

Basically, you are calculating the End of Month of today's date and adding 1 to get the first day of next month.

This way whenever you open the excel sheet it will automatically update the first cell as per today's date and then the subsequent columns as per that date.

[SunnyJoshi]

• Yes this was the way i used but my concern was when people play with file they add/remove the columns which makes the formulas ineffective. But thank you so much. If you still can provide anything which helps me.

[SunnyJoshi]

It can’t help the actual end user. It won’t be user as it will be having 10k+ rows and only 10 columns which will create 40k+ rows.

[VJR · Accepted solution]

Hi @SunnyJoshi 

Sure, I can help you with the solution you want in this case.

If you have noticed I have also mentioned about inserting the formulas via PAD too. You can simply use the "Set cell value" and write the Formulas in the cells when the Flow runs.

If you want to do the date calculation via PAD actions then it will be a bit tedious. 

But it is as below.

Copy-Paste the below code in a blank Flow editor and modify as required.

DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateOnly CurrentDateTime=> CurrentDateTime
DateTime.Add DateTime: CurrentDateTime TimeToAdd: 1 TimeUnit: DateTime.TimeUnit.Months ResultedDate=> ResultedDate
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: ResultedDate CustomFormat: $'''01/MM/yyyy''' Result=> StartDate
DateTime.Add DateTime: StartDate TimeToAdd: 1 TimeUnit: DateTime.TimeUnit.Days ResultedDate=> NextDay
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: NextDay CustomFormat: $'''dd/MM/yyyy''' Result=> NextDay
Display.ShowMessageDialog.ShowMessage Message: $'''The Start date is: %StartDate%
The next date is: %NextDay%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed2

. 

[SunnyJoshi]

Thank you this solved my problem.