Power query insert rows in between based on number of days between moths.

Occasional Contributor

Hi!

I am not sure if power query is the best way to do this… But I want to insert/copy X new rows in between already existing rows based on the number of days between the dates.

Please see pic for a better understanding as it is hard to explain in text what I want.

 

Basically, I want to copy every row for each month and product and using the same “värde” (value) for that month.

 

Ex. I want the first row to fill down for 31 days with the “värde” (value) 5, the second row should fill down for 28 days with the “värde” (value) 7, the third for 31 days with the “värde” (value) 3 and so on…

I know there is a lot of smart people here and I would be very happy if someone could help me out as I am very new to power query.

 

Thanks in advance!

 

// Victor

6 Replies
A file (without sensitive data), Excel version and operating system would be advantageous to get a solution proposal.

Thank you for your patience and understanding

Nikolino

@Victor107200 Difficult to help without a file with your data. But you would want to add a calendar table (one with every day of the year). Then you need to transform the "Attribut" column to the first day of the month and data type is as a date. As per your screenshot it's a text (ABC). Then merge data with the calendar table and fill down.

 

If you run into trouble, come back here.

 

Go kväll!

 

Thanks for your answers!

 

I have attached a file were I try to explain what I want to accomplish.

@Victor107200 Added a DailySales sheet to your file with a pivot table to check the totals.

 

By the way, I needed to copy the numbers in the forecast table and paste them back as values. The EDATE function looking at "maj" and "okt" caused value errors on my system.

Thanks a lot Riny! I would never have figured this out my self and I have been looking throughe so many youtube videos... I think this might work for me. I will try and redo the steps you have done and see if I get the same results on the actual data.

@Victor107200 Use this one instead. With a slight modification it is now more dynamic. Allowing for new products and markets.