Здравствуйте! Возник следующий вопрос. Допустим в ячейке есть текст типа 1;2;3;4. Необходимо сделать в соседней ячейке выпадающий список на основе этих данных (1;2;3;4). Знаю что в Data Validation как раз в таком виде записывается список, но если просто сослаться на эту ячейку, то в выпадающем списке будет вся строка с разделителями.
Выше я указал лишь пример. На практике будет "умная" таблица (ListObject) со столбцом, где в каждой ячейке будет текстовая строка  с разделителями и данные будут разные. В соседней ячейке нужно будет выбрать одно значение из текста с разделителем.
С помощью формул и дополнительного столбца (номер элемента из списка) у меня получилось вычленять нужные значения. Но хотелось сделать через выпадающий список если такое и возможно.
Использую Excel 2016

Андрей VG, спасибо за предложенное решение и оно работает. Только я чуть подправил вашу формулу, вместо ALL взял функцию ALLSELECTED, тогда будет отображаться актуальное число элементов в группе с учетом "ручной" фильтрации

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

Скрытый текст
Названия строк Число элементов в столбце   Подгруппа Желаемый результат 1 1.1 1 4 1.2 1 4 1.3 1 4 1.5 1 4 2 2.1 1 10 2.10 1 10 2.2 1 10 2.3 1 10 2.4 1 10 2.5 1 10 2.6 1 10 2.7 1 10 2.8 1 10 2.9 1 10 3 3.5 1 2 3.6 1 2

Недавно я узнал, что можно опубликовать модель Power Pivot (далее PP), созданную в Excel на аналитическом сервере. А потом из любой книги excel подключаться к данной модели как аналитическому Кубу. Изначальная структура была такой - есть SQL сервер с базой данных. Потом подключались из PP к этой БД и создавали модель. Далее созданную модель переносили на тот же сервер (только уже аналитический сервер). Затем из любой книги подключались к этой модели как к аналитическому кубу. В результате все вычисления происходили на сервере, а конечный пользователь получал обычную сводную таблицу, разгружая свой ПК от вычислений.
Но в эту цепь я хочу добавить Power Query (Далее PQ). Цепочка будет такой:
1. Из PQ подключаюсь к  SQL БД и произвожу необходимые преобразования таблиц
2. Запрос PQ будет источником информации для модели PP.
3. Публикую модель PP на сервер.
4. Подключаюсь к модели PP из любой книги

Вопрос: как будет обновляться модель PP (её данные)  если источником для неё является не БД SQL, а запрос PQ, который основан на БД SQL.

P.S:.Я не ищу ответа как это сделать, а интересует возможно ли это. И ещё я полный дилетант в серверах и базах данных

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

Андрей Лящук, У вас очень компактный вариант получился. К сожалению в версии Excel 2016 в PQ нет функции List.Split, но ваш вариант работает в Power BI

artyrH, Ваше решение отлично подходит, воспользуюсь им. Благодарю!

Строка может быть любой длины

Здравствуйте, уважаемые знатоки Power Query и я зыка M. Возникла задача, с которой пока не знаю как справиться, прошу вашей помощи. Имею столбец с текстовой строкой в которой есть разделители (допустим это символ "|"). Необходимо разделить данный текст на строки по каждому Второму разделителю.
Например была строка:
TEG1|Data 1|TEG2|Data 2|TEG3|Data 3
Хочу получить:
TEG1|Data 1
TEG2|Data 2
TEG3|Data 3.
Сначала надеялся, что есть готовая функция в разделе Splitter Functions, но её нет. Затем пришла в голову мысль превратить текст в список, а из него сделать список, который бы содержал положение всех моих разделителей ("|"). Затем убрать каждый чётный элемент, и разделить исходный текст по позиции. Но беда в том, что я не знаю как создать список, который содержит позиции Всех моих разделителей. Функции типа List.PositionOf не подходят, так как возвращают положение первого вхождения.
В приложенном файле есть исходная таблица, и желаемый результат

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

Здравствуйте уважаемые знатоки языка DAX. Обращаюсь к вам, дабы оценить возможность решения задачи на языке DAX, так как совсем ещё новичок. Сразу оговорюсь, что данная задача у меня решена на VBA, но это определенного рода костыль.
И так краткая суть задачи. Есть таблица в которой есть столбцы Префикс Семейство П_N(порядковый номер) Суффикс. Эти столбцы дают мне позиционное обозначение. Есть также столбец Описание. По этому столбцу строиться сводная таблица, и он содержит в себе позиционные обозначения. Например получаем набор SF1, SF2, SF3, SF5, SF6, SF8. Я хочу получить на основе позиций текстовую строку такого плана SF1-SF3, SF5, SF6, SF8, и эта строка должна отображаться напротив моего описания, то есть быть в области значений, значит это должна быть мера.
Есть определённое правило как строиться эта строка - если подряд идущих позиций больше чем 2, то разделитель "-", иначе разделитель запятая ","

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

На языке DAX группирую по полю Описание, далее по полю Семейство, далее по полю Префикс, далее по полю Суффикс. Таким образом, получил ряд таблиц (думаю это делается через функцию GENERATE) с порядковыми номерами.
Таблицы сортируются по возрастанию.
Каждой таблице добавляется столбец индекс, который начинается с 1. И добавляется столбец, который вычисляется как П_N - Индекс. Таким образом можно разделить таблицы с номерами на группы, которые объединяют подряд идущие номера. Из каждой группы берётся первый и последний номер. Перед номером ставятся Семейство & Префикс, после номера Суффикс. Далее идёт разделитель, если количество номеров в группе более 2, то разделитель межу позициями "-", иначе ", ". Между группами разделитель ", ". А между разными Семействами Префиксами и Суффиксами "; "

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

 

Андрей Лящук, Ваше решение работает как надо, благодарю вас за отклик. Выглядит, это конечно страшно, но главное работает. Буду разбираться, что к чему

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

Как можно применить данную строку, к интересующим меня столбцам?

Да

Здравствуйте, уважаемые знатоки Excel и Power Query. Возникла задача, решение которой я никак не могу найти. В приложенном файле есть таблица на листе "Контакты", она формируется из нескольких запросов. Последний столбец (если смотреть в Power Query) содержит эту же таблицу с листа "Контакты". Мне нужно модифицировать столбцы "Описание_1", "Описание_2", "Цвет" по условию если значения этих столбцов во вложенной таблице не равно null, тогда установить значения в исходной (не во вложенной) таблице равное значению из вложенной таблице, иначе оставить как есть. Это позволит мне менять значения вручную, а если его нет, то будет значение по умолчанию, или нулевое значение.
В принципе, я могу эту задачу решить, через развертывания вложенной таблицы, создания условных столбцов, удаления лишних и.т.д. Но это как-то не оптимально.
Я понимаю, что задачу, наверное можно решить через Table.TransformColumns, но со всем запутался с синтаксисом, да и мозг ещё не привык к функциональному программированию. Прошу вас, натолкните на правильные мысли.

Вообщем попробовал я с длиной текста, и разочаровался. Оказывается для мер есть ограничении - не более 255 символов. Но я нашёл способ как обратится к ячейке с данными (не меру). Но работает всё как-то криво, через раз так сказать. То возвращает error, то не обновляются данные. Вообщем это не вариант.

Эта формула сработала, благодарю! Теперь осталось проверить свою теорию на длину символов. Как проверю отпишусь здесь

Здравствуйте! Возникла следующая задача. Есть простая умная таблица данных. Она загружена в модель данных PP. В PP создана мера, которая поваляет в значениях выводить текст. Сводной таблицы в книге нет и не должно быть. С помощью семейства формул КУБ() я могу добраться к данным по значению индекса и выгрузить их на лист. Однако в VBA нет подобных формул. Можно ли добраться к данным с помощью VBA .
Зачем мне всё это. Исходная таблица не будет на листе, а будет Power Query и будет загружаться только в модель данных, но не на лист. В столбце дата будет текст с длиной строки более 32768, поэтому выгружать на лист нельзя. Идея такая, что если получиться достучаться до PP минуя выгрузку на лист и взять данные в переменную VBA. Очень прошу помощи, если такое вообще возможно.
PS. я совсем новичок в кубах, и узнал о их существовании только вчера.

Нашёл причину. Нужно было в свойствах самой умной таблицы поставить галочку "сохранять сведения о сортировке...". После этого стало работать как надо

Хотя сейчас проверил, предыдущие решения, то происходит тоже самое. Оказывается так происходит на версии Excel 2016, а вчера всё тестировал на версии 2019

Андрей VG, Ваше решение выглядит очень лаконично, что хорошо. Однако если удалить из исходной таблицы некоторые столбцы, то на их месте в запросе появится пустые столбцы Столбец1 , Столбец2...

Проверил с 1000000 строк. Разница если и  есть, то незначительная. Делал все на глаз без макросов. Ваш код, вроде чуть быстрее

Оно как. О таких тонкостях я не знал!

PooHkrd, чуть подправил ваш код, чтобы запрос не зависел от количества столбцов и их названия.

Я не против. Я могу это сделать сам, или это должен сделать администратор?  

Это отличный вариант, всё работает как надо. Благодарю!

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

PooHkrd, Здорово, работает. Но теперь возник другой вопрос, а как вы заставили PQ, чтобы он изменил сам источник, а не вставил новую таблицу?

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

В моем примере ключ не показателен, значения могут повторяться. Индекс и планировался как ключ