Страницы: 1 2 След.
RSS
Подстановка кода из другой таблицы по неполному условию, только Power Query или DAX
 
Давненько ничего не спрашивал, но тут что-то завис и не могу подобраться к решению

Есть таблица Данные, в ней много разных столбцов, в том числе столбцы Продукт, Тип, Канал. Есть таблица Справочник, в ней столбцы Продукт, Тип, Канал и Код.

Задача:
подтащить Код из Справочник в Данные, не используя в коде PQ или DAX возможные значения столбцов Продукт, Тип, Канал, так как справочник может более-менее меняться, и придется править код.
Решаться будет в Power BI, поэтому только Power Query или DAX. В реальной задаче столбцов с условиями до 10 в обоих таблицах.

Подвох:
В Данных столбцы проверки заполнены всегда, пустых значений в них нет.
В Справочнике не в каждой строке заполнены значения в столбцах Продукт, Тип, Канал. Например, может быть не заполнен столбец Тип, что означает "любой Тип". Или столбец Канал, что означает "любой Канал". Может быть не заполнено несколько столбцов. В конце концов, встречаются условия только по одному столбцу.
Логика подстановки должна быть такой, что сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо.
На картинке нагляднее. Там вместо пустых значений стоят *, но это не принципиально.


В общем, чтой-та я подвис. Способов перепробовал уже довольно много, ни один не стоит показа, так как не привел к результату.

ЗЫ Заполнить справочник всеми необходимыми сочетаниями - крайний вариант, очевидный и оставлен как самый хреновый. Но дополнить справочник какой-то информацией можно.
Изменено: Максим Зеленский - 17.07.2020 14:58:38
F1 творит чудеса
 
может глупость предложу, но как-то так:
Код
(a,b,c)=>
let
    z = Table.SelectRows(Справочник, each [Продукт]=a or [Продукт]="*"),
    y = Table.SelectRows(z, each [Тип]=b or [Тип]="*"),
    x = Table.SelectRows(y, each [Канал]=c or [Канал]="*")[Код]{0}
in
    x
вечером перепишу на произвольный список столбцов
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, спасибо! Вот что значит "глаз замылился". Выглядит неплохо, допилю буферами и списком столбцов справочника.
Пока лучше ничего не приходит в голову всё равно.
F1 творит чудеса
 
ОК, финишную редакцию - в студию  ;)
Соблюдение правил форума не освобождает от модераторского произвола
 
Переделал обход столбцов на аккумуляторе. В боевом применении там еще будут мелкие чистки, но и так неплохо.
Код
let
    Source = Данные,
    sprav = Table.Buffer(Справочник),
    columns = List.Buffer(Table.ColumnNames(Table.RemoveColumns(sprav, {"Код"}))),
    fn2 = (row, columns)=> List.Accumulate(columns, sprav, (state, current)=>
        Table.SelectRows(state, each Record.Field(_, current) = Record.Field(row, current) or Record.Field(_, current) = "*"))[Код]{0}?,
    Invoked = Table.AddColumn(Source, "fn2", each fn2(_, columns))
in
    Invoked


Но может кому-то из коллег будет интересно и сделают более производительный вариант ;)
F1 творит чудеса
 
Пока пилил свое топорное решение на DAX, уже все сделали!
Сделал 4 раза ВПР (максимум возможных составных ключей в справочнике)
 
Vladimir Chebykin, ага, спасибо! там можно чуть ускорить переменными, но блин, 10 lookupvalues к ряду писать... но тоже вполне рабочий вариант
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
10 lookupvalues к ряду писать.
можно в PQ сделать 3 составных ключа дополнительно и, соответственно, 4 связи в PP. То же самое будет, просто чуть проще на восприятие. А так да, это "топорное" решение!
 
Цитата
Vladimir Chebykin написал:
можно в PQ сделать 3 составных ключа дополнительно
составной ключ в справочнике с пропуском не будет же вязаться к данным, в которых этот ключ заполнен. или я не понял идею?
F1 творит чудеса
 
Максим Зеленский, я неверно представил себе идею в голове. Попробовал сделать, и получается, что 3 составных ключа нужны на стороне таблицы данных, а это ненужная избыточность. Поэтому лучшего варианта, чем LOOKUPVALUE я придумать не смог!
 
Для кучи тот самый - хреновый :)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Справочник"]}[Content],
    Clm = {"Тип","Канал"},
    Replace = List.Accumulate(Clm, Source, (x,y)=>
        let i=Table.Column(Данные,y) 
        in Table.ReplaceValue(x,i,"*",(a,b,c)=> 
                           if a=c 
                           then List.Distinct(b) 
                           else {a},{y})),
    Expand = List.Accumulate(Clm,Replace,(a,b)=>Table.ExpandListColumn(a,b)),
    Duplicate = Table.Distinct(Expand,{"Продукт"}&Clm),
    #"Changed Type" = Table.TransformColumnTypes(Duplicate,{{"Продукт", type text}, {"Тип", type text}, {"Канал", type text}, {"Код", Int64.Type}})
in
    #"Changed Type"
 
Vladimir Chebykin, может быть, так тоже годится:
Код
=
VAR t1 =
    FILTER(
        'Справочник';
        'Справочник'[Продукт] = 'Данные'[Продукт]
            || ISBLANK( 'Справочник'[Продукт] )
    )
VAR t2 =
    FILTER(
        t1;
        'Справочник'[Тип] = 'Данные'[Тип]
            || ISBLANK( 'Справочник'[Тип] )
    )
VAR t3 =
    FILTER(
        t2;
        'Справочник'[Канал] = 'Данные'[Канал]
            || ISBLANK( 'Справочник'[Канал] )
    )
RETURN
    CALCULATE( MIN( 'Справочник'[Код] ); t3 )

Переменные не обязательны, для наглядности
F1 творит чудеса
 
Михаил Л, спасибо за вариант. Посмотрю обязательно, сейчас уже убегаю
F1 творит чудеса
 
Доброе время суток.
Версия на Join в предположении, что Тип и Канал никогда не содержат какого-нибудь одинакового значения.
Updated
Переделал для *. Тестовый вариант на 100000 с копейками строк. Код Максима 12 секунд, на Join 4 секунды.
Изменено: anvg - 17.07.2020 23:01:41
 
Цитата
anvg написал:
12 секунд, на Join 4 секунды
Я конечно извиняюсь, но Сумма по коду и Сумма по fn2 разные.
У кого верно?
Если у Максима не верно, то и мой вариант не верный :)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Справочник"]}[Content],
    Clm = List.Buffer({"Продукт","Тип","Канал"}),
    Replace = List.Accumulate(Clm, Source, (x,y)=>
        let i=List.Buffer(List.Distinct(Table.Column(Данные,y))) 
        in Table.ReplaceValue(x,i,"*",(a,b,c)=> 
                           if a=c then b else {a},{y})),
    Expand = List.Accumulate(Clm,Replace,(a,b)=>Table.ExpandListColumn(a,b)),
    Duplicate = Table.Distinct(Expand,Clm),
    #"Changed Type" = Table.TransformColumnTypes(Duplicate,{{"Продукт", type text}, {"Тип", type text}, {"Канал", type text}, {"Код", Int64.Type}})
in
    #"Changed Type"
Изменено: Михаил Л - 18.07.2020 07:33:13
 
Цитата
Михаил Л написал:
Я конечно извиняюсь, но Сумма по коду и Сумма по fn2 разные.
Не уловил, где? Берём исходник, подставляем и сравниваем с эталонным решением (я его только упорядочил по Продукт, Тип, Канал).
Можно получить немного доказательств в чём вы нашли разность?
 
Цитата
anvg написал:
в чём вы нашли разность?
На этих таблицах разницы не видно.
Если же скачать Тестовый вариант и, обновив запросы, посмотреть сводной Сумму, то будет видна разница
 
Цитата
Михаил Л написал:
Если же скачать  Тестовый вариант
В принципе, да. Вариант Максима зависим от порядка строк в справочнике. Достаточно пересортировать справочник, чтобы получался иной результат :)  . А надеятся на существование предпорядка....
К сожалению, ни вариант Максима, ни ваш вариант не разбирал.
 
Цитата
anvg написал:
Достаточно пересортировать справочник, чтобы получался иной результат
Если перед обновлением в таблице Справочник в столбцах Канал и Тип звездочки отправить вниз таблицы, то результаты трех запросов совпадают :)  
 
Цитата
Михаил Л написал:
Канал и Тип звездочки отправить вниз таблицы
Добро, осталось придумать как это эффективно использовать :)  Что-то тёзка мимо ходит, а у него всегда на такие классы задач были сногсшибательные решения.
 
anvg, Спасибо за вариант! поразбираю еще, кое-что не до конца прочувствовал. Вариант такой я продумывал, но немного останавливало то, что придется захардкодить названия столбцов. Не дай бог что-то добавят или удалят - надо переписывать... Я, конечно, не ставил такое ограничение, так что тут вопрос ко мне.
Цитата
anvg написал:
в предположении, что Тип и Канал никогда не содержат какого-нибудь одинакового значения
Можете пояснить вот это ограничение, немного не понял?
Цитата
anvg написал:
зависим от порядка строк в справочнике.
да, согласен. В принципе, переместить строки, в которых явно указан только продукт, вниз - не большая проблема. И да, придется задавать авторам справочника жесткое условие по очередности строк.
Но можно попробовать разбить проверку на 2 части... чуть позже попробую наваять.
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
захардкодить названия столбцов.
А как без этого? Или вы про шаг addDataKeys? Тут можно выкрутиться
Скрытый текст

Цитата
Максим Зеленский написал:
Можете пояснить вот это ограничение, немного не понял?
Не обращайте внимание, текст относился к первому варианту, когда вместо * использовался null :)
Цитата
Максим Зеленский написал:
Но можно попробовать разбить проверку на 2 части... чуть позже попробую наваять.
Тут основной вопрос в размере справочника. Чем больше, тем больше в нём ненужных просмотров. Формально предложенный алгоритм 4 * N * Log(M), где N число строк в Данных, M в Справочнике, плюс мелочовка поиска первого не null.
 
Цитата
anvg написал:
А как без этого?
Я имел в виду вполне вероятный сценарий, когда в процессе эксплуатации изменится состав столбцов справочника. Например, было 8 столбцов с разными условиями, а стало 6 или 10. В своем варианте я бегаю по динамическому списку столбцов аккумулятором, в вашем, насколько могу судить, надо прикрутить аккумулятор к addDataKeys + expandDataKeys, т.е. там, где собираются столбцы . Еще поковыряю ваш код, да и в любом случае, задача может оказаться не одноразовой.
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
а стало 6 или 10
Интересно было бы посмотреть решение задачи на DAX, как исходно предлагалось в теме :) . Только алгоритм с Join для 2, 3 столбцов. При 4 нужно будет делать 8 Join, при 5 - 16 и так далее. Тогда лучше строить из Справочника дерево поиска. Вечером попробую набросать, если тёзка не опередит.
Изменено: anvg - 21.07.2020 08:08:04
 
Дратути :)
Код
let
    Source  = Справочник,
    Columns = List.Buffer(List.Intersect({Table.ColumnNames(Source), Table.ColumnNames(Данные)})),
    Custom1 = Table.AddColumn(
        Справочник, 
        "a", 
        each List.PositionOf(
            Table.ToRecords(Данные), 
            _, 
            3, 
            (a, b) => List.Accumulate(
                Columns, 
                true, 
                (acc, curr) => acc and 
                    (Record.Field(b, curr) = "*" or 
                     Record.Field(b, curr) = Record.Field(a, curr))
            )
        )
    ),
    Custom2 = Table.AddColumn(Custom1, "count", each List.Count([a])),
    Expand  = Table.ExpandListColumn(Custom2, "a"),
    Custom3 = Table.NestedJoin(Table.AddIndexColumn(Данные, "i", 0), "i", Expand, "a", "Код"),
    Custom4 = Table.TransformColumns(Custom3, {"Код", each Table.Min(_, "count")[Код]}),
    Result  = Table.RemoveColumns(Custom4, {"i"})
in
    Result
 
Цитата
Андрей Лящук написал:
Дратути
Великолепно, только медленно как-то. Не узнаю вас :)  А если что-то будет более приближенное к реальности?
 
Андрей Лящук, спасибо, искал что-то такое! :) идея изначально шуршать не по Данным, а по Справочнику прям зудела, но, с другой стороны, вряд ли приводит к уменьшению количества просмотров.

кстати, чему равен 3 в 10 строке??? что-то мне справк не дает ответ.
Occurrence.First = 0, Occurrence.Last = 1, Occurrence.All = 2, ???? = 3...
Occurrence.Type тож ничего про это не знает. Судя по всему, всё, что >=2 будет Occurrence.All
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
вряд ли приводит к уменьшению количества просмотров.
К сожалению, да.
На тестовом наборе в файле Андрей Лящук - 176 секунд, мой - 0,2 секунды.
 
пара вариантов, по сути идентичных
Скрытый текст
Скрытый текст


Цитата
Максим Зеленский написал:
чему равен 3 в 10 строке???
3-е значение из набора  , ну подумаешь - упустил, что индексация с 0. Работает же ведь    
Изменено: Андрей Лящук - 21.07.2020 22:18:02
 
Не сочтите за наглость  :oops:
Вроде получилось код Андрея адаптировать по свои данные (точнее данные под код)
Осталось одно НО:
Цитата
Максим Зеленский написал:
Подвох:В Данных столбцы проверки заполнены всегда, пустых значений в них нет.
В Справочнике не в каждой строке заполнены значения в столбцах Продукт, Тип, Канал. Например, может быть не заполнен столбец Тип, что означает "любой Тип". Или столбец Канал, что означает "любой Канал". Может быть не заполнено несколько столбцов. В конце концов, встречаются условия только по одному столбцу.
Логика подстановки должна быть такой, что сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо.
По задаче Максима - сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо.
А мне необходимо проверять по порядку. (построчно по справочнику от первой сроки к последней).
Помогите пожалуйста с справиться с этой жуткой задачей.
Страницы: 1 2 След.
Наверх