Дмитрий Голубев, вариант решения на Power Query
| Код |
|---|
let
Source = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],{{"Столбец1", type text}}),
Headers = Table.FirstN(Source,8)[Столбец1],
Rows = Table.FromColumns( {{""} & Table.Skip(Source,8)[Столбец1], Table.Skip(Source,8)[Столбец1] & {""}} ),
Grouped = Table.Group(
Rows,
{"Column1", "Column2"},
{{"a", each if Text.Contains([Column2]{2},"@") then [Column2] else List.InsertRange([Column2], 2, {null}) }},
0,
(a,b)=>Number.From(Text.Contains(b[Column1],":") and not Text.Contains(b[Column2],":") ) ),
FilteredEmpty = Table.SelectRows(Grouped, each [Column2] <> "")[a],
Out = Table.FromRows(FilteredEmpty,Headers)
in
Out |
