Давненько ничего не спрашивал, но тут что-то завис и не могу подобраться к решению
Есть таблица Данные, в ней много разных столбцов, в том числе столбцы Продукт, Тип, Канал. Есть таблица Справочник, в ней столбцы Продукт, Тип, Канал и Код.
Задача: подтащить Код из Справочник в Данные, не используя в коде PQ или DAX возможные значения столбцов Продукт, Тип, Канал, так как справочник может более-менее меняться, и придется править код. Решаться будет в Power BI, поэтому только Power Query или DAX. В реальной задаче столбцов с условиями до 10 в обоих таблицах.
Подвох: В Данных столбцы проверки заполнены всегда, пустых значений в них нет. В Справочнике не в каждой строке заполнены значения в столбцах Продукт, Тип, Канал. Например, может быть не заполнен столбец Тип, что означает "любой Тип". Или столбец Канал, что означает "любой Канал". Может быть не заполнено несколько столбцов. В конце концов, встречаются условия только по одному столбцу. Логика подстановки должна быть такой, что сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо. На картинке нагляднее. Там вместо пустых значений стоят *, но это не принципиально.
В общем, чтой-та я подвис. Способов перепробовал уже довольно много, ни один не стоит показа, так как не привел к результату.
ЗЫ Заполнить справочник всеми необходимыми сочетаниями - крайний вариант, очевидный и оставлен как самый хреновый. Но дополнить справочник какой-то информацией можно.
(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, спасибо! Вот что значит "глаз замылился". Выглядит неплохо, допилю буферами и списком столбцов справочника. Пока лучше ничего не приходит в голову всё равно.
можно в PQ сделать 3 составных ключа дополнительно и, соответственно, 4 связи в PP. То же самое будет, просто чуть проще на восприятие. А так да, это "топорное" решение!
Максим Зеленский, я неверно представил себе идею в голове. Попробовал сделать, и получается, что 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"
Доброе время суток. Версия на Join в предположении, что Тип и Канал никогда не содержат какого-нибудь одинакового значения. Updated Переделал для *. Тестовый вариант на 100000 с копейками строк. Код Максима 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"
Михаил Л написал: Я конечно извиняюсь, но Сумма по коду и Сумма по fn2 разные.
Не уловил, где? Берём исходник, подставляем и сравниваем с эталонным решением (я его только упорядочил по Продукт, Тип, Канал). Можно получить немного доказательств в чём вы нашли разность?
Михаил Л написал: Если же скачать Тестовый вариант
В принципе, да. Вариант Максима зависим от порядка строк в справочнике. Достаточно пересортировать справочник, чтобы получался иной результат . А надеятся на существование предпорядка.... К сожалению, ни вариант Максима, ни ваш вариант не разбирал.
Михаил Л написал: Канал и Тип звездочки отправить вниз таблицы
Добро, осталось придумать как это эффективно использовать Что-то тёзка мимо ходит, а у него всегда на такие классы задач были сногсшибательные решения.
anvg, Спасибо за вариант! поразбираю еще, кое-что не до конца прочувствовал. Вариант такой я продумывал, но немного останавливало то, что придется захардкодить названия столбцов. Не дай бог что-то добавят или удалят - надо переписывать... Я, конечно, не ставил такое ограничение, так что тут вопрос ко мне.
Цитата
anvg написал: в предположении, что Тип и Канал никогда не содержат какого-нибудь одинакового значения
Можете пояснить вот это ограничение, немного не понял?
Цитата
anvg написал: зависим от порядка строк в справочнике.
да, согласен. В принципе, переместить строки, в которых явно указан только продукт, вниз - не большая проблема. И да, придется задавать авторам справочника жесткое условие по очередности строк. Но можно попробовать разбить проверку на 2 части... чуть позже попробую наваять.
Максим Зеленский написал: Можете пояснить вот это ограничение, немного не понял?
Не обращайте внимание, текст относился к первому варианту, когда вместо * использовался null
Цитата
Максим Зеленский написал: Но можно попробовать разбить проверку на 2 части... чуть позже попробую наваять.
Тут основной вопрос в размере справочника. Чем больше, тем больше в нём ненужных просмотров. Формально предложенный алгоритм 4 * N * Log(M), где N число строк в Данных, M в Справочнике, плюс мелочовка поиска первого не null.
Я имел в виду вполне вероятный сценарий, когда в процессе эксплуатации изменится состав столбцов справочника. Например, было 8 столбцов с разными условиями, а стало 6 или 10. В своем варианте я бегаю по динамическому списку столбцов аккумулятором, в вашем, насколько могу судить, надо прикрутить аккумулятор к addDataKeys + expandDataKeys, т.е. там, где собираются столбцы . Еще поковыряю ваш код, да и в любом случае, задача может оказаться не одноразовой.
Интересно было бы посмотреть решение задачи на DAX, как исходно предлагалось в теме . Только алгоритм с Join для 2, 3 столбцов. При 4 нужно будет делать 8 Join, при 5 - 16 и так далее. Тогда лучше строить из Справочника дерево поиска. Вечером попробую набросать, если тёзка не опередит.
Андрей Лящук, спасибо, искал что-то такое! идея изначально шуршать не по Данным, а по Справочнику прям зудела, но, с другой стороны, вряд ли приводит к уменьшению количества просмотров.
кстати, чему равен 3 в 10 строке??? что-то мне справк не дает ответ. Occurrence.First = 0, Occurrence.Last = 1, Occurrence.All = 2, ???? = 3... Occurrence.Type тож ничего про это не знает. Судя по всему, всё, что >=2 будет Occurrence.All
Не сочтите за наглость Вроде получилось код Андрея адаптировать по свои данные (точнее данные под код) Осталось одно НО:
Цитата
Максим Зеленский написал: Подвох:В Данных столбцы проверки заполнены всегда, пустых значений в них нет. В Справочнике не в каждой строке заполнены значения в столбцах Продукт, Тип, Канал. Например, может быть не заполнен столбец Тип, что означает "любой Тип". Или столбец Канал, что означает "любой Канал". Может быть не заполнено несколько столбцов. В конце концов, встречаются условия только по одному столбцу. Логика подстановки должна быть такой, что сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо.
По задаче Максима - сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо. А мне необходимо проверять по порядку. (построчно по справочнику от первой сроки к последней). Помогите пожалуйста с справиться с этой жуткой задачей.