Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query - извлечение из текста фамилии и даты последней записи.
 
#1
16.06.2019 14:08:56
Добрый день! Просьба помочь. Необходимо выбрать из текста в PQ дату последнего комментария и кто его отправил. Иногда, последний комментарий находится в середине текста. Между отправителем и датой отправления может быть одинаковый код. Отправитель может иметь фамилию или фамилию+имя через пробел/точку, причем на русском или английском.
 
 
 
#2
16.06.2019 14:29:56
Доброе время суток
А почему из А4 выбрано не  pavel.baranov и 2018/11/22? Другими словами, почему это не сообщение?
Изменено: Андрей VG - 16.06.2019 14:30:41
 
 
 
#3
16.06.2019 15:18:23
Это предпоследний ответ в переписке, последний оказался почему-то в середине. Таких правда мало, но есть. В основном последние по дате - ясно в конце.
 
 
 
#4
16.06.2019 15:31:09
Цитата
olegMir написал:
последний оказался почему-то в середине
Тогда так.
 
 
 
#5
16.06.2019 15:32:49
Спасибо! Изучаю
 
 
 
#6
16.06.2019 16:28:20
.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[ТЕКСТ]],
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"ТЕКСТ", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ТЕКСТ"),
    Custom1 = Table.AddColumn(#"Split Column by Delimiter", "a", each Text.Middle([ТЕКСТ], Text.PositionOfAny([ТЕКСТ],{"/"})-4,10)),
    Custom2 = Table.AddColumn(Custom1, "b", each Text.Middle([ТЕКСТ],0, Text.PositionOfAny([ТЕКСТ],{"/"})-4)),
    #"Changed Type" = Table.TransformColumnTypes(Custom2,{{"a", type date}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"a"}),
    #"Grouped Rows" = Table.Group(#"Removed Errors", {"Индекс"}, {{"Количество", each List.Max([a]), type date}, {"a", each _, type table}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"a", "b"}, {"a.1", "b"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded {0}", "Пользовательская", each if [Количество] = [a.1] then [b] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [Пользовательская] <> null and [Пользовательская] <> ""),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Индекс"},#"Filtered Rows",{"Индекс"},"Filtered Rows",JoinKind.LeftOuter),
    #"Expanded {0}2" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Количество", "a.1", "b"})[[ТЕКСТ],[b],[Количество]]
in
    #"Expanded {0}2"
Изменено: artyrH - 16.06.2019 16:34:11
 
 
 
#7
16.06.2019 17:03:30
artyrH, спасибо!
 
 
 
#8
16.06.2019 17:04:06
Вот, и мышкоклацательное решение появилось. :)  artyrH, спасибо.
 
 
 
#9
16.06.2019 17:05:10
мне оно пока ближе :)
 
 
 
#10
16.06.2019 17:22:06
Цитата
olegMir написал:
оно пока ближе
тогда добавлю еще пару шагов)
а то при одинаковых датах отбиралось не последнее сообщение
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[ТЕКСТ]],
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"ТЕКСТ", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ТЕКСТ"),
    #"Reversed Rows" = Table.ReverseRows(#"Split Column by Delimiter"),
    Custom1 = Table.AddColumn(#"Reversed Rows", "a", each Text.Middle([ТЕКСТ], Text.PositionOfAny([ТЕКСТ],{"/"})-4,10)),
    Custom2 = Table.AddColumn(Custom1, "b", each Text.Middle([ТЕКСТ],0, Text.PositionOfAny([ТЕКСТ],{"/"})-4)),
    #"Changed Type" = Table.TransformColumnTypes(Custom2,{{"a", type date}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"a"}),
    Custom3 = Table.AddColumn(#"Removed Errors", "b1", each Text.Remove([b],{"0".."9"})),
    #"Grouped Rows" = Table.Group(Custom3, {"Индекс"}, {{"Количество", each List.Max([a]), type date}, {"a", each _, type table}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"a", "b1"}, {"a.1", "b"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded {0}", "Пользовательская", each if [Количество] = [a.1] then [b] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [Пользовательская] <> null and [Пользовательская] <> ""),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Индекс"}),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Индекс"},#"Removed Duplicates",{"Индекс"},"Filtered Rows",JoinKind.LeftOuter),
    #"Expanded {0}2" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Количество", "a.1", "b"})[[ТЕКСТ],[b],[Количество]]
in
    #"Expanded {0}2"
 
 
 
#11
16.06.2019 17:34:41
Еще раз,  Андрей VG и artyrH спасибо!! Хотел artyrH об этом написать, но поскромничал. Вероятность ошибок теперь уменьшится :)  
 
 
 
#12
16.06.2019 19:41:51
А я решил наклацать целиком.
Получилось всё на мышке кроме одного шага (пришлось всё же формулу одного столбца сделать)
Мышки гоу-гоу!
F1 творит чудеса
 
 
 
#13
16.06.2019 20:12:23
Максим, благодарю!!!
 
 
 
#14
16.06.2019 20:26:57
Максим, только "Куликов" из 3-й строки А4 обиделся - спрятался, ни его, ни даты  - наверно за Баранова переживает   :cry:  
 
 
 
#15
16.06.2019 20:42:38
ну и не мышковый вариант (функция):
код
F1 творит чудеса
 
 
 
#16
16.06.2019 20:47:34
olegMir, да, верно... что-то не дотумкал. Сейчас поклацаю ещё.

PS Но зато не мышковый код работает с разными именами, в том числе содержащими цифры, например, "pavel.baranov99". Для этого нужно расскоментировать вложенную функицю (строки 18-26) и заменить 29 строку на 30-ю
F1 творит чудеса
 
 
 
#17
16.06.2019 20:57:27
olegMir, поклацал мышкой еще.
Берет последний комментарий, если даты совпадают - берет последний по счёту в строке, выводит фамилию (текст до первого пробела до даты)
Мышки не сдаются
F1 творит чудеса
 
 
 
#18
16.06.2019 21:23:10
Максим, "мышка" творит чудеса, а в функции на 10 шаге  :(
Спасибо за помощь вдвойне  :)  
 
 
 
#19
16.06.2019 22:08:57
Цитата
Максим Зеленский написал:
Мышки не сдаются
и тачпады тоже :)  
 
 
 
#20
16.06.2019 23:18:01
Много шагов не мало  
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[ТЕКСТ]],
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"ТЕКСТ", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ТЕКСТ"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "ТЕКСТ", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"ТЕКСТ.1", "ТЕКСТ.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "ТЕКСТ.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"ТЕКСТ.1.1", "ТЕКСТ.1.2"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "ТЕКСТ.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"ТЕКСТ.2.1", "ТЕКСТ.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter3",{"ТЕКСТ.1.2", "ТЕКСТ.2.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Дата"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Дата", type datetime}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Дата", null}}),
    #"Filled Down" = Table.FillDown(#"Replaced Errors",{"Дата"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Юзер", each if [ТЕКСТ.2.2]=null then null else [ТЕКСТ.1.1]),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"Юзер"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down1", "Текст", each if [ТЕКСТ.2.2]=null then [ТЕКСТ.1.1] else [ТЕКСТ.2.2])[[Юзер],[Дата],[Текст],[Индекс]],
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Added Custom1", "Юзер", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Юзер"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter4", {"Юзер", "Дата", "Индекс"}, {{"Текст", each Text.Combine(_[Текст],"#(lf)")}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Индекс"}, {{"Дата", each List.Max([Дата]), type datetime}, {" ", each _, type table}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows1", " ", {"Юзер", "Дата", "Текст"}, {"Юзер", "Дата1", "Текст"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each ([Дата] = [Дата1])),
    #"Grouped Rows2" = Table.Group(#"Filtered Rows", {"Индекс"}, {{" ", Table.Last, type record}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "ТЕКСТ", each Source[ТЕКСТ]{[Индекс]}),
    #"Expanded {0}1" = Table.ExpandRecordColumn(#"Added Custom2", " ", {"Дата", "Юзер", "Текст"})[[ТЕКСТ],[Дата], [Юзер], [Текст]]
in
    #"Expanded {0}1"
 
 
 
#21
17.06.2019 10:51:12
Цитата
olegMir написал:
а в функции на 10 шаге  
ммм... там два запроса - функция:
функция

и вызов
вызов функции в другом запросе:
F1 творит чудеса
 
 
 
#22
17.06.2019 14:25:08
Протестировал на скорость решения на 200 тысячах строк в каждой из которых от двух до 10 записей. Генератор под спойлером.
Первый мышиный вариант Максима 22 секунды, но 43 тысячи записей вместо 200 тысяч.
Первый не мышиный вариант Максима 30 секунд. Всё как положено 200 тысяч в выводе.
Мышиный вариант тёзки – в общем после 10 минут ожидания – прибил.
Второй вариант artyrH – 57 секунд.
Мой вариант 23 секунды (но берёт только первое максимальное по дате значение).
Но! Если добавить / между «не/принял» и так далее… То ошибки выделения даты (кроме меня).
Скрытый текст
 
 
 
#23
18.06.2019 00:33:33
Любопытно... Но первый мой вариант и должен был выдавать неверный результат. А вот второй, наверное, подольше будет, но не думаю, что критично
F1 творит чудеса
 
 
 
Страницы: 1
Наверх