Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Разделить и разнести данные таблицы по общему разделителю в Power Query
 
#1
09.07.2019 14:13:46
Добрый день, уважаемые форумчане.
В диапазоне А2:Е8 содержатся исходные данные.
Как можно разделить данные на разные строки по разделителю "*", "/", в нескольких столбцах, чтобы не создавалось дубликатов при разделении данных.
Можно ли как-то разнести данные как в диапазоне М2:М8
файл примера во вложении.

Заранее спасибо.
 
 
 
#2
09.07.2019 16:08:28
Доброе время суток.
Вариант.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    maker = Table.AddColumn(Source, "temp", (rec) =>
    let
        base = {Text.BeforeDelimiter(rec[Модель], "*")},
        parts = Text.Split(Text.AfterDelimiter(rec[Модель], "*"), "/"),
        others = List.Transform(List.Range(Record.FieldValues(rec), 1), each if _ = null then {null} else Text.Split(Text.From(_), "/")),
        toTable = Table.FromRows(List.Zip({base, parts} & others)),
        fillModel = Table.FillDown(toTable, Table.ColumnNames(toTable)),
        addModelSize = Table.AddColumn(fillModel, "temp", each [Column1] & "*" & [Column2]),
        oldColNames = {"temp"} & List.Range(Table.ColumnNames(fillModel), 2)
    in
        Table.RenameColumns(Table.SelectColumns(addModelSize, oldColNames), List.Zip({oldColNames, Record.FieldNames(rec)}))
    )
in
    Table.Combine(maker[temp])
 
 
 
#3
09.07.2019 16:19:51
Как-то так:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    SplitColumn = Table.SplitColumn(Source, "Модель", Splitter.SplitTextByEachDelimiter({"*"}, QuoteStyle.Csv, false), {"Модель.1", "Модель.2"}),
    AddedTabs = Table.AddColumn(SplitColumn, "Таб", each // из каждой строки формируем таблицу
    let 
        Q = List.Count(Text.Split([Модель.2], "/")),  // Считаем количество строк в будущей таблице
        L = List.Transform(  // Преобразуем строку в список и преобразуем каждый элемент этого списка в новые столбцы
            Record.ToList(_), 
            each if Text.From(_) <> null  //Проверяем элемент списка на равенство null
                then if Text.Contains(Text.From(_), "/" )  // Проверяем элемент списка содержит ли он разделитель
                    then Text.Split( Text.From(_), "/" ) // Если разделитель есть, то делим текст на список значений
                    else List.Repeat( {_}, Q ) // Если разделителя нет, то формируем список из повторяющихся элементов
                else List.Repeat( {_}, Q ) )  // Если элемент = null, то формируем список из повторяющихся null
    in
        Table.FromColumns( L, Table.ColumnNames(SplitColumn) ), type table ), // Формируем таблицу из получившегося списка списков
    Combine = Table.Combine( AddedTabs[Таб] ),  // объединяем таблицы
    MergedColumns = Table.CombineColumns(Combine,{"Модель.1", "Модель.2"},Combiner.CombineTextByDelimiter("*", QuoteStyle.None),"Модель")
in
    MergedColumns
Изменено: PooHkrd - 09.07.2019 16:54:52 (подправил комменты)
Вот горшок пустой, он предмет простой...
 
 
 
#4
09.07.2019 16:29:27
Андрей VG, PooHkrd, спасибо оба варианта работают.
Осталось разобраться только как они работают :)  
 
 
 
#5
09.07.2019 16:42:03
Цитата
ymal_qeb написал:
Осталось разобраться
Добавил комменты в код
Вот горшок пустой, он предмет простой...
 
 
 
#6
09.07.2019 16:51:28
Теперь все понятно, еще раз спасибо.
 
 
 
#7
09.07.2019 16:53:52
Уже постфактум заметил, что первый столбец тоже надо трансформировать, получилось не оптимально:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    split = Table.SplitColumn(Source, "Модель", Splitter.SplitTextByEachDelimiter({"*"}), {"Модель", "name"}),
    cols = List.Skip(Table.ColumnNames(split)),
    transform = Table.TransformColumns(split, List.Zip({cols, List.Repeat({each try Text.Split(_, "/") otherwise {_}}, List.Count(cols))})),
    merge = Table.CombineColumns(transform, cols, each Table.FillDown(Table.FromColumns(_, cols), cols), "temp"),
    expand = Table.ExpandTableColumn(merge, "temp", cols),
    merge1 = Table.CombineColumns(expand,{"Модель", "name"},Combiner.CombineTextByDelimiter("*"),"Модель"),
    types = Table.TransformColumnTypes(merge1, List.Transform(List.Skip(cols), each {_, type number}))
in
    types
 
 
 
#8
09.07.2019 17:10:51
Андрей VG, не могли описать как Вы делаете запросы с пользовательскими функциями. или показать этапы создания. что типа, сначала сделал такой запрос:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],

        base = {Text.BeforeDelimiter(Source[Модель]{0}, "*")},
        parts = Text.Split(Text.AfterDelimiter(Source[Модель]{0}, "*"), "/"),
        others = List.Transform(List.Range(Record.FieldValues(Source{0}), 1), each if _ = null then {null} else Text.Split(Text.From(_), "/")),
        toTable = Table.FromRows(List.Zip({base, parts} & others)),
        fillModel = Table.FillDown(toTable, Table.ColumnNames(toTable)),
        addModelSize = Table.AddColumn(fillModel, "temp", each [Column1] & "*" & [Column2]),
        oldColNames = {"temp"} & List.Range(Table.ColumnNames(fillModel), 2),
   
       d= Table.RenameColumns(Table.SelectColumns(addModelSize, oldColNames), List.Zip({oldColNames, Record.FieldNames(Source{0})})),
    a=Table.Combine(maker[temp]),
    Custom1 = d
in
    Custom1
потом этот запрос сделал функцией. потом сделал следующий запрос и т.д.
разбирать Ваши запросы как то получается, а вот применить под похожие задачи уже не получается
все хочу допереть, но пока не прет
 
 
 
#9
09.07.2019 18:29:16
Цитата
Aleksei_Zhigulin написал: получилось не оптимально
я наверное что то не увидел, но, по моему, получилось как надо. или все таки  есть подводные камни?
увидел что два запроса от Вас. первый без трансформации первого столбца, а второй запрос такой, какой и надо по теме
Изменено: artyrH - 09.07.2019 18:59:08
 
 
 
#10
09.07.2019 18:48:30
artyrH, нет-нет, с кодом всё нормально, просто мне больше нравилось, как он выглядел без необходимости преобразования первого столбца :)  Соответственно, прикрутил трансформацию первого столбца уже поверх сделанного (шаги split и merge1). Будь я внимательнее и пойми условия задачи сразу, вероятно, решением могло быть иным.
 
 
 
#11
09.07.2019 18:50:31
Aleksei_Zhigulin, спасибо
 
 
 
#12
09.07.2019 19:04:20
Цитата
artyrH написал:
потом этот запрос сделал функцией.
А зачем?! Я её сразу пишу. Чуть подшаманеный вариант с выделенной именованной функцией. В том виде как написано выше - это называется лямбда-выражение (оно же - анонимная функция)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    // именованная функция
    transformation = (rec as record) as table =>
    let
        valueList = Record.FieldValues(rec),
        nameList = Record.FieldNames(rec),
        main = Text.Split(valueList{0}, "*"),
        base = main{0} & "*",
        firstCol = List.Transform(Text.Split(main{1}, "/"), each base & _),
        others = List.Transform(List.Range(valueList, 1), each if _ = null then {null} else Text.Split(Text.From(_), "/")),
        toTable = Table.FromRows(List.Zip({firstCol} & others), nameList)
    in
        Table.FillDown(toTable, nameList),
    maker = Table.AddColumn(Source, "temp", transformation)
in
    Table.Combine(maker[temp])
 
 
 
#13
09.07.2019 19:24:05
Цитата
Андрей VG написал:
Я её сразу пишу
с Вашим то опытом
мне, как начинающему пользователю PQ, что бы посоветовали для того чтоб начать делать пользовательские функции? может есть информация где нибудь? или наработки? Вы же не всегда писали эти функции на автомате
Андрей VG написал:
вариант с выделенной именованной функцией
так намного легче воспринимается
модераторы, вы хоть дату изменений моих сообщений не меняйте. #9 я изменил в 18:48. верните это время, а то нелепо выглядит после #10
Изменено: artyrH - 09.07.2019 19:29:41
 
 
 
#14
09.07.2019 19:48:46
До кучи
Код
let
    Source  = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Columns = List.Buffer(Table.ColumnNames(Source)),
    Split   = Table.TransformColumns(Source,List.Zip({Columns,List.Repeat({each try Text.SplitAny(Text.From(_),  "*/") otherwise {}},List.Count(Columns))})),
    Concat  = Table.TransformColumns(Split,{{Columns{0},each let s=_{0} in List.Transform(List.Skip(_,1),each s&"*"&_)}}),
    Result  = Table.Combine(List.Transform(Table.ToRows(Concat),each Table.FillDown(Table.FromColumns(_,Columns),Columns)))
in
    Result
 
 
 
#15
09.07.2019 22:21:12
Цитата
Андрей Лящук написал:
До кучи
Тест по скорости. Исходные размножены до 295 тысяч строк - вывод чуть больше миллиона.
PooHkrd, Андрей Лящук и я ровно по 32 секунды. Aleksei_Zhigulin - 25 секунд, плюс вывод начинается сразу и процессор после вывода сразу уходит в покой, в отличии от наших алгоритмов.
Цитата
artyrH написал:
что бы посоветовали для того чтоб начать делать пользовательские функции? может есть информация где нибудь? или наработки?
Мне, пусть сугубо прикладному программисту, но уже с 20-летним стажем - сложно что-то советовать, кроме - учиться программировать. Power Query - язык функционального программирования. Этим близок к процедурному программированию, ну, со своими особенностями.
В качестве учебной базы использовал вот этот блог  Chris Webb's BI Blog (впрочем и сейчас тоже почитываю и учусь даже здесь - бывают отличные примеры), плюс тренировка на форуме.
 
 
 
#16
09.07.2019 22:25:31
Андрей VG, спасибо
 
 
 
#17
09.07.2019 22:42:34
Цитата
artyrH написал:
модераторы, вы хоть дату изменений моих сообщений не меняйте
Никто из модераторов дату и время не меняет.
 
 
 
Страницы: 1
Наверх