Power Query: как найти продажи без товара в справочнике

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

Сначала договоритесь, какая таблица главная

В этой задаче каждая строка продаж должна остаться в результате. Поэтому начинайте с левого внешнего объединения: слева продажи, справа справочник. В редакторе Power Query выберите запрос продаж, команду объединения запросов, справочник и одинаковые ключевые столбцы. Тип ключа должен совпадать: текстовый артикул нельзя надёжно сопоставить с числом, которое потеряло ведущие нули.

До объединения проверьте уникальность артикула в справочнике. Если цена зависит от периода или склада, один артикул не описывает нужную строку. Сначала определите полный ключ и только затем присоединяйте значения.

Сохраните признак отсутствующего совпадения

После объединения Power Query создаёт вложенную таблицу для каждой строки. До раскрытия можно посчитать число совпадений. Ноль означает неизвестный ключ, единица — однозначное совпадение, больше единицы — неоднозначный справочник. В M операция выглядит так; Sales и Products — уже подготовленные запросы с текстовым SKU.

let
    Joined = Table.NestedJoin(Sales, {"SKU"}, Products, {"SKU"}, "Product", JoinKind.LeftOuter),
    Checked = Table.AddColumn(Joined, "MatchCount", each Table.RowCount([Product]), Int64.Type)
in
    Checked

Что делать с неизвестной себестоимостью

Раскройте стоимость для совпавших строк, а записи с MatchCount=0 вынесите в отдельный запрос ссылкой. В нём сгруппируйте неизвестные SKU и добавьте выручку или количество продаж: владелец справочника увидит приоритет исправления. Не подменяйте неизвестное значение нулевой стоимостью, если такой смысл не задан правилами учёта.

Выручку можно показать полностью, а маржу — только для строк с подтверждённой себестоимостью, с отдельным показателем покрытия. В отчёте должно быть видно, что часть товаров пока не оценена. Тогда пользователь не перепутает технический пропуск с высокой прибылью.

Примите обновление контрольными числами

Для маленькой проверки возьмите продажи A на 100 и B на 200, а в справочнике оставьте только A. После левого объединения должны сохраниться две продажи и выручка 300. B появится в очереди неизвестных. После добавления B в справочник повторное обновление должно убрать его из очереди без ручного редактирования результата запроса.

Сравните сумму до и после объединения. Если она выросла, ищите несколько совпадений справа. Если уменьшилась, проверьте тип объединения и фильтры после раскрытия. Удобно хранить контрольный итог отдельным запросом, чтобы проверка повторялась на следующем месяце.

В рабочем процессе назначьте владельца справочника. Аналитик сообщает отсутствующие ключи, владелец добавляет корректные сведения, отчёт обновляется повторно. Исправление в итоговой таблице Excel пропадёт при обновлении, поэтому править надо источник или отдельную управляемую таблицу корректировок.

Соберите обновляемый отчёт на практике

В платном курсе вы объедините выгрузки со справочниками и научитесь проверять отсутствующие ключи. В практикуме по продажам и марже нужно обновить отчёт при появлении нового товара и сверить контрольные числа. Это позволяет отработать весь цикл: найти пропуск, исправить источник и получить проверенный отчёт следующего месяца.

Откройте программу курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».

Начните с пробного урока о рабочей книге и первом запросе Power Query.

Для следующего этапа: объединение файлов из папки и обновление отчёта.