Power Query Help to Calculate Stock Splits

[rasinc]

I am trying to do some stock analysis and am preparing data for use in some charts. I have set up the stock price downloads to use the STOCKHISTORY function but have now come across an issue that it does not correct for stock splits properly.

I have taken the STOCKHISTORY data and run it through Power Query in Excel to pair it down to the Stock Symbol, Date, Close Price and Diff (%). I have a query also that contains the date of a split and the number of new shares per old share.

The first sample data below is the date and the factor for the split. Any price dated before that date should be divided by 2. The rest is sample data of the prices and dates around the time of the split.

What I am trying to do is go through each row and if the date is before the date listed below (May 16/22), then divide the price in that record by 2. I have pulled the date and factor into a query to try and reference it using the column labels indicated in the sample below (Split Date and New shares per Old).

I have tried a simple comparison of [Date] < [Split Date] but I can't seem to figure out how to reference the other query properly and it keeps telling me I can't use "<" for comparisons with a list (if I recall correctly).

Can someone point me in the right direction?

TIA rasinc

let
    Source = Excel.CurrentWorkbook(){[Name="FromArray_6"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Stock", each "CM"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Stock", "Date", "Open", "High", "Low", "Close"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Stock", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Stock", "Date", "Close"}),
    

// code is going wrong here Added Custom1
    #"Added Custom1" = #!"Table.AddColumn(#""Removed Other Columns"", ""Close 2"", each if [Date] Date.IsInPreviousNDays(Table.Column(CMSplit,""Split Date""), 10000) then [Close] / Table.Column(CMSplit, ""New Shares per Old"")#(lf)else [Close])",
    
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", let earliest = List.Min(#"Added Custom1"[Date]) in each [Date] = earliest),
    FirstClose = #"Filtered Rows"{0}[Close],

    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Date", Order.Ascending}}),
    #"Inserted Percent Of" = Table.AddColumn(#"Sorted Rows", "Percent Of", each ([Close] / FirstClose * 100) - 100),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Percent Of",{{"Percent Of", type number}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Percent Of", "Diff (%)"}})
in
    #"Renamed Columns1"

Split Date	New shares per old
5/16/2022	2

Columns AF is the Date and AJ is the Close Price. The other columns are removed during the Power Query transformation as I don't currently need them. A final column for Percent Diff of Close price from start date is added with Power Query code above.

PQStockAnalysis.xlsx
	AF	AG	AH	AI	AJ
131	5/30/2022	$ 69.90	$ 71.02	$ 69.22	$ 70.34
132	5/24/2022	$ 69.60	$ 70.78	$ 68.57	$ 69.18
133	5/16/2022	$ 68.97	$ 70.76	$ 67.70	$ 68.57
134	5/09/2022	$ 138.28	$ 140.23	$ 133.70	$ 137.29
135	5/02/2022	$ 142.11	$ 143.79	$ 138.42	$ 139.82
136	4/25/2022	$ 143.02	$ 145.71	$ 140.62	$ 142.02
137	4/18/2022	$ 145.19	$ 149.43	$ 144.08	$ 144.29
138	4/11/2022	$ 147.06	$ 148.35	$ 142.93	$ 144.65
139	4/04/2022	$ 150.99	$ 152.28	$ 145.35	$ 146.74
140	3/28/2022	$ 157.92	$ 158.24	$ 150.54	$ 151.37
Banks