Power Query: Merge via Filter (Объединение таблиц по составному ключу с помощью фильтра)
ฝัง
- เผยแพร่เมื่อ 5 ต.ค. 2024
- Один из уроков онлайн-курса по Power Query от сертифицированного тренера Microsoft Сергея Ожиганова. Разбор реальной задачи ученицы.
Часть 2: • Power Query: Merge via...
Хотите научиться виртуозно решать задачи с помощью Power Query?
Полная версия курса тут: online.powerex...
Бесплатный онлайн-курс «Основы Power Query»: online.powerex...
Группа в ВК: sozhpq
Если выполнять только на кнопках - очень здорово )
Если с использованием языка - то после мерджа просто делаем еще доп.столбец с формулой такого типа: =Table.Max(Table.SelectRows([столбец мерджа], (r)=> r[Дата начала действия]
Максим, я ждал от Вас чего-то подобного :-) Круто! Завтра попробую. Спасибо!
Максим, не могли вы объяснить этот момент - (r)=>, как power query понимает, что эта r есть вложенная таблица??
@@MrPANTEL1987 видео с разбором дополнительных способов решения, включая идею Максима, в процессе подготовки. Выйдет на днях.
Power Excel Training отлично! Спасибо! Я встречал этот способ, но не могу уложить в голове как он работает
Вы обалденно четко и ясно объясняете!
Спасибо!
Одна из учениц курса упростила запрос qPartner на один шаг с помощью простейшей внимательности к интерфейсу. Честно, я туда и не посмотрел.
Пока это загадка, предлагаю всем подумать самостоятельно :-)
Светлана, респект!
merge по двум ключевым столбцам с "нечетким совпадением для слияния"?
Это же охрененно! Это же вот я прям с утра попробую на своей задаче на работе. Мне этот урок пока недоступен в полном курсе, но я уже на 100% уверен, что свою задачу я точно решу.
большое спасибо.очень толково
Очень элегантное решение))
Сергей, добрый день. Прошу разобрать пример, который довольно таки часто требуется на практике: разложить дебиторскую кредиторскую задолженность по срокам ее возникновения. Т.е. согласно одного и того же договора есть много платежей и накладных. И нужно отобрать те платежи (или накладные), которые остались не закрытыми. Закрытие по методу ФИФО.
очень интересно. только условие на дату надо ставить "меньше или равно"
Очень интересное решение. Спасибо большое за Ваш труд! Если есть возможность, разберите расчёт стажа работы, на основании двух дат в столбцах, в формате __ лет, __ месяцев, __ дней.
Елена, здравствуйте!
Спасибо за вопрос!
К сожалению в M отсутствуют необходимые функции, нужно будет "изобретать велосипед" с арифметикой из-за разного количества дней в году и месяце.
В отличии от M в языке DAX имеется необходимая функция DATEDIFF() (в Excel РАЗНДАТ())
Поэтому, я бы рекомендовал этот вопрос решать с помощью DAX.
Если надо обязательно в PQ сделать, то потребуется какое-то время на разработку решение. Попытался найти готовое решение в интернет - безуспешно, хотя может плохо искал.
В PQ попыталась сделать через логические функции работая с частями обеих дат (год, месяц, день), для года вроде получилось, а дальше пока не продвинулась, т.е. сравнивая меньше или больше месяц, день, и прибавляла к разнице для года 1, а для месяцев 12.
За информацию про DAX, спасибо, буду разбираться. Пока не успела подружиться с эти языком, буду учиться)) Спасибо за ваши уроки, благодаря Вам, постоянно совершенствую свои экономические формы отчётности.
И встроенная функция Возраст в PQ не дает корректный результат даже для расчёта количества лет, если я правильно поняла, потому что деление на 365 и соответственно не учитывается високосный год.
@@PowerExcelTrainingSOZH А в DAX расчёт без ошибок получается, а то разность дат в excel выдаёт результат с ошибками?
Классное решение! Спасибо!
Эх, а сколько же времени можно было сэкономить, если бы в power query добавили возможность ставить условия как при join в sql
Не знаю, приходит в голову, но не проверял.
Добавить индекс ( в первую таблицу)
Объеденить по коду клиента,
добавить вспомогательный столбец, (если "дата начала действия" меньше "даты" то 1 иначе 0),
отфильтровать по вспомогательному столбцу (=1) (правда тогда потеряем продажи, если данные во второй таблице не полные ;( ).
Отсортировать Код покупателя, дата продажи, дата начала действия ( от большего к меньшему).
удалить дубликаты по столбцу индекс и отсортировать по нему.
сработает?
Сколько времени считает если основная таблица примерно 300 000 строк а табл. условий 7000 строк? у меня пол дня! 16г оперативки 12 ядер. но на максимум они не используються. почемуто.
Скорее всего поможет формула Table.Buffer(). Таблицу, которую много раз фильтруем, сначала кэшурем этой формулой и PQ не будет каждый раз грузить её с листа.
Очень интересно узнать побольше про возможность создавать функции в П.К. и было бы очень интересно узнать подробнее об этом, если есть ссылка, пожалуйста поделитесь, с удовольствие изучу.
Константин, П.К. = PQ = Power Query?
Приходите на полный курс, там эта тема раскрывается в нескольких уроках на практических задачах.
13:32 А если отсортировать наоборот, то можно было не удалять строки, а сразу выбрать нулевую (первую) :) Мелочь, конечно.
Можно использовать фильтр "является последней", если тип колонки дата