Глава 9. Объединение таблиц и запросов Power Query
Это продолжение перевода книжки Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независящими, потому рекомендую читать поочередно.
Одна из традиционных заморочек, которую решают экспертам Excel – это объединение 2-ух таблиц данных, и следующее создание сводной таблицы. Как правило, обработка основывается на функциях ВПР() либо ИНДЕКС(ПОИСКПОЗ()). Power Query представил очередной относительно обычной способ объединения 2-ух таблиц. Представим, вы желаете соединить две Таблицы, расположенные на листе Excel:
Рис. 9.1. Начальные Таблицы на листе Excel
Скачать заметку в формате Word либо pdf, примеры в формате архива
Создание запроса-подключения
Чтоб соединить запросы, они должны существовать. Наличие Таблицы в Excel недостаточно; Power Query должен распознавать данные как запрос. Откройте файл Merge.xlsx. Чтоб загрузить таблицу Inventory в Power Query, щелкните всякую ячейку в ней, пройдите по меню Данные –> Из таблицы/спектра. Таблица будет импортирована в Power Query и отобразится в окне редактора. На вкладке Основная щелкните раскрывающийся перечень в нижней части клавиши Закрыть и загрузить. Изберите Закрыть и загрузить в… –> Лишь сделать подключение.
Запрос отображается в области Запросы и подключения книжки Excel, но Power Query не сделал новейшую таблицу ни на текущем, ни на новеньком листе Excel:
Рис. 9.2. Новейший запрос, сделанный лишь для подключения к Таблице; чтоб прирастить изображение кликните на нем правой клавишей мыши и изберите Открыть картину в новейшей вкладке
Если вы поторопились, и как обычно, кликнули Закрыть и загрузить, то на новеньком листе увидите новейшую Таблицу. Чтоб поправить ситуацию, щелкните правой клавишей мыши запрос на панели Запросы и подключения. В редакторе Power Query пройдите по меню Основная –> стрелочка на кнопочке Закрыть и загрузить –> Закрыть и загрузить в… –> Лишь сделать подключение. Power Query предупредит, что вы собираетесь удалить данные. Подтвердите.
И напротив, если вы сделали запрос лишь для подключения, то в предстоящем сможете через этот интерфейс добавить Таблицу на лист Excel.
Сейчас импортируйте в Power Query таблицы Sales. Аналогичным образом загрузите ее лишь создав подключение.
Объединение запросов
Данные –> Получить данные –> Соединить запросы –> Соединить. Раскроется диалоговое окно Слияние. Изберите таблицу Sales в верхнем раскрывающемся перечне, и таблицу Inventory – в нижнем. Но, клавиша Ok как и раньше не подсвечена:
Рис. 9.3. Вы избрали таблицы, но почему вы не сможете продолжить?
Снова пристально прочтите аннотацию, содержащуюся под заголовком окна. Power Query не понимает, какие поля вы желаете применять для выполнения слияния. Для вас необходимо в каждой таблице выделить столбцы с идентификатором. При этом так, чтоб в одной таблице этот столбец содержал неповторимые значения, а в иной таблице значения могут повторяться. Таковая связь именуется «один ко почти всем», и ее внедрение – наилучший метод гарантировать, что вы получите результаты, надлежащие вашим ожиданиям.
Power Query также поддерживает соединения один-к-одному и многие-ко-многим.
В нашем примере столбец SKU Number содержит неповторимые продукты в таблице Inventory. Столбце SKU Number представлен и в таблице Sales. Тут значения SKU Number могут повторяться много раз. Используйте этот столбец для связывания таблиц. Щелкните заголовок SKU Number в каждой таблице:
Рис. 9.4. Столбцы для связывания выбраны корректно
Направьте внимание, что предложенные вами столбцы для установления связи были испытаны Power Query, и одобрены. Если, к примеру, в таблице Sales вы выделите столбце Brand клавиша Ok, как и раньше будет неактивна, а в нижней части окна Слияние покажется сообщение о ошибке.
Нажмите клавишу Ok. Раскроется редактор Power Query, в котором 2-ая таблица представлена одним столбцом:
Рис. 9.5. Новейший (крайний) столбец таблицы Sales содержит надлежащие записи таблицы Inventory
Разверните столбец Inventory. Вопросец лишь в том, какие столбцы таблицы Inventory для вас необходимы. Итак, щелкните значок развернуть. Снимите галочку со столбцов, которые уже есть в таблице Sales (SKU Number and Brand), снимите флаг Употреблять начальное имя столбца как префикс, нажмите Ok. Сейчас сведения о продукте объединены с продажами:
Рис. 9.6. Детали из таблицы Inventory объединены с данными таблицы Sales
Переименуйте запрос OneToMany. Основная –> Закрыть и загрузить. На листе Excel отобразится 20 строк Таблицы, как если б свою работу выполнила функция ВПР:
Рис. 9.7. Слияние на базе связи один-ко-многим
Почти все-ко-многим
При выполнении процедуры слияния необходимо быть внимательным. Если вы попытаетесь выполнить слияние другим образом, вы обнаружите другой итог:
- Данные –> Получить данные –> Соединить запросы –> Соединить.
- Изберите Sales в высшей части, а Inventory– в нижней
- Щелкните заголовок Brand в каждой таблице
- Жать Ok
- Щелкните значок развернуть
- Снимите галочку со столбцов SKU Number and Brand
- Снимите флаг Употреблять начальное имя столбца как префикс
- Нажмите Ok
- Переименовать запрос ManyToMany
- Основная –> Закрыть и загрузить
Новенькая процедура слияния различается 2-мя качествами: (1) таблицы Sales и Inventory в окне Слияние переставлены местами, (2) для связи избран столбец Brand. Тем не наименее, в Таблице на листе Excel возникло 22 записи – на 2 больше, чем начальное количество транзакций. Чтоб осознать, почему это вышло, вернитесь в редактор Power Query, перейдите к первому шагу запроса ManyToMany (цифра 1 на рис. 9.8). Если вы перейдете к строке 19 и щелкните пробел справа от слова Table (2), вы увидите подготовительный просмотр данных в таблице, которые при слиянии будут объединены в таблице Sales (3).
Рис. 9.8. Слияние многие-ко-многим в действии
В прошлом слиянии вы связали данные на базе столбца SKU Number. В этом примере слияние осуществляется на базе столбца Brand. Но бренду OK Springs соответствует два артикула в таблице Inventory. На базе этого примера вы сможете узреть, что необходимо быть усмотрительным при разработке слияний, чтоб не попасться в ловушку многие-ко-многим.
Удаление дублей объектов
В состав типовых конфигураций 1С на обыденных формах заходит обработка "Поиск и подмена дублированных частей справочников". Она весьма комфортна, дозволяет за куцее время соединить справочники с схожими наименованиями либо по остальным схожим реквизитам. Но для решения неких задач данного функционала было не довольно, также были выявлены некие ошибки, которые не дозволяли поменять ссылки во всех отысканных объектах.
Авторская разработка "Удаление дублей объектов" создана для поиска дублированных частей по набору реквизитов и следующему слиянию отысканных дублей в единый элемент.
Имеет ряд преимуществ по сопоставлению с типовой обработкой:
(1) Допускается поиск дублей по нескольким реквизитам (включая обладателя и родителя для справочников).
Пример использования: соединить дубли подчиненных справочников. На практике использовалось для справочника "Единицы измерения номенклатуры" и "Свойства номенклатуры". Реквизитами для поиска дублей будут "Обладатель,Наименование". Если запустить поиск дублей лишь по одному реквизиту "Наименование", то все единицы измерения для различных номенклатур соединятся в одну, к примеру "Шт", что будет не корректно.
(2) Возможность подменять/соединять воединыжды дубли всех ссылочных объектов, не только лишь справочники (документы, счета БУ, планы видов черт, узлы обменов данных, счета расчетов, Бизнес-процессы и задачки).
Пример использования: поменять во всех местах один счет БУ на иной.
(3) При подмене ссылок обрабатываются общие реквизиты.
Пример использования: объединение организаций. В неких конфигурациях это общий реквизит, соответственно его также необходимо обрабатывать при подмене.
(4) При подмене ссылок в движениях документов удаляются дубли записей регистров сведений.
Пример использования: документ, создающий движения по зависимому регистру сведений (подчиненному регистратору). В движениях документа по регистру сведений из-за объединения дублей могут показаться дубли ключей записей регистра сведений, из-за этого движения документа не запишутся и подмена дублей вылетит с ошибкой.
(5) При обработке последовательностей документов добавлена проверка наличия документов (в типовой обработке возникала ошибка).
На практике были случаи, когда в последовательности документов было 0 документов, но типовая обработка обращалась к элементу по индексу 0, что приводило к ошибке. Вставлена проверка перед воззванием к элементам последовательности.
(6) при подмене в проводках корректно обрабатываются субконто забалансовых счетов.
На практике были подмены в проводках с забалансовыми счетами. Доработан функционал для замен в таковых проводках.
Применима для конфигураций на обыденных формах, для толстого клиента 1С. Тестировалась на платформе 8.3.10.2561, на прошлых платформах 8.2.x большая возможность корректной работы.
{Инструкция}
1. Поначалу необходимо избрать объект метаданных, в котором будем удалять дубли. По дефлоту обработка дает избрать один из справочников. Необходимо надавить на ". " в поле объект и избрать подходящий справочник.
Обработка также допускает выбор всех ссылочных метаданных (к примеру Документы, Планы видов черт и т.д.). Для этого необходимо надавить на "X" и попробовать избрать объект, будут предложены все ссылочные метаданные.
2. Разглядим на примере подчиненного справочника "Договоры контрагентов". Выберем Реквизит поиска "Наименование". Нажмем клавишу "Отыскать дубли".
В последующем окне покажутся задублированные элементы по наименованию. По сути неправильно соединять воединыжды все Главные контракта в 1, т.к. они принадлежат различным контрагентам.
Вернемся к началу поиска. Выберем очередной реквизит поиска "Обладатель" (при выбирании реквизиты добавляются через ",").
Опосля прибавления Обладателя в реквизиты поиска подборка дублей сократилась. Сейчас договоры разбиты по группам, в которых они имеют однообразное наименование у 1-го обладателя. В этом случае корректно соединять воединыжды такие дубли.
В поисковых результатах в верхней таблицы перечисляются группы дублей, в нижней — состав группы.
Запустим автоопределение правильных частей (В группах дублей определяется элемент, который почаще употребляется).
Опосля автоопределения правильных частей в каждой группе будет избран элемент с большим количеством ссылок. Исполняем подмену.
В последующем окне обработка отыщет все ссылки на дубли (не считая ссылок на верный элемент) и предложит выполнить подмену на верный элемент.
Жмем "Выполнить подмену частей".
Подмена может занять некое время, в зависимости от количества ссылок.
При успешном выполнении дубли не покажутся в последующем поиске.
Плюсы
(1) допускается поиск дублей по нескольким реквизитам (включая обладателя)
(2) при подмене ссылок обрабатываются общие реквизиты
(3) при подмене ссылок в движениях документов удаляются дубли записей регистров сведений.
Гарантия возврата средств
ООО «Инфостарт» гарантирует Для вас 100% возврат оплаты, если программка не соответствует заявленному функционалу из описания. Средства можно возвратить в полном объеме, если вы заявите о этом в течение 14-ти дней со денька поступления средств на наш счет.
Программка так испытана в работе, что мы с полной уверенностью можем отдать такую гарантию. Мы желаем, чтоб все наши покупатели оставались довольны покупкой.