Страницы: 1
RSS
Power Query - при объединении таблиц из разных файлов разносить данные по какому-либо признаку, например по месяцам
 
Здравствуйте, гуру!
Возникла проблема (ну, наверно, как у всех :)) - огромные файлы на каждый проект. У каждого проекта свои сроки. Все файлы в одной папке. Инструмент PQ для создания сводного файла - идеален. Но... Как обычно - чего-то не хватает.
Загружая файлы получается что первый проект начинается с января, второй там с марта - и все данные идут друг под другом. Сейчас приходится вручную перетаскивать данные по временной шкале чтобы под январем стояли данные всех проектов января, а под мартом - соответственно, марта.
Есть ли возможность автоматизировать процесс (в запросе) чтобы сводный файл по какой либо временной шкале разносил данные файлов.
Надеюсь во вложении все понятней будет.
PS. Приемы про сборку таблиц читал )
 
ИМХО, имеет смысл в PQ преобразовать все в плоскую таблицу, затем использовать ее как источник для сводной
Изменено: TheBestOfTheBest - 22.05.2018 07:03:13
Неизлечимых болезней нет, есть неизлечимые люди.
 
Ммм... Каждая проект - таблица порядка двухсот строк и 30-40 столбцов. то есть в плоском виде будет занимать 8000 строк. Проектов - около сорока, то есть в сводной будет строк 320 000. И сводная делается чтобы потом можно было крутить-вертеть аналитику по проектам. Ну, допустим. А как PQ может сделать плоскую таблицу? Чисто попробовать.
Собственно "сводная" я может неправильно сказал - меня устроит просто таблица с данными по всем проектам. То есть конечным результатом должна стать бесконечно уходящие в разные стороны строки и столбцы цифр. )
 
Цитата
игорь сергеев написал:
А как PQ может сделать плоскую таблицу? Чисто попробовать.
И какого ответа Вы ожидаете, подробную инструкцию по созданию плоских таблиц в PQ? Включите google уже
Цитата
игорь сергеев написал:
То есть конечным результатом должна стать бесконечно уходящие в разные стороны строки и столбцы цифр.
Не думаю что это Вам требуется. т.к. для этой реализации потребуется бесконечный источник данных, но даже PQ имеет ограничения. "Сведение" (или "сводничество", как правильно?) можно делать и в PQ.
Неизлечимых болезней нет, есть неизлечимые люди.
 
Выложите пример в виде файлов в архиве, желательно, чтобы структура данных максимально точно повторяла реальные данные - смогу показать как превращать данные в плоскую таблицу в ходе сборки. На вашем примере корректно не покажешь, Вряд ли вы сможете его переделать потом под сборку файлов.
Вот горшок пустой, он предмет простой...
 
Вы удивитесь (как и я впрочем) - но нет в гугле информации о преобразовании таблицы в плоскую при помощи PQ.
А все-таки: если задать временную шкалу года на три (совершенно искусственно) то сможет PQ потом раскидать данные по этой временной шкале?
Вопрос только: куда её задать?  
 
Цитата
игорь сергеев написал:
Вы удивитесь (как и я впрочем) - но нет в гугле информации о преобразовании таблицы в плоскую при помощи PQ.
ДА НУ НА ФИГ!
Конкретно, смотрите второе сообщение на форуме в первой же ссылке.
Изменено: PooHkrd - 22.05.2018 09:18:18
Вот горшок пустой, он предмет простой...
 
Доброе время суток
Цитата
PooHkrd написал:
ДА НУ НА ФИГ!
Offtop
Коллега, да не нервничайте так. Просто у ТС свой креативный язык - вот поисковые системы его и не понимают. А может просто не искал, а зачем когда есть такой замечательный сири-форум? Зачем пальчики-то утруждать?  :D
 
Нет, просто я в курсе что поисковики персонифицируют выдачу под конкретных людей, но это по общим запросам, а по такому конкретному запросу, подозреваю что первая десятка у всех будет более-менее одинаковая. Уж не говорю про буржуйский сектор, там ссылок про unpivot вообще немеряно.
Вот горшок пустой, он предмет простой...
 
PooHkrd, берегите нервы.
Опыт и практика - великое дело! Век живи, Век учись!
 
Если смотреть пример, то вообще непонятна загвоздка. При добавлении одинаковых таблиц в запрос PQ сам расставит данные как вам нужно. Если очередность месяцев "поплывёт", то можно "Отменить свертывание столбцов", отсортировать как нужно и вернуть необходимый вид через "Столбец сведения". Это если делать только кнопками. В расширенном редакторе наверняка есть более аккуратное решение
 
Спасибо всем кто ответил. Чтобы не терять время можно было бы просто написать так: "PQ такое делать не умеет, так что как-нибудь..." )
По поводу преобразования в плоскую таблицу - жалко что сами не заходите по предложенным ссылкам, я их уже столько пересмотрел... Даже на этом форуме не нашлось ответа https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=81826. Но это по вопросу преобразования в плоскую.  
Предлагаю забыть про плоские таблицы. А по исходному моему вопросу - я так понимаю, объединить таблицы по признаку месяц не получиться. На всякий случай выложу кусочек исходного файла (самый минимум)
 
игорь сергеев, касательно "плоской таблицы"  https://youtu.be/J-M7deAgmRs?t=25m13s
 
Цитата
игорь сергеев написал:
"PQ такое делать не умеет, так что как-нибудь..." )
Не так уж и много преобразований данных мне встречалось, которые PQ делать не умеет.
Цитата
игорь сергеев написал:
Даже на этом форуме не нашлось ответа
Повторяю, ответ во втором сообщении по указанной вами ссылке. Другой вопрос, что вам лениво разворачивать цепочку дальше.
Вам даже лениво сделать архив с исходными файлами в ответ на сообщение, в котором я явно указал что могу решить вашу проблему.
Вот горшок пустой, он предмет простой...
 
Цитата
игорь сергеев написал:
Даже на этом форуме не нашлось ответа  https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=81826 .
У Вашей таблицы не такая структура (во всяком случае в той. которая выложена для примера).
Цитата
игорь сергеев написал:
По поводу преобразования в плоскую таблицу - жалко что сами не заходите по предложенным ссылкам, я их уже столько пересмотрел...
Это врядли, просто у каждого свое восприятие, кто-то понимает, кто-то - нет.

Цитата
игорь сергеев написал:
Предлагаю забыть про плоские таблицы.
Забудьте и двигайте вручную.

Пример преобразования одного файла, если google отключен (файл д.б. в папке С:\1). 2010.
Изменено: TheBestOfTheBest - 22.05.2018 11:11:46
Неизлечимых болезней нет, есть неизлечимые люди.
 
Без нормального примера исходных файлов, на основе первого файла: просто объединение в PQ и преобразование в плоскую и построение сводной. Все только кнопками мыши
Изменено: StepanWolkoff - 22.05.2018 11:29:33
 
можно и без пивота, если он по религиозным соображениям не нужен (или по соображениям производительности :) )
Код
let
    Source = Folder.Files("C:\1\1"),
    #"Added Custom" = Table.AddColumn(Source, "WB", each Table.PromoteHeaders(Excel.Workbook([Content]){[Name = "Лист1"]}[Data], [PromoteAllScalars=true])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "WB"}),
    WB = Table.Combine(#"Removed Other Columns"[WB]),
    Custom1 = List.Buffer(List.Skip(Table.ColumnNames(WB),2)),
    Custom2 = Table.FromColumns({Custom1},1),
    #"Inserted Parsed Date" = Table.AddColumn(Custom2, "Выполнить анализ", each Date.From(DateTimeZone.From([Column1])), type date),
    #"Sorted Rows" = Table.Sort(#"Inserted Parsed Date",{{"Выполнить анализ", Order.Ascending}}),
    Column1 = #"Sorted Rows"[Column1],
    Custom3 = Table.ReorderColumns(WB, Column1)
in
    Custom3
F1 творит чудеса
 
Всем еще раз спасибо. Пока остановился на следующем - в каждом из сорока файлов сделал плоскую в PQ (спасибо  TheBestOfTheBest) и в сводном файле сборку этих 40 плоских таблиц через PQ. То есть сейчас обновляю каждую из сорока таблиц и потом сборку в общем файле. 41 раз обновиться. Все равно, конечно, быстрее чем ссылками.  
 
Цитата
игорь сергеев написал:
каждом из сорока файлов сделал плоскую в PQ
Цитата
игорь сергеев написал:
сводном файле сборку этих 40 плоских таблиц через PQ.
Мда, ну и сила лени у вас
Цитата
PooHkrd написал:
Выложите пример в виде файлов в архиве, желательно, чтобы структура данных максимально точно повторяла реальные данные - смогу показать как превращать данные в плоскую таблицу в ходе сборки
Человек готов был подсказать, как таких танцев с бубном избежать.
 
Цитата
игорь сергеев написал:
41 раз обновиться.
мда. мой код вам все и без плоских собирает "как надо" одним обновлением. Но нет, мы пионеры, мы без трудностей не можем :)
F1 творит чудеса
 
Максим Зеленский, подготовил как вы и просили папку с тремя файлами. Строки одинаковые (если там где-то вдруг ошибочно что-то не так -считаем что одинаковые)
Вот начало и длительность проекта разные. в них затык. Посмотрите ...  
 
Вот и начинается  :D
В заголовках не названия месяцев с годом, а даты, причем в разных таблицах разные. Собственно поэтому у вас и не собираются таблицы друг под другом.
На этапе сборки нужно преобразовывать даты в месяца или группировать потом в сводной будете?
Вот горшок пустой, он предмет простой...
 
Еще смешнее - таблицы еще и называются по-разному.
Автор, ответьте:
1. у вас всегда будут умные таблицы в файлах?
2. Всегда одна? Если нет - как понять, какая таблица нужная?
2. Их название может различаться в разных файлах?  
F1 творит чудеса
 
Если уж очень не хочется пивотить (дальше сами)
Код
// PQ по месяцам
let
    Source = Folder.Files("C:\Users\m.zelenskiy\Downloads\xls\PQ по месяцам"),
    #"Added Custom" = Table.AddColumn(Source, "WB", (t)=>
        let
            Таблица = Excel.Workbook(t[Content]){[Kind = "Table"]}[Data],
            Promoted = Table.PromoteHeaders(Таблица, [PromoteAllScalars=true]),
            MonthsHeaders = List.Buffer(List.Skip(Table.ColumnNames(Promoted), 2)),
            #"Converted to Table" = Table.FromColumns({MonthsHeaders}),
            #"Inserted Parsed Date" = Table.AddColumn(#"Converted to Table", "ToDate", each Date.From(DateTimeZone.From([Column1])), type date),
            #"Calculated Start of Month" = Table.TransformColumns(#"Inserted Parsed Date",{{"ToDate", Date.StartOfMonth, type date}}),
            ToText = Table.TransformColumnTypes(#"Calculated Start of Month",{{"ToDate", type text}}),
            Custom1 = Table.RenameColumns(Promoted, Table.ToRows(ToText))
        in
            Table.AddColumn(Custom1, "Name", each t[Name])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"WB"}),
    WB = Table.Combine(#"Removed Other Columns"[WB]),
    Custom1 = List.Buffer(List.RemoveMatchingItems(Table.ColumnNames(WB),{"План выполнения по контракту", "Name", "сумма"})),
    Custom2 = Table.FromColumns({Custom1},1),
    #"Inserted Parsed Date" = Table.AddColumn(Custom2, "Выполнить анализ", each Date.From(DateTimeZone.From([Column1])), type date),
    #"Sorted Rows" = Table.Sort(#"Inserted Parsed Date",{{"Выполнить анализ", Order.Ascending}}),
    Column1 = #"Sorted Rows"[Column1],
    Custom3 = Table.ReorderColumns(WB, {"Name", "План выполнения по контракту", "сумма"} & Column1)
in
    Custom3
F1 творит чудеса
 
Немного допилил файл уважаемого StepanWolkoff.

игорь сергеев это было нужно?
 
скорее так
Изменено: TheBestOfTheBest - 23.05.2018 16:56:52
Неизлечимых болезней нет, есть неизлечимые люди.
 
Цитата
TheBestOfTheBest написал:
скорее так
Ближе к запросу ТС, но как получать из этого аналитику??
Тем более, говорили про плоские таблицы.
 
Никак, это не требуется исходя из #1. Плоская таблица формируется внутри запроса PQ и является частью алгоритма обработки (о чем и намекал ТС).
Неизлечимых болезней нет, есть неизлечимые люди.
 
Вот! Спасибо еще раз TheBestOfTheBest (и другим пользователям за, несомненно, дельные замечания )!
Собрать данные в единую плоскую и из неё потом сформировать необходимую двумерную, но привязанную к сквозному порядку.
Здорово. Спасибо  TheBestOfTheBest!!!
Страницы: 1
Наверх