Price tracking in power query

Hi SYBTRON 

Please try this

let
    Source = Folder.Files("D:\STOCK ANALYSIS\stock analysis 3\equity"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type number}, {"Column7", Int64.Type}, {"Column8", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Column8", "Column3", "Column4", "Column5"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Stock"}, {"Column2", "Date"}, {"Column6", "Closing price"}, {"Column7", "Volume"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Volume"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Custom", each fn([Date], -1, Day.Thursday)),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Inserted Date" = Table.AddColumn(#"Removed Columns2", "Date.1", each Date.From(Text.From([Date], "en-IN")), type date),
    #"Removed Columns3" = Table.RemoveColumns(#"Inserted Date",{"Date"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"Date.1", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"Stock", "Date", "Closing price"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Custom", each fn([Date], -1, Day.Thursday)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type date}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Expiry"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns3",{{"Stock", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Sorted Rows",{{"Closing price", "Closing Price"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns4", "Weekday", each Date.DayOfWeek([Date])),
    #"Added Custom" = Table.AddColumn(#"Added Custom3", "Custom", each let a=Date.AddDays([Expiry],-28),
b=Date.AddDays([Expiry],-15)
in if [Date]>a and [Date]<=b then "from "&Text.From(a)&" to"&Text.From(b) else if [Date]>b and [Date]<=[Expiry] then "from "&Text.From(b)&" to "&Text.From([Expiry]) else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let _Mindate=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Stock]=[Stock] and x[Custom]=[Custom])[Date]),
_minprice=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Stock]=[Stock] and x[Custom]=[Custom] and x[Date]=_Mindate)[Closing Price])
in
([Closing Price]-_minprice)/_minprice),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1]>=-0.5 and [Custom.1]<0 then 
"-5% to 0" else if [Custom.1]>=0 and [Custom.1]<0.5 then " 0 to 5%" else "Rising"),
    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Custom.3", each let a=Date.AddDays([Expiry],-15),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.1])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.1] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
    #"Added Custom6" = Table.AddColumn(#"Added Custom4", "Custom.4", each let a=Date.AddDays([Expiry],-15),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.2])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.2] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
    #"Added Custom5" = Table.AddColumn(#"Added Custom6", "Custom.5", each let a=Date.AddDays([Expiry],-1),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.2])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.2] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Weekday", "Custom", "Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.3", Percentage.Type}})
in
    #"Changed Type1"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.