Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сведение данных с нескольких таблиц на одну в соответствии с датами в PQ
 
#1
13.02.2020 18:39:27
Такая вот задачка. Есть Листы продаж (лист 1 и лист 2), в них даты и количество продаж. Нужно все свести в таблицу 3, с нужными датами. В чем особенности:
На листе 1 и 2 частенько бывают повторы, а точнее не повторы, а именно дата встречается несколько раз например:

01.01.2020 - 5
01.01.2020 - 4

В таком случае нужно вывести в общую таблицу в дате 01.01.2020 - 9. Прикладываю фаилик с примером, думаю все понятно. Спасибо!

Еще важный нюанс, нужно все это сделать автоматический, в ручную в целом у меня получилось это через слияние, но это для каждого отдельного листа надо делать.
 
 
 
#2
13.02.2020 18:54:12
Доброе время суток
Цитата
dodge написал:
у меня получилось это через слияние,
Тогда в чём вопрос, если постановка задачи звучит
Цитата
dodge написал:
Листы продаж (лист 1 и лист 2), в них даты и количество продаж. Нужно все свести в таблицу 3,
 
 
 
#3
13.02.2020 21:40:26
Слияние функция не автоматическая. Т.е. там в ручную нужно вбивать 2 таблицы для слияния. Как сделать чтобы все автоматический свелось в 1 таблицу.  
 
 
 
#4
13.02.2020 21:57:51
Цитата
dodge написал:
Слияние функция не автоматическая. Т.е. там в ручную нужно вбивать 2 таблицы для слияния.
Ещё раз. Может я, как и остальные не понимаю. У вас уже слиты в ручную две таблицы.
Зачем нужно их автоматическое слияние?
Если нужно автоматически сливать две таблицы, то нужно где-то как-то определить - какие две таблицы сливать?
 
 
 
#5
13.02.2020 22:27:20
Каряво объяснил изначально. Вот подправил фаил. В нем есть запрос "Сводная", на который собираются таблицы из листов под номером 1....n. Нужно эти таблицы вывести в 1 таблицу с датами как на листе экселя "Общая". Думаю так понятнее.
Изменено: dodge - 13.02.2020 22:32:50
 
 
 
#6
13.02.2020 23:04:26
dodge, наверное так надо. В шаге Filter наверное надо будет поправить.
Или надо сколько листов, столько же и столбцов + столбец с датами?
Код
let
    Источник = Excel.CurrentWorkbook(),
    Filter = Table.SelectRows(Источник, each not Text.Contains([Name], "Сводная")),
    #"Removed Other Columns" = Table.Combine(Table.SelectColumns(Filter,{"Content"})[Content]),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Даты"}, {{"сумма", each List.Sum([Столбец1]), type number}, {"сумма2", each List.Sum([Столбец2]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Даты", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Даты", type date}})
in
    #"Changed Type"
 
 
 
#7
13.02.2020 23:22:19
Цитата
abc1 написал:
Или надо сколько листов, столько же и столбцов
Где-то так, коллега. ТС так и постеснялся ответить на вопрос. Видимо ожидает выбрать из наилучшего предложения как нам лучше реорганизовать Рабкрин. :)
Цитата
Андрей VG написал:
где-то как-то определить - какие две таблицы сливать?
В предположении, что имена таблиц начинаются с _
Код
let
    Source = Excel.CurrentWorkbook(),
    tables = Table.SelectRows(Source, each Text.StartsWith([Name], "_"))[Content],
    union = Table.Combine(tables),
    calcFields = List.Range(Table.ColumnNames(union), 1),
    aggregations = List.Transform(calcFields, each {_, (sub) => List.Sum(Table.Column(sub, _)), Number.Type}),
    result = Table.Group(union, {"Даты"}, aggregations)
in
    result

Ну, а если не с _, то ТСу предстоит обдумать, как получить нужные tables - остальная динамика, учитывая статику по общему столбцу для всех таблиц Даты, который в свою очередь является первым столбцом таблиц, обеспечена.
Изменено: Андрей VG - 13.02.2020 23:34:59
 
 
 
#8
13.02.2020 23:54:13
Сорян, старался объяснить нормально, но немного криво получилось. Андрей VG, все как у вас получилось и нужно, только еще чтобы общая таблица начиналась с 01.01.2020 и шла до 03.03.2020 (с шагом в 1 день).
 
 
 
#9
14.02.2020 08:31:41
dodge, к запросу Андрея добавьте еще несколько шагов
Код
    spis= #table(type table [Date = date], List.Zip({List.Dates(#date(2020,1,1),1+Number.From(#date(2020,3,1)-#date(2020,1,1)),#duration(1,0,0,0))})),
    Type = Table.TransformColumnTypes(result,{{"Даты", type date}}),
    Merge = Table.Join(spis,{"Date"},Type,{"Даты"},JoinKind.LeftOuter),
    Remove = Table.RemoveColumns(Merge,{"Даты"})
in
    Remove
 
 
 
#10
14.02.2020 09:13:42
abc1, гм, я правильно понял, это вы
Код
List.Zip({list})
используете для одного списка как замену
Код
List.Transform( list, each {_} )
Интересный подход, спасибо за приемчик.
Изменено: PooHkrd - 14.02.2020 09:15:19
Вот горшок пустой, он предмет простой...
 
 
 
#11
14.02.2020 09:27:18
Цитата
PooHkrd написал:
я правильно понял
Признаюсь, даже не вникал в этот момент :)
Скопировал прием Максима Зеленского из этой темы. Благодарность автору приема
 
 
 
#12
14.02.2020 09:44:16
abc1, это зря, каждое решение прежде всего надо разобрать и понять механику работы. Ибо при всем уважении к Максиму и он тоже человек и вполне может накосячить. Но запись коротенькая получается, это да.
Вот горшок пустой, он предмет простой...
 
 
 
#13
14.02.2020 09:49:05
PooHkrd, вот сейчас и посмотрел
#table не хабает списки дат без применения List.Zip - это факт
 
 
 
#14
14.02.2020 10:00:05
abc1, правильно потому что эта функция должна получить на входе список строк, что представляет собой список списков. Функция List.Zip объединяет элементы из нескольких списков в общий, если же ей скормить только один список , то она каждую дату преобразует в список из одной даты, а такое #table уже понимает.
Вот горшок пустой, он предмет простой...
 
 
 
#15
14.02.2020 10:11:12
Цитата
PooHkrd написал:
эта функция должна получить на входе список строк, что представляет собой список списков.
А что собственно мешает использовать другой метод создания таблицы из списка?
Код
Table.FromColumns({List.Dates(#date(2020,1,1),1+Number.From(#date(2020,3,1)-#date(2020,1,1)),#duration(1,0,0,0))}, type table [col=Date.Type])

ровно тоже самое без List.Zip
 
 
 
#16
14.02.2020 10:36:11
Андрей VG, да ничего, мне понравился прием юезотносительно создания таблиц, а просто как формат краткой записи преобразования элементов списка в списки. Как-то самому такое в тыковку не дошло, а время от времени такое требуется. Теперь вот можно блеснуть, и потом послушать вопросы, ух ты, а это тут зачем?  ;) А еще чаще "А почему на моем не обновленном PQ твой запрос не работает?" :D
Вот горшок пустой, он предмет простой...
 
 
 
#17
14.02.2020 17:40:37
Андрей VG, abc1, огромное спасибо, все работает
 
 
 
#18
09.03.2020 19:16:34
Коллеги, такой вопрос появился. Можно ли как-то заменить/есть ли аналоги функции Table.Combine - потому что когда происходит объединение таблиц с большим количеством строк, и собственно таблиц становится много, он их все сумирует, нереально при этом затормаживая процесс обработки.

Он не замещает одинаковые позиции в строках, а дублирует их. Вот например скрин из запроса.
https://i.ibb.co/vP9TZfQ/Screenshot-2.jpg
Можно ли сделать что-то вроде того, что я стрелками изобразил на скрине. Спасибо!
Изменено: dodge - 09.03.2020 19:29:49
 
 
 
#19
09.03.2020 19:33:46
Цитата
dodge написал:
Он не замещает одинаковые позиции в строках, а дублирует их.
А где это было описано в постановке задачи? В конце запроса остаются уникальные даты в силу
result = Table.Group(union, {"Даты"}, aggregations)
А ничего другого вы не описали. Так что пишите полноценное определение задачи. Далеко не все как тёзка готовы к agile (мне так его и на работе за глаза хватает).
Изменено: Андрей VG - 09.03.2020 19:37:22
 
 
 
#20
09.03.2020 19:37:14
Нигде не написано, я без предъяв :D  Хорошо, что и так работает, но просто при использовании данного запроса, понял, что именно этот пункт сильно его утяжеляет и тормозит, вот думаю как его обойти.
 
 
 
#21
09.03.2020 19:53:14
Цитата
dodge написал:
менно этот пункт сильно его утяжеляет и тормозит,
Вот и опишите задачу полностью. А Table.Combine
Код
tables = Table.FromColumns({Table.SelectRows(Source, each Text.StartsWith([Name], "_"))[Content]}, {"temp"}),
union = Table.ExpandTableColumn(tables, "temp", {"ну а тут укажите список столбцов"})

По практике развертывания данных подтаблиц после Table.Group во временный столбец через Table.ExpandTableColumn быстрее, чем через Table.Combine, но заметно при достаточно большом числе подтаблиц. Будет ли работать  в этом случае - поэкспериментируйте - отпишитесь на форуме. Пора уже быть участником, а не только потреблять.
 
 
 
#22
02.04.2020 14:41:07
Дабы не плодить темы опять, кое-что спрошу тут, поскольку все по этой же задаче вопросы. Вопросы больше даже теоретические)

В чем суть, у меня запрос, который выгружает большой объем данных. Запрос формирует таблицу примерно из 60 строк, в каждой строке таблица (из 1 столбца), в которой около 3000 строк. Вот скриншот:
https://prnt.sc/rrh4af
И вот в таком виде, все работает отлично. И очень быстро, такой объем (в таком вот виде) он выгружает за 2-3 минуты максимум. Выгружает в таком виде в Excel.
https://prnt.sc/rrh5l6

НО, мне нужны сами данные из каждой таблицы, в идеале нужно сводить их в 1 таблицу. Именно это я спрашивал здесь и мне порекомендовали вот такой способ (спасибо Андрей VG).
Способ этот работает, все оке, НО очень муторно из-за Table.Combine, поскольку эта функция по сути создает новую таблицу в памяти с количеством строк 60*3000, способ занимает примерно минут 40-50 на данный объем данных (60 строк в каждой строке - таблица 1 столбец, 3000 строк), в зависимости от мощности компьютера. Table.ExpandTableColumn по сути делает тоже самое, да немного быстрее, но все равно очень медленно цифры по времени тоже около 40 минут.

Я попробовал другой вариант, каждую таблицу (на каждой из 60 строк) я привел в максимально комфортный и удобный мне вид, таким образом, чтобы потом их просто все вместе свести. Сведение сделал через простую функцию.
Код
Fun_ = (table, n) =>
        let
            Tab = 
            if n < r then Table.Join(table,{"DATE"},Tables_Total[List_Total]{n},{"DATE"})
            else
                table
            in
                if n = r-1 then Tab else @Fun_(Tab, n+1)

Думаю суть понятна, начиная с 1 строки столбца, где все мои таблицы, я их начинаю объединять каждый раз добавляя новую таблицу через переменную "n". На выходе получаю объединенную таблицу. И пока этот способ самый быстрый, минут за 20 он все делает. НО, хочется быстрее. Отсюда 2 вопроса

1) Как раз теоретический. Почему в ситуации, когда я не разворачиваю таблицы, а просто загружаю их в таком виде https://prnt.sc/rrh4af все грузится ну просто ахренеть как быстро, а стоит мне их каким-то образом разворачивать, выводить данные, объединять в 1, все начинает тупить. Ведь по сути, если я вывел эти таблицы в таком виде (как на скриншоте) они уже полностью заведены в память и значит разворот их или сведение в 1 не должно занимать много времени? Или я тут не прав.

2) Собственно все тот же вопрос. Как мне максимально быстро свести все данные в 1 таблицу (т.е. у меня есть столбец, в каждой строке которого еще таблица в 1 столбец, нужно просто все это объединить). Может есть какие-то еще варианты о которых я не знаю? Я просмотрел документацию по PQ, ответа не нашел. Пробовал функцию Table.FromColumns (ну т.е. сводил все столбцы в 1 таблицу) - по времени также как функция. Около 20 минут. Желательно бы быстрее(
Спасибо за помощь!
Изменено: dodge - 02.04.2020 15:38:37
 
 
 
#23
02.04.2020 15:06:51
Цитата
dodge написал:
если я вывел эти таблицы в таком виде (как на скриншоте) они уже полностью заведены в память
нет. Это не сами таблицы, а promises этих таблиц. Обещание рассчитать таблицу, когда она понадобится. Lazy evaluation, все дела
Соответственно, если у вас
Цитата
dodge написал:
есть столбец, в каждой строке которого еще таблица в 1 столбец
то быстродействие зависит от того, как вы получаете эти вложенные таблицы. если особой функцией, то там может быть большой ой.
Так что с точки зрения быстродействия надо смотреть запрос целиком. Иначе ответ простой - если таблицы имеют одинаковый набор столбцов, то либо разворачивайте, либо Table.Combine, практически незаметная разница. Но что вам на самом деле надо - знаете только вы :) как объединить, что получить, что делать с остальными столбцами и так далее.
F1 творит чудеса
 
 
 
Страницы: 1
Наверх