Convert month data into day wise data

[manjesh_kumar]

Hello Experts,

Good Day.

I have a situation where I have the budget data in a macro form. I need to explode this data into daywise so that I can make a comparison between Actuals Vs Budget in one of my reports. How can I achieve this in my activity.

Every line item needs to have 90 rows per line (i.e. Jan - 31 Days, Feb - 28 Days, March - 31 Days)

LOCATION	SEGMENT	Jan-21	Feb-21	Mar-21
BANGALORE	MOBILE	1,000	1,300	1,800
BANGALORE	LAPTOP	1,200	1,500	1,700
CHENNAI	MOBILE	1,300	2,300	3,000
CHENNAI	GAMING	4,000	3,000	3,500

Regards,
Manjesh

[NIVED_NAMBIAR (NIVED N)]

Hi @manjesh_kumar
SO each row under the column month represent each day is it right

[manjesh_kumar]

Hello @NIVED_NAMBIAR ,

The column Jan-21 needs to be exploded day wise 1-Jan-2021 to 31-Jan-2021 like wise for the rest of the columns.

Regards,
Manjesh

[NIVED_NAMBIAR (NIVED N)]

Hi @manjesh_kumar
do u need the 1-Jan-2021 as column right ?

[prasath17 (Guru)]

@manjesh_kumar

Jan-21 needs to be exploded day wise 1-Jan-2021 to 31-Jan-2021

HI @manjesh_kumar - Please check this…

Enumerable.Range(1, Date.DaysInMonth(cdate("Jan-21").Year, cdate("Jan-21").Month)).Select(function (d) New Date(cdate("Jan-21").Year, cdate("Jan-21").Month, d).ToString("d-MMM-yyyy")).ToList

[manjesh_kumar]

Hello @prasath17 / @NIVED_NAMBIAR

Sorry for the confusion my bad . I am attaching a excel file for reference. There are 2 sheets input and output. Take the input sheet and convert into output sheet.
Sales Budget.xlsx (25.6 KB)

Regards,
Manjesh

[manjesh_kumar]

Hello @prasath17 @NIVED_NAMBIAR ,

Good Day.

Did you find some time for the query…

Regards,
Manjesh

[NIVED_NAMBIAR (NIVED N)]

Hi @manjesh_kumar
i had one doubt while going through the excel file
what is budget column values, how it is calculated around ?

Regards,
Nived N

[manjesh_kumar]

Hello @NIVED_NAMBIAR ,

It is the number of days for that month . Ex: Jan-21. So there are 31 days in that month, then divide that budget amount by the number of days.

Example

LOCATION	SEGMENT	Jan-21	Feb-21	Mar-21
BANGALORE	MOBILE	1,000	1,300	1,800

Jan-21 : 1000/31 days = 32.26 / day
Feb-21 : 1300/28 days = 46.43 / day
Mar-21 : 1800/31 days = 58.06 / day

I hope this clarifies, if there is anything let me know.

Regards,
Manjesh

[manjesh_kumar]

Hello @Marius_Puscasu,

Good Day.

Maybe can you help my query stated above, would be of great help.

Regards,
Manjesh

[kumar.varun2 (Varun Kumar)]

Hi @manjesh_kumar

Please check the xaml attached if it solves your problem

90 Rows per Line.xaml (9.2 KB)

Also, the input and the output you have sent are not correct.
Please check cell D369 of your Input sheet. It is not null. Before running the workflow please delete the content of cell D369.

The output is also not correct

The highlighted values must be 32.26 (Am I right?)

Please let me know if the solution works for you.

[manjesh_kumar]

Hello @kumar.varun2 ,

Thank you, it worked perfectly as expected.

Regards,
Manjesh

[kumar.varun2 (Varun Kumar)]

@manjesh_kumar

you have marked the wrong post as solution

[manjesh_kumar]

Hello @kumar.varun2 ,

Absent minded Corrected it.

Regards,
Manjesh