Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Вставить данные, полученные в результате выполнения запроса в Power Query, в исходную умную таблицу
 
#1
03.02.2020 13:08:02
Добрый день!

Поскольку вопрос теоретический, думаю, файл с примером нужен не особенно. Суть в следующем.

Есть некий объект (изделие), имеющий уникальный идентификационный номер (ID). Информация о появлении (выпуске) изделия с указанием ID хранится в умной таблице Excel. Изделие может быть вдальнейшем использовано, либо испорчено (брак).
Информация об использовании изделий, периодически выгружается из большой базы данных производственной службы за период (неделя). Естественно с указанием ID, а таккже указанием ряда дополнительных показателей. Выгрузка из БД происходит в таблицу excel.
В свою очередь, информация о порче (браке) заводится в умную таблицу "врукопашную".
Задача состоит в том, чтобы перетянуть в умную таблицу показатели выгрузки из базы данных.

Сейчас это реализовано с помощью кода VBA. Идёшь по выгрузке, берёшь изделие, переходишь в умную таблицу, ищешь ID, перетягиваешь информацию. И всё это по каждой позиции выборки. Долго и неэффективно.
Попробовал реализовать задачи отбора значений и связки между данными умной таблицы и выгрузки в Power Query. Получилось значительно веселей. Выгрузил на лист, получил записи, которые должны быть вставлены в умную таблицу. Или, точнее, получил записи умной таблицы, которые должны быть скорректированы.

Собственно, вопрос. Есть ли механизм переноса информации из запроса в таблицу - источник без использования VBA, например, с помощью Power Query? Или ещё как-то?
В последнее время склоняюсь к тому. что несмотря на предельную гибкость, предоставляемую VBA, эффективность программ оставляет желать.

Попытка сохранить результаты запроса на самой первоначальной умной таблице (фактически, перезаписать таблицу) не увенчалась успехом, что, наверно, логично.
 
 
 
#2
03.02.2020 13:19:02
Доброе время суток
Цитата
quasarrr написал:
вопрос теоретический
Существующее решение Self Referencing Tables in Power Query.
 
 
 
#3
03.02.2020 13:23:56
С помощью Power Query возможно, посмотрите по поиску на форуме соответствующие примеры из тем.
Вот горшок пустой, он предмет простой...
 
 
 
#4
03.02.2020 14:01:53
Спасибо за ответ.
Не могу открыть ссылку. Она рабочая? Посмотрите, пожалуйста. Может, домен "ua"  на моей работе не приветствуется   :cry:  
 
 
 
#5
03.02.2020 14:09:25
Цитата
quasarrr написал:
Она рабочая?
Рабочая, только что проверил.
Цитата
quasarrr написал:
домен "ua"
Домен au - Австралия.
 
 
 
#6
03.02.2020 14:13:06
И правда au   :D

Но не открывается. Из дома попробую.
 
 
 
#7
03.02.2020 14:17:52
quasarrr, и моя не открывается? Там все тоже самое.
Вот горшок пустой, он предмет простой...
 
 
 
#8
03.02.2020 14:31:15
PooHkrd, поиск по форуму? Открылся.

Изучаю. Там, в основном, Ваши дебаты с ymal_qeb. Продираюсь через   ;)  
 
 
 
#9
04.02.2020 15:57:43
Спасибо за ссылку. Поучительно. И почему мой работодатель не взлюбил Австралию? Так и не даёт туда прорваться. Загадка.   :D

Но на мой взгляд немного не та ситуация, которая описана в статье.
Здесь задача внести изменения в исходную таблицу на основании поступившей информации, изменить отдельные поля в отдельных столбцах. И при этом оставить таблицу на месте. И ещё не попортить другие столбцы (есть, например, столбец с формулой для проверки проверки корректности ввода, его бы не трогать).

VBA? Нет вариантов? Или я не вкурил?
 
 
 
#10
04.02.2020 16:07:49
Цитата
quasarrr написал:
внести изменения в исходную таблицу на основании поступившей информации, изменить отдельные поля в отдельных столбцах.
Так в чём проблема?
Есть исходная таблица. Есть новая откуда-то взятая. Соединяем по по первичному ключу (Table.Join, Table.NestedJoin) и составляем по результатам обновлённую и дополненную таблицу на вывод, которая замещает ранее существовавшую. В чём проблема?
Цитата
quasarrr написал:
И ещё не попортить другие столбцы
А другие столбцы и не будут портиться вы же их будете выводить.
Цитата
quasarrr написал:
есть, например, столбец с формулой для проверки проверки корректности ввода, его бы не трогать
А вот вычисляемые столбцы как раз и не выводим. Правда, для столбцов с формулами есть проблема - не всегда срабатывает автозаполнение. Чтобы работало, нужно чтобы новые добавленные строки были всегда в низу выводимой таблицы.
 
 
 
#11
04.02.2020 16:24:24
Проблема, наверно, в "...которая замещает ранее существовавшую".

Не понимаю механизм замещения  :( . Пока не доходит, как применить статью к этому.
 
 
 
#12
04.02.2020 16:28:49
Цитата
quasarrr написал:
Поскольку вопрос теоретический,
Пока вопрос теоретический и не поймете. Давайте пример данных и источник из которого нужно брать дополнительные данные, и чего-то обновить в исходной основной таблице. Можете посмотреть примеры из моих тем. Ну, или продолжайте дальше теоретизировать.
Вот горшок пустой, он предмет простой...
 
 
 
#13
04.02.2020 16:44:48
Цитата
quasarrr написал:
Не понимаю механизм замещения
А чего там понимать-то :qstn:
Код
let
    oldTable = #table(type table [key = Int64.Type, value = Text.Type, formula = Text.Type], {{0, "old0", "true"}, {1, "old1", "true"}, {2, "old2", "false"}}),
    newTable = #table(type table [newkey = Int64.Type, newvalue = Text.Type], {{1, "new1"}, {2, "new2"}, {3, "newest"}}),
    oldCommon = Table.RemoveColumns(oldTable, {"formula"}),
    oldOrder = Table.AddIndexColumn(oldCommon, "id"),
    join = Table.Join(oldOrder, {"key"}, newTable, {"newkey"}, JoinKind.FullOuter),
    merge = Table.AddColumn(join, "temp", each [
        key = if [key] = null then [newkey] else [key],
        value = if [key] = null or [newvalue] <> null then [newvalue] else [value],
        id = if [id] = null then 10000000 else [id]
    ]),
    result = Table.FromRecords(merge[temp]),
    restoreOrder = Table.Sort(result, {{"id", Order.Ascending}}),
    return = Table.RemoveColumns(restoreOrder, {"id"})
in
    return
oldTable - та самая таблица таблица на листе с формульным столбцом formula
newTable - источник обновления/дополнения.
 
 
 
#14
04.02.2020 17:04:32
Андрей VG, но результат запишется на другое место, верно? Не на место исходной таблицы?

PooHkrd, ок, модельный пример.
На листе "Исходная" - исходная таблица, на листе "ДопИнфа" - источник с дополнительными данными, на листе "Результат" - соответственно, результирующая таблица.

В результирующей таблице добавилась информация в строках 4, 6, 7
Значение "Брак" в первоначальную таблицу введена вручную.
Из нюансов - изделие 218207 передано на доработку - ввод этой строки вручную, передача для использования в производстве (следующая строка) - загружается из файла с доп. информацией.

результирующую таблицу нужно разместить на месте первоначальной.
 
 
 
#15
04.02.2020 17:32:43
quasarrr, пробуйте. Обновляйте зеленую табличку.
Цитата
quasarrr написал:
Но результат запишется на другое место, верно?
Обратите пристальное внимание на название таблицы в которую выгружается результат, и на название таблицы, которая является источником для запроса. Это одна и та же таблица. Т.е. вы из неё берете данные, делаете с ними что хотите и выгружаете обратно с учетом произведенных в запросе изменений.
Изменено: PooHkrd - 04.02.2020 17:37:12
Вот горшок пустой, он предмет простой...
 
 
 
#16
04.02.2020 17:43:41
Цитата
quasarrr написал: Но результат запишется на другое место, верно
нет, PooHkrd, вам уже показал и доказал это :)
вывод
Цитата
quasarrr написал: Поучительно
неверное утверждение.
Вообще self reference - это не особенность Power Query. Если поискать, то эти решения можно найти и для таблиц , связанных с  SQL запросами, в темах где-то с 2011 года. Тогда Power Query ещё не пахло. :)
 
 
 
#17
04.02.2020 19:34:20
Спасибо!

То, что доктор прописал!
Убивает, правда, столбец с формулами, но его можно открепить от таблицы, соответственно, не загружать и не обрабатывать.
 
 
 
#18
04.02.2020 20:48:05
Цитата
quasarrr написал:
Убивает, правда, столбец с формулами, но его можно открепить от таблицы
А ещё можно формулу реализовать при помощи средств pq. И это самый верный способ. Но это уже вопрос для новой темы.
Вот горшок пустой, он предмет простой...
 
 
 
#19
04.02.2020 21:42:14
PooHkrd,
правильно ли я понял, что можно формулу записать средствами pq в столбец и она будет работать после выгрузки таблицы в эксель?
Просто в данном случае формула полезна на этапе ручного ввода информации в таблицу. на других этапах (преобразования таблицы в pq) формула практического смысла не имеет.
 
 
 
#20
05.02.2020 00:17:52
Нет, можно сделать расчетный столбец в pq, в котором будет повторен алгоритм реализованный в формуле. Тогда после каждого ввода новой инфы вручную или макросом надо будет обновлять таблицу.
Вот горшок пустой, он предмет простой...
 
 
 
Страницы: 1
Наверх