Страницы: 1
RSS
Power Query Merge c условием
 
Коллеги, добрый день. Направьте пожалуйста, в какую сторону подумать для решения задачки. Имеется справочник с деталями по каждой строке, и есть справочник 2 для категорий выборки, но для отбора заполнены там только часть полей. Задача отобрать из справочника1 позиции по критериям справочника 2.  Если бы в таблице категорий были бы заполнены все поля, помог бы простой Merge  по этим полям. Как учесть условие: если значение в сравниваем поле категорий = null, то не фильтруем выборку по этому полю?
Возможно по-другому надо решать, не объединением таблиц, выслушаю любой совет! Пример прилагаю.
 
Анна, добрый день!
Код
let
    Источник = признаки,
    add = Table.AddColumn(Источник, "Пользовательская", 
        each let
                  a = List.Skip(List.Select(Record.FieldValues(_), each _ <> null),1),
                  b = Каталог,
                  c = Table.SelectRows(b, each List.ContainsAll(Record.FieldValues(_),a))
              in
                  c
    )
in
    add

upd. хотя подумал сейчас, если у двух характеристик может быть одинаковое значение, например два раза "широкий", то может выдать некорректное значение.
А вообще, как у первой категории в желаемом результате вышло 173  -_- значит я задачу не понял
Изменено: whateverlover - 26.09.2022 16:56:26
 
whateverlover, пока только посмотрел задачу ТС.
При различии значений всех параметров задача тривиальна. Решаемо и при наличии одинаковых значений (ну будет там куб, дшв одинаковые :) ), но пока жопка с оптимальным кодом :) .
Давайте совместно подумаем :)
Изменено: surkenny - 26.09.2022 17:02:11
 
whateverlover, спасибо большое, протестирую на разных сочетаниях
 
surkenny, пока просто рассуждение  :D
берем record, убираем из него все values = null (как  :qstn: )
берем record из второй таблицы, убираем из него поля, которых нет в первом record
сравниваем values обоих записей)
 
whateverlover, так я и не спорю. Проблема будет в
a null 1
b 1 null
Обе записи попадут в критерий Длина = 1
 
surkenny, а вот так?
Код
let     
    rec1 = [price = "a", color = null, length = 1],
    rec2 = [price = "b", color = 1, length = null],
    trimnull = Record.SelectFields(rec1, Table.SelectRows(Record.ToTable(rec1), each [Value] <> null)[Name]),
    make_rec2_same_as_rec1 = Record.SelectFields(rec2, List.Intersect({Record.FieldNames(rec2),    Record.FieldNames(trimnull)})),
    res = trimnull = make_rec2_same_as_rec1
in
    res

upd. ну я немного не то сделал, но суть в том, что rec2 не попадет под length = 1, не смотря на то, что 1 есть в color...дома буду, надо попробовать на примере Анны  :)
Изменено: whateverlover - 26.09.2022 17:54:24
 
Код
let
    step1 = признаки,
    step2 = Table.AddColumn(step1, "Количество", each 
        [a = Record.SelectFields(_,List.Skip(Table.SelectRows(Record.ToTable(_), each [Value] <> null)[Name],1)),
         b = Table.SelectRows(Каталог, each Record.SelectFields(_, List.Intersect({Record.FieldNames(_), Record.FieldNames(a)}))=a),
         c = Table.RowCount(b)
        ][c]
    )[[Наименование],[Количество]]
in
    step2

Анна, протестируйте этот вариант и откройте наконец тайну, почему Категория1 = 173.
 
whateverlover,  спасибо большое, протестирую и напишу как все прошло) хотя у меня и самый первый Ваш вариант сработал, по крайней мере на ограниченном наборе данных. А 173 это количество зеленых широких предметов (по признакам категории1)  
Изменено: Анна - 26.09.2022 23:38:19
 
whateverlover, вот так будет пошустрее (по сути разница в шаге b: при объединении записей значение одноименных полей берется из второй записи):
Код
let
  step1 = признаки, 
  step2 = Table.AddColumn (
    step1, 
    "Количество", 
    ( r ) =>
      [
        a = Record.SelectFields (
          r, 
          Table.SelectRows ( Record.ToTable ( Record.RemoveFields ( r, { "Наименование" } ) ), ( row ) => row[Value] <> null )[Name]
        ), 
        b = Table.SelectRows ( Каталог, ( row ) => row = row & a ), 
        c = List.Sum ( b[Количество] )
      ][c]
  )
in
  step2
Изменено: surkenny - 27.09.2022 01:28:57
 
surkenny, whateverlover, спасибо, работает! Подскажите пожалуйста, как этот этап расширить - в рабочей задаче в таблице каталок много других служебных столбцов, которые также нужно подтянуть, вместе с суммой (суммировать буду на след этапе по группировке отобранного и служебных столбцов):
    b = Table.SelectRows ( Каталог, ( row ) => row = row & a ), // здесь  происходит mapping и в столбцы mappingа добавляются данные из каталога, так?
След шагом нужно чтобы затянулись также другие столбцы из каталога (в примере который делала нет, но предположим это Год, Менеджер, Сумма)
PS Пока писала, кажется поняла что делать)
Изменено: Анна - 27.09.2022 11:45:51
 
Цитата
Анна написал:
здесь выводятся только столбцы, по которым происходит mapping, так?
Не совсем, тут лишь выбираются строки, которые соответствуют критериям, указанным в "признаки".
Для понимая вместо [c] укажите [b] и увидите, что этот шаг вывел.
А вот шаг "с" уже формирует то, что мы в итоге и получаем, только Количество.
Но у меня с воображением туго, могу только на примере подсказать.
Изменено: whateverlover - 27.09.2022 11:43:28
 
Цитата
Анна написал:
здесь выводятся только столбцы, по которым происходит mapping, так?
Нет. Здесь мы делаем объединение всех полей записи (строка таблицы - это запись, наименование полей - это наименования столбцов таблицы) строки таблицы Каталог с записью a. В записи a мы удаляем поле Наименование (в этот список можно еще через запятую добавить другие поля, которые не относятся к фильтрующим критериям) и оставляем записи с непустыми значениями. В итоге все записи строки таблицы Каталог, по именам совпадающие с записью a, будут заменены на значения из a.
То есть если строка row = [Год=2022, Код = 1, ЕщеЧтоТо = 10, Длина=10, Ширина = 15], а запись a = [Длина = 15], то на выходе имеем row & a = [Код=2022, Код = 1, ЕщеЧтоТо = 10, Длина=15, Ширина = 15] (Значение поле Длина заменилось на значение этого поля из записи a, остальные поля без изменений, так как их не было в записи a). Теперь просто сравниваем row и row & a. Приведенная для примера выше строка не подойдет:
[Год=2022, Код = 2, ЕщеЧтоТо = 12, Длина=10, Ширина = 15] & [Длина = 15] <> [Код=2022, Код = 2, ЕщеЧтоТо = 12, Длина=10, Ширина = 15]
А вот такая подойдет:
[Год=2022, Код = 2, ЕщеЧтоТо = 12, Длина=15, Ширина = 15] & [Длина = 15] = [Код=2022, Код = 2, ЕщеЧтоТо = 12, Длина=15, Ширина = 15].
В шаге b сохранены все столбцы таблицы Каталог, удовлетворяющие текущим значениям критериев таблицы Признаки. Далее уже делайте с ними все, что хотите.
Изменено: surkenny - 27.09.2022 11:57:28
 
surkenny, благодарю, все получилось :)  
Страницы: 1
Наверх