Excel Power Query: Trying to produce a file

[mduntley]

So I am trying to produce a file in Power Query using two tables. There is a problem that I am facing

1. Table 2 has set dates for a fee schedule and Table 1 shows an effective date of 7/15. I got the 8/31/2022 and 9/18/2022 Dates in, but I also need to get the 7/13 date also as 7/15 will be after 7/13, but I don't need anything before 7/13. I am producing this file and importing it into another program.

Book3
	A	B	C	D	E
1	Table 1
2	FSC	Effective Date	CPT	Modifier	Price
3	557	7/15/2022	36590		398
4			36590	26	319
5			36590	TC	79
6
7	Table 2
8	FEE SCHEDULE ID	FEE SCHEDULE NAME	Start Date
9	557	FSC Name	6/1/2021
10	557	FSC Name	10/1/2021
11	557	FSC Name	1/1/2022
12	557	FSC Name	5/2/2022
13	557	FSC Name	7/1/2022
14	557	FSC Name	7/13/2022
15	557	FSC Name	8/31/2022
16	557	FSC Name	9/18/2022
17
18	Table I am getting
19	FEE SCHEDULE ID	Start Date	CPT	Modifier	Price
20	557	8/31/2022	36590		398
21	557	8/31/2022	36590	26	319
22	557	8/31/2022	36590	TC	79
23	557	9/18/2022	36590		398
24	557	9/18/2022	36590	26	319
25	557	9/18/2022	36590	TC	79
26
27	Result that I want
28	FEE SCHEDULE ID	Start Date	CPT	Modifier	Price
29	557	7/13/2022	36590		398
30			36590	26	319
31			36590	TC	79
32	557	8/31/2022	36590		398
33			36590	26	319
34			36590	TC	79
35	557	9/18/2022	36590		398
36			36590	26	319
37			36590	TC	79
Sheet1

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Effective Date", type date}}),
    tbl1 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    lst = Table.TransformColumnTypes(tbl1,{{"Start Date", type date}})[Start Date],
    N = List.Count(List.Select(lst, each _ > tbl[Effective Date]{0})) +1,
    ReplaceEffectiveDate = (tbl as table, dt as date) => 
        let 
            lst = Table.ToRecords(tbl),
            rcd = Record.TransformFields(lst{0}, {{"Effective Date", each dt}}),
            Result = Table.FromRecords({rcd} & List.Skip(lst,1))
        in 
            Result,
    tbl2 = List.Accumulate(List.LastN(List.Sort(lst),N), Table.FirstN(tbl,0), (s,c)=> s & ReplaceEffectiveDate(tbl, c)),
    Result = Table.RenameColumns(tbl2,{{"FSC", "Fee Schedule ID"}, {"Effective Date", "Start Date"}})
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	FSC	Effective Date	CPT	Modifier	Price		Fee Schedule ID	Start Date	CPT	Modifier	Price
2	557	7/15/2022	36590		398		557	7/13/2022	36590		398
3			36590	26	319				36590	26	319
4			36590	TC	79				36590	TC	79
5							557	8/31/2022	36590		398
6	FEE SCHEDULE ID	FEE SCHEDULE NAME	Start Date						36590	26	319
7	557	FSC Name	6/1/2021						36590	TC	79
8	557	FSC Name	10/1/2021				557	9/18/2022	36590		398
9	557	FSC Name	1/1/2022						36590	26	319
10	557	FSC Name	5/2/2022						36590	TC	79
11	557	FSC Name	7/1/2022
12	557	FSC Name	7/13/2022
13	557	FSC Name	8/31/2022
14	557	FSC Name	9/18/2022
15
Sheet1