Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск ближайшего значения для требуемой даты в Power Query
 
#1
23.07.2019 12:44:59
Добрый день, уважаемые форумчане.
В диапазоне A2:C12 содержатся данные для объединения.
В примере 1 если объединять по 2 столбцам (Дата;ИНН) данные из Таблица2 и Таблица1 и будет точное совпадение то данные объединятся. (Результат: Таблица2_2)
В примере 2 в Таблице3 нет полного сопоставления строки с Таблица1 и данные из столбца [Наименование], не подтягиваются. (Результат: Таблица3_2)
Как можно объединить данные по нечеткому совпадения столбца [Дата] (найти ближайшее наибольшее значение ниже даты указанной в столбце I:I) и точному совпадению столбца [ИНН].
Можно ли как-то добиться результата как в диапазоне M2:O4
файл примера во вложении.

----------------
вроде как получило, но не знаю на сколько это надежно и как быстро будет работать на большом диапазоне данных (пример 8.1), может есть более оптимальный вариант решения.

Заранее спасибо.
Изменено: ymal_qeb - 23.07.2019 12:55:38
 
 
 
#2
23.07.2019 13:05:18
Вот так можно:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дата", type date}, {"ИНН", type text}, {"№ документа", type text}}),
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип",{"ИНН"},Таблица1,{"1.ИНН"},"Таблица1",JoinKind.LeftOuter),
    #"Добавляем столбец" = Table.AddColumn(#"Объединенные запросы", "Таблица2", each let d = [Дата] in Table.Max( Table.SelectRows( [Таблица1], each [1.Дата] <= d ), "1.Дата" ), type record),
    #"Разворачиваем запись" = Table.ExpandRecordColumn(#"Добавляем столбец", "Таблица2", {"Наименование"}, {"Наименование"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Разворачиваем запись",{"ИНН", "Таблица1"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы",{{"Наименование", "Сокращенное юр. наименование"}})
in
    #"Переименованные столбцы"
Вот горшок пустой, он предмет простой...
 
 
 
#3
23.07.2019 13:07:08
Доброе время суток
Ещё вариант
Код
let
    whatTable = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],{{"Дата", type date}, {"ИНН", type text}, {"№ документа", type text}}),
    whereTable = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],{{"Дата", type date}, {"ИНН", type text}, {"Наименование", type text}}),
    unionTable = whereTable & whatTable,
    neededOrder = Table.Sort(unionTable,{{"ИНН", Order.Ascending}, {"Дата", Order.Ascending}}),
    addIndex = Table.AddIndexColumn(neededOrder, "id"),
    getWhatOnly = Table.SelectRows(addIndex, each [#"№ документа"] <> null)[[Дата], [ИНН], [#"№ документа"], [id]],
    queryTable = Table.TransformColumns(getWhatOnly, {"id", each _ - 1}),
    result = Table.Join(queryTable, {"id"}, addIndex[[id], [Наименование]], {"id"}),
    #"Removed Columns" = Table.RemoveColumns(result,{"id"})
in
    #"Removed Columns"

Предполагается, что всегда есть меньшая дата в Таблица1 для даты в Таблица3 для данного ИНН.
Изменено: Андрей VG - 23.07.2019 13:08:48
 
 
 
#4
23.07.2019 13:20:56
Ну, и по мотивам варианта от Андрея и его ранее предлагаемых решений по поиску минмаксов оптимальным способом, вот такой вариант:
Код
let
    whatTable = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],{{"Дата", type date}, {"ИНН", type text}, {"№ документа", type text}}),
    whereTable = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],{{"Дата", type date}, {"ИНН", type text}, {"Наименование", type text}}),
    unionTable = whereTable & whatTable,
    SortedRows = Table.Sort(unionTable,{{"ИНН", Order.Ascending}, {"Дата", Order.Ascending}}),
    FilledDown = Table.FillDown(SortedRows,{"Наименование"}),
    FilteredRows = Table.SelectRows(FilledDown, each ([#"№ документа"] <> null)),
    RemovedColumns = Table.RemoveColumns(FilteredRows,{"ИНН"})
in
    RemovedColumns

аналогично, корректно будет работать только в случае наличия в Таблице1 дат более ранних чем в Таблице3.
Изменено: PooHkrd - 23.07.2019 13:22:33
Вот горшок пустой, он предмет простой...
 
 
 
#5
23.07.2019 13:42:04
PooHkrd, Андрей VG, спасибо Ваши варианты работают как надо.
 
 
 
#6
23.07.2019 14:36:42
Цитата
PooHkrd написал:
решений по поиску минмаксов
Коллега, спасибо за решение. Подобный подход устраняет одну очевидную проблему. Если две или более даты одного и того же ИНН в таблице запроса имеют одну и ту же ближайшую меньшую дату в таблице поиска, то мой вариант будет ошибочным для одной или более дат (для больших).
 
 
 
Страницы: 1
Наверх