Expand Data between two dates

[joshua1990]

I have a table in Excel / Power Query that has the following structure:

• We have the Article and the Country in Column A and B
• Column C shows us the Quarter
• Then we have Column D and E that shows are the effective from and to date (It is always a Monday)
• In Column F we have the Sales Target per Group
• And in the last column we have the Target Value

PPC	LocationID	Cycle	Effective from	Effective until	Group	Value
A	1	Q1	2024-01-01	2024-04-01	A1	135.540
A	1	Q1	2024-01-01	2024-04-01	A2	180.718
A	1	Q1	2024-01-01	2024-04-01	A3	-
A	1	Q1	2024-01-01	2024-04-01	A4	532.836
A	1	Q1	2024-01-01	2024-04-01	A5	232.353
A	2	Q1	2024-01-01	2024-04-01	A1	6.672
A	2	Q1	2024-01-01	2024-04-01	A2	8.896
A	2	Q1	2024-01-01	2024-04-01	A3	34.311
A	2	Q1	2024-01-01	2024-04-01	A4	58.821
A	2	Q1	2024-01-01	2024-04-01	A5	11.436
A	3	Q1	2024-01-01	2024-04-01	A1	4.707
A	3	Q1	2024-01-01	2024-04-01	A2	6.278
A	3	Q1	2024-01-01	2024-04-01	A3	24.213
A	3	Q1	2024-01-01	2024-04-01	A4	78.741
A	3	Q1	2024-01-01	2024-04-01	A5	8.070
A	1	Q2	2024-04-08	2024-07-08	A1	135.540
A	1	Q2	2024-04-08	2024-07-08	A2	180.718
A	1	Q2	2024-04-08	2024-07-08	A3	-

Now, I would like to have a table in the end that fills the same elements into the dates between the from and until date.

So, I would like to get the values extrapolated for the weeks between the effective from and until date.

See the expected result below:

Article	Country	Quarter	Date	Group	Value
A	1	Q1	2024-01-01	A1	135.540
A	1	Q1	2024-01-01	A2	180.718
A	1	Q1	2024-01-01	A3	-
A	1	Q1	2024-01-01	A4	532.836
A	1	Q1	2024-01-01	A5	232.353
A	1	Q1	2024-01-08	A1	135.540
A	1	Q1	2024-01-08	A2	180.718
A	1	Q1	2024-01-08	A3	-
A	1	Q1	2024-01-08	A4	532.836
A	1	Q1	2024-01-08	A5	232.353
A	1	Q1	2024-01-15	A1	135.540
A	1	Q1	2024-01-15	A2	180.718
A	1	Q1	2024-01-15	A3	-
A	1	Q1	2024-01-15	A4	532.836
A	1	Q1	2024-01-15	A5	232.353

How would you do that with Power Query?

If required, I have a calendar table with all the dates etc.

[slorin]

Hi @joshua1990 

add column

List.Dates(
  [Effective from],
  Duration.Days([Effective until]-[Effective from])/7+1,
  #duration(7,0,0,0)
)

then expand

Stéphane

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    comb = Table.CombineColumns(
        Source, 
        {"Effective from", "Effective until"},
        (w) => List.Generate(
            () => w{0}, 
            (x) => x <= w{1},
            (x) => Date.AddDays(x, 7)
        ),
        "Date"
    ),
    result = Table.ExpandListColumn(comb, "Date")
in
    result

then sort and/or rename columns to your liking

[Omid_Motamedise]

What is the transformation logic?