Страницы: 1
RSS
Power Query объединить таблицы по столбцу с приблизительным совпадением времени
 
Добрый день.
Есть две таблицы, в оранжевой - детализация части строк из синей. Сопоставить можно по столбцам "Поставщик + время операции", но время может отличаться, т.к. данные из разных источников.
Разница обычно не больше 1 минуты, но для надежности, допустим, до 3. Дельта может быть в любую сторону.

Округления до даты недостаточно, в один день может быть несколько операций с одним поставщиком.
Округлять до интервала минут - цифры могут попасть в разные интервалы и в разные часы могут. На этом мысль остановилась.
Помогите, пожалуйста.
PQ в экселе, приблизительного сопоставления нет.  
Изменено: Xel - 26.01.2021 15:57:10
 
Попробуйте этот метод Прием . Возможно поможет
 
Xel, результат-то какой нужен?
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
результат-то какой нужен?
Привет, Алексей. Выдать на гора определение, что такое приблизительно совпадающее время. А там и решение приложится :)
 
Цитата
PooHkrd написал:
Xel , результат-то какой нужен?
Просто объединенные таблицы, наваляла руками, надеюсь, без ошибок.
С этим я уже знаю, что делать, там в исходной таблице еще есть столбцы, информацию из которых надо "привязать" к конкретным купленным товарам, а не к документам.
Изменено: Xel - 26.01.2021 16:34:17
 
Цитата
Андрей VG написал:
Выдать на гора определение, что такое приблизительно совпадающее время
В таблице 2 находятся строки с тем же поставщиком и временем, отличающимся не более, чем на 3 минуты.

FuzzyLookup я почти приспособила к делу, спасибо. Но вдруг есть какой-нибудь фантастический фокус, который можно провернуть прямо в PQ без нечеткого поиска :oops:  
FL для такой арифметики кажется слишком могучим (Excel 2016  FL есть только в виде надстройки)
Изменено: Xel - 26.01.2021 16:39:09
 
Цитата
Xel написал:
без нечеткого поиска
Это каким таким образом, если сопоставление нечёткое? Лобовое решение в пределах 180 секунд.
Код
let
    table1 = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    typedTable1 = Table.TransformColumnTypes(table1, {{"Поставщик", type text}, {"Сумма по документу", Int64.Type}, {"Время", type datetime}, {"товар", type text}}),
    table2 = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    typedTable2 = Table.TransformColumnTypes(table2,{{"Поставщик", type text}, {"Товар", type text}, {"Время", type datetime}, {"Сумма", Int64.Type}}),
    join = Table.NestedJoin(typedTable1, {"Поставщик"}, typedTable2, {"Поставщик"}, "temp", JoinKind.LeftOuter),
    filter = Table.AddColumn(join, "t2", (rec) => Table.SelectRows(rec[temp], each Number.Abs(Duration.TotalSeconds(_[Время] - rec[Время])) <= 180)),
    #"Removed Columns" = Table.RemoveColumns(filter,{"temp"}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Columns", "t2", {"Поставщик", "Товар", "Время", "Сумма"}, {"t2.Поставщик", "t2.Товар", "t2.Время", "t2.Сумма"})
in
    #"Expanded {0}"
 
Джойн только по поставщику и потом фильтр по разнице времени.  8-0
Спасибо огромное!  
 
Если в таблице2 время всегда позже чем в таблице1, то можно так:
Код
let
    T1 = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],{{"Время", type datetime}}),
    T2 = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],{{"Время", type datetime}}),
    Sorted = Table.Sort(Table.DuplicateColumn(T1, "Время", "время") & Table.DuplicateColumn(T2, "Время", "Время2"),{{"Поставщик", Order.Ascending}, {"Время", Order.Ascending}}),
    FilledDown = Table.FillDown(Sorted,{"Сумма по документу", "товар", "время"}),
    Filtered = Table.RemoveColumns( Table.SelectRows(FilledDown, each [Товар] <> null or [товар] <> "?"),{"Время"})
in
    Filtered

Если нет, то можно также, но надо будет группировкой докрутить малость.
Изменено: PooHkrd - 26.01.2021 18:38:53 (Не учел один нюанс со временем. Подправил код.)
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх