let
Source = Web.Contents("[URL]https://archives.nseindia.com/content/historical/DERIVATIVES/2022/OCT/fo21OCT2022bhav.csv.zip[/URL]"),
UnzipContents=(ZIPFile) =>
let
Header = BinaryFormat.Record([
MiscHeader = BinaryFormat.Binary(14),
BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
ExtrasLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
]),
HeaderChoice = BinaryFormat.Choice(
BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
each if _ <> 67324752 // not the IsValid number? then return a dummy formatter
then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
else BinaryFormat.Choice(
BinaryFormat.Binary(26), // Header payload - 14+4+4+2+2
each BinaryFormat.Record([
IsValid = true,
Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
Extras = BinaryFormat.Text(Header(_)[ExtrasLen]),
Content = BinaryFormat.Transform(
BinaryFormat.Binary(Header(_)[BinarySize]),
(x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
)
]),
type binary // enable streaming
)
),
ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
Entries = List.Transform(
List.RemoveLastN( ZipFormat(ZIPFile), 1),
(e) => [FileName = e[Filename], Content = e[Content] ]
)
in
Table.FromRecords(Entries),
//Function End
Files = UnzipContents(Source),
// Function End
Content = Files{0}[Content],
#"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CONTRACT_D", type text}, {"PREVIOUS_S", type number}, {"OPEN_PRICE", type number}, {"HIGH_PRICE", type number}, {"LOW_PRICE", type number}, {"CLOSE_PRIC", type number}, {"SETTLEMENT", type number}, {"NET_CHANGE", type number}, {"OI_NO_CON", Int64.Type}, {"TRADED_QUA", Int64.Type}, {"TRD_NO_CON", Int64.Type}, {"TRADED_VAL", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "OI", each [OI_NO_CON]*50),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([CONTRACT_D] = "FUTIDXNIFTY24-NOV-2022" or [CONTRACT_D] = "FUTIDXNIFTY27-OCT-2022" or [CONTRACT_D] = "FUTIDXNIFTY29-DEC-2022"))
in
#"Filtered Rows"