let
htmlText = Web.Page(Web.Contents("http://cms.indigointeractive.ru/rasp/tableV3.php?terminalid=10")),
#"Развернутый элемент Data" = Table.ExpandTableColumn(htmlText, "Data", {"Kind", "Name", "Children", "Text"}, {"Kind", "Name", "Children", "Text"}),
#"Развернутый элемент Children" = Table.ExpandTableColumn(#"Развернутый элемент Data", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.1", "Name.1", "Children.1", "Text.1"}),
#"Развернутый элемент Children.1" = Table.ExpandTableColumn(#"Развернутый элемент Children", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.2", "Name.2", "Children", "Text.2"}),
#"Развернутый элемент Children1" = Table.ExpandTableColumn(#"Развернутый элемент Children.1", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.3", "Name.3", "Children.1", "Text.3"}),
#"Развернутый элемент Children.2" = Table.ExpandTableColumn(#"Развернутый элемент Children1", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.4", "Name.4", "Children", "Text.4"}),
#"Развернутый элемент Children2" = Table.ExpandTableColumn(#"Развернутый элемент Children.2", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.5", "Name.5", "Children.1", "Text.5"}),
#"Развернутый элемент Children.3" = Table.ExpandTableColumn(#"Развернутый элемент Children2", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.6", "Name.6", "Children", "Text.6"}),
#"Развернутый элемент Children3" = Table.ExpandTableColumn(#"Развернутый элемент Children.3", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.7", "Name.7", "Children.1", "Text.7"}),
#"Развернутый элемент Children.4" = Table.ExpandTableColumn(#"Развернутый элемент Children3", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.8", "Name.8", "Children", "Text.8"}),
#"Развернутый элемент Children4" = Table.ExpandTableColumn(#"Развернутый элемент Children.4", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.9", "Name.9", "Children.1", "Text.9"}),
#"Удаленные столбцы" = Table.RemoveColumns(#"Развернутый элемент Children4",{"Text.6", "Text.5", "Text.4", "Text.3", "Text.2", "Text.1", "Text", "Caption", "Source", "ClassName", "Id", "Kind", "Name", "Kind.1", "Name.1", "Kind.2", "Name.2", "Kind.3", "Name.3", "Kind.4", "Name.4", "Kind.5", "Name.5", "Kind.6", "Name.6", "Kind.7", "Name.7", "Kind.8", "Name.8", "Kind.9", "Name.9", "Children.1"}),
#"Удалены пустые строки" = Table.SelectRows(#"Удаленные столбцы", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Added Index" = Table.AddIndexColumn(#"Удалены пустые строки", "Index", 0, 1),
maxIndex = List.Max(#"Added Index"[Index]),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Text.9] = "путь" then if [Index] + 1 > maxIndex then [Index] else [Index] + 1 else null),
renameTo = Table.RenameColumns(#"Added Custom"[[Custom]], {"Custom", "Custom1"}),
joinedRenamed = Table.Join(#"Added Custom", {"Index"}, renameTo, {"Custom1"}, JoinKind.LeftOuter),
defineWay = Table.AddColumn(joinedRenamed, "def", (rec) =>
if rec[Custom1] = null then
null
else
let
test = try Number.From(rec[Text.8])
in
test
),
#"Added Custom1" = Table.AddColumn(defineWay, "Custom.1", each if [def] = null then null else if [def][HasError] then [Custom1] - 1 else [Custom1]),
joinDefCustom = Table.Join(#"Added Index", {"Index"}, #"Added Custom1"[[Custom.1]], {"Custom.1"}, JoinKind.LeftOuter),
#"Sorted Rows" = Table.Sort(joinDefCustom,{{"Index", Order.Ascending}}),
#"Filled Up" = Table.FillUp(#"Sorted Rows",{"Custom.1"}),
result = Table.Group(#"Filled Up", {"Custom.1"}, {"temp", (sub) =>
let
list8 = List.RemoveNulls(sub[Text.8]),
poses8 = List.Positions(list8),
recs8 = Record.FromList(list8, List.Transform(poses8, each "f" & Text.From(_))),
list9 = List.RemoveNulls(sub[Text.9]),
poses9 = List.Positions(list9),
recs9 = Record.FromList(list9, List.Transform(poses9, each "f" & Text.From(_ + 4)))
in
recs8 & recs9 & [f6 = List.RemoveNulls(sub[Text.7]){0}?]
}),
#"Removed Columns" = Table.RemoveColumns(result,{"Custom.1"}),
#"Expanded temp" = Table.ExpandRecordColumn(#"Removed Columns", "temp", {"f0", "f1", "f2", "f3", "f4", "f5", "f6"}, {"f0", "f1", "f2", "f3", "f4", "f5", "f6"})
in
#"Expanded temp"
|
