Power pivot как объединить однотипные таблицы

Функции объединения таблиц в DAX: UNION, INTERSECT и EXCEPT в Power BI и Power Pivot

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы разберем 3 функции, которые способны создавать таблицы в DAX на основе объединения двух и более исходных таблиц. И это функции UNION, INTERSECT и EXCEPT в Power BI и Power Pivot.

Рассмотрим подробно каждую из них в отдельности.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

DAX функция UNION в Power BI и Power Pivot

UNION () — создает новую таблицу, объединяя любое количество таблиц с единой структурой столбцов. То есть, объединяет строки нескольких одинаковых таблиц в одну единую таблицу.

Пример формулы с использованием DAX функции UNION.

Примеры мы будем рассматривать в Power BI, так как в этой программе имеется возможность физического создания вычисляемых таблиц в модели данных — что нам нужно для наглядности демонстрации примеров.

В надстройке Excel (Powerpivot) в самой модели данных вычисляемые таблицы создавать нельзя. Они в Power Pivot создаются только виртуально, во время самого вычисления формулы.

Итак, в Power BI Desktop имеется несколько исходных таблиц с одинаковой структурой столбцов — «Продажи Отдел 1», «Продажи Отдел 2», «Продажи Отдел 3»:

Объединим все эти таблицы в одну общую при помощи DAX функции UNION, создав во вкладке «Моделирование» в Power BI Desktop, вычисляемую таблицу на основе следующей формулы:

Как итог вычисления этой формулы на основе UNION, будет создана общая таблица по продажам всех отделов:

DAX функция INTERSECT в Power BI и Power Pivot

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

Синтаксис: INTERSECT (‘Левая Таблица’; ‘Правая Таблица’)

Пример формулы с использованием DAX функции INTERSECT.

В модели данных имеются 2 таблицы с одинаковой структурой столбцов «Города Где Прибыль Больше 1 млн» и «Города Где Магазинов Меньше 5»:

Требуется создать таблицу, в которой должна быть информация о городах, имеющих общую прибыль по городу более 1 млн и при этом количество магазинов в городе меньше 5. То есть, нам нужно создать таблицу из строк первой таблицы, которые есть во второй. А для этого, хорошо подойдет рассматриваемая DAX функция INTERSECT.

Напишем пример формулы на основе INTERSECT:

И итог работы данной формулы на основе DAX функции INTERSECT следующий:

То есть, INTERSECT создала в модели данных таблицу, состоящую из двух строк: города Санкт-Петербург и Екатеринбург, в которых общей прибыли больше 1 млн, и при этом, магазинов в каждом городе менее 5.

DAX функция EXCEPT в Power BI и Power Pivot

EXCEPT () — создает таблицу из строк левой таблицы, которых нет в правой. В обеих таблицах должна быть идентичная структура столбцов.

Синтаксис: EXCEPT (‘Левая Таблица’; ‘Правая Таблица’)

Пример формулы с использованием DAX функции EXCEPT.

В модели данных Power BI имеются 2 таблицы с одинаковой структурой столбцов «Продажи Менеджеров» и «Топ 3 Максимальные Продажи»:

Задача — создать новую таблицу с оставшимися менеджерами и их продажами, не входящих в Топ 3. То есть, нужно создать таблицу из строк первой таблицы, которых нет во второй. И с решением данной задачи нам отлично поможет DAX функция EXCEPT.

Напишем соответствующую формулу с участием EXCEPT:

Результатом выполнения этой формулы с функцией EXCEPT, будет являться новая таблица со строками из «Продажи Менеджеров», которых нет в «Топ 3 Макс Продажи»:

На этом, с кратким обзором DAX функций, которые создают таблицы в Power BI и Power Pivot на основе строк из других таблиц, в этой статье все.

Пожалуйста, оцените статью:

1. 5
2. 4
3. 3
4. 2
5. 1

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

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

Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

Функции удаления фильтров в DAX: ALL, ALLSELECTED, ALLEXCEPT и ALLNOBLANKROW в Power BI и Power Pivot

Как в Power BI (Power Pivot) отследить одно отфильтрованное значение? DAX функции HASONEVALUE и HASONEFILTER

Как в Power BI (Power Pivot) вывести найденный текст? DAX функции LEFT, RIGHT и MID

Объединение данных из двух отдельных таблиц в Power Pivot без использования объединения на основе связанного ключа

Я пытаюсь объединить данные в таблицу фактов, чтобы получить общее представление о показах и расходах. Однако, поскольку я имею дело с 2,5 миллионами строк, как только я создаю составной ключ для объединения данных =[Date]&[Vendor]&[Campaign]&[Ad Group]&[Keyword] , размер моего файла скачет с Я предполагаю, что от 11 до 100 Мб из-за высокого уровня мощности в моей новой колонке.

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

Создание связанных таблиц

Как Вы могли убедиться из моих предыдущих постов, меры являются мощными инструментами анализа данных и позволяют производить немыслимые до этого виды расчётов. Однако, до сих пор при знакомствами с мерами мы использовали только одну таблицу t_sales. Но вся прелесть Power Pivot в том, что с его помощью можно производить расчёты, комбинируя данные из нескольких таблиц. По моему личному мнению, если бы даже Powe Pivot не имел встроенного движка функций DAX, одна только способность связывания таблиц, уже оправдывала бы его существование.

Создание связей

Так как же создаются связи между таблицами? Всё очень просто. Заходим в окно Power Pivot и в правом нижнем углу, нажимаем на иконку со всплывающей надписью «Диаграмма».

Либо по кнопке «Представление диаграммы» на вкладке «Главная».

Откроется окно представления диаграммы, в котором все таблицы нашей модели данных показаны в виде отдельных окошек, со списком колонок.

Как видим, эти таблицы между собою пока никак не связаны. Чтобы создать между ними связь, нам сначала нужно определить идентичные колонки. Идентичными колонками, называют колонки содержащие одинаковые данные. К примеру, и в таблице t_sales и в таблице t_products есть колонки КодПродукта, содержащие одинаковые данные (при этом не обязательно, чтобы названия колонок в обоих таблицах были одинаковыми). Свяжем эти две таблицы между собою кликнув по названию колонки КодПродукта в t_sales и удерживая левую кнопку мыши нажатой, перетащим эту колонку к другой колонке КодПродукта в t_products.

Точно также, связь между таблицами можно создать через команду «Создание связи» на вкладке «Конструктор».

Но можно ли создавать связь между таблицами по любым идентичным столбцам? Например столбцы «ЦенаЗаШтуку» в t_sales и «Цена» в t_products содержат одинаковые данные. Попробуем создать между ними связь путём перетаскивания. Power Pivot выдаст ошибку: «Не удалось создать связь, поскольку в каждом столбце содержатся повторяющиеся значения. Выберите по крайней мере один столбец, содержащий только уникальные значения.»

То есть для того, чтобы установить связь между таблицами, один из связывающих столбцов должен содержать только уникальные, не повторяющиеся значения. К примеру, цена у нескольких продуктов может быть одинаковой (повторяться), поэтому использовать эти столбцы для создания связи между таблицами не получится. А вот «КодПродукта» в t_products содержит только уникальные значения, поэтому мы и смогли использовать его для создания связи.

Таблицы, содержащие столбцы с уникальными значениями, по которым устанавливается связывание, называются «таблицами поиска» (lookup tables).

Ниже представлена сводная таблица на основе данных таблицы t_sales.

Как это работает

Функция CALCULATE () и связанные таблицы

Давайте создадим ещё одну связь между таблицами. Свяжем таблицу t_sales с таблицей t_clients.

В таблице t_sales, в столбце КоличествоДетейНаПопечении, имеется информация о том сколько отпрысков находятся на попечении каждого клиента. Создадим меру, которая бы на основании этих данных, рассчитывала сумму продаж клиентам с детьми:

Power Pivot Базовый №7. Множество таблиц, повторение пройденного

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

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

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

Работа с множеством таблиц в Power Pivot почти не отличается от работы с одной таблицей. Нужно только создать связи между таблицами.

Решение

Сначала мы импортируем все имеющиеся таблицы, перейдем в окно Power Pivot — Представление диаграммы:

Соединим таблицы между собой. У нас получится диаграмма снежинка.

Создадим меру «Sum of Sales»:

Теперь с помощью функции CALCULATE мы вычислим сумму продаж штатов CA, WA:

Создадим сводную таблицу и диаграмму с этими мерами:

Создадим меру, которая находит сумму продаж для выбранных месяцев:

Теперь найдем меру, которая вычисляет процент от общем суммы для выбранных месяцев: