Страницы: 1
RSS
[Power Query] Обработка составного текста в ячейке с выводом в две колонны
 
Доброго времени суток, Планетяне!

В 2х колоннах имеются составные данные (разделены через "///"), например:
Код
person 1(person1@aaa.com)[1]///person 2(person2@aaa.com)[0]///person 3(person3@aaa.com)[0]

Каждая ячейка колонны разбивается на две части (новые колонны), и в каждой остаются данные при условии, что в квадратных скобках стоит [0]:
NameEmail
person 2, person 3 person2@aaa.com; person3@aaa.com

Написал функцию относительно произвольной ячейки  - все работает (кроме создания списка), а при вызове функции ко всей колонне результат содержит ошибку:
Код
Expression.Error: We cannot convert the value "person 1(person1@aaa..." to type Table.

Помогите, пожалуйста, с исправлением функции.
Заранее спасибо!
Изменено: Maximilian - 04.06.2019 23:29:14
 
Покажите код запроса как вы вызываете функцию и код самой функции в сообщении. А то я с телефона из файла посмотреть не могу.
Вот горшок пустой, он предмет простой...
 
PooHkrd, функция обработки:
Код
(data as text) =>

let
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(data, {{data, Splitter.SplitTextByDelimiter("///", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), data),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{data, type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type1", "Name", each Text.BeforeDelimiter(data, "("), type text),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Email", each Text.BetweenDelimiters(data, "(", ")"), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Done", each Text.BetweenDelimiters(data, "[", "]"), type text),
    #"Filtered Rows1" = Table.SelectRows(#"Inserted Text Between Delimiters1", each ([Done] = "0")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Email"})
in
    #"Removed Other Columns1"

И сам обработка вводной таблицы с вызовом функции:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="input"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Review", type text}, {"Approve", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Rev", each fnProcess([Review])),
    #"Invoked Custom Function1" = Table.AddColumn(#"Invoked Custom Function", "App", each fnProcess([Approve]))
in
    #"Invoked Custom Function1"
 
У вас в функции параметр data объявлен с типом текст, а в функции Table.TransformColumns вы  его подсовываете вместо таблицы. Вот оно и ругается.
Вот горшок пустой, он предмет простой...
 
PooHkrd, огромное спасибо, большой шаг!

код на данный момент:
Код
(data as text) =>

let
    #"Converted to Table" = Table.FromList(Text.Split(data,"///"), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Converted to Table", "Name", each Text.BeforeDelimiter([Column1], "("), type text),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Email", each Text.BetweenDelimiters([Column1], "(", ")"), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "[", "]"), type text),
    #"Filtered Rows1" = Table.SelectRows(#"Inserted Text Between Delimiters1", each ([Text Between Delimiters] = "0")),
    Name = try Text.Combine(#"Filtered Rows1"[Name],", ") otherwise null,
    Email = try Text.Combine(#"Filtered Rows1"[Email],"; ") otherwise null,
    Table = #table({"Name", "Email"}, {{Name, Email}})
in
    Table

Возникла проблема: если ячейка в основном запросе пуста (null), тогда все падает. Решил добавлением проверки при вызове функции:
Код
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "rev", each if [Review] = null then null else fnProcess2([Review])),

Вопрос: является ли написанная функция оптимальной в плане скорости?
Изменено: Maximilian - 05.06.2019 01:49:18
 
Maximilian, нет, конечно. Вы же вместо того чтобы работать с данными в ячейке, в данном случае с текстом, работаете с таблицей. То есть делаете не как правильно, а как умеете тыкать в кнопки. Как доберусь до работы попробую накидать преобразования как считаю нужным, если другие хорошие люди не опередят.
Вот горшок пустой, он предмет простой...
 
Переделывать ваш подход не буду - времени нет.
Но вот так должно сработать:
Код
let
    FnProcess = (data as text) =>
    let
        #"Converted to Table" = Table.FromList(Text.Split(data,"///"), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Inserted Text Before Delimiter" = Table.AddColumn(#"Converted to Table", "Name", each Text.BeforeDelimiter([Column1], "("), type text),
        #"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Email", each Text.BetweenDelimiters([Column1], "(", ")"), type text),
        #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "[", "]"), type text),
        #"Filtered Rows1" = Table.SelectRows(#"Inserted Text Between Delimiters1", each ([Text Between Delimiters] = "0")),
        Table = #table({"Name", "Email"}, {{Text.Combine(#"Filtered Rows1"[Name],", "), Text.Combine(#"Filtered Rows1"[Email],"; ")}})
    in
        Table,
    Source = Excel.CurrentWorkbook(){[Name="input"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Review", type text}, {"Approve", type text}}),
    AddRev = Table.AddColumn(ChangedType, "Rev", each try fnProcess([Review]) otherwise null ),
    AddApp = Table.AddColumn(AddRev, "App", each try fnProcess([Approve]) otherwise null)
in
    AddApp
Вот горшок пустой, он предмет простой...
 
PooHkrd, но разве это не тоже самое - только команды совмещены, из:
Код
Name = Text.Combine(#"Filtered Rows1"[Name],", "),
Email = Text.Combine(#"Filtered Rows1"[Email],"; "),
Table = #table({"Name", "Email"}, {{Name, Email}})
получилось:
Код
Table = #table({"Name", "Email"}, {{Text.Combine(#"Filtered Rows1"[Name],", "), Text.Combine(#"Filtered Rows1"[Email],"; ")}})

После вашего:
Цитата
PooHkrd написал:
Вы же вместо того чтобы работать с данными в ячейке
думал, что можно сделать что-то вроде:
Код
1. Text.Split(data,"///")
2. разбить на разные списки (имя, почта, 0/1)
3. отфильтровать ненужное (убрать из списков "1")
4. создать списки с разделителем имен и почты 
И все без генерации таблицы.

Или оптимизация скорости как раз в этом (совмещение команд) и заключается?
 
Цитата
Maximilian написал:
1. Text.Split(data,"///")
2. разбить на разные списки (имя, почта, 0/1)
3. отфильтровать ненужное (убрать из списков "1")
4. создать списки с разделителем имен и почты
именно так, но я же специально написал, что ваш подход я не изменял, потому что времени сейчас нет ковыряться.
Я просто убрал ненужные проверки из функции в шаги где вы эту функцию вызываете, и ошибки не появляются. Код работает? Результат тот что хотелось?
Если сделать через списки, то быстродействие не сильно улучшится, просто код будет написан "правильнее". Если учесть что на выходе из одной ячейки вам нужно получить 2 столбца, тот ваш подход не такой уж "корявый". Тут главное чтоб работало. ИМХО.
Изменено: PooHkrd - 05.06.2019 10:34:13
Вот горшок пустой, он предмет простой...
 
PooHkrd, понял, спасибо большое! Ненужные проверки тоже обнаружил и убрал поутру.

Я через таблицы делал, так как там появляются столбцы, а как со списком - не представляю.
Если у вас будет свободное время, в целях моего обучения, сможете более "корректный" вариант написать?
 
Доброе время суток.
Вариант
Код
let
    toRecord = (item) =>
        if item <> null then
        let
            parts = Text.Split(item, "///"),
            neededParts = List.Select(parts, each Text.Contains(_, "[0]")),
            names = List.Transform(neededParts, each Text.BeforeDelimiter(_, "(")),
            emails = List.Transform(neededParts, each Text.BetweenDelimiters(_, "(", ")"))
        in
            [Name = Text.Combine(names, ", "), Email = Text.Combine(emails, "; ")]
        else null,
    Source = Excel.CurrentWorkbook(){[Name="input"]}[Content],
    maker = Table.TransformColumns(Source, {
        {"Review", each toRecord(_)},
        {"Approve", each let res = toRecord(_) in if res = null then null else Record.RemoveFields(res, "Name")}
    }),
    #"Expanded {0}" = Table.ExpandRecordColumn(maker, "Review", {"Name", "Email"}, {"Review.Name", "Review.Email"}),
    #"Expanded {0}1" = Table.ExpandRecordColumn(#"Expanded {0}", "Approve", {"Email"}, {"Approve.Email"})

in
    #"Expanded {0}1"
 
Андрей VG, обалдеть, спасибо!

Уже несколько раз замечал такое оформление вызова:
Код
#"Expanded {0}1"
Это ведь просто стиль названия?

И еще вопрос: понял все, кроме:
Код
{"Approve", each let res = toRecord(_) in if res = null then null else Record.RemoveFields(res, "Name")}
Вы заменяете данные в "Approve", но зачем res проверяется на null еще раз (это же есть в самой функции?!)? Делает
Код
Record.RemoveFields(res, "Name")
что-то особенное?
 
Цитата
Maximilian написал:
Уже несколько раз замечал такое оформление вызова
Это при раскрытии столбцов в английской локали PQ формируется такое название шага.
Record.RemoveFields() удаляет поля из записи. Пользуйтесь справочниками, это не сложно.
Если вам поле Name во втором случае требуется, то просто уберите конструкцию с if
Изменено: PooHkrd - 05.06.2019 12:23:18
Вот горшок пустой, он предмет простой...
 
PooHkrd, спасибо за пояснения.

Справочник под рукой, может не так спросил - не была понятна необходимость появления функции.

Всем спасибо!
 
Цитата
Maximilian написал:
не была понятна необходимость появления функции
Андрей так понял задачу, что вам не нужно было поле Name во втором столбце, вот и удалил.
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх