let
Источник = Json.Document(File.Contents(Excel.CurrentWorkbook(){[Name="fPath"]}[Content]{0}[Column1])),
subTable = Table.FromRecords(Источник),
diff = (me as number, col as list) =>
let
to = Table.FromColumns(List.Transform(col,each Text.ToList(_))),
filtered = Table.Group(to, Table.ColumnNames(to), { {"unique", each _{0}} })[[unique]], // !!!
rec = Table.TransformColumns(filtered, {"unique", each Record.FieldValues(_)}), // list of transversal characters from [product-tk] column
wklist = Table.FirstValue(Table.SelectRows(rec, each List.Count(List.Distinct([unique])) > 1)), // ?!1!? row of different values
wk = Text.ToList(wklist{me}), // (posenum - me - current of it)
digits = {"A".."E"},
done = if List.Count(List.Intersect({wk, digits}))=1 then
let
step1=List.ReplaceMatchingItems(wk,{{"A",1},{"B",2},{"C",3},{"D",4},{"E",5}}),
step2=Number.From(step1{0})
in step2
else Number.From(wk{0})
in done,
transformed = Table.TransformColumns(subTable, {
{"productIds", (items) => Table.FromRows(List.Transform(List.Positions(items), each {items{_}, (_ + 1)}), {"productIds", "WL"}), Table.Type}, // !!!
{"calendarEntries", (recs) =>
Table.RemoveColumns(
Table.AddColumn(
Table.TransformColumns(
Table.AddColumn(Table.FromRecords(recs), "mny", each Text.Range([productCode], Text.Length([productCode]) - 3,3), List.Type) // "mn-year"
,{"productCode", (value)=>Text.Range(value,0,Text.Length(value) - 3)}), // "tk"
"wk", each
let
tt = Table.Column(Table.TransformColumns(Table.FromRecords(recs),{"productCode", (value)=>Text.Range(value,0,Text.Length(value) - 3)}),"productCode"), // list of column values
pos = List.PositionOf(tt, Text.From([productCode])),
w = if List.Count(List.Distinct(tt))=1 then 1 else
let
nn = diff(pos, tt) // "wk"
in nn
in w
, Number.Type),
{"firstTrade", "lastTrade"}),
Table.Type}
}),
joined = Table.AddColumn(transformed, "joined", each Table.Join([productIds], {"WL"}, [calendarEntries], {"wk"}), Table.Type), // !!!
#"Removed Columns" = Table.RemoveColumns(joined,{"productIds", "calendarEntries"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"joined"}),
#"Expanded {0}" = Table.ExpandTableColumn(#"Removed Errors", "joined", {"productIds", "contractMonth", "settlement", "productCode", "mny", "expirationDate"}, {"productIds", "contractMonth", "settlement", "productCode", "mny", "expirationDate"}),
product=Table.RenameColumns(#"Expanded {0}", {{"productCode", "tk"}, {"expirationDate", "expire"}}), //, MissingField.Ignore
unixcol = Table.TransformColumns(product, {"expire", (value)=> Text.Upper(Date.ToText(Date.From((Value.FromText(value)/86400000)+25569), "MMMyy", "en-US"))}), // "expirationDate" UNIX
main = Table.AddIndexColumn(unixcol, "№п/п", 1),
#"Removed Columns1" = Table.RemoveColumns(main,{"name", "productId", "daily", "sto", "contractMonth"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"№п/п", "label", "optionType", "weekly", "productIds", "settlement", "tk", "mny", "expire"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"weekly", type logical}, {"settlement", type date}})
in
#"Changed Type"
|