Страницы: 1 2 След.
RSS
Обработка сложной иерархии в плоскую таблицу на базе функции Зеленского
 
В продолжении извечной темы обработки данных из 1С, на базе функции Зеленского

У меня есть много выгрузок из 1С, в принципе воспользовавшися функцией Максима я их уже обработал, но получилось очень сложная конструкция и есть желание ее упростить.
Код
let
    Sourse = Excel.CurrentWorkbook(){[Name="Account_20"]}[Content],

    ReplaceValue = Table.ReplaceValue(Sourse, each [Column1], each if [Статьи затрат] = "Обороты" then Number.From(List.Max(Company[Column1]))+1 else [Column1],Replacer.ReplaceValue,{"Column1"}),
    SelectColumns = Table.SelectColumns(ReplaceValue,{"Column1", "Статьи затрат", "Код", "BalansO_D", "BalansO_K", "Turnover_D", "Turnover_K", "BalansE_D", "BalansE_K", "Comp", "Period"}),
    Trim = Table.TransformColumns(SelectColumns,{{"Статьи затрат", each Text.Trim(_, {" "}), type text}}),
    Headers = List.Buffer( List.Transform({0..List.Max(Trim[Column1])}, Text.From ) ),
    ReplacedValue = Table.ReplaceValue(Trim,null,0,Replacer.ReplaceValue,{"Column1"}),
    AddedRecs = Table.AddColumn(ReplacedValue, "recs", each Record.FromList( List.ReplaceRange( List.Repeat({null},List.Count(Headers)), [Column1], 1, {[Статьи затрат]} ), Headers ), type record ),
    ExpandedRecs = Table.ExpandRecordColumn(AddedRecs, "recs", Headers ),
    AddIndex = Table.AddIndexColumn(ExpandedRecs, "Индекс", 1, 1),
    FillDown = Table.FillDown(AddIndex,{"0"}),
    Group = Table.Group(FillDown, {"0"}, {{"Tab", each _, type table}}),
    ReplaceN = Table.ReplaceValue(Group, each [Tab], each Table.FillDown([Tab], {"1"}),Replacer.ReplaceValue,{"Tab"}),
    RemoveColumns = Table.RemoveColumns(ReplaceN,{"0"}),
    Expand = Table.ExpandTableColumn(RemoveColumns, "Tab", {"Column1", "Статьи затрат", "Код", "BalansO_D", "BalansO_K", "Turnover_D", "Turnover_K", "BalansE_D", "BalansE_K", "Comp", "Period", "0", "1", "2", "3", "4", "5", "6", "7", "8", "Индекс"}, {"Column1", "Статьи затрат", "Код", "BalansO_D", "BalansO_K", "Turnover_D", "Turnover_K", "BalansE_D", "BalansE_K", "Comp", "Period", "0", "1", "2", "3", "4", "5", "6", "7", "8", "Индекс"})
in
    Expand

Обрабатываю я данные через группировку по каждому уровню с последующим FillDown внутри вложенных таблиц по последующему уровню (см. последние четыре строки).

Как данные строки запустить через цикл по каждому уровню? Помогите пожалуйста.
Изменено: mitox - 06.07.2020 10:56:53
 
mitox,наверное, как то так
Код
let
    Sourse = Excel.CurrentWorkbook(){[Name="Account_20"]}[Content],
    Headers = List.Transform(List.Sort(List.Distinct(Sourse[Column1]),Order.Ascending), Text.From),
    Index = Table.AddIndexColumn(Sourse,"id"),
    Duplicats = Table.DuplicateColumn(Table.DuplicateColumn(Index, "Column1", "a"), "Статьи затрат", "b"),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Duplicats, {{"a", type text}}, "ru-RU"), Headers, "a", "b"),
    Custom = Table.RemoveColumns(Table.FillDown(Table.Sort(Pivot,{"id",0}),Headers),{"id"})
in
    Custom
Изменено: Михаил Л - 06.07.2020 12:42:32
 
Михаил Л, по-моему, вот так быть не должно

 
F1 творит чудеса
 
Михаил Л, Спасибо большое. Изучаю. Точнее - проверяю сведенные итоги.
 
mitox,шаг ReplaceValue ссылается на запрос Company, которого нет в файле. Что там?
F1 творит чудеса
 
Максим Зеленский, Там литера обозначающая компанию, которая не имеет ни какого отношения к группировке
 
Михаил Л, Такой алгоритм дает ошибку, так как при FillDown возникает ошибка на таблицах где строки с разным уровнем группировки. Именно поэтому я пошел через Группировку.

upd. Еще раз на всякий случай проверю, чтоб не быть голословным
Изменено: mitox - 06.07.2020 13:13:27
 
mitox, как должен выглядеть результат с учетом того, что на нижнем уровне группировок у вас только обороты, а сальдо - на верхних?
F1 творит чудеса
 
Максим Зеленский, Минутку. Поправлю исходный файл, чтобы подсветить ошибку на которую я все время наталкивался. А также отвечу на Ваш вопрос.
 
Вроде так собираются обороты нормально в вашем случае, но если балансы не на нижнем уровне - х.з., что с  ними делать. Чтобы не потерялся столбец Код, прицепил его к "Статьи затрат".
Код
// Account (2)
let
    Source = Excel.CurrentWorkbook(){[Name="Account_20"]}[Content],
    LevelsN = List.Buffer(List.Sort(List.Distinct(Table.TransformColumnTypes(Source[[Column1]], {{"Column1", type number}}, "en-US")[Column1]))),
    LevelsT = List.Transform(LevelsN, Text.From),
    LastLevel = Text.From(List.Max(LevelsN)),
    NoLastLevel = List.RemoveItems(LevelsT,{LastLevel}),
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Merged Columns" = Table.CombineColumns(#"Added Index",{"Статьи затрат", "Код"},(lst)=>Text.Combine(lst,"=="),"Статьи затрат"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Merged Columns", {{"Column1", type text}}, "en-US"), LevelsT, "Column1", "Статьи затрат"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Индекс", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",NoLastLevel),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each (Record.Field(_,LastLevel) <> null)),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Индекс", Order.Ascending}})
in
    #"Sorted Rows1"
F1 творит чудеса
 
Михаил Л, Извиняюсь за некорректный пример (вы не могли проверить корректность) - пронумеровал все статьи и подсветил ошибку в файле если использовать предложенный вариант.
Максим Зеленский, Добавил итоговый результат.
 
Максим Зеленский, Такой алгоритм выдает ошибку, так как FillDown не имеет ограничения снизу если последующая статья имеет меньшее количество группировок. Тогда самая крайняя группировка заползает на последующую статью создает в ней не существующий уровень. Файл во вложении - подсветил.

UPD 2  Добавил скрины - по ним видно что появились два доп уровня там где их не должно быть

UPD1. Именно поэтому использовал Группировку и FillDown по вложенной таблице - которая и ограничивает заполнение своим размером.
Изменено: mitox - 06.07.2020 14:10:25
 
Цитата
mitox написал:
подсветил ошибку в файле если использовать предложенный вариант.
Так правильно же. В столбец "4" значение - "бббббб3", в столбец "8" - "Обороты"
 
Цитата
Михаил Л написал:
Так правильно же. В столбец "4" значение - "бббббб3", в столбец "8" - "Обороты"
Столбцы 5 и 6 должны быть пустыми
 
Мой вариант который длинный и который хочется упросить:
Код
let
    Sourse = Excel.CurrentWorkbook(){[Name="Account_20"]}[Content],
    ReplaceValue = Table.ReplaceValue(Sourse, each [Column1], each if [Статьи затрат] = "Обороты" then Number.From(List.Max(Company[Column1]))+1 else [Column1],Replacer.ReplaceValue,{"Column1"}),
    SelectColumns = Table.SelectColumns(ReplaceValue,{"Column1", "Статьи затрат", "Код", "BalansO_D", "BalansO_K", "Turnover_D", "Turnover_K", "BalansE_D", "BalansE_K", "Comp", "Period"}),
    Trim = Table.TransformColumns(SelectColumns,{{"Статьи затрат", each Text.Trim(_, {" "}), type text}}),
    Headers = List.Buffer( List.Transform({0..List.Max(Trim[Column1])}, Text.From ) ),
    ReplacedValue = Table.ReplaceValue(Trim,null,0,Replacer.ReplaceValue,{"Column1"}),
    AddedRecs = Table.AddColumn(ReplacedValue, "recs", each Record.FromList( List.ReplaceRange( List.Repeat({null},List.Count(Headers)), [Column1], 1, {[Статьи затрат]} ), Headers ), type record ),
    ExpandedRecs = Table.ExpandRecordColumn(AddedRecs, "recs", Headers ),
    AddIndex = Table.AddIndexColumn(ExpandedRecs, "Индекс", 1, 1),
    FillDown = Table.FillDown(AddIndex,{"0"}),
    ColumnNamesTable = Table.SelectRows(Table.FromList(Table.ColumnNames(FillDown), Splitter.SplitByNothing(), null, null, ExtraValues.Error), each ([Column1] = "BalansE_D" or [Column1] = "BalansE_K" or [Column1] = "BalansO_D" or [Column1] = "BalansO_K" or [Column1] = "Comp" or [Column1] = "Period" or [Column1] = "Turnover_D" or [Column1] = "Turnover_K")),
    ColumnNames = Table.ColumnNames(FillDown),
    Group = Table.Group(FillDown, {"0"}, {{"Tab", each _, type table}}),
    ReplaceN = Table.ReplaceValue(Group, each [Tab], each Table.FillDown([Tab], {"1"}),Replacer.ReplaceValue,{"Tab"}),
    RemoveColumns = Table.RemoveColumns(Group,{"0"}),
    Expand = Table.ExpandTableColumn(RemoveColumns, "Tab", ColumnNames, ColumnNames),
    Group1 = Table.Group(Expand, {"1"}, {{"Tab", each _, type table}}),
    ReplaceN1 = Table.ReplaceValue(Group1, each [Tab], each Table.FillDown([Tab], {"2"}),Replacer.ReplaceValue,{"Tab"}),
    RemoveColumns1 = Table.RemoveColumns(ReplaceN1,{"1"}),
    Expand1 = Table.ExpandTableColumn(RemoveColumns1, "Tab",   ColumnNames, ColumnNames),
    Group2 = Table.Group(Expand1, {"2"}, {{"Tab", each _, type table}}),
    ReplaceN2 = Table.ReplaceValue(Group2, each [Tab], each Table.FillDown([Tab], {"3"}),Replacer.ReplaceValue,{"Tab"}),
    RemoveColumns2 = Table.RemoveColumns(ReplaceN2,{"2"}),
    Expand2 = Table.ExpandTableColumn(RemoveColumns2, "Tab",   ColumnNames, ColumnNames),
    Group3 = Table.Group(Expand2, {"3"}, {{"Tab", each _, type table}}),
    ReplaceN3 = Table.ReplaceValue(Group3, each [Tab], each Table.FillDown([Tab], {"4"}),Replacer.ReplaceValue,{"Tab"}),
    RemoveColumns3 = Table.RemoveColumns(ReplaceN3,{"3"}),
    Expand3 = Table.ExpandTableColumn(RemoveColumns3, "Tab",   ColumnNames, ColumnNames),
    Group4 = Table.Group(Expand3, {"4"}, {{"Tab", each _, type table}}),
    ReplaceN4 = Table.ReplaceValue(Group4, each [Tab], each Table.FillDown([Tab], {"5"}),Replacer.ReplaceValue,{"Tab"}),
    RemoveColumns4 = Table.RemoveColumns(ReplaceN4,{"4"}),
    Expand4 = Table.ExpandTableColumn(RemoveColumns4, "Tab",   ColumnNames, ColumnNames),
    Group5 = Table.Group(Expand4, {"5"}, {{"Tab", each _, type table}}),
    ReplaceN5 = Table.ReplaceValue(Group5, each [Tab], each Table.FillDown([Tab], {"6"}),Replacer.ReplaceValue,{"Tab"}),
    RemoveColumns5 = Table.RemoveColumns(ReplaceN5,{"5"}),
    Expand5 = Table.ExpandTableColumn(RemoveColumns5, "Tab",   ColumnNames, ColumnNames),
    Group6 = Table.Group(Expand5, {"6"}, {{"Tab", each _, type table}}),
    ReplaceN6 = Table.ReplaceValue(Group6, each [Tab], each Table.FillDown([Tab], {"7"}),Replacer.ReplaceValue,{"Tab"}),
    RemoveColumns6 = Table.RemoveColumns(ReplaceN6,{"6"}),
    Expand6 = Table.ExpandTableColumn(RemoveColumns6, "Tab",   ColumnNames, ColumnNames),
    Sort = Table.Sort(Expand6,{{"Индекс", Order.Ascending}}),
    Filtered = Table.SelectRows(Sort, each ([8] = "Обороты")),
    Del = Table.SelectColumns(Filtered, ColumnNamesTable[Column1]&List.Transform({0..List.Max(Trim[Column1])-1}, Text.From )),
    Types = Table.TransformColumnTypes(Del,{{"Period", type date}, {"Comp", type text}, {"0", type text}, {"1", type text}, {"2", type text}, {"4", type text}, {"5", type text}, {"3", type text}, {"6", type text}, {"BalansO_D", type number}, {"BalansO_K", type number}, {"Turnover_D", type number}, {"Turnover_K", type number}, {"BalansE_D", type number}, {"BalansE_K", type number}})
in
    Types
UPD Упростил немного ))
Изменено: mitox - 06.07.2020 16:26:02
 
Цитата
mitox написал:
Столбцы 5 и 6 должны быть пустыми
Да, уловил. Сейчас поправим
F1 творит чудеса
 
Цитата
mitox написал:
= Table.ExpandTableColumn(RemoveColumns, "Tab", ColumnNames, ColumnNames)
Достаточно так написать:
Код
= Table.ExpandTableColumn(RemoveColumns, "Tab", ColumnNames)
Если этим шагом будете переименовывать хотя бы один столбец, то применяете 4 аргумент. Раз не переименовываете, то достаточно трех аргументов.
 
Михаил Л, Спасибо. Буду знать.

UPD Как бы в цикл завернуть последние строки которые повторяются?
Изменено: mitox - 06.07.2020 16:35:41
 
Цитата
mitox написал:
Как бы в цикл завернуть последние строки
В цикл не знаю как, но можно другими вариантами. Хотя можно и циклом(не циклом, а функцией)
 
Цитата
mitox написал:
В продолжении извечной темы обработки данных из 1С
А почему вы из 1С в плоском виде не выгружаете? Она же позволяет это делать.
 
Иванов Вадим, Пока не получается достучаться до 1С-ника. А нужно уже сейчас.
 
Цитата
mitox написал:
Пока не получается достучаться до 1С-ника
А если попробовать так? Никого не дожидаясь?
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
А если  попробовать так ? Никого не дожидаясь?
Я излазил за неделю весь форум, и это пробовал - не хватает прав. Специфика настройки 1С.
 
Цитата
PooHkrd написал:
Никого не дожидаясь
Ни дожидаясь так не дожидаясь
Код
let
    fn=(i)=> let Sourse = i,
    Headers = List.Transform(List.Sort(List.Distinct(Sourse[Column1]),Order.Ascending), Text.From),
    Index = Table.AddIndexColumn(Sourse,"id"),
    Duplicats = Table.DuplicateColumn(Table.DuplicateColumn(Index, "Column1", "a"), "Статьи затрат", "b"),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Duplicats, {{"a", type text}}, "ru-RU"), Headers, "a", "b"),
    Custom = Table.RemoveColumns(Table.FillDown(Table.Sort(Pivot,{"id",0}),Headers),{"id","Column"}) in Custom,
    Sourse = Excel.CurrentWorkbook(){[Name="Account_20"]}[Content],
    names = Table.ColumnNames(Sourse),
    Custom_1 = Table.FromColumns({{0}&List.RemoveLastN(Sourse[Column1])}&Table.ToColumns(Sourse),{"Column"}&names),
    Custom_2 = Table.Combine(Table.Group(Custom_1, {"Column","Column1"},{{"c",each fn(_)}},0,(a,b)=>Number.From(b[Column]>b[Column1]))[c]),
    Custom_3 = List.Transform(List.Sort(List.Distinct(Custom_2[Column1]),Order.Ascending), Text.From),
    Custom_4 = Table.FillDown(Table.SelectColumns(Custom_2,names&Custom_3),{"0","1"})
in
    Custom_4
 
Михаил Л, Туплю. В какую часть кода добавить?

UPD/ Нашел))) Точно туплю...

UPD 1. Не - не работает, вторая и третья колонки не до конца заполняются.
Изменено: mitox - 06.07.2020 17:26:32
 
Добавил функцию и еще немного сократил
Код
let
    Sourse = Excel.CurrentWorkbook(){[Name="Account_20"]}[Content],
    ReplaceValue = Table.ReplaceValue(Sourse, each [Column1], each if [Статьи затрат] = "Обороты" then Number.From(List.Max(Company[Column1]))+1 else [Column1],Replacer.ReplaceValue,{"Column1"}),
    SelectColumns = Table.SelectColumns(ReplaceValue,{"Column1", "Статьи затрат", "Код", "BalansO_D", "BalansO_K", "Turnover_D", "Turnover_K", "BalansE_D", "BalansE_K", "Comp", "Period"}),
    Trim = Table.TransformColumns(SelectColumns,{{"Статьи затрат", each Text.Trim(_, {" "}), type text}}),
    Headers = List.Buffer( List.Transform({0..List.Max(Trim[Column1])}, Text.From ) ),
    ReplacedValue = Table.ReplaceValue(Trim,null,0,Replacer.ReplaceValue,{"Column1"}),
    AddedRecs = Table.AddColumn(ReplacedValue, "recs", each Record.FromList( List.ReplaceRange( List.Repeat({null},List.Count(Headers)), [Column1], 1, {[Статьи затрат]} ), Headers ), type record ),
    ExpandedRecs = Table.ExpandRecordColumn(AddedRecs, "recs", Headers ),
    AddIndex = Table.AddIndexColumn(ExpandedRecs, "Индекс", 1, 1),
    FillDown = Table.FillDown(AddIndex,{"0"}),
    ColumnNamesTable = Table.SelectRows(Table.FromList(Table.ColumnNames(FillDown), Splitter.SplitByNothing(), null, null, ExtraValues.Error), each ([Column1] = "BalansE_D" or [Column1] = "BalansE_K" or [Column1] = "BalansO_D" or [Column1] = "BalansO_K" or [Column1] = "Comp" or [Column1] = "Period" or [Column1] = "Turnover_D" or [Column1] = "Turnover_K")),
    ColumnNames = Table.ColumnNames(FillDown),

    F = (Table, N) =>
        let
            Group = Table.Group(Table, {Text.From(N)}, {{"Tab", each _, type table}}),
            ReplaceN = Table.ReplaceValue(Group, each [Tab], each Table.FillDown([Tab], {Text.From(N+1)}),Replacer.ReplaceValue,{"Tab"}),
            RemoveColumns = Table.RemoveColumns(ReplaceN,{Text.From(N)}),
            Expand = Table.ExpandTableColumn(RemoveColumns, "Tab", ColumnNames)
        in
            Expand,
    F1 = F(FillDown, 0),
    F2 = F(F1, 1),
    F3 = F(F2, 2),
    F4 = F(F3, 3),
    F5 = F(F4, 4),
    F6 = F(F5, 5),
    F7 = F(F6, 6),
    Sort = Table.Sort(F7,{{"Индекс", Order.Ascending}}),
    Filtered = Table.SelectRows(Sort, each ([8] = "Обороты")),
    Del = Table.SelectColumns(Filtered, ColumnNamesTable[Column1]&List.Transform({0..List.Max(Trim[Column1])-1}, Text.From )),
    Types = Table.TransformColumnTypes(Del,{{"Period", type date}, {"Comp", type text}, {"0", type text}, {"1", type text}, {"2", type text}, {"4", type text}, {"5", type text}, {"3", type text}, {"6", type text}, {"BalansO_D", type number}, {"BalansO_K", type number}, {"Turnover_D", type number}, {"Turnover_K", type number}, {"BalansE_D", type number}, {"BalansE_K", type number}, {"7", type text}})
in
    Types
Как бы ее теперь в цикл завернуть?
 
Код тут
F1 творит чудеса
 
Цитата
mitox написал:
вторая и третья колонки не до конца заполняются
Ну не знаю.. Может, изменить {"0","1"} на {"0","1","2","3"}
 
Максим Зеленский, Спасибо огромное.
 
Цитата
mitox написал:
Пока не получается достучаться до 1С-ника
1С-ник не нужен. Это встроенная в отчеты и обработки возможность. И PooHkrd, вам правильную ссылку на инструкцию дал.
Изменено: Иванов Вадим - 06.07.2020 22:44:46
Страницы: 1 2 След.
Наверх