May 05 2021 10:53 AM
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
May 05 2021 11:15 AM
May 05 2021 11:34 AM
@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!
May 05 2021 01:54 PM
Thanks for your answers!
I have attached a file were I try to explain what I want to accomplish.
May 05 2021 09:10 PM
@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.
May 05 2021 11:15 PM
May 05 2021 11:19 PM
@Victor107200 Use this one instead. With a slight modification it is now more dynamic. Allowing for new products and markets.