BI — это просто

Как провести конкатенацию строк (соединить текст) в Power BI либо Power Pivot? DAX функции CONCATENATE и CONCATENATEX

Содержание статьи: (кликните, чтоб перейти к соответственной части статьи):

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы побеседуем о том, как в Power BI либо PowerPivot провести конкатенацию строк (соединить текст). А непосредственно, разберем DAX функции CONCATENATE и CONCATENATEX, дозволяющие конкатенировать (соединять воединыжды) текст.

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

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

Также, подписывайтесь на наши социальные сети. Поэтому что конкретно в их, Для вас будут доступны оперативно и любой денек наши животрепещущие фишки, секреты, выработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

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

CONCATENATE () — производит объединение 2-ух текстовых строк в одну единую.

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

В качестве примера формулы разглядим DAX функцию CONCATENATE вложенную саму в себя:

Результатом выполнения данной для нас формулы будет последующая единая текстовая строчка:

Аналогом функции CONCATENATE является оператор объединения текста в языке DAX «амперсанд и» — &:

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

CONCATENATEX () — соединяет воединыжды итог выражения, вычисленного для каждой строчки таблицы.

• ‘Таблица’ — таблица, содержащая строчки, для которых будет вычислено выражение
• Выражение — выражение, вычисляемое для каждой строчки таблицы
• «Разделитель» — разделитель объединяемых частей текста (необязательный элемент)

Разглядим пример формулы на базе DAX функции CONCATENATEX.

В Power BI Desktop имеется начальная таблица «Городка», содержащая столбец, в каждой строке которого, прописан лишь один город:

Задачка — сделать единую текстовую строчку, где будут объединены городка из всех строк начальной таблицы, с разделителем — «, » (запятая и пробел).

Для решения данной для нас задачки отлично подойдет функция CONCATENATEX, в 1-ый параметр которой, мы пропишем начальную таблицу «Городка». Во 2-м параметре, в качестве выражения, просто укажем начальный столбец [Город], тогда, когда это выражение будет выполнятся для каждой строчки начальной таблицы, результатом будет возвращение значения из ячейки этого столбца.

В 3-ем параметре функции CONCATENATEX мы пропишем разделитель «, » (запятая и пробел).

Итого, у нас получится последующая формула:

И результатом выполнения данной для нас формулы в Power BI будет единая строчка с перечислением всех городов через запятую:

На этом, с разбором функций конкатенации CONCATENATE и CONCATENATEX, позволяющих в Power BI и Power Pivot соединить различные текстовые строчки в одну, все.

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

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

Фурроров Для вас, друзья!
С почтением, Будуев Антон.
Проект «BI — это просто»

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

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

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

Что еще поглядеть / почитать?

Как проверить столбец в Power BI (Power Pivot) на наличие фильтров? DAX функции ISFILTERED и ISCROSSFILTERED

Вычисление выражений на последнюю / первую даты периода в Power BI и Power Pivot: DAX функции группы CLOSINGBALANCE и OPENINGBALANCE (YEAR, QUARTER, MONTH)

VAR и RETURN: создание переменных в DAX (Power BI)

Как применять функцию CONCATENATE в Гугл Sheets

Присоединяется функция CONCATENATE в Гугл Sheets
вкупе несколько кусков данных. Эта функция полезна при управлении большенными
наборы инфы, любой из которых просит аналогичного подхода.

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

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

Обычной пример

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

Естественно, в этом примере мы предполагаем
1-ое имя находится в ячейке A1, а 2-ое — в ячейке B1. Вы сможете адаптировать это к вашему
свою таблицу, заменив эти ссылки своими своими.

Нажатие Enter с сиим определенным
Пример будет создавать MaryTruman. Видите ли, имя
встык прямо против фамилии. Функция CONCATENATE сделала свое дело в
этот сценарий, но есть и остальные варианты, которые вы сможете включить в него, чтоб расширить его
способности, такие как добавить место либо данные из остальных ячеек.

Внедрение пробела в формуле CONCATENATE

Знать, как применять пробелы с CONCATENATE,
принципиально, поэтому что наборы данных нередко не инсталлируются буквально так, как вы желаете.
Как и в нашем примере выше, мы желаем, чтоб имя смотрелось представительно, добавив
место меж 2-мя клеточками.

Пробелы включены в эти листы Гугл
функция с внедрением двойных кавычек:

= CONCATENATE (A1, ””, B1)

Если вы не видите тут, есть пространство снутри
эти цитаты. Мысль использования кавычек заключается в том, что вы вводите данные
вручную и не выбирая данные таблицы.

Иными словами, A1 и B1 являются
разумеется, уже является частью таблицы, потому вы ссылаетесь на их, вводя
они как они есть (буковка ячейки плюс номер ячейки). Тем не наименее, чтоб включить
ваши собственные данные в формуле, вы должны заключить их в кавычки.

Добавление текста в формулу CONCATENATE

Функция CONCATENATE в состоянии сделать больше, чем просто
соедините пару ячеек и поставьте меж ними пробел. Ниже приведен пример того, как
используйте CONCATENATE, чтоб сформировать целое предложение, используя данные ячейки.

В этом примере функции CONCATENATE мы объединяем округ и номер его ранга, но заместо того, чтоб бросить его на месте, мы используем пробелы и собственные введенные вручную данные для сотворения полного предложения:

= CONCATENATE (A2, «is», «ranged», C2, «по сопоставлению с этими иными церемониальными окрестностями.»)

Чтоб формула работала как обыденный британский,
не забудьте поставить места где нужно. Вы не сможете добавить пробел сходу опосля
ссылка на ячейку (как C2 выше), но вы сможете применять двойные кавычки. В виде
Вы сможете созидать выше, мы употребляли пробел пару раз в наших цитатах, чтоб создать
предложение читать нормально.

Применение формулы CONCATENATE в остальных местах

В конце концов, единственное реальное внедрение КОНКАТЕНАТА
функция, когда имеет дело с достаточным количеством данных, что время экономится по сопоставлению с
ввод данных вручную. Итак, все, что для вас необходимо создать, чтоб формула работала
с иными клеточками перетащите его вниз.

Нажмите на ячейку один раз, чтоб она была выделена.
Вы должны узреть небольшую рамку в правом нижнем углу ячейки, как
это:

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

Что любопытно,
Гугл Sheets имеет аналогичную функцию под заглавием SPLIT. Но заместо присоединения
ячейки, он делит одну ячейку на несколько ячеек в зависимости от того, какой персонаж
Вы выбираете пометить как точку разделения.

Как сделать ряд ячеек в Excel

Результат: Изучите два различных метода резвого объединения ряда ячеек. Это включает в себя способ Ctrl + щелчок левой клавишей мыши и бесплатный макрос VBA, который дозволяет стремительно и просто создавать формулы объединения либо Ampersand.

Уровень мастерства: Средний

Сцепление: не плохое и нехорошее

Функция CONCATENATE быть может весьма полезна для объединения значений нескольких ячеек в одну ячейку либо формулу. Одно из фаворитных применений — создание формул VLOOKUP на базе нескольких критериев.

Но вы не сможете соединить спектр ячеек, ссылаясь на спектр в функции CONCATENATE. Это затрудняет и отбирает много времени при написании формул, если у вас много ячеек, которые необходимо соединить.

Вариант № 1: Ctrl + щелчок левой клавишей мыши, чтоб избрать несколько ячеек

Вы сможете задерживать кнопку Ctrl при выбирании ячеек для прибавления в формулу CONCATENATE. Это сберегает время при вводе запятой опосля всякого выбора ячейки.

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

Возможно, это самый резвый метод добавить несколько ячеек в формулу CONCATENATE . Это удачный метод, если вы объединяете несколько ячеек, но это может занять много времени, если вы объединяете много ячеек вкупе.

Вариант № 2: CONCATENATE макроса спектра

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

Макрос Concatenate употребляет InputBox, который дозволяет для вас выбирать спектр ячеек. Потом он делает формулу Concatenate либо Ampersand, создавая аргумент для каждой ячейки в избранном спектре.

Вы сможете назначить макрос кнопочке на ленте либо композиции кнопок. Макрос дозволяет весьма стремительно создавать формулы.

Как работает макрос?

Макрос в основном делит ссылку на спектр, заданную при помощи InputBox, а потом делает формулу в активной ячейке. Вот резюме высочайшего уровня:

1. Изберите ячейку, в которую необходимо ввести формулу, и запустите макрос.
2. Возникает InputBox и дает для вас избрать ячейки, которые вы желаете соединить. Вы сможете избрать спектр ячеек при помощи мыши либо клавиатуры.
3. Нажмите ОК
4. Макрос делит спектр на ссылки на одну ячейку, потому эта ссылка на спектр (A2: C2) преобразуется в (A2, B2, C2).
5. Формула Concatenate либо Ampersand создается в активной ячейке.

Функции макроса Concatenate

1. Тип формулы. Макрос «Concatenate» дозволяет сделать формулу «Concatenate» либо «Ampersand».
2. Знак разделителя — Вы также сможете добавить знак разделителя меж каждой ячейкой. Это комфортно, если вы желаете добавить запятые, пробелы, тире либо хоть какой знак меж соединенными ячейками.
3. Абсолютные ссылки — макрос также дает для вас возможность создать ссылки на ячейки абсолютными (привязанными). Это добавит символ $ перед буковкой столбца либо номером строчки. Это комфортно, если вы копируете формулу в определенном направлении и не желаете, чтоб относительные ссылки на ячейки изменялись.

Функция Concatenate либо формулы Ampersand

Клеточки также могут быть объединены при помощи знака
Ampersand (&). Это кандидатура использованию функции CONCATENATE. Последующие две формулы приведут к одному и тому же результату.

Тот, который вы используете, — это вопросец личных предпочтений. Функция Concatenate может иметь маленькое преимущество, так как вы сможете применять трюк Ctrl + щелчок левой клавишей мыши, чтоб стремительно добавить несколько ячеек в формулу.

Снова же, макрос дозволяет для вас сделать или Concatenate , или формулу Ampersand.

Код VBA

Вот код для макросов Concatenate и Ampersand .

Как применять код

Добавьте клавиши макроса на ленту

В приведенных выше примерах я добавил этот код в свою книжку личных макросов. Потом я добавил клавиши макросов на ленту для всякого из 4 макросов. Я сделал новейшую группу на вкладке Формулы и добавил к ней клавиши.

Как клавиши макросов окажутся на ленте, вы сможете щелкнуть их правой клавишей мыши и избрать «Добавить на панель резвого доступа», чтоб добавить их в QAT.

Назначить сочетание кнопок для макросов

Вы также сможете назначить сочетание кнопок для макроса, чтоб создать этот процесс весьма резвым. Когда InputBox открыт, фокус ворачивается на рабочий лист, что значит, что вы сможете избрать спектр ввода при помощи клавиатуры. В последующем снимке экрана я создаю объединенную формулу лишь при помощи клавиатуры.

Я запускаю макрос, помещая клавишу макроса на панель резвого доступа, а потом нажимаю сочетание кнопок Alt + Button Position для QAT. Ознакомьтесь с данной для нас статьей о том, как применять сочетания кнопок QAT для получения наиболее подробной инфы.

Доп ресурсы

Concatenate с разрывами строк — Дейв Брунс из ExcelJet имеет хорошую статью и видео о том, как добавить разрывы строк в формулу конкатенации. Хороший совет для присоединения почтовых адресов.

Сочитайте ячейки без сцепления — Дебра Далглиш в Contextures разъясняет, как создавать формулы Ampersand.

Concatenate нескольких ячеек с внедрением Transpose — Chandoo имеет увлекательный подход к данной для нас дилемме при помощи функции TRANSPOSE.

Заключение

Этот инструмент должен существенно убыстрить и упростить создание формул Concatenate либо Ampersand. Это быть может не то, что вы используете любой денек, но это здорово иметь в вашем наборе инструментов.