Страницы: 1
RSS
Перенос и модификация таблиц с листа на лист (Power Query)
 
Коллеги, приветствую!
Прошу помощи в следующем вопросе, поскольку сам не смог сделать.
На вкладке "Исходный" размещены таблицы, которые нужно перенести на вкладку "Конечный".
По поводу вкладки "Исходный":
- таблицы между собой разделяются всегда одной пустой строкой
- значения в рамках любого столбца могут повторяться, но в совокупности каждая строка уникальна.
Таблицы вкладки "Исходный" переносятся в таком же виде и содержании, но:
- добавляется столбец с номером по порядку, который считает порядок для каждой таблицы по-отдельности.
- для каждой таблицы добавляется шапка (в исходном файле шапка только в первой таблице)
- в столбце "Количество документов" считается сумма
- проставляется номер таблицы по порядку
- между таблицами расстояние в три строки.
Подскажите, что и как из этого можно реализовать. Наверное, можно сделать в VBA, но желательно в Power Query, поскольку PQ знаю в большей степени, чем VBA, чтобы потом в случае чего внести корректировки.
Заранее благодарю!
 
наверняка можно сделать лучше
 
Я так сделал:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    TCN = {"№ п/п"} & Table.ColumnNames(Source),
    AddedIndex1 = Table.AddIndexColumn(Source, "Индекс", 1, 1),
    AddedCustom = Table.AddColumn(AddedIndex1, "tmp", each if [Контрагент] =null then [Индекс] else null),
    FilledDown = Table.FillDown(AddedCustom,{"tmp"}),
    RemovedColumns = Table.RemoveColumns(FilledDown,{"Индекс"}),
    #"Filtered Rows" = Table.SelectRows(RemovedColumns, each ([Контрагент] <> null)),
    Grouped = Table.Group(#"Filtered Rows", {"tmp"}, {{"tmp2", each _, type table}}),
    AddedIndex2 = Table.TransformColumns(Grouped, {{"tmp2", each Table.AddIndexColumn(_,"№ п/п",1,1)}}),
    AddedCustom1 = Table.AddColumn(AddedIndex2, "table", each #table(TCN, {TCN}) & [tmp2] & #table({"Кол-во документов"}, {{List.Sum([tmp2][#"Кол-во документов"])}, {null}})),
    RemovedOther = Table.SelectColumns(AddedCustom1,{"table"}),
    Expanded = Table.ExpandTableColumn(RemovedOther, "table", TCN, TCN),
    RemovedBottom = Table.RemoveLastN(Expanded,1),
    RemovedTop = Table.Skip(RemovedBottom,1)
in
    RemovedTop
F1 творит чудеса
 
artyrH, Максим Зеленский,
Коллеги, спасибо большое за помощь, буду разбираться
 
еще вариант  
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    TCN = {"№ п/п"} & Table.ColumnNames(Source),
    AddedIndex1 = Table.AddIndexColumn(Source, "Индекс", 1, 1),
    AddedCustom = Table.AddColumn(AddedIndex1, "tmp", each if [Контрагент] =null then [Индекс] else null),
    FilledDown = Table.FillDown(AddedCustom,{"tmp"}),
    RemovedColumns = Table.RemoveColumns(FilledDown,{"Индекс"}),
    #"Filtered Rows" = Table.SelectRows(RemovedColumns, each ([Контрагент] <> null)),
    Grouped = Table.Group(#"Filtered Rows", {"tmp"}, {{"tmp2", each _, type table}}),
    #"Added Index" = Table.AddIndexColumn(Grouped, "Индекс", 1, 1),
    AddedIndex2 = Table.TransformColumns(#"Added Index", {{"tmp2", each Table.AddIndexColumn(_,"№ п/п",1,1)}}),
    AddedCustom1 = Table.AddColumn(AddedIndex2, "table", each Table.FromList({null,null}, Splitter.SplitByNothing()) & #table(TCN, {TCN}) & [tmp2] & #table({"Кол-во документов"}, {{List.Sum([tmp2][#"Кол-во документов"])}})),
    RemovedOther = Table.SelectColumns(AddedCustom1,{"Индекс", "table"}),
    Expanded = Table.ExpandTableColumn(RemovedOther, "table",TCN,TCN),
    #"Promoted Headers" = Table.PromoteHeaders(Expanded, [PromoteAllScalars=true]),
    #"Added Index1" = Table.AddIndexColumn(#"Promoted Headers", "Индекс", 1, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Индекс.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Индекс"},#"Added Index2",{"Индекс.1"},"Added Index2",JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Added Index2", {"Column11"}, {"Column11.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded {0}", "Column", each if [Column11.1] = "Кол-во документов" then "Таблица" & Text.From([1]) else [Column2]),
    Custom1 = Table.SelectColumns(#"Added Conditional Column",{"Column","Column3","Column4","Column5","Column6","Column7","Column8","Column9","Column10","Column11","Column12"})
in
    Custom1

на базе решения, которое предложил Максим Зеленский
Изменено: artyrH - 03.04.2019 08:42:13
 
Ещё вариант. Предполагается, что по заполненным строкам сумма не может быть пустой. В противном случае на втором шаге нужно вместо суммы указать другой столбец, по которому не бывает пропусков. Ну, либо переписать второй шаг, чтобы он определял номера полностью пустых строк.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    x = {-1}&List.PositionOf(Source[Сумма], null, 2),
    tables = List.Generate(()=>[i=0, a=x{i}+1, b = x{i+1}-a ], each [i] < List.Count(x), each [i=[i]+1, a=x{i}+1, b = x{i+1}?-a ], each Table.Range(Source,[a],[b])),
    transform = List.Transform(tables, each _ & #table({"Кол-во документов"}, {{List.Sum([#"Кол-во документов"])},{null},{null}})),
    final = Table.RemoveLastN(Table.Combine(transform), 2)
in
    final
Изменено: Aleksei_Zhigulin - 03.04.2019 15:31:38
 
artyrH, Aleksei_Zhigulin,
Больше спасибо!
Вариант artyrH более подходящий по двум причинам:
1. Там важным моментом этой задачи было добавление шапки к каждой таблице и название таблицы.
2. Он более понятен в плане написания кода. Я процессе осваивания PQ. Хотя некоторые моменты не совсем понятны в варианте artyrH, но в целом понимаю, что происходит и как это подкорректировать в случае необходимости. Вариант Aleksei_Zhigulin чересчур мудреный :)
 
reactortyz, хозяин - барин  :)  Но я, действительно, не добавил первый столбец, исправляюсь:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    x = List.Buffer({-1}&List.PositionOf(Source[Сумма], null, 2)),
    tables = List.Generate(()=>[i=0, a=x{i}+1, b = x{i+1}-a ], each [i] < List.Count(x), each [i=[i]+1, a=x{i}+1, b = x{i+1}?-a ],
                                each [c = Table.Range(Source,[a],[b]), d = #table({"№ п/п"}, {{"Таблица "&Text.From([i]+1)}}) &
                                Table.AddIndexColumn(c, "№ п/п", 1, 1) & #table({"Кол-во документов"}, {{List.Sum(c[#"Кол-во документов"])},{null}})][d]),
    final = Table.RemoveLastN(Table.Combine(tables), 1)
in
    final
Изменено: Aleksei_Zhigulin - 04.04.2019 14:44:37
Страницы: 1
Наверх