Соединение двух таблиц, имеющих отношение "многие ко многим" в powerpivot
У меня к вам вопрос, как соединить две таблицы, которые имеют отношение «многие ко многим». См. Две таблицы ниже:
Я бы хотел иметь таблицу, которая дает мне для каждого SKU полный список типов категорий. Итак, для двух приведенных выше таблиц результат будет следующим:
В настоящее время я пытался сделать это, вставив две таблицы в powerpivot и добавив между ними фиктивную таблицу (см. Ниже), но безуспешно. Я открыт для предложений — не обязательно в Powerpivot, но это был бы мой предпочтительный инструмент.
Заранее спасибо за вашу помощь!
Ответы 1
Это невозможно сделать в Power Pivot, так как у вас есть несколько идентификаторов ключей в обеих таблицах. Однако это можно сделать в Power Query, а затем проанализировать в сводной таблице.
В качестве ссылки на Power Pivot я предполагаю, что вы используете Excel 2016.
По очереди открывайте каждую из таблиц, так как таблицы данных PQ — по сути, открывайте, а затем немедленно закрывайте их — вы просто пытаетесь загрузить каждую из них в модель данных. В идеале (без дублирования) вам нужно «только загружать», а не «загружать и сохранять».
Здесь есть несколько вариантов в зависимости от ваших конкретных требований: вы можете открыть одну из таблиц PQ, вы можете выбрать таблицу по ссылке (опять же, без дублирования) или просто объединить две таблицы. в PQ «Merge» выберите общие (ссылки) поля данных
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Создание связи между таблицами PowerPivot
Связь – это соединение между двумя таблицами с данными, которое определяет зависимость между ними. Аналогом связи в PowerPivot являются функции подстановки в Excel (ВПР, ГПР, ИНДЕКС). К примеру, если вернуться к предыдущей статье об импорте данных в PowerPivot, у нас имеется основная таблица с данными о продажах в каждом магазине и подстановочная таблица, с информацией о каждом магазине (Название, регион и т.д.). Обе эти таблицы могут быть связаны через номер магазина. Если бы мы работали в Excel и нам понадобилось напротив каждой записи о продаже проставить в каком регионе это произошло, одним из вариантов решения данной задачи было бы использование формулы подстановки ВПР:
=ВПР(номер_магазина; таблица_StoreInfo; 6; ЛОЖЬ)
В PowerPivot подобные задачи решаются путём установления связей между таблицами.
Связи в PowerPivot могут быть созданы двумя путями: вручную, соединив две таблицы в окне PowerPivot или поля в окне Диаграмм, или автоматически, если PowerPivot обнаружит существующие связи во время импорта. Для создания связей вручную с помощью соединения колонок двух разных таблиц необходимо, чтобы данные в колонках были идентичными. К примеру, колонка с номером магазина (StoreID) в основной таблице и колонка с номером магазина в подстановочной таблице (Store) имеют одинаковые данные. Не обязательно при этом чтобы названия колонок в таблицах совпадали.
Зачем создавать связи
Для того чтобы выполнить какой-нибудь значимый анализ, необходимо, чтобы источники данных были связаны. В частности, связи позволяют вам:
- Фильтровать данные таблиц с колонками из связанных таблиц
- Интегрировать столбцы из нескольких таблиц в отчете сводной таблицы
- Находить значения в связанных таблицах с помощью формул DAX
Создание связи с помощью Конструктора
- Вы будете связывать одну колонку в основной таблице с колонкой в подстановочной таблице. Для упрощения процесса создания связи, выделите ячейку в колонке, которую вы хотите связать, в основной таблице.
- Перейдите по вкладке Конструктор в группу Связи –> Создание связи
- В появившемся диалоговом окне Создание связи вы увидите выбранную таблицу и колонку в первых двух полях
- Если вы пропустили первый шаг, в полях Таблица и Столбцы могут быть некорректные данные. Выберите таблицу Demo и столбец StoreID.
- В поле Связанная таблица подстановки в выпадающем меню выберите StoreInfo, в поле Связанный столбец подстановки выберите Store.
- После щелчка Создать, две таблицы будут связаны.
Создание связей в представлении диаграммы
Представление диаграммы создано для упрощения создания связей между двумя таблицами. Здесь можно визуально увидеть, как все таблицы соотносятся друг с другом.
- В окне PowerPivot перейдите по вкладке В начало в группу Просмотр –> Представление диаграммы. Экран представления данных поменяется на представление диаграммы, где вы увидите все существующие связи между таблицами.
- Щелкните правой кнопкой мыши по основной таблице, в выпадающем меню выберите Создать связь.
- В появившемся диалоговом окне Создание связи, укажите таблицы и столбцы, которые должны быть связаны (как описывалось выше).
- Проверьте список созданных связей, перейдя по вкладке Конструктор в группу Связи -> Управление связями.
СОВЕТ: Для быстрого создания связи выберите столбец, который необходимо связать, и перетащите его в столбец другой таблицы, с которым необходимо его связать.
Создание связей в представлении схемы Power Pivot
Работа с несколькими таблицами позволяет сделать данные более интересными и релевантными для сводных таблиц и отчетов, которые их используют. При работе с данными в надстройке Power Pivot вы можете использовать представление диаграммы для создания подключений между импортированными таблицами и управления ими.
Создание связей между таблицами необходимо для того, чтобы каждая таблица имела столбец, содержащий совпадающие значения. Например, при соединении разделов «Клиенты» и «Заказы» каждая запись раздела «Заказ» должна будет содержать код клиента или идентификатор, разрешенные для одного клиента.
В окне Power Pivot выберите Представление диаграммы. Макет электронной таблицы «Представление данных» изменится на макет визуальной диаграммы, а все таблицы будут автоматически упорядочены на основе их связей.
Щелкните правой кнопкой диаграмму таблицы и выберите пункт Создание связи. Откроется диалоговое окно «Создание связи».
Если таблица из реляционной базы данных, то столбец будет предустановлен. Если не выбран ни один столбец, выберите один из таблицы, содержащей данные, которые будут использоваться для корреляции строк в каждой таблице.
В поле Связанная таблица подстановки выберите таблицу, содержащую хотя бы один столбец данных, связанный с таблицей, выбранной в поле Таблица.
В поле Столбец выберите столбец, содержащий данные, относящиеся к столбцу в поле Связанный столбец подстановки.
Нажмите кнопку Создать.
Примечание: Хотя Excel проверяет соответствие типов данных между каждым столбцом, он не проверяет наличие в столбцах соответствующих данных и создает отношение, даже если значения не соответствуют. Для проверки связи создайте сводную таблицу, содержащую поля из обеих таблиц. Если данные неправильные (например, пустые ячейки или одинаковые значения повторяются в каждой строке), необходимо выбрать разные поля и, возможно, разные таблицы.
Найдите связанный столбец
Если модели данных содержат много таблиц или таблицы содержат большое количество полей, может быть сложно выбрать столбцы для использования в связях таблицы. Одним из способов нахождения связанного столбца является нахождение его в модели. Этот метод удобен, если известно какой столбец (или ключ) необходимо использовать, но вы не уверены, включает ли в себя столбец другие таблицы. Например, таблицы фактов в хранилище данных обычно содержат много ключей. Можно начать с ключа в этой таблице и затем приступить к поиску модели для таблиц, содержащих этот же ключ. Любую таблицу, содержащую соответствующий ключ, можно использовать в связях для таблицы.
В окне Power Pivot нажмите кнопку Найти.
В окне функции Найти введите ключ или столбец в качестве условия поиска. Элементы поиска должны состоять из имени поля. Нельзя выполнять поиск по характеристикам столбца или типам данных, содержащихся в них.
Щелкните поле Показать скрытые поля во время поиска метаданных. Если ключ был скрыт для уменьшения помех в модели, он, возможно, не отобразится в окне функции «Представление диаграммы».
Нажмите кнопку Найти далее. Если совпадение найдено, столбец в диаграмме таблицы будет выделен. Сейчас известно, какая таблица содержит совпадающий столбец, который может быть использован в связях таблицы.
Изменение активной связи
Таблицы могут иметь несколько связей, но только одна может быть активной. Активные связи используются по умолчанию в вычислениях DAX и навигации по сводному отчету. Неактивные связи могут быть использованы в вычислениях DAX посредством функции USERELATIONSHIP. Дополнительные сведения см. в записи функции USERELATIONSHIP (DAX).
Многочисленные связи существуют, если таблицы были импортированы способом, при котором в исходном источнике данных были заданы многочисленные отношения для этой таблицы, или если вручную были созданы дополнительные связи для поддержки вычислений DAX.
Для изменения активной связи используйте неактивное отношение. Текущая активная связь автоматически станет неактивной.
Наведите указатель на линию связей между таблицами. Неактивная связь отобразится в виде пунктирной линии. (Связь неактивна, потому что между двумя столбцами уже существует косвенная связь.)
Щелкните правой кнопкой линию и выберите функцию Пометить как активную.
Примечание: Активировать отношение можно, только если нет других отношений между двумя таблицами. Если таблицы уже связаны, но нужно изменить режим соотношения, необходимо сначала пометить текущую связь как неактивную, а затем активировать новую.
Размещение таблицы в представлении диаграммы.
Чтобы увидеть все таблицы на экране, щелкните значок По размеру экрана, находящийся в правом верхнем углу представления диаграммы.
Для настройки удобного отображение используйте элемент управления Перетащите для увеличения и мини-карту и перетащите таблицы в необходимый макет. Для прокрутки экрана также можно использовать полосы прокрутки и колесо мыши.