Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Собрать данные с листов 2018/2019 в нескольких файлах через PQ с доп столбцом имя файла.
 
#1
22.01.2019 09:38:05
Добрый день. Есть журналы специалистов которые находятся на сетевом диске. которые заполняются в режиме реального времени во время приёма клиентов. хочу с помощью PQ собирать данные со всех файлов в этой папке. с листов 2018 и 2019 в каждом файле и на основе этих данных уже заниматься анализом.
Вроде бы получилось сделать что-бы собирал все данные из всех файлов.
но не получается добавить в таблицу столбец из какого файла скажем так строка. файл = фио специалиста.
и берёт данные из всех файлов либо с листа 2018 либо с листа 2019 а надо и с того и с другого.

Структура во всех файлах одинаковая по листу ( 2018 и 2019) так же кол-во и названия столбцов одинаковые.

Прикладываю файл. попытался сам сделать. но что-то не получилось. может подскажите как быть. PQ использую всего неделю.
для того чтобы сделать использовал статьи эту и эту
Изменено: Wild.Godlike - 22.01.2019 13:51:12
 
 
 
#2
22.01.2019 10:27:22
Пару исходных журналов приложите.
 
 
 
#3
22.01.2019 10:31:07
Alexey_Spb, Здравствуйте, прикрепил к первому сообщению.
 
 
 
#4
22.01.2019 11:05:19
Wild.Godlike, один из файлов (наугад) удалил - превышен максимально допустимый размер. Освежите свои знания Правил.
 
 
 
#5
22.01.2019 11:17:44
Цитата
Wild.Godlike написал:но не получается добавить в таблицу столбец из какого файла скажем так строка. файл = фио специалиста.
Надо заменить одну строку на это:
Код
    #"Другие удаленные столбцы1" = Table.SelectColumns(#"Вызвать настраиваемую функцию1", {"Name", "Преобразовать файл из Журналы"}),

Вы удалили сами столбцец Name, который содержал имя файла..
Еще добавил тупое убирание .xlsx из колонки имени:
Код
Name = Table.TransformColumns(#"Столбец расширенной таблицы1", {"Name", each Text.Start(_, Text.Length(_) - 5)})
Изменено: Alexey_Spb - 22.01.2019 12:01:40
 
 
 
#6
22.01.2019 11:27:03
Alexey_Spb,Спасибо это помогло, а что надо сделать чтобы данные собирались не только с листа 2018 но и с листа 2019.
 
 
 
#7
22.01.2019 11:56:41
Прошу смотреть пример. Можете сделать любой диапазон лет.
Код
    Loaded = Table.TransformColumns
               (
                Pages, 
                  {
                   "Data", 
                   (Row) =>
                     List.Transform
                      (
                       {2018..2019},
                       each Table.Combine({Table.PromoteHeaders(Row{[Item=Text.From(_),Kind="Sheet"]}[Data])})
                      )
                  }
               ),
Таблицу допилите на свой вкус.
 
 
 
#8
22.01.2019 11:57:01
Цитата
Wild.Godlike написал:
но и с листа 2019
Попробуйте.
З.Ы. Подправил файл, лучше выгружайте новую версию.
Изменено: PooHkrd - 22.01.2019 12:23:41
Вот горшок пустой, он предмет простой...
 
 
 
#9
22.01.2019 12:36:23
Alexey_Spb,не получается адаптировать под свою таблицу ( уже и путь поменял к источнику в вашем файле всё равно. сложно с английским (
PooHkrd, постарался сделать так же каки вы в итоге вот такие вот ошибки.
 
 
 
#10
22.01.2019 12:54:00
Цитата
Wild.Godlike написал:
не получается адаптировать под свою таблицу
А что именно не получается. Вы таблицу далее можете обрабатывать всеми средствами Power query
 
 
 
#11
22.01.2019 13:04:00
Alexey_Spb,не понимаю почему в вашем запросе jr только подключение.
 
 
 
#12
22.01.2019 13:10:05
Wild.Godlike, правой мышкой по нему, "Загрузить в..." и выбирайте, куда.
F1 творит чудеса
 
 
 
#13
22.01.2019 13:26:07
Максим Зеленский, Alexey_Spb,

Изменил путь из сообщения №9
Т.к. папка сетевая и с файлами работают в режиме реального времени(как писал в первом сообщении) есть временные файлы в этой же папке которые вызывают ошибку. (чтобы отсечь ненужные файлы для обработки)
добавляю пользовательский столбец с формулой( 123 скришот)
Код
=Excel.Workbook([Content])
Далее в этом столбце удаляю ошибки Error и удаляю столбец.
оставляю всё как вы делали ( 321 скриншот)
в итоге при загрузке ошибка ( 111 скришот)
Файлик прикрепил, может я что-то не так адаптировал (

Вы следить за размером вложения будете? [МОДЕРАТОР]
Изменено: Wild.Godlike - 22.01.2019 13:50:19
 
 
 
#14
22.01.2019 13:37:43
Цитата
Wild.Godlike написал:
Изменил путь из сообщения №9Т.к. папка сетевая и с файлами работают в режиме реального времени есть временные файлы в этой же папке которые вызывают ошибку
Щелкните в списке шагов запроса jr на Source, после чего отфильтруйте столбец Name - "не начинается с символов ~$". Дайте добро на вставку нового шага после Source.
Суть этого шага в том что вы убираете временные файлы (они начинаются с ~$)
Изменено: Alexey_Spb - 22.01.2019 13:50:37
 
 
 
#15
22.01.2019 13:41:03
Alexey_Spb,Сделал такая же ошибка.....ключу не соответствует (
 
 
 
#16
22.01.2019 13:45:46
А так?
Код
Filtered = Table.SelectRows(Source, each not Text.StartsWith([Name], "~$")),

P.S. В файле надо исправить путь
Изменено: Alexey_Spb - 22.01.2019 13:48:08
 
 
 
#17
22.01.2019 13:48:42
Alexey_Spb,Загрузил почему то только 18 строчек из первого файла и то не доконца.
 
 
 
#18
22.01.2019 13:50:40
Чет мне кажется что у вас там файлы разнородные, например, не во всех могут быть два листа с именами 2018 и 2019. Или еще что..

Сначала скопируйте и обкатайте "чистые" данные на другой папке, а не на сетевой куда каждый пишет что хочет...

P.S. Это все решаемо, есть обработчики ошибок в PQ, но просто у вас непонятно с чем столкнешься.
Изменено: Alexey_Spb - 22.01.2019 13:52:15
 
 
 
#19
22.01.2019 13:54:54
Цитата
Alexey_Spb написал:
например, не во всех могут быть два листа с именами 2018 и 2019
я за этот вариант. неправильно названные листы, пробел в названиях в хвосте и т.п.
F1 творит чудеса
 
 
 
#20
22.01.2019 14:50:06
Перепроверю ещё раз всё и отпишусь.
Изменено: Wild.Godlike - 22.01.2019 14:52:43
 
 
 
#21
22.01.2019 14:57:57
Ошибка "Поле "Статус" не найдено, означает что в одной из собираемых таблиц отсутствует столбец с названием Статус. Про корректность названий листов и мусор в папке где лежат исходники вам уже описали выше.
Вот горшок пустой, он предмет простой...
 
 
 
#22
22.01.2019 15:08:09
Wild.Godlike, чтобы избежать захламления файла пользователями, следует делать форму ввода (хоть расписанную рюшечками) с проверкой вводимых данных (стандартная функция экселя, да и то не всегда помогает - например, если пользователь "вырежет" значение через Ctrl-X).

А на скрытом листе будет таблица в первой нормальной форме, в которую формулами подтягиваются данные из пользовательской формы. А таблицы уже собирает PQ.
Весь файл и лист с формой обязательно защитить паролем. Иначе вы вечно глюки будете вылавливать потому что, например, секретарше Маше захотелось объединить несколько ячеек, да и позакрашивать их чтобы ячейки под цвет ногтей были. Ну и другую. красоту навести типа удаления или добавления столбцов и т.д.

Таблицы в первой нормальной форме вызывают у большинства юзеров рвотный рефлекс.

P.S. Делал таймшит отдела на 50 человек в экселе еще до появления PQ (макросы + формулы), знаком с этими проблемами непонаслышке.
Изменено: Alexey_Spb - 22.01.2019 15:15:59
 
 
 
#23
22.01.2019 16:47:55
В общем сделал вот так, оказалось проблемсы были из за того что в некоторых файлах нет листа 2019 т.к.(например сотрудник больше не работает откуда там появится листу 2019) сделал 2 отдельных запроса и один общий. Думаю пока что и так сойдёт, хотя может можно как то слепить всё в одно)
Может кому то понадобится.

ВСЕМ СПАСИБО :)

P.S. В запросе 2019 я просто отфильтровал тех у кого нет этого листа в списке, скажем так жосткая привязка )
Изменено: Wild.Godlike - 22.01.2019 18:01:24
 
 
 
#24
22.01.2019 17:45:39
А у меня возник вопрос к Максиму Зеленскому и PooHkrd, а так же другим знатокам PQ.

Хотел предложить автору такой вариант, нечувствительный к отсутствующим листам.
   
Код
Loaded = Table.TransformColumns
               (
                Pages, 
                  {
                   "Data", 
                   (СurrentTable) =>
                     Table.Combine(
                         List.Accumulate
                          (
                           {2018..2019},
                           {},
                           (acc, this) => acc & (try {Table.PromoteHeaders(СurrentTable{[Item=Text.From(this), Kind="Sheet"]}[Data])} otherwise {})
                           )
                          )
                }
               ),
Это вариант предыдущего кода, но использован List.Accumulate, который из полного списка лет аккумулирует новый список из таблиц с данными, в которых нет ошибок (если ошибка то к аккумулируемому списку добавляется пустой список, то есть по сути ничего не происходит). А потом таблицы с годами сливаются в одну через Table.Combine - в итоге в ячейках будут таблицы, содержащие данные всех существующих листов с годами.

Хотел узнать почему конструкция try в данном случае НЕ работает, то есть в этих местах запрос добавляет в аккумулируемый список ошибку, а не пустой список.

Понятно, что ошибки можно убрать по-другому, вопрос в том почему "не работает" try. Почему это происходит, что я не так понимаю в работе try..otherwise?

Приложил пример, где это наглядно видно. Экспериментальные данные с одним удаленным листом тоже прилагаю.
Изменено: Alexey_Spb - 22.01.2019 17:54:45
 
 
 
#25
22.01.2019 17:57:35
Alexey_Spb, а какую ошибку возвращает хоть?
Вот горшок пустой, он предмет простой...
 
 
 
#26
22.01.2019 18:00:26
Да ту, что мы все видели в этой теме ранее - она возникает когда пытаемся получить из Worksheet (как таблицы) несуществующую строку (так как листа с таким именем не существует).
Цитата
Expression.Error: The key didn't match any rows in the table.
То есть как будто try нет вообще, который специально добавлен чтобы эту ошибку перехватывать..  
Изменено: Alexey_Spb - 22.01.2019 19:12:59
 
 
 
#27
22.01.2019 18:27:31
Alexey_Spb, подозреваю это потому что в вашей записи try видит данные типа list, одинм из элементов которого является выражение, которое выдает ошибку, таким образом try ошибку не видит. Т.е. вы не там его применяете.
попробуйте так:
Код
{try Table.PromoteHeaders(СurrentTable{[Item=Text.From(this), Kind="Sheet"]}[Data]) otherwise null}
Изменено: PooHkrd - 22.01.2019 18:29:15
Вот горшок пустой, он предмет простой...
 
 
 
#28
22.01.2019 18:36:12
PooHkrd, спасибо большое. Да, похоже действительно в этом дело. А я почему-то думал, что отлавливаются все ошибки, произошедшие внутри выражения, неважно на каком уровне.

Все работает:
Код
    Loaded = Table.TransformColumns
               (
                Pages, 
                  {
                   "Data", 
                   (Row) =>
                   Table.Combine(
                         List.Accumulate
                          (
                           {2018..2019},
                           {},
                           (acc, this) => 
                             let 
                              Table = try Table.PromoteHeaders(Row{[Item=Text.From(this), Kind="Sheet"]}[Data]) otherwise null
                             in if Table = null then acc else acc & {Table} 
                           )
                          )
PooHkrd написал:
Код
{try Table.PromoteHeaders(СurrentTable{[Item=Text.From(this), Kind="Sheet"]}[Data]) otherwise null}
Спасибо, так ошибка будет отлавливаться, но возникнет проблема - в случае ошибки в список-аккумулятор добавится null, что вызовет ошибку в Table.Combine, которая его не переварит. Или тогда надо фильтровать null'ы из списка.
Изменено: Alexey_Spb - 22.01.2019 19:13:20
 
 
 
#29
22.01.2019 18:44:19
Alexey_Spb, List.RemoveNulls() и всего делов!
Вот еще вариант не чувствительный к отсутствию листов. Почти весь сделан на кнопках.
Изменено: PooHkrd - 22.01.2019 19:10:31
Вот горшок пустой, он предмет простой...
 
 
 
#30
22.01.2019 23:58:53
Alexey_Spb, наверное лучше вашу идею так:
Код
    Pages = Table.AddColumn(Filtered, "Data", each Excel.Workbook([Content], true, true)),
    Loaded = Table.TransformColumns
               (
                Pages, 
                  {
                   "Data", 
                   (Row) =>
//                   Table.Combine(
                         List.Accumulate
                          (
                           {2018..2019},
                           {},
                           (acc, this) => acc & {try Row{[Item=Text.From(this), Kind="Sheet"]}[Data] otherwise #table({},{})}
                           )
//                      )
                }
               ),

Обратите внимание на шаг Pages, 1-й и 2-й аргумент в Excel.Workbook
Ну и на аккумулятор - так как на каждом шагу мы по идее добавляем по одной таблице в список, вместо ошибочной можно возвращать пустую таблицу как элемент списка, а не пустой список. При развороте пустая таблица спокойно самоуничтожается.

Хотя сама идея использования аккумулятора мне здесь как-то не нравится, не могу пока сформулировать, почему точно, но вот что-то не то.
F1 творит чудеса
 
 
 
Страницы: 1 2 След.
Наверх