Добрый день. Есть журналы специалистов которые находятся на сетевом диске. которые заполняются в режиме реального времени во время приёма клиентов. хочу с помощью PQ собирать данные со всех файлов в этой папке. с листов 2018 и 2019 в каждом файле и на основе этих данных уже заниматься анализом. Вроде бы получилось сделать что-бы собирал все данные из всех файлов. но не получается добавить в таблицу столбец из какого файла скажем так строка. файл = фио специалиста. и берёт данные из всех файлов либо с листа 2018 либо с листа 2019 а надо и с того и с другого.
Структура во всех файлах одинаковая по листу ( 2018 и 2019) так же кол-во и названия столбцов одинаковые.
Прикладываю файл. попытался сам сделать. но что-то не получилось. может подскажите как быть. PQ использую всего неделю. для того чтобы сделать использовал статьи эту и эту
Alexey_Spb,не получается адаптировать под свою таблицу ( уже и путь поменял к источнику в вашем файле всё равно. сложно с английским ( PooHkrd, постарался сделать так же каки вы в итоге вот такие вот ошибки.
Изменил путь из сообщения №9 Т.к. папка сетевая и с файлами работают в режиме реального времени(как писал в первом сообщении) есть временные файлы в этой же папке которые вызывают ошибку. (чтобы отсечь ненужные файлы для обработки) добавляю пользовательский столбец с формулой( 123 скришот)
Код
=Excel.Workbook([Content])
Далее в этом столбце удаляю ошибки Error и удаляю столбец. оставляю всё как вы делали ( 321 скриншот) в итоге при загрузке ошибка ( 111 скришот) Файлик прикрепил, может я что-то не так адаптировал (
Вы следить за размером вложения будете? [МОДЕРАТОР]
Wild.Godlike написал: Изменил путь из сообщения №9Т.к. папка сетевая и с файлами работают в режиме реального времени есть временные файлы в этой же папке которые вызывают ошибку
Щелкните в списке шагов запроса jr на Source, после чего отфильтруйте столбец Name - "не начинается с символов ~$". Дайте добро на вставку нового шага после Source. Суть этого шага в том что вы убираете временные файлы (они начинаются с ~$)
Ошибка "Поле "Статус" не найдено, означает что в одной из собираемых таблиц отсутствует столбец с названием Статус. Про корректность названий листов и мусор в папке где лежат исходники вам уже описали выше.
Wild.Godlike, чтобы избежать захламления файла пользователями, следует делать форму ввода (хоть расписанную рюшечками) с проверкой вводимых данных (стандартная функция экселя, да и то не всегда помогает - например, если пользователь "вырежет" значение через Ctrl-X).
А на скрытом листе будет таблица в первой нормальной форме, в которую формулами подтягиваются данные из пользовательской формы. А таблицы уже собирает PQ. Весь файл и лист с формой обязательно защитить паролем. Иначе вы вечно глюки будете вылавливать потому что, например, секретарше Маше захотелось объединить несколько ячеек, да и позакрашивать их чтобы ячейки под цвет ногтей были. Ну и другую. красоту навести типа удаления или добавления столбцов и т.д.
Таблицы в первой нормальной форме вызывают у большинства юзеров рвотный рефлекс.
P.S. Делал таймшит отдела на 50 человек в экселе еще до появления PQ (макросы + формулы), знаком с этими проблемами непонаслышке.
В общем сделал вот так, оказалось проблемсы были из за того что в некоторых файлах нет листа 2019 т.к.(например сотрудник больше не работает откуда там появится листу 2019) сделал 2 отдельных запроса и один общий. Думаю пока что и так сойдёт, хотя может можно как то слепить всё в одно) Может кому то понадобится.
ВСЕМ СПАСИБО
P.S. В запросе 2019 я просто отфильтровал тех у кого нет этого листа в списке, скажем так жосткая привязка )
Это вариант предыдущего кода, но использован List.Accumulate, который из полного списка лет аккумулирует новый список из таблиц с данными, в которых нет ошибок (если ошибка то к аккумулируемому списку добавляется пустой список, то есть по сути ничего не происходит). А потом таблицы с годами сливаются в одну через Table.Combine - в итоге в ячейках будут таблицы, содержащие данные всех существующих листов с годами.
Хотел узнать почему конструкция try в данном случае НЕ работает, то есть в этих местах запрос добавляет в аккумулируемый список ошибку, а не пустой список.
Понятно, что ошибки можно убрать по-другому, вопрос в том почему "не работает" try. Почему это происходит, что я не так понимаю в работе try..otherwise?
Приложил пример, где это наглядно видно. Экспериментальные данные с одним удаленным листом тоже прилагаю.
Да ту, что мы все видели в этой теме ранее - она возникает когда пытаемся получить из Worksheet (как таблицы) несуществующую строку (так как листа с таким именем не существует).
Цитата
Expression.Error: The key didn't match any rows in the table.
То есть как будто try нет вообще, который специально добавлен чтобы эту ошибку перехватывать..
Alexey_Spb, подозреваю это потому что в вашей записи try видит данные типа list, одинм из элементов которого является выражение, которое выдает ошибку, таким образом try ошибку не видит. Т.е. вы не там его применяете. попробуйте так:
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}
)
)
Спасибо, так ошибка будет отлавливаться, но возникнет проблема - в случае ошибки в список-аккумулятор добавится null, что вызовет ошибку в Table.Combine, которая его не переварит. Или тогда надо фильтровать null'ы из списка.
Обратите внимание на шаг Pages, 1-й и 2-й аргумент в Excel.Workbook Ну и на аккумулятор - так как на каждом шагу мы по идее добавляем по одной таблице в список, вместо ошибочной можно возвращать пустую таблицу как элемент списка, а не пустой список. При развороте пустая таблица спокойно самоуничтожается.
Хотя сама идея использования аккумулятора мне здесь как-то не нравится, не могу пока сформулировать, почему точно, но вот что-то не то.