Добрый день. имею с десятка 2 таблиц, по которым происходит сложный подбор значений используя Индекс(Поискпоз). В принципе все тоже самое можно сделать на ВПР и ГПР, но мне изначально никогда не нравились эти ограниченные "попсовые" функции. Сейчас осваиваю потихоньку PowerQwery, честно сказать я не в восторге от той производительности которую имею в процессе обновления записей, но это не по теме.... Подскажите можно ли реализовать на PowerQwery или на PowerPivot, то что реализовано формулами во вложенном образце. Я понял как объединять значения ячеек по ключевым полям, аналогично тому как это работает в ВПР, но можно ли как-то реализовать ГПР в функционале PowerQwery или PowerPivot. Или все это бесперспективно?
Спасибо всем откликнувшимся.
PS С помощью PQ или PP необходимо сделать разбросанные записи упорядоченными, согласно таблицы распределения значений.
Добрый человек!!! Я даже не представлял себе что это может работать именно так. Не перестаю удивляться, да мышление совершенно иное. Таких файлов, загруженных из 1с с атрибутами в разных столбцах у меня с десяток. Если все это работает в одном файле на Индексах, тормоза дикие. Размеры файлов от 30 мб и выше. Спасибо за направление, учиться мыслить иначе.
lostandleft написал: Если все это работает в одном файле на Индексах, тормоза дикие.
При желании, можно организовать и в PQ аналог ГПР, но это времени надо много, тормоза тоже будут дикие. А этот запрос, я по сути сделал весь кнопками, руками практически не делал ничего. З.Ы. не рекомендую овекрвотить, расстроите модераторов - заставят сообщения редактировать.
abc1, Нет таблица распределения формируется ручками. Из 1с это выгрузить невозможно. А для аналитики нужны совершенон разные субконто по различным счетам. Вот и получается что на одном счете БУ берем аналитику по движению дебета, на другом по движению кредита, а в третьем счете по типу документа.
lostandleft, вы бы сразу из далека зашли и показали бы структуру выгрузки из 1С и к какому виду её нужно привести. Не исключено что это можно сделать без табличек сделанных вручную. Здесь чего только не разбирали при помощи PQ.
Господа апну тему. Предложенное решение, не работает к сожалению. На протяжении всей субботы пытался ковырять сворачивание и разворачивание таблицы. Гуглил Ютубил. Не понимаю как работает этот аппарат. Предложенный PooHkrd, трюк со сворачиванием и разворачиванием таблицы работает только в случае уникальных записей для ВСЕХ полей. В случае же не уникальных записей, выплевывается куча ошибок. Кроме того, в случае наличия незаполненных полей в таблице распределения запрос не выполняется в принципе. Формулами это легко решается, и ГПР это не помеха, он только лишь в одной ячейке выдает Ошибку, которую можно легко поменять. PQ же вообще загибается на этом. К сожалению, предложенное решение не работоспособно в реальном документе. Реальный документ динамический, он меняется, и заполняется по ходу. Кроме того, в нем огромное количество повторений,первого столбца.
Я попытался что-то собрать сам, на основании тех данных что мне Вы дали, собрал второй документ, но в нем та-же самая петрушка, все работает только при уникальных записях, при не уникальных, повторяющихся записях все летит в тартар.
Во втором документе (Новые пробы--На форум замена индекс и ГПР (1).xlsx) похожая задачка, но, как мне показалось, должна быть несколько проще. В таблице Подмена, 2 столбца, в первом столбце указан клиент, во втором столбце ссылка на столбец из которого нужно взять новое значение. Так для Сары нужно взять значение из столбца Детали 4, а для Кары из столбца Детали 2 Если удалить последние строки, то все отрабатывается хорошо, и таблица имеет тот вид что должна. Но если есть повторяющиеся значения, в столбце Клиент, ничего не получается. Очень пытаюсь все это дело освоить, но что-то с трудом....нужен полный аналог ГПР ... других вариантов не вижу
lostandleft написал: Предложенный PooHkrd , трюк со сворачиванием и разворачиванием таблицы работает только в случае уникальных записей для ВСЕХ полей.
Предложено было частное представление данных, было получено частное решение. Как вы думаете, зачем это пишут в правилах?
Цитата
2.3. Приложите файл(ы) с примером (общим весом не более 300Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе.
Ещё же хотел предупредить, Алексея. Но он не сторонник додумывания за...
Цитата
lostandleft написал: но, как мне показалось, должна быть несколько проще
Решается аналогично через Join (тот самый приближённый аналог ВПР/ГПР ) И вообще, согласно правилам хорошего тона, на форему рекомендуется следовать
Цитата
2.6. Один вопрос - одна тема. Не следует в открываемой теме обозначать и задавать сразу несколько вопросов.
А у вас вопрос скорее о фильтрации результата Unpivot по нужным данным.
Андрей VG, abc1, Господа, не работает ни один предложенный Вами вариант решения, к сожалению. Вариант предлоенный Андреем не отрабатывает в том случае, если в таблице распределения проставить одинаковые значения в какой-либо из строк. С формулами все работает с PQ - нет. Также, в том случае если какое-либо значение в таблице распределения пустое, PQ вообще выдаст ошибку в запросе, и запрос не выполнит.
Вариант предложенный ABC вообще не понял, ели честно, итогового столбца Результат - нет вообще. Ожидаемый результат - должен быть выполнен запросом PQ Из имеющихся 4 столбцов (детали 1 детали2 детали 3 и детали 4) нужно выбрать значение и подставить его в последний столбец, аналогично тому, как это сделано формулой в таблице.
lostandleft написал: Вариант предлоенный Андреем не отрабатывает в том случае, если в таблице распределения проставить одинаковые значения в какой-либо из строк.
Приведите пример и требуемый результат, а не картинки.
Андрей VG, Требуемый результат приведен в таблице с шапкой УПОРЯДОЧЕНО. В Файле, который уже дважды повторен в этой теме.
Результат должен быть динамический. Если меняем значение в таблице распределения, все должно отрабатывать так, как это отрабатывает в формулах.
В Таблице распределения, в строке Персики, указано, что значение для Цены берется из столбца под номером 1, значение Количества, Цвета и Вкуса, также берется из столбца номер 1. Что и отражено в таблице Упорядочено - Все столбцы отражают Сладкий.
PQ запрос этого обработать в данной вариации не может, к сожалению.
Картинка 2 показывает, что если какое-то значение из таблицы распределения НЕ проставлено, то PQ вообще запрос не делает, выплевывает ошибку.
Какой пример еще привести, я не понимаю, извините. Прогшу просто сделать запрос, который предоставляет точно такой результат как формульный вариант с ГПР. И задача мне эта уже мозги от имела основательно. Проще все на формулах оставить. Чем еще 5-6 -10 часов на это потратить.
Если Это все изрядно надоело, я Вас понимаю... Плюнте на это, оно не решаемо.
lostandleft написал: Прогшу просто сделать запрос, который предоставляет точно такой результат как формульный вариант с ГПР.
Я с ВПР то почти не работаю, а уж с ГПР знаком по наслышке
Цитата
lostandleft написал: ребуемый результат приведен в таблице с шапкой УПОРЯДОЧЕНО. В Файле, который уже дважды повторен в этой теме.
Дважды вам к вашим файлам выложены решения. Где и что в этих решениях работает не так? Откройте выложенный файл и пометьте такое место. Картинки - как постановку задачи - не рассматриваю. Или вы хотите решение тоже в виде картинки? Подождите того, кому будет интересно это будет сделать.
abc1, DrillPipe, Андрей VG, Господа! Ваше терпение безмерно, спасибо за ответы. Задачка, на которую я уже повесил ярлык нерешаемой, и хотел было плюнуть на нее - решена и первый и второй фаил разгадал DrillPipe, за что огромное спасибо. Также решение готовое, в итоге по первому файлу предложил Андрей VG, что интересно, оба решения очень отличаются друг от друга. Оказывается задачу можно решать по разному. Решение DrillPipe, безумно элегантное, еще не понимаю как оно сделано, но выглядит очень интересно. Решение от Андрей VG, сложное для восприятие, с большим числом вложенных формул, но работает также замечательно.
abc1, и Вам спасибо за участие, но к сожалению, в Вашем решении раздваиваются пустые строки (первая строка), этого быть не должно.
Сегодня и завтра обещают быть интересными, изучая те решения, которые Были предложены, очень жаль что у Вас версии эксель английские, буду пытаться что-то понять. Спасибо еще раз!
lostandleft написал: жаль что у Вас версии эксель английские
Если у меня будет английский Excel, как же я буду им пользоваться? Для PQ поставил только шаги на английском. Так, по моему, легче воспринимается и сокращать удобнее.
Полторы тысяч лет назад, все знали, что Земля была центром вселенной. Пятьсот лет назад, все знали, что Земля плоская, а пятнадцать минут назад, ты знал, что люди были единственными на этой планете. Представь, что ты будешь «знать» завтра.
к/ф MIB Agent K
PS {...решена и первый и второй фаил разгадал DrillPipe} первое не совсем правильно решено - я сравниваю строки по наименованию продукта, а не по номеру строки - но «допилить» до отбора по номеру не сложно.
let
Подмена = Table.Buffer(Excel.CurrentWorkbook(){[Name="Подмена"]}[Content]),
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
AddResult = Table.AddColumn(Source, "Result", each
let
podmena = Table.SelectRows(Подмена, (r)=> r[Что ищем] = [Клиент])[Столбец]?{0}?
in
if podmena = null then null else Record.FieldOrDefault(_, podmena, null))
in
AddResult
let
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
colSelector = Excel.CurrentWorkbook(){[Name="Подмена"]}[Content],
join = Table.Join(Source, {"Клиент"}, colSelector, {"Что ищем"}, JoinKind.LeftOuter),
result = Table.AddColumn(join, "Результат", each if [Столбец] <> null then Record.FieldOrDefault(_, [Столбец], "что-то пошло не так ☹") else null)
in
result
Андрей VG, спасибо за запрос. А вообще, ради интереса, есть ли возможность в шаге result к формуле приклеить это - "[["&Text.Combine(Table.ColumnNames( Source)&{"Результат"},"],[")&"]]" ?
Код
= Table.AddColumn(join, "Результат", each if [Столбец] <> null then Record.FieldOrDefault(_, [Столбец],
"что-то пошло не так ☹") else null) и "[["&Text.Combine(Table.ColumnNames(Source)&{"Результат"},"],[")&"]]"
Понимаю что так не делают. Чисто ради интереса. Пробовал использовать Expression.Evaluate, но не пошло
представляет собой сокращённое выражение выборки столбцов или полей для таблицы или записи, представленное как текст. [[Клиент],[Сумма],[Вес],[Тип],[Дата],[Детали 1],[Детали 2],[Детали 3],[Детали 4],[Нужный столбец],[Результат]] Но, для какой таблицы или записи вы хотите сделать такую выборку? Именно поэтому Expression.Evaluate и не считает.
abc1 написал: Для таблицы, которую возвращает шаг result.
А смысл, если будет тоже самое, что и в result?
Код
let
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
colSelector = Excel.CurrentWorkbook(){[Name="Подмена"]}[Content],
join = Table.Join(Source, {"Клиент"}, colSelector, {"Что ищем"}, JoinKind.LeftOuter),
result = Table.AddColumn(join, "Результат", each if [Столбец] <> null then Record.FieldOrDefault(_, [Столбец], "что-то пошло не так ☹") else null),
s = "expressionTable[["&Text.Combine(Table.ColumnNames(Source)&{"Результат"},"],[")&"]]",
r = Expression.Evaluate(s, [expressionTable=result])
in
r
Или я чего-то не понимаю, и вы пытаетесь таким способом нечто присоединить к Source?
Андрей VG, я так понимаю, что коллеге очень нравится возможность вместо Table.SelectColumns указывать нужные столбцы столбцы в формате [[col1],[col2]]. Но тогда названия столбцов в коде прибиты гвоздями, а хочется динамичности и поменьше шагов в запросе. Вот и изгаляется в меру своих знаний. А вы ему такое решение предложили, что проще уж использовать Table.SelectColumns. abc1, угадал?
PooHkrd написал: нравится возможность вместо Table.SelectColumns указывать нужные столбцы столбцы в формате [[col1],[col2]]
Все верно, именно так и хотел. Надо же хотя бы убедиться что этим путем действовать непрактично. Конечно, если еще шагом придется извлекать столбцы, то я воспользуюсь Table.SelectColumns. Андрей VG, спасибо за науку!