Страницы: 1
RSS
Разделение текста на составляющие в Power Query
 
Всем привет!

Как разделить текст ФИО Должность в Power Query на две колонки ФИО и Должность?
Файл-пример прикладываю

Спасибо!
 
можно решить эту задачу простым способом
 
или вот так
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"ФИО Должность", type text}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "ФИО", each Text.BeforeDelimiter([ФИО Должность], " ", 2)),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Добавлен пользовательский объект", "Должность", each Text.BetweenDelimiters([ФИО Должность], " ", " ", {1, RelativePosition.FromEnd}, {1, RelativePosition.FromStart})),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект1",{"ФИО Должность"})
in
    #"Удаленные столбцы"
Изменено: azma - 18.07.2019 06:14:33
 
Доброе время суток
Цитата
azma написал:
или вот так
Всё это хорошо. Но что если ФИО будет таким Алиев Ильхам Гейдар оглы или Франсиско Мартинес де ла Роса?
 
Андрей VG, эт точно, у меня было дело на складе грузчик работал Евгений Ромеович Дель Грандэ, и это я не в Италии работал, а в Тамбове! С ним такая схема не прокатит.
CainV, чтобы вашу задачу решить правильно очень желательно иметь справочник всех возможных должностей на предприятии, и тогда все что не должность это ФИО.
Изменено: PooHkrd - 18.07.2019 09:25:04
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
чтобы вашу задачу решить правильно очень желательно иметь справочник всех возможных должностей на предприятии, и тогда все что не должность это ФИО
PooHkrd, скажите пожалуйста, где я могу про это читать?
 
azma, про что именно?
Как найти вхождение названия должности в текст? Это Text.Contains
Как удалить часть текста? Это Text.Replace
Можете в поиск по форуму вбить обе эти функции тут вариантов их применения немеряно.
Например, если добавить такой справочник в пример ТС, то решение могло бы выглядеть так:
Код
let
    Должности = Excel.CurrentWorkbook(){[Name="СпрДолжностей"]}[Content],
    Исходник = Excel.CurrentWorkbook(){[Name="Исходник"]}[Content],
    Декарт = Table.Join( Исходник, {}, Должности, {} ),
    ФильтруемПоСовпадению = Table.SelectRows(Декарт, each Text.Contains([ФИО Должность], [Должность])),
    ДобавляемФИО = Table.AddColumn(ФильтруемПоСовпадению, "ФИО", each Text.Trim( Text.Replace( [ФИО Должность], [Должность], "" ) ))
in
    ДобавляемФИО
Изменено: PooHkrd - 18.07.2019 11:20:54
Вот горшок пустой, он предмет простой...
 
спасибо!
 
PooHkrd, со справочником работает идеально, спасибо!!!!
 
Цитата
CainV написал:
работает идеально,
Ну, я бы так не сказал, хотя на базе в 300 000 строк в два раза быстрее. Пример прилагаю. Мой тоже может мазать, например, Художников Семён Иванович Агент
 
Андрей VG, большое спасибо за замечание, каюсь, упустил.
Исправляю свой полукнопочный вариант, и этот вариант Художникова корректно распознает  ;) :
Код
let
    Должности = Excel.CurrentWorkbook(){[Name="Должности"]}[Content],
    Исходник = Excel.CurrentWorkbook(){[Name="Персоны"]}[Content],
    Декарт = Table.Join( Исходник, {}, Должности, {} ),
    ФильтруемПоСовпадению = Table.SelectRows(Декарт, each Text.Contains([Персона], [Должность])),
    Проверочка = Table.AddColumn(ФильтруемПоСовпадению, "Проверка", each Text.AfterDelimiter([Персона], [Должность]), type text),
    ФильтруемЛишнее = Table.SelectRows(Проверочка, each ([Проверка] = "")),
    ДобавляемФИО = Table.AddColumn(ФильтруемЛишнее, "ФИО", each Text.Trim( Text.Replace( [Персона], [Должность], "" ) )),
    УдаляемНеНужныеСтолбцы = Table.RemoveColumns(ДобавляемФИО,{"Персона", "Проверка"})
in
    УдаляемНеНужныеСтолбцы

За ваш вариант отдельное спасибо.
Изменено: PooHkrd - 19.07.2019 10:34:34
Вот горшок пустой, он предмет простой...
 
Вот так отлично. Спасибо!
Updated
Хотя, если подумать. Пусть есть должности главный инженер и инженер (и обе с маленькой буквы :) ). Тогда оба в вашем приёме будут иметь true по концу строки и будет неопределённость. Так что попаразитировал на вашем варианте.
Код
let
    persons = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Персоны"]}[Content], "id"),
    positions = Excel.CurrentWorkbook(){[Name="Должности"]}[Content],
    crossJoin = Table.Join(persons, {}, positions, {}),
    addSign = Table.AddColumn(crossJoin, "sign", each if Text.EndsWith([Персона], [Должность]) then Text.Length([Должность]) else 0),
    findMax = Table.Group(addSign, {"id"}, {"temp", each Table.Max(_, "sign")})[[temp]],
    toTable = Table.ExpandRecordColumn(findMax, "temp", {"Персона", "Должность", "sign"}),
    addName = Table.AddColumn(toTable, "ФИО", each Text.Trim(Text.Range([Персона], 0, Text.Length([Персона]) - [sign]))),
    #"Removed Columns" = Table.RemoveColumns(addName,{"sign"})
in
    #"Removed Columns"
Изменено: Андрей VG - 19.07.2019 13:08:41
 
Ну, если уж так заморачиваться, то вот так (не глядя на ваше решение):
Код
let
    Должности = Table.AddColumn(Excel.CurrentWorkbook(){[Name="Должности"]}[Content], "Длина", each Text.Length([Должность])),
    Исходник = Excel.CurrentWorkbook(){[Name="Персоны"]}[Content],
    Сравнение = Table.AddColumn(Исходник, "Должности", each let t = [Персона] in Table.SelectRows( Должности, each Text.Contains( t, [Должность], Comparer.OrdinalIgnoreCase ) ), type table),
    Зачистка = Table.AddColumn(Сравнение, "Должность", each Table.Max( [Должности], "Длина" )[Должность] ),
    ФИО = Table.AddColumn(Зачистка, "ФИО", each Text.Start( [Персона], Text.Length( Text.BeforeDelimiter( Text.Lower([Персона]), Text.Lower([Должность]) ) ) - 1 ), type text),
    ОставляемНужное = Table.SelectColumns(ФИО,{"ФИО", "Должность"})
in
    ОставляемНужное

Заодно запрос будет не чувствителен к регистру при поиске должностей в строках.
Изменено: PooHkrd - 19.07.2019 16:08:45
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Заодно запрос будет не чувствителен
Но стал очень медленным. На 271000 записей персон почти 208 секунд. Мой предложенный вариант через декартово произведение, впрочем, не лучше.
Более быстрые варианты на List.Generate (требует упорядоченности по невозрастанию длины фразы должности) 21 секунда
Скрытый текст

С List.Accumulate не требует упорядоченности, но 39 секунд (почти по классике - в два раза больше просмотров. Что в общем-то странно, как-будто остальной код ничего не весит. Впрочем, List.Accumulate всегда был не быстрым)
Скрытый текст
 
Цитата
Андрей VG написал:
Но стал очень медленным.
Андрей, а можно точнее? Стал 208 сек, а самый первый мой вариант за какое время отрабатывал? Есть одна идейка как ускорить, хочу понять за какую планку бороться?
З.Ы. и еще, сейчас проверил, в последнем варианте у меня кейс с Художниковым поломался :) Надо наколдовать что-то правильное.
В общем, наколдовалось вот такое:
Код
let
    Должности = Table.AddColumn(Table.AddColumn(Excel.CurrentWorkbook(){[Name="Должности"]}[Content], "Длина", each Text.Length([Должность])), "НижРегДолж", each Text.Lower([Должность]) ),
    Исходник = Table.AddColumn(Excel.CurrentWorkbook(){[Name="Персоны"]}[Content], "НижРегПерс", each Text.Lower([Персона])),
    Декарт = Table.Join( Исходник, {}, Должности, {} ),
    ФильтруемПоСовпадению = Table.SelectRows(Декарт, each Text.Contains([НижРегПерс], [НижРегДолж])),
    Проверочка = Table.AddColumn(ФильтруемПоСовпадению, "Проверка", each Text.AfterDelimiter([Персона], [Должность]), type text),
    ФильтруемЛишнее = Table.SelectRows(Проверочка, each ([Проверка] = "")),
    Соритруем = try Table.Sort(ФильтруемЛишнее,{{"Персона", Order.Ascending}, {"Длина", Order.Descending}}) otherwise null,
    УдаляемДубликаты = Table.Distinct(Соритруем, {"Персона"}),
    ДобавляемФИО = Table.AddColumn(УдаляемДубликаты, "ФИО", each Text.Trim( Text.Replace( [Персона], [Должность], "" ) )),
    ОставляемНужное = Table.SelectColumns(ДобавляемФИО,{"ФИО", "Должность"})
in
    ОставляемНужное

На нубуке I3 (4 потока) 2,4ГГц + 8 Гб отработало за 80 сек, на серваке ксеон 2.6ГГц 8 потоков + 32 Гб - 54 секунды.
Ваш крайний вариант с List.Generate запустить не смог, внутри функции ругается на нехватку элементов в перечислении. Разбираться не стал, пока не очень я с этими генераторами дружу.
В файле мой запрос зовется Отчет2, ваш крайний Отчет4. Если обновить запрос Персоны, то сгенерится таблица на 300 тыс строк и можно тестировать.
Для себя делаю вывод: копаться в таблицах внутри ячеек это зло - работать с кроссджойном без последующей группировки выходит шустрее, хотя казалось бы строк в обработке такое же количество.
Изменено: PooHkrd - 22.07.2019 12:52:12
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Ваш крайний вариант с List.Generate запустить не смог
Честно говоря, не стал разбираться. Просто собрал свои два последних варианта и ваш с CrossJoin в один файл (19 мегабайт). Ваш последний отработал за 31 секунду. Моя версия с List.Accumulate неожиданно поломалась - 260 секунд :(  Что к чему, понять не могу. Только добавил начальную нумерацию. Но List.Generate работает за 19 секунд, что несколько опровергает изречение
Цитата
PooHkrd написал:
копаться в таблицах внутри ячеек это зло
:)
Updated.
Пока получился вот такой самый быстрый на поиске максимально длинной должности по принципу, что персона кончается на эту должность.
Код
let
    persons = Table.AddColumn(Excel.CurrentWorkbook(){[Name="Персоны"]}[Content], "lower", each Text.Lower([Персона])),
    positions = List.Buffer(Excel.CurrentWorkbook(){[Name="Должности"]}[Content][Должность]),
    maker = Table.AddColumn(persons, "Должность", (rec) =>
    let
        person = rec[lower],
        calc = List.Transform(positions, each if Text.EndsWith(person, _) then [position = _, length = Text.Length(_)] else null)
    in
        List.Max(calc, null, (a, b) => if a = null or b = null then -1 else a[length] - b[length])[position]
    ),
    addName = Table.AddColumn(maker, "ФИО", each Text.Range([Персона], 0, Text.Length([Персона]) - Text.Length([Должность]) - 1)),
    #"Removed Columns" = Table.RemoveColumns(addName,{"Персона", "lower"})
in
    #"Removed Columns"
На примере по ссылке отработало за 14 секунд.
Изменено: Андрей VG - 22.07.2019 22:15:42
 
Цитата
Андрей VG написал:
Updated.Пока получился вот такой самый быстрый на поиске максимально длинной должности по принципу, что персона кончается на эту должность.
у меня выходит ошибка, почему так не знаю.
прикрепите свои файл .
 
Цитата
azma написал:
у меня выходит ошибка
Покажите, что выводит PQ, если в расширенном редакторе набрать только List.Max
Тестировал: дома Excel 365 64bit PQ 2.70, на работе Excel 2016 32bit PQ 2.61. Работает.
 
Цитата
Андрей VG написал:
Но List.Generate работает за 19 секунд, что несколько опровергает изречение
Андрей, тут имелось в виду именно ковыряние в полукнопочном режиме: фильтрация, соритровка и все такое, List.Generate это вообще отдельная тема в PQ, которая за один проход решает очень серьезные вопросы. Я вроде очевидные циклы научился делать с его помощью, но работать с записями и списками внутри, пока практики все же не хватает. Надо руку набивать.
Цитата
Андрей VG написал:
Ваш последний отработал за 31 секунду.
Блин, ну как так-то? Это на каком железе запускали? Я понимаю, что моему нубуку 6 лет уже. Но чтоб прямо на столько разница была.
Изменено: PooHkrd - 23.07.2019 09:47:42
Вот горшок пустой, он предмет простой...
 
Цитата
Андрей VG написал:
Покажите, что выводит PQ, если в расширенном редакторе набрать только List.MaxТестировал: дома Excel 365 64bit PQ 2.70, на работе Excel 2016 32bit PQ 2.61. Работает.
Да, в вашем файле все работает.
Видимо что-то я не то сделал
Страницы: 1
Наверх