Страницы: 1
RSS
Странное объединение запросов в Power Query. Внешнее соеднинение слева не работает как ВПР
 
Офис 2013, PQ версия 2.59.51.35.201 (вроде бы последняя).
Имеются 2 таблицы вида:
Таблица 1:
                                     
СтрокиЗначения
Стр 11
Стр 22
Стр 33
Таблица 2:
                             
Строки 2Значния 2
Стр 111
Стр 1111
Если объединить эти таблицы через Слияние по ключу Строки с Типом соединения Внешнее слева, то согласно описанию должны получить аналог ВПР, но получаем таблицу:
                                                                                                                                           
СтрокиЗначенияТаблица2.Строки 2Таблица2.Значния 2
Стр 11Стр   111
Стр 11Стр 1111
Стр 22
Стр 33
Т.е. вывод ВСЕХ совпадений из таблицы №2 с дублированием первой строчки из таблицы №1. Для ВПР вывести все совпадающие строки без фокусов - это нереально. Понятно, что можно ввести столбец-счетчик и сливать по двум столбцам, но почему все ( в.тч. и Николай в своей книге) упорно пишут про аналог ВПР, когда это не так ? Или я что-то не так делаю ?
 
Join это не аналог ВПР, хотя и похоже. Аналог ВПР это совместное использование Table.Join и Table.Group. Если вы используете для слияния кнопки интерфейса, то при разворачивании столбцов обратите внимание что можно указывать как агрегировать значения, если в правой таблице найдено строк более одной.
Цитата
Veniamin Loginov написал:
Или я что-то не так делаю ?
Помимо книжек, где все объясняется в форме, понятной для начинающих, нужно еще смотреть справочники от производителя инструмента. Там это все указано. Хоть и не в очевидной форме.  ;)
З.Ы. Проверил книжку - да, у Николая этот момент упущен. И, кстати, этот момент упущен не только у него. Видел не мало курсов, где про такой нюанс тренера забывают.
Изменено: PooHkrd - 21.01.2020 10:45:00
Вот горшок пустой, он предмет простой...
 
PooHkrd, Можно поподробнее про дополнительную агрегацию ? В редакторе PQ после того, как нажимаю на Развернуть таблицу особо выбора нет. Только Expand и Aggregate где можно только сложить и посчитать, но не вывести 1е совпадение.
У Николая так и написано в книжке (стр. 96) - Этот тип слияния имитирует поведение классической функции ВПР, т.е. на выходе мы получим все элементы из первой таблицы и в дополнительном столбце найденное совпадение из второй таблицы.  Найденное - т.е. одно и менее. И да, практически везде указывают именно так - аналог ВПР.
Изменено: Veniamin Loginov - 21.01.2020 10:56:07
 
Ну как вариант - заранее убрать дубликаты из 2 таблицы (тем более, что и ВПРкой работать по списку с дублями не самый логичный поступок).
 
Цитата
Veniamin Loginov написал:
Можно поподробнее
Можно, но где пример того что есть и того, что хочется получить на выходе? Желательно с описанием логики агрегации строк правой таблицы если их более одной: мин/макс/первую сверху/первую снизу/что-то еще?
Вот горшок пустой, он предмет простой...
 
PooHkrd, Пример в первом сообщении. Собственно, уже понятно, что ВПР это не про join и перед объединением придется группировать вторую таблицу по первому столбцу. Вы упоминали про возможность выбора после сопоставления. Интересует этот момент. Я его не нашел.
 
Цитата
Veniamin Loginov написал:
Пример в первом сообщении.
Если пример в первом сообщении тогда решение такое:
Код
= Table.Join( Таблица1, {"Строки"}, Table.Group( Таблица2, {"Строки 2"}, {{"Значения 2", (x)=>x[#"Значния 2"]{0}? }} ), {"Строки 2"}, JoinKind.LeftOuter )
Изменено: PooHkrd - 21.01.2020 11:27:09
Вот горшок пустой, он предмет простой...
 
PooHkrd, Я так полагаю, это не кнопками натыкано ? :-)
 
Veniamin Loginov, чтобы тыкать кнопками, нужен как минимум файл-пример с данными. А вы мне не оставили выбора. Тут еще бывают персонажи, на картинках примеры предоставляют, так им решения тоже в картинках выдают.  ;)
Изменено: PooHkrd - 21.01.2020 11:45:29
Вот горшок пустой, он предмет простой...
 
PooHkrd, Намек понял, но к своему стыду не нашел кнопки вложения.
 
Показываю секретную кнопку:
Вот горшок пустой, он предмет простой...
 
Ясно :-)
Еще один мамонт с вашего позволения - после создания соединения можно как-то поменять его тип или только удалять и делать заново ?
Изменено: Veniamin Loginov - 21.01.2020 12:04:20
 
Veniamin Loginov, попробуйте так
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Merge = Table.NestedJoin(Источник,{"Строки"},Таблица2,{"Строки 2"},"Таблица2",JoinKind.LeftOuter)
in
    Table.AddColumn(Merge, "впр", each try [Таблица2]{0}[Значния 2] otherwise null)
 
artyrH, можно так
Код
[Таблица2][Значния 2]{0}?

Без try.
Вот горшок пустой, он предмет простой...
 
PooHkrd, Жму лапу, ставлю банку алтайского меда :-). Уже догадался о таком варианте из вашего первого сообщения. Самое изящное решение. Его бы в книжки добавить. А вводят в смуту.
 
Цитата
PooHkrd написал:
Без try
я знаю что ошибки в Excel не отразятся. добавил чтоб у автора темы не возникло вопросов - что это за error, а про знак вопроса совсем забыл :)  
Изменено: artyrH - 21.01.2020 16:02:05
 
Цитата
Veniamin Loginov написал:
Его бы в книжки добавить.
Если мне не изменяет мой склероз есть такая книжка от гражданина Ken Puls
M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query
Вот горшок пустой, он предмет простой...
 
Продолжу тему слияний.

Классика. Есть две таблицы:
ID Котлеты
1 5
26
37
48
IDМухи
29
310
411
После внешнего соединения слева разворачиваю таблицу и получаю следующее

IDКотлетыМухи
26 9
37 10
48 11
Да, в таблице с мухами нет ID=1. И что? Зачем строку удалять? Кто это придумал?

= Table.NestedJoin(#"Измененный тип2",{"ID"},#"табличка с мухами",{"ID"},"Мухи",JoinKind.LeftOuter)
= Table.ExpandTableColumn(#"Объединенные запросы", "Мухи", {"Мухи"}, {"Мухи"})

Подскажите, пожалуйста, что и куда нужно дописать чтобы все строки из таблицы с котлетами остались на месте после слияния независимо от того есть ключ во второй таблице или нет?

Нужно вот так:
ID КотлетыМухи
15 null
26 9
37 10
48 11
Изменено: А_ндрей - 27.04.2020 19:02:01
 
Цитата
А_ндрей написал:
что и куда нужно дописать
вы наверное хотели написать - приложить?
Цитата

2.3. Приложите файл(ы) с примером (общим весом не более 300Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе.
Ещё бы рекомендовал вам использовать кнопку <> для оформления кода.
 
А_ндрей, ваша проблема не воплотилась :)  
 
Михаил Л, вы совершенно правы.

После вашего сообщения решил попробовать на десяти строчках реальных данных. В общем после разворачивания в таблице сбивается сортировка и пустые строки просто уезжают вниз таблицы. facepalm.

Спасибо.
Страницы: 1
Наверх