Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Якубович
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Формирование остатков из оборотов (оборотно-сальдовая ведомость), Как из транзакционных данных собрать остатки на каждый день.
 
#
03.05.2024 15:28:56
Коллеги, приветствую.

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

Функция fОстатки формирует накопленный итог, чтобы найти исходящий остаток на каждую дату, в которой была транзакция.

В запросе "Входящие и исходящие остатки" много раз группирую, применяю функцию fОстатки и сдвигаю на одну строку исходящие остатки, чтобы получить входящие на следующую дату  (делаю за счёт индекса со смещением на 1 и LeftJoin таблицы на саму себя). Число операций можно сократить, если функцию fОстатки впихнуть при группировке, но у меня переделать функцию не получилось.

Буту признателен за любые советы и рекомендации по автоматизации.  
 
Перейти    
Группировка и отображение элементов сводной таблицы, Группировка мелких значений сводной таблицы
 
#
10.09.2020 12:00:00
Pelena, спасибо, не знал о такой возможности вторичной круговой диаграммы, удобно, жаль такого функционала нет на стандартной круговой диаграмме (чтобы не появлялось вспомогательного рисунка).

Андрей VG, да, это то что нужно, спасибо. Не подскажете, почему не имея связей между таблицами PP все равно позволяет корректно использовать взаимосвязи? Т.е. не обязательно иметь взаимосвязь, чтобы меры в PP рассчитывались? Можно Вас попросить прокомментировать алгоритм меры pct после return?
Код
pct:=Var allSum = SUM('TData'[Платеж])
Var stats = SUMMARIZE('TData'; 'TData'[Сотрудник]; "person amount"; SUM('TData'[Платеж]) / allSum)
Var otherSum = SUMX(FILTER(stats; [person amount] < 0,1); [person amount])
Return IF(HASONEVALUE('Dynamic'[Учётки]);
    IF(VALUES('Dynamic'[Учётки]) = "Другие"; otherSum;
       Var personSum = SUMX(FILTER(stats; 'TData'[Сотрудник] = VALUES('Dynamic'[Учётки])); [person amount])
       Return IF(personSum >= 0,1; personSum; BLANK())
    )
;
1)
Изменено: Якубович - 10.09.2020 12:00:45
 
Перейти    
Группировка и отображение элементов сводной таблицы, Группировка мелких значений сводной таблицы
 
#
06.09.2020 11:42:10
Добрый день,

Столкнулся с проблемой при визуализации отчёта в Excel. Мной была построена модель данных с кучей различных аналитик, готовлю дашборд и при построении круговых диаграмм и прочей аналитики появляется сектор, состоящий из мелких значений.

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

Основная задача не потерять не значимые элементы, а просто их сгруппировать, т.о. установка фильтра на значение доли или топ 5 элементов не подходит.

Доп. ограничение: к графику будут подключены различные срезы, таким образом таблица будет меняться.
Изменено: Якубович - 06.09.2020 11:54:29
 
Перейти    
Работа с виртуальными таблицами и переменными в DAX, Union, lookupvalue, VAR в PowerPivot
 
#
25.04.2020 13:49:51
PooHkrd,
Спасибо за наводку
 
Перейти    
Ошибка при создании модели данных Excel 2016, К сожалению, открыть модель данных в PowerPivot невозможно...
 
#
24.04.2020 15:29:46
Спасибо,

проблема решилась пересозданием профиля в реестре:
Скрытый текст
 
Перейти    
Работа с виртуальными таблицами и переменными в DAX, Union, lookupvalue, VAR в PowerPivot
 
#
24.04.2020 15:26:47
PooHkrd,

Спасибо за краткий ликбез.

Есть ли какая-нибудь продвинутая литература (кроме мануала) по PP и PQ, которую нужно изучить юному падавану "модельных" наук ( именно по части моделирования систем)?
 
Перейти    
Ошибка при создании модели данных Excel 2016, К сожалению, открыть модель данных в PowerPivot невозможно...
 
#
24.04.2020 14:51:11
Цитата
PooHkrd написал:
снеси Офис, почисти систему и поставь заново,
Ох уж эта удаленка и корпоративный доступ...  :cry:  

Спасибо за совет, уточню, реестр чистить обязательно или простая переустановка должна помочь?

Какой еще софт теоретически может быть связан с PP, который тоже лучше будет апдейтнуть?
 
Перейти    
Работа с виртуальными таблицами и переменными в DAX, Union, lookupvalue, VAR в PowerPivot
 
#
24.04.2020 14:47:45
PooHkrd,

Шикарная идея, спасибо.

Только на большом массиве данных не быстрее ли будет через DAX поиск сделать? Если джойнить 100000*12 и 3000*3, например.
 
Перейти    
Ошибка при создании модели данных Excel 2016, К сожалению, открыть модель данных в PowerPivot невозможно...
 
#
24.04.2020 14:26:37
Добрый день,

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

Ради интереса решил создать новый файл с простейшей моделью данных из 2 файлов, ситуация повторилась, т.е. проблема не в файле. Однако ошибка уже немного другая:
Скрытый текст

Версия Excel 2016 (16.0.4966.1000) MSO (16.0.4966.1000), 64 разрядная версия.
 
Перейти    
Работа с виртуальными таблицами и переменными в DAX, Union, lookupvalue, VAR в PowerPivot
 
#
22.04.2020 17:13:43
Цитата
PooHkrd написал:
Так в чем проблема дополнить этим столбцом таблицу сразу в PQ без посредников?
Проблема в интервальном поиске Т.е. курс есть за 04.04 и 06.04, а мне надо заполнить 05.04. Единственный варимант - расширять данные на весь календарь, лефтджойнить и заполнять вниз данными. Решил пойти по-другому и сделать меру, чтобы не загружать лишний раз память в большом отчёте.

DrillPipe, спасибо, то, что нужно!
 
Перейти    
Работа с виртуальными таблицами и переменными в DAX, Union, lookupvalue, VAR в PowerPivot
 
#
22.04.2020 15:31:30
Vladimir Chebykin, данный пример описывает суть проблемы.

Реальная задача : таблица, которая уже сделана с помощью PQ,  дополнена в PP столбцом с функцией DAX. Для того, чтобы сформировать справочник надо эту таблицу из PP как-то снова запихнуть в PQ, а затем объединенной таблицы снова выгрузить в PP, чтобы создать меру.

+ виртуальная таблица не занимает память, а вот PQ занимает.
 
Перейти    
Работа с виртуальными таблицами и переменными в DAX, Union, lookupvalue, VAR в PowerPivot
 
#
22.04.2020 11:29:40
День добрый,

Прошу помочь разобраться со следующей проблемой, мне необходимо в PP создать меру, которая использует виртуальную таблицу, а затем извлекает из неё определенное значение.

Алгоритм создания меры следующий:

1. Берем две таблицы в PP и объединяем их через UNION и записываем в переменную.
2. Создаем переменную, которая будет производить интервальный поиск по дате из виртуальной таблицы.
3. Ищем курс из виртуальной таблицы с помощью lookupvalue за определенную дату и выводим его.

Код:
Код
=VAR 
Curr=Union('Курсы';'Курсы1') 
VAR 
Dat=If (
Hasonevalue ('Поиск'[Дата]);
Calculate (
LASTNONBLANK ('Curr'[Дата] ; 1);
Filter ('Curr'; 'Curr'[Дата]<= Values ('Поиск'[Дата]))
)
)
RETURN
LOOKUPVALUE(
'Curr'[Курс];
'Curr'[Валюта];'Поиск'[Валюта поиска]; 
'Curr'[Дата];Dat
)

Вопрос в следующем, как обратиться к конкретному столбцу виртуальной таблицы?
Сайт майкрософт говорит, что:
Цитата
К столбцам в табличных переменных нельзя обращаться через синтаксис ИмяТаблицы[ИмяСтолбца].
Тогда как к ним обращаться? Через SELECTCOLUMNS или другую функцию?
Также не очень понятно, работает ли lookupvalue с виртуальными таблицами или алгоритм необходимо менять?
Скрытый текст
 
Перейти    
Промежуточный поиск в power pivot, ВПР с интервальным просмотром в DAX
 
#
22.04.2020 00:52:18
Господа, дополнительный вопрос... в отдельную тему [МОДЕРАТОР]
 
Перейти    
Промежуточный поиск в power pivot, ВПР с интервальным просмотром в DAX
 
#
20.04.2020 20:08:33
Нашел в заморских интернетах интересный способ промежуточного поиска в DAX.Создаем меру промежуточного поиска:
Код
If (
Hasonevalue ('Поиск'[Дата]);
Calculate (
LASTNONBLANK ('Курсы'[Дата]; 1);
Filter ('Курсы'; 'Курсы'[Дата]<= Values ('Поиск'[Дата]))
)
)
Затем создаем столбец с получившимся курсом.
Код
=LOOKUPVALUE('Курсы'[Курс];'Курсы'[Валюта];'Поиск'[Валюта поиска];'Курсы'[Дата];[ПоискДаты])
/
LOOKUPVALUE('Курсы'[Курс];'Курсы'[Валюта];[Валюта пересчёта];'Курсы'[Дата];[ПоискДаты])
Вуаля, задачка решена.

Помогите, пожалуйста, улучшить отчёт, хочу создать срез Валюта, в котором можно выбрать только одно значение. Выбранное значение встанет в формулу как [Валюта пересчёта] и таблица с курсами автоматически пересчитается в указанную в срезе валюту.
Изменено: Якубович - 20.04.2020 22:36:48
 
Перейти    
Промежуточный поиск в power pivot, ВПР с интервальным просмотром в DAX
 
#
18.04.2020 14:14:53
Добрый день.

Пытаюсь решить следующую задачу в PP, есть таблица с значениями курсов, курсы есть не за каждую дату. В случае, если данных нет, то необходимо взять предыдущее значение по этой валюте. Курс определяется по любой паре из таблицы. Получается найти при точном совпадении, но как реализовать поиск промежуточных значений не расширяя календарь через PQ?
 
Перейти    
Перекрестное соединение двух таблиц (Power Pivot,Query,DAX), Реализуем декартово произведение множеств в Power Pivot и Query
 
#
09.04.2020 15:26:10
PooHkrd, я вот от этого и шел и не мог понять, почему и не работает.
Цитата
Максим Зеленский написал:
должны быть таблицы, а не столбцы
Совсем забыл, что здесь столбец и таблица разные вещи, привык работать со списками и не перестроился на другую логику.

Вопрос решили.
 
Перейти    
Power query, поиск проблемного запроса/ошибки и расчет времени работы запроса, Поиск ошибок в сложных файлах,состоящих из множества запросов, и замер скорости работы запросов
 
#
09.04.2020 15:20:24
Максим Зеленский,

Максим, да, это то что нужно! Спасибо!

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

P.s. Жаль не могу сказать "Вы выиграли автомобиль!")
 
Перейти    
Использованием вычисляемого объекта и срезов в сводной таблице, Настройка срезов при наличии пустых строк в таблице с вычисляемым объектом
 
#
09.04.2020 15:08:35
PooHkrd,отлично, спасибо за уточнение)
 
Перейти    
Перекрестное соединение двух таблиц (Power Pivot,Query,DAX), Реализуем декартово произведение множеств в Power Pivot и Query
 
#
09.04.2020 12:12:37
Vladimir Chebykin, спасибо за уточнение.

Можно попросить на примере показать, как это работает? Вот этот самый пресловутый Crossjoin.
Код
=CROSSJOIN(Cur[Валюта];'Date'[Дата])

В приложенном файле формируется ошибка:
Цитата
Невозможно определить одно значение для столбца "Дата" в таблице "Date". Это может происходить, если для получения одного результата формула меры ссылается на столбец, содержащий множество значений, без указания агрегата, например MIN, MAX, COUNT или SUM.

Если я правильно Вас понял, то crossjoin отработал, только PP хочет, чтобы мы с его помощью что-то посчитали, верно? Тогда как обращаться к столбцам виртуальной таблицы, как и к обычным или есть какие-то особенности?
Изменено: Якубович - 09.04.2020 12:25:15
 
Перейти    
Использованием вычисляемого объекта и срезов в сводной таблице, Настройка срезов при наличии пустых строк в таблице с вычисляемым объектом
 
#
09.04.2020 11:54:34
PooHkrd, спасибо) Не полностью описал имеющиеся ограничения)

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

Интерес лежит именно в решении вопроса без использования надстроек.
 
Перейти    
Power query, поиск проблемного запроса/ошибки и расчет времени работы запроса, Поиск ошибок в сложных файлах,состоящих из множества запросов, и замер скорости работы запросов
 
#
09.04.2020 11:48:26
Андрей VG, спасибо.

Как я понял, 2 строка выбирает все источники в книге (включая функции), нужно ли выбирать конкретные запросы или #sourse пойдет на вход.

А вот что делает третья строка? Можно объяснить синтаксис?
Код
let test = try _[Value] in test[HasError]

quasarrr,Михаил Л,  спасибо!

Считаю второй вариант Михаила наиболее точно решает поставленную задачу (В варианте Андрея еще не разобрался).

Осталось придумать, как запихнуть автоматизацию выборов запросов через #shared и получится великолепное и изящное решение.

Также висит вопрос по скорости отработки запросов, как нам искать улиток из семейства запросов? Возможно ли как-то рассчитать скорость не только всего запроса, но и его операторов в отдельности?

P.s. Как также красиво писать код на форуме?
Изменено: Якубович - 09.04.2020 15:15:28
 
Перейти    
Использованием вычисляемого объекта и срезов в сводной таблице, Настройка срезов при наличии пустых строк в таблице с вычисляемым объектом
 
#
09.04.2020 00:10:58
Здравствуйте,

пытаюсь сформировать сводную таблицу с использованием именно вычисляемого объекта (вычисляемое поле не подходит). При его использовании возникает множество пустых строк, которые не дают корректно отработать срезам (не исчезают города, которые не относятся к региону). Фильтры не помогают избавиться от "лишних" городов в срезах.

Возможно ли как-то это решить стандартными методами, без применения Power Pivot и DAX?

 
 
Перейти    
Перекрестное соединение двух таблиц (Power Pivot,Query,DAX), Реализуем декартово произведение множеств в Power Pivot и Query
 
#
08.04.2020 23:46:43
Цитата
Андрей VG написал:
Можно использовать для создания таблиц в Power BI
Спасибо за совет! К сожалению, PBI не поставить на работе, приходится обходиться только PP и PQ.
Цитата
Андрей VG написал:
Power Pivot - только для виртуальных таблиц
Что Вы имеете ввиду?
 
Перейти    
Перекрестное соединение двух таблиц (Power Pivot,Query,DAX), Реализуем декартово произведение множеств в Power Pivot и Query
 
#
08.04.2020 22:55:31
День добрый, форумчане.

Столкнулся со следующей задачей:

Нужно объединить 2 таблицы (Дата и Валюта), чтобы получить декартово произведение (перекрестное соединение) этих двух множеств. В PQ это решается достаточно просто:

1. При объединении 2 запросов выбрать "Полное внешнее соединение",
2. В запросе с множеством валют создать столбец по типу {Дата_1_в_числовом_формате..Дата_2_в_числовом_формате} и раскрыть получившейся список.

Однако, в книге Роба Колли "Формулы DAX для Power Pivot" говорится о том Как НЕ надо использовать Power Query:
Скрытый текст
Из вышесказанного следует, что на большом массиве данных следует использовать DAX, а не возможности языка М. Так вот, как же реализовать задачу с помощью формул DAX в PP?

Мной была предпринята попытка использовать формулу CROSSJOIN, но выдаётся какая-то ошибка, которая всё портит. Также интересуют другие, более быстрые на больших массивах данных, способы решение поставленной задачи в Excel.

 
 
Перейти    
Power query, поиск проблемного запроса/ошибки и расчет времени работы запроса, Поиск ошибок в сложных файлах,состоящих из множества запросов, и замер скорости работы запросов
 
#
08.04.2020 22:20:52
Цитата
Михаил Л написал:
Не знаю без примера
Ваша взяла, сектор приз на барабане, пример в студию!

Цитата
Михаил Л написал:
проверить каждый запрос
Так если их не 20,а 100, по 1000 строк минимум, глазами не найти. У PQ же определенно есть  логи исполнения запросов, в настройках есть некая "трассировка", может это она?

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

Внимание, вопрос. Как не руками и не нажимая на каждый запрос определить, кто выдаёт ошибку.

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

P.s. по правилам форума нельзя выкладывать файлы более 100 кб,  тогда как выкладывать "тяжёлые файлы"?
P.s.s. файл сформирован в 19 офисе, здесь выдается кол-во ошибок, на работе стоит 16 офис и в файле консолидаторе число ошибок не выдается.
 
Перейти    
Power Query извлечение числа в отдельную колонку
 
#
08.04.2020 21:28:38
Михаил Л, там же вопрос "методологический" :)  
 
Перейти    
Power Query извлечение числа в отдельную колонку
 
#
08.04.2020 20:34:12
Цитата
mitox написал:
необходимость подчищать извлеченный текст
Можно применить на столбец формат десятичное число и использовать оператор ЕСЛИОШИБКА для PQ:
Код
=try <operation> otherwise <alternate result> (например, null)

После этого необходимо скрыть пустые значения, и останутся только данные с цифрами.
 
Перейти    
Power Query извлечение числа в отдельную колонку
 
#
08.04.2020 20:09:35
Цитата
mitox написал:
Как можно решить задачу?
Значение, которое необходимо вытащить всегда последнее?

Можно воспользоваться функцией разделить столбец по разделителю, выбираем "пробел" и ,"Самый правый разделитель".
 
Перейти    
Power query, поиск проблемного запроса/ошибки и расчет времени работы запроса, Поиск ошибок в сложных файлах,состоящих из множества запросов, и замер скорости работы запросов
 
#
08.04.2020 19:54:22
Цитата
Murderface_ написал:
Можно просто задать требуемое имя для столбцов в итоговом запросе. Таким образом, если появится лишний столбец, то вы его не увидите.
Возможно не правильно сформулировал, предположим есть 20 запросов, они преобразуют файлы от 20 человек до 2 столбцов: Дата и Сумма. Так как заполняют люди, то всегда может всплыть косяк заполнения данных (например 30.02.2019.). Так как запросы сохраняются в модель данных, то выводится только итоговый запрос, который сконсолидировал данные всех 20 человек. Если хотя бы 1 запрос содержит некорректные данные, то весь запрос не исполнится и не загружается в таблицу.

Вопрос в том, что надо найти тот самый запрос, который не отработал. Есть какая-то фишка в PQ для быстрого поиска засбоивших запросов?
 
Перейти    
Power query, поиск проблемного запроса/ошибки и расчет времени работы запроса, Поиск ошибок в сложных файлах,состоящих из множества запросов, и замер скорости работы запросов
 
#
29.02.2020 13:03:38
Добрый день,

мной был сформирован файл-консолидатор информации из множества других отчётов в power query. Эти отчёты готовят коллеги, структура не меняется, но всегда может появиться лишний столбец или ещё что-нибудь, что ломает итоговый запрос консолидатора. Итоговый запрос работает просто, объединяет все запросы в книге. Если один запрос некорректный, то итоговый запрос не загружается и найти прохвоста вручную ах как не просто (запросов очень много). Есть ли какая-то функция или ещё что-то, что поможет найти сбойный запрос и причину возникшей ошибки (каждый раз может быть разной). Также интересует узнать, сколько отрабатывает каждый запрос, как это можно сделать?

p.s. обсуждаем не проблему файла, а метод/подход, приложить файл нет возможности и надобности.
Изменено: Якубович - 29.02.2020 17:25:29
 
Перейти    
Страницы: 1
Наверх