Adding Columns for Weekends and duplicating data

Hi Community,

I need your help.

I have a dataset with consecutive weekday prices. I need to calculate the average monthly prices, including Saturdays and Sundays. Since the prices for the weekends are not available in my dataset, I need to generate those rows and copy the last updated rate available (generally the current week's Friday rate).

Occasionally, this can also happen if a holiday falls in the middle of a week. For example, if the holiday is on a Tuesday, I will have prices for Monday, Wednesday, Thursday, and Friday, but I will need to generate the row for Tuesday and copy the rate from Monday.

This is how it looks now.

and this is how I need it to be (colored blue are the dates and rates added)

Thank you

Question Add Columns.yxmd

Workflow

Date Time

Transformation

Preparation

Accepted answers

binu_acs

@grromerro one way of doing this

Question Add Columns.yxmd

All comments

OTrieger

@grromerro
One way to do it will be by selecting the first day of the month, then based on a formula determine the number of days for that month, go to Generate Rows and generate the number of days, now create the dates with a formula to add the row number to the initial date. Match the table of rates with your table. With Multi Row Formula add the missing rates.

binu_acs

@grromerro one way of doing this

Question Add Columns.yxmd

grromerro

Thank you so much, this was a fantastic solution!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703