Страницы: 1
RSS
Excel. Сводные таблицы. PQ. Снять ограничение на 1000 строк при "проваливании", Excel 2016-2019
 
Сегодня столкнулся с такой проблемой.
У человека есть сводная таблица, построенная на запросе объединении 2х таблиц в одну, около 1,6млн строк. Запросы оформлены в Power Query
При просмотре детализации цифры сводной таблицы, показывает по умолчанию только 1000 записей.
В аналогичном файле, только построенным на OLAP кубе, в настройках подключения, можно изменить параметр показа максимального количества записей по умолчанию. В данном случае, этот параметр не доступен для редактирования, он пустой и серый.

I. Было найдено решение в интернете. Подходит для Excel 2016:
1) поместите мышь вне сводной таблицы
2) пройдите по меню Данные -> Подключения
3) в диалоговом окне Подключения к книге выберите строку ThisWorkbookDataModel
4) нажмите кнопку Свойства
5) на вкладке Использование в поле Максимальное число извлекаемых записей введите количество строк, которые вы хотите получить
6) нажмите Ok

II. Но, у человека оказался Excel 2019.
Там нет меню Данные -> Подключения.
2) меню Данные -> Запросы и подключения.
3) Справа открывается окно, на вкладке Подключения выбираешь ThisWorkbookDataModel.
остальные шаги аналогичные.

НО! Excel 2019 не видит это подключение, там пусто.
III.Методом тыка, было найдено решение:
1) Открываем файл. Ни в коем случае не открываем Подключения, даже если просто посмотреть.
2) Пишем пустой макрос. (тем кому лень писать руками sub и т.д.)
3) Выбираем макрос и редактируем его.
4) Вставляем следующие 2 строки
   MsgBox (ThisWorkbook.Connections.Count)
   MsgBox (ThisWorkbook.Connections.Item(1).Name)
5) Выполняем макрос. Всплывающие окна закрываем.
6) Теперь повторяем шаги описанные параграфом выше, и во вкладке Подключения выбираешь ThisWorkbookDataModel.


Спасибо за внимание.
 
Доброе время сутокMarchuk,  спасибо!
Но с Excel 365 такой маневр не проходит :(
 
Цитата
Marchuk написал:
При просмотре детализации цифры сводной таблицы, показывает по умолчанию только 1000 записей
при двойном клике на цифре - таблица на отдельном листе, это имеете ввиду?
F1 творит чудеса
 
Все верно.

Цитата
Андрей VG написал: Но с Excel 365 такой маневр не проходит
У меня его нету. Помочь не смогу.
 
Цитата
Андрей VG написал:
с Excel 365 такой маневр не проходит
Если итоговый запрос Power Query загружен в модель данных, подключение ThisWorkbookDataModel видно в панели "Запросы и подключения", и параметр настраивается
F1 творит чудеса
 
А если в модель данных не подключен то невозможно? Кстати это очень проблемная вещь особенно при веб скрапинге с power query. Если например я парсировал

30 000 страниц то невозможно отправлять данные в  модель потому что начинает крутить заново и по второму обороту все делать и как следствие тормозить. Делаю банальным прокручиванием по 1000 страниц. Потом копирую и вставляю в Excel таблицу.
Power Query, Power Pivot, Power BI и MS Excel спасут мир
 
Цитата
Максим Зеленский написал:
Если итоговый запрос Power Query загружен в модель данных, подключение ThisWorkbookDataModel видно в панели "Запросы и подключения"
Увы, в 365 нет такого. Если создать сводную по модели и зайти в "Источник данных" и попытаться выбрать "Свойства подключения", то перебрасывает на отфильтрованную панель "Запросы и подключения" с пустым набором :(  
В прочем, для таких фишек можно спокойно использовать DAX Studio - чуть больше телодвижений на составление запроса в FILTER, но не смертельно.
 
Максим Зеленский, посмотрел, есть такое, но только в случае, если запрос загружался в модель именно используя O365.
Если модельку сделать в 2016, а потом открыть в О365, то там пусто.
Если модельку сделать в О365, потом открыть её в 2016 и сохранить, потом открыть в О365, то в подключениях становится опять пусто.  :D
В 2016 схема работает шикарно. ТСу большое спасибо.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
но только в случае, если запрос загружался в модель именно используя O365.
возможно, проверить не могу, снес давно 2016
Цитата
Андрей VG написал:
Увы, в 365 нет такого.
ну как же нет...


F1 творит чудеса
 
Цитата
Максим Зеленский написал:
ну как же нет...
Это если проделать с полностью пустым файлом. Да.
Но у меня в модель есть загрузки ещё и помимо Power Query. В картинках:
1. Подключение запроса Power Query к модели Power Pivot
2. Список прочих подключений - но ThisWorkbookDataModel нет.
3. Пытаемся посмотреть подключение сводной, созданной по модели
4. Выбрасывает с фильтрацией в пустой список подключений, даже значок сброса фильтра показывает
 
Цитата
Андрей VG написал:
о перебрасывает на отфильтрованную панель "Запросы и подключения" с пустым набором
добрый день. попробуйте
1. Вы на второй фотке, например на соединение AccessDb_facts нажмите правой кнопкой мышки и выбирите Свойства
2. на фотке под номером 4 нажмите Очистить фильт, должно появится ThisWorkbookDataModel
Опыт и практика - великое дело! Век живи, Век учись!
 
Цитата
sanych09 написал:
AccessDb_facts нажмите правой кнопкой мышки и выбирите Свойства
Зачем мне свойства этого соединения, если разговор идёт про свойства соединения ThisWorkbookDataModel?
Цитата
sanych09 написал:
а фотке под номером 4 нажмите Очистить фильт, должно появится ThisWorkbookDataModel
Проделал и не раз и до публикации и после. Если бы это произошло, то я бы не писал об этом :)  Максимум бы сообщил, что посыпаю голову пеплом, ввёл сообщество в заблуждение - у меня появилось. Но нет.
ThisWorkbookDataModel на моём файле не виден от слова совсем. Программно, да ThisWorkbook.Connections(4) (в моём случае) - это и есть ThisWorkbookDataModel. Только сколько не ковырял VBA модель, так и не нашёл, как хотя бы в коде изменить тот самый параметр.
 
Цитата
Андрей VG написал:
Только сколько не ковырял VBA модель, так и не нашёл, как хотя бы в коде изменить тот самый параметр.
Сам параметр находил. А как его изменить средствами VBA, ответа не нашел (он ошибку 1004 вроде дает). В инете много спрашивают, а решения не видел.
Изменено: Marchuk - 13.02.2020 08:41:32
 
Цитата
Marchuk написал:
Сам параметр находил.
Вы про MaxDrillthroughRecords у объекта класса OLEDBConnection? В моём случае, WorkbookConnection с именем ThisWorkbookDataModel не имеет ветки ни на OLEDBConnection, ни на ODBCConnection. Только ModelConnection, а там и в её подобъектах нет такого параметра. Думаю, что именно из-за того, что у меня в книге в модели таблицы представлены смесью разных подключений, и происходит проблема. WorkbookConnection для ThisWorkbookDataModel не OLEDBConnection (нечто своё особенное)  и поэтому не видно на панели Запросы и подключения.
 
Андрей VG, т.е. теоретически, если бы все подключения были бы через PQ то скорее всего подключение модели появилось бы в списке?
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
если бы все подключения были бы через PQ то скорее всего подключение модели появилось бы в списке?
Да, Алексей, вчера проверил - один запрос PQ был с таблицы этой же книги, другой из CSV файла, третий с листа другой книги (загрузку их баз данных через Power Query не проверял). Если источники данных в модели только из Power Query, то ThisWorkbookDataModel видна.
 
Андрей VG, чой-та они там намутили. В 2016 все видно и без разницы откуда. Модельку грузил и напрямую с таблиц листа и PQ и мастером РР какую-то таблицу подключал. А тут... тьфу.
Вот горшок пустой, он предмет простой...
 
если не будет лень и будет время, задам вопрос разрабам.
F1 творит чудеса
Страницы: 1
Наверх