Страницы: 1
RSS
Как создать строку итогов в Power Query аналогично умной таблице в Excell
 
Здравствуйте, на просторах интернета не нашел решения следующей проблемы поэтому обращаюсь к вам.
С помощью Power Query собираю данные с листов внутри данной книги и вставляю на отдельный лист, однако строка итогов с числовыми данными каждый раз меняется с подсчета суммы на подсчет количества значений. По сему пытаюсь подсчитать итог по столбцу непосредственно в Power Query, но решения этого вопроса не нашел. Есть ли возможность в Power Query подсчитать итоги по столбцам (количество строк изменяется) и выгрузить это в Excell, либо как настроить чтобы автосумма по полученному в Excell диапазону данных не слетала на подсчет количества?
Спасибо.
 
Добрый день!
Мне видится это как через PQ в модель данных и там итоги делаем. (обычно так делаю)
Хотя тут у нас есть гуру PQ и ждем их ответа. мне интересен ответ.  
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Вообще если нажать в свойствах внешних данных таблицы такую галочку как на картинке, то у меня при обновлении формула в итогах не меняется. Ну и на PQ добавил в ваш запрос шаги для вычисления итогов, Все шаги кнопочные кроме последнего, чутка руками дописал.
Вот горшок пустой, он предмет простой...
 
Добрый день!
Чтобы автосумма не слетала на подсчет количества, необходимо  на вкладке "Конструктор"  в свойствах запроса отметить пункт: "сохранить сведения о сортировке/фильтре/формате для столбца"
 
Цитата
Alhim777 написал:
отметить пункт: "сохранить сведения о сортировке/фильтре/формате для столбца"
В чём подвох? По умолчанию при создании таблицы из запроса этот пункт не отмечен.
 
PooHkrd, благодарю за оперативную подсказку, воспользовался сохранением свойств внешних данных. Но хочу разобраться в принципах создания итоговых строк в PQ.  Можно ли скриншеты и объяснить общий принцип вывода итогов по столбцам (особенно если их несколько и итоги разные количество/сумма/среднее)?
 
Цитата
sfs написал:
Можно ли скриншеты и объяснить общий принцип вывода итогов по столбцам
Какой именно шаг в приложенном мной файле вам непонятен?
Принцип простейший делаем группировку с нужными агрегаторами и объединяем с исходной таблицей.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Какой именно шаг в приложенном мной файле вам непонятен?
Коллега, может ТС хочет чего-нибудь поизящнее, как вчерашний? :)
Вариант попроще.
Код
let
    Source = #table({"a", "b"}, {{"v1",11}, {"v2", 14}})
in
    Source & Table.FromRecords({[a="Итого", b=List.Sum(Source[b])]})
 
Цитата
PooHkrd написал:
Какой именно шаг в приложенном мной файле вам непонятен?Принцип простейший делаем группировку с нужными агрегаторами и объединяем с исходной таблицей.
Принципы объединения с исходной таблицей, как это делается, по каким правилам, какой синтаксис, и можно ли это сделать не входя в режим редактирования на языке запросов?
 
Цитата
sfs написал:
можно ли это сделать не входя в режим редактирования на языке запросов?
Можно конечно. Вот, исключительно мышкоклацательный вариант. При помощи кнопки на первой картинке получается второй запрос и в нем делаем группировку. При помощи кнопки на второй картинке получается третий запрос, в нем ничего дополнительного уже не делал.
Все те же действия реализованы в ранее приведенном примере, только в рамках исходного запроса.
Можно еще применить вариант, который предложил Андрей, но тут вообще все придется ручками в редакторе набирать. Что тоже крайне полезно.
Вот горшок пустой, он предмет простой...
 
Отлично, спасибо
 
Здравствуйте. Извините, но для меня почему-то проблема не решилась.
В чем дело, Есть лист на котором собирается таблица с помощью PQ этот лист делался год назад. Таблица собирается, на это дело применяется стиль таблицы (из вкладки "Конструктор"), дальше сверху на все это дело лепится еще гора всяких правил условного форматирования. Работает, все прекрасно, единственное что бесит, это когда обновляется таблица с помощью PQ, в случае добавления новых строк в таблицу, то почему-то автоматически дублируется правило условного форматирования. В итоге при каждом обновлении PQ мне приходится удалять вручную правило. Но и это не беда.
Понадобилось с помощью PQ сделать еще два листа чтоб на них были выборки за каждый год отдельно. В итоге должно получиться три абсолютно одинаковых листа по форматированию, на первом листе все данные, на втором за 2019, на третьем за 2020. Дублирую выборку PQ вставляю результат на новые листы, все просто, все работает. Но вот что делать с форматированием???
1. Применяю стиль форматирования таблицы к новым таблицам, при обновлении PQ этот стиль пропадает. Перепробывал уже миллион вариантов:
1.1 Дублирую стиль, получается новый стиль, применяю стиль к новой таблице, при обновлении пропадает.
1.2. Применяю стиль, сохраняю книгу, закрываю вообще excel. Открываю, обновляю, опять пропадает.
1.3 Галочка "сохранить сведения о сортировке/фильтре/формате" стоит. Беру снимаю, обновляю, поведение стандартное, беру ставлю, применяю стиль к таблице, опять все пересохраняю, закрываю, открываю заново, обновляю - результат тот же.
Такое впечатление что та галочка просто не стоит или на ее наличие excel не смотрит вообще.

По поводу УФ не могу понять, можно ли сделать так чтоб один формат применялся на разных листах одновременно? То-есть у меня есть правило УФ, хочу чтоб оно работало на всех трех листах.

Файл большой, прикреплю ссылкой на гугл-драйв: https://drive.google.com/file/d/1cG2eO0vxZh-ocTa4nPW0iF0jpaSsOSwo/view?usp=sharing

Правда не знаю будет ли толк, потому как он, при обновлении, будет пытаться взять данные с другого файла.
 
yegorkowalew, вам одну строку итогов создать? или две?
 
Речь вообще шла не о строке итогов, а о стиле таблиц, в эту тему отписался потому как оно связано друг с другом.
 
yegorkowalew, попробуйте на листе Даты 2019 обновить запрос. Сохранится ли стиль?  
 
Да!!! Осталось!!!
Можете подсказать что Вы сделали???
 
yegorkowalew, да вроде только оставил галку на автоформат данных на вкладке Данные->Свойства. и стиль таблиц Оперативный 2020 назначил по умолчанию
 
Испытал эти действия на своей версии файла. Да, когда оставляешь галку на автоформате данных, то при обновлении данных, стиль таблицы не сбрасывается. Мало того! Если стиль таблицы уже не сбрасывается, можно снять галку на автоформате и стиль таблицы уже при дальнейших обновлениях таблицы не сбрасывается.
В общем мистика какая-то, будем считать что это глюк екселя.

Большое спасибо!

Второй вопрос по условному форматированию оставляю открытым. Можно ли применять одно и тоже условное форматирование сразу на разных листах?
Страницы: 1
Наверх