Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 След.
Выпадающий список на основе текста с разделителем
 
Здравствуйте! Возник следующий вопрос. Допустим в ячейке есть текст типа 1;2;3;4. Необходимо сделать в соседней ячейке выпадающий список на основе этих данных (1;2;3;4). Знаю что в Data Validation как раз в таком виде записывается список, но если просто сослаться на эту ячейку, то в выпадающем списке будет вся строка с разделителями.
Выше я указал лишь пример. На практике будет "умная" таблица (ListObject) со столбцом, где в каждой ячейке будет текстовая строка  с разделителями и данные будут разные. В соседней ячейке нужно будет выбрать одно значение из текста с разделителем.
С помощью формул и дополнительного столбца (номер элемента из списка) у меня получилось вычленять нужные значения. Но хотелось сделать через выпадающий список если такое и возможно.
Использую Excel 2016
Количество элементов в группе
 
Андрей VG, спасибо за предложенное решение и оно работает. Только я чуть подправил вашу формулу, вместо ALL взял функцию ALLSELECTED, тогда будет отображаться актуальное число элементов в группе с учетом "ручной" фильтрации
Изменено: Machestro - 24.11.2019 11:10:17
Количество элементов в группе
 
Здравствуйте, уважаемые знатоки языка DAX. Пытаюсь решить вроде простую задачу, но совсем запутался в контекстах языка DAX. Есть таблица с двумя колонками Группа и Подгруппа. Необходимо создать меру, которая всегда будет показывать актуальное количество элементов в группе, то есть с учетом фильтров. В приложенном файле показано наглядно, что необходимо получить.
Скрытый текст
Публикация модели Power Pivot на сервере, Публикация модели Power Pivot + Power Query на сервере
 
Недавно я узнал, что можно опубликовать модель 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:.Я не ищу ответа как это сделать, а интересует возможно ли это. И ещё я полный дилетант в серверах и базах данных
Разделение столбца по каждому второму разделителю
 
Цитата
Андрей VG написал:
Кто вам сказал такую глупость
Увы, это личный опыт! Работаю на крупном предприятии, и не могу просто попросить обновить Excel. Какая была версия установлена изначально, такой и пользуемся.
Изменено: Machestro - 31.05.2019 09:57:00
Разделение столбца по каждому второму разделителю
 
Андрей Лящук, У вас очень компактный вариант получился. К сожалению в версии Excel 2016 в PQ нет функции List.Split, но ваш вариант работает в Power BI
Разделение столбца по каждому второму разделителю
 
artyrH, Ваше решение отлично подходит, воспользуюсь им. Благодарю!
Разделение столбца по каждому второму разделителю
 
Цитата
Arturus написал:
Если строка небольшой длины
Строка может быть любой длины
Разделение столбца по каждому второму разделителю
 
Здравствуйте, уважаемые знатоки 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 не подходят, так как возвращают положение первого вхождения.
В приложенном файле есть исходная таблица, и желаемый результат
Изменено: Machestro - 31.05.2019 08:07:59
Если подряд идущих позиций больше чем 2, то разделитель "-", иначе разделитель запятая ",", Можно ли решить задачу с помощью DAX и Power Pivot
 
Проверил я свой алгоритм на Power Query, и он работает. С Power Query немного проще работать, так как я вижу промежуточные результаты и таблицы. Но нужно именно в сводной таблице это реализовать, так как будут различные фильтры применяться, и нужно динамическое изменение.
Изменено: Machestro - 27.04.2019 19:37:11
Если подряд идущих позиций больше чем 2, то разделитель "-", иначе разделитель запятая ",", Можно ли решить задачу с помощью DAX и Power Pivot
 
Здравствуйте уважаемые знатоки языка DAX. Обращаюсь к вам, дабы оценить возможность решения задачи на языке DAX, так как совсем ещё новичок. Сразу оговорюсь, что данная задача у меня решена на VBA, но это определенного рода костыль.
И так краткая суть задачи. Есть таблица в которой есть столбцы Префикс Семейство П_N(порядковый номер) Суффикс. Эти столбцы дают мне позиционное обозначение. Есть также столбец Описание. По этому столбцу строиться сводная таблица, и он содержит в себе позиционные обозначения. Например получаем набор SF1, SF2, SF3, SF5, SF6, SF8. Я хочу получить на основе позиций текстовую строку такого плана SF1-SF3, SF5, SF6, SF8, и эта строка должна отображаться напротив моего описания, то есть быть в области значений, значит это должна быть мера.
Есть определённое правило как строиться эта строка - если подряд идущих позиций больше чем 2, то разделитель "-", иначе разделитель запятая ","

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

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

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

 
Изменено: Machestro - 29.04.2019 21:05:08
Table.TransformColumns и вложенная таблица
 
Андрей Лящук, Ваше решение работает как надо, благодарю вас за отклик. Выглядит, это конечно страшно, но главное работает. Буду разбираться, что к чему
Table.TransformColumns и вложенная таблица
 
Задам вопрос по другому. Вот есть формула, которая не разворачивая вложенную таблицу, делает то, что нужно.
Код
Table.ReplaceValue(#"Объединенные запросы1",each [Цвет],each if [#"Контакты"][Цвет]{0} <> null then [#"Контакты"][Цвет]{0} else [Цвет],Replacer.ReplaceValue,{"Цвет"})

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

Изменено: Machestro - 06.04.2019 17:30:43
Table.TransformColumns и вложенная таблица
 
Цитата
Андрей Лящук написал:
Во вложенной таблице всегда одна строка?
Да
Table.TransformColumns и вложенная таблица
 
Здравствуйте, уважаемые знатоки Excel и Power Query. Возникла задача, решение которой я никак не могу найти. В приложенном файле есть таблица на листе "Контакты", она формируется из нескольких запросов. Последний столбец (если смотреть в Power Query) содержит эту же таблицу с листа "Контакты". Мне нужно модифицировать столбцы "Описание_1", "Описание_2", "Цвет" по условию если значения этих столбцов во вложенной таблице не равно null, тогда установить значения в исходной (не во вложенной) таблице равное значению из вложенной таблице, иначе оставить как есть. Это позволит мне менять значения вручную, а если его нет, то будет значение по умолчанию, или нулевое значение.
В принципе, я могу эту задачу решить, через развертывания вложенной таблицы, создания условных столбцов, удаления лишних и.т.д. Но это как-то не оптимально.
Я понимаю, что задачу, наверное можно решить через Table.TransformColumns, но со всем запутался с синтаксисом, да и мозг ещё не привык к функциональному программированию. Прошу вас, натолкните на правильные мысли.
Достать данные куба из Power Pivot с помощью VBA
 
Вообщем попробовал я с длиной текста, и разочаровался. Оказывается для мер есть ограничении - не более 255 символов. Но я нашёл способ как обратится к ячейке с данными (не меру). Но работает всё как-то криво, через раз так сказать. То возвращает error, то не обновляются данные. Вообщем это не вариант.
Достать данные куба из Power Pivot с помощью VBA
 
Цитата
Максим Зеленский написал:
Evaluate("CUBEVALUE.....")
Эта формула сработала, благодарю! Теперь осталось проверить свою теорию на длину символов. Как проверю отпишусь здесь
Достать данные куба из Power Pivot с помощью VBA
 
Здравствуйте! Возникла следующая задача. Есть простая умная таблица данных. Она загружена в модель данных PP. В PP создана мера, которая поваляет в значениях выводить текст. Сводной таблицы в книге нет и не должно быть. С помощью семейства формул КУБ() я могу добраться к данным по значению индекса и выгрузить их на лист. Однако в VBA нет подобных формул. Можно ли добраться к данным с помощью VBA .
Зачем мне всё это. Исходная таблица не будет на листе, а будет Power Query и будет загружаться только в модель данных, но не на лист. В столбце дата будет текст с длиной строки более 32768, поэтому выгружать на лист нельзя. Идея такая, что если получиться достучаться до PP минуя выгрузку на лист и взять данные в переменную VBA. Очень прошу помощи, если такое вообще возможно.
PS. я совсем новичок в кубах, и узнал о их существовании только вчера.
Изменено: Machestro - 01.04.2019 10:16:11
Как в Power Query к данным добавить префикс
 
Нашёл причину. Нужно было в свойствах самой умной таблицы поставить галочку "сохранять сведения о сортировке...". После этого стало работать как надо
Как в Power Query к данным добавить префикс
 
Цитата
Machestro написал:
появится пустые столбцы Столбец1 , Столбец2...
Хотя сейчас проверил, предыдущие решения, то происходит тоже самое. Оказывается так происходит на версии Excel 2016, а вчера всё тестировал на версии 2019
Изменено: Machestro - 29.03.2019 06:00:40
Как в Power Query к данным добавить префикс
 
Андрей VG, Ваше решение выглядит очень лаконично, что хорошо. Однако если удалить из исходной таблицы некоторые столбцы, то на их месте в запросе появится пустые столбцы Столбец1 , Столбец2...
Как в Power Query к данным добавить префикс
 
Проверил с 1000000 строк. Разница если и  есть, то незначительная. Делал все на глаз без макросов. Ваш код, вроде чуть быстрее
Изменено: Machestro - 28.03.2019 14:25:45
Как в Power Query к данным добавить префикс
 
Цитата
PooHkrd написал:
List.Buffer и шаг КоличествоСтолбцов добавлены для оптимизации скорости
Оно как. О таких тонкостях я не знал!
Как в Power Query к данным добавить префикс
 
PooHkrd, чуть подправил ваш код, чтобы запрос не зависел от количества столбцов и их названия.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Заголовки = Table.ColumnNames(Источник),
    ВсеВТекст = Table.TransformColumnTypes(Источник,List.Transform(Заголовки, each {_, type text})),
    ДобавленныйПрефикс = (t,i) => 
        if i <= List.Count(Заголовки) - 1
            then @ДобавленныйПрефикс( Table.TransformColumns( t, {{Заголовки{i}, each Заголовки{i} & " = " & _, type text}}), i + 1)
            else t,
    Итог = ДобавленныйПрефикс(ВсеВТекст, 0)
in
    Итог
Как в Power Query к данным добавить префикс
 
Цитата
PooHkrd написал:
Но я бы все таки предложил поменять тему на
Я не против. Я могу это сделать сам, или это должен сделать администратор?  
Как в Power Query к данным добавить префикс
 
Цитата
PooHkrd написал:
Вот такой вариант:
Это отличный вариант, всё работает как надо. Благодарю!
Как в Power Query к данным добавить префикс
 
Здравствуйте, уважаемые знатоки Excel! Пытаюсь решить следующую задачу. Необходимо всем данным во всех столбцах добавить префикс, который равен названию столбца. Как добавлять одинаковый префикс всем столбцам я разобрался, а вот чтобы имя столбца, что-то не выходит. В приложенном файле, для понимания просто созданы две таблицы без запросов- источник и какой результат хотелось бы увидеть.
Нумерация строк в умной таблице с фиксацией
 
PooHkrd, Здорово, работает. Но теперь возник другой вопрос, а как вы заставили PQ, чтобы он изменил сам источник, а не вставил новую таблицу?
Нумерация строк в умной таблице с фиксацией
 
Нужно просмотреть все индексы в колонке, найти максимальный и прибавить единицу. У каждой строки должен быть свой уникальный индекс, и не важно, что значения у строк одинаковые
Нумерация строк в умной таблице с фиксацией
 
В моем примере ключ не показателен, значения могут повторяться. Индекс и планировался как ключ
Изменено: Machestro - 13.03.2019 09:18:39
Страницы: 1 2 След.
Наверх