Александр Кравчук, самое простое, на мой взгляд, но халтурное: через DAX-studio сохранить искомую таблицу в .csv, и потом подключиться к нему из Excel.
Для этого - устанавливаете, сбсн, DAX-studio; - в PowerBI Desktop, в котором, вроде как, по условию все хорошо загружается, идете на вкладку внешние инструменты, вызываете DAX sudio - потом внутри DAX-studio во вкладке Output выберете опцию "File"; - в окне кода пишете : EVALUATE 'Таблица1'
3. Вкладка "RUN", в появившемся окне выбрать вариант сохранения в CSV UTF-8
4. Потом в новой книге Excel подключение к текстовому/csv файлу, с раскодировкой UTF-8
До этой же таблицы в excel-ном примере можно докопаться, но, скорее всего, придется навороченную функцию писать, а поздновато (
mechanix85, посмотрите статью Имке, у нее в примере реализована выгрузка части данных, которые невозможно обработать запросом, в отдельный результат - может пригодится. А по сабжу,
Цитата
написал: количество строк не совпадает с количеством уникальных строк
может, через антисоединение сначала выводить заказы, для которых в справочнике нет ключей? Потом заполнить справочник, проверить, чтобы ключи были уникальные, потом обновлять отчет?
ну и я не понял, как у вас в справочник по заказу №1 попало два перевозчика.
mechanix85, спасибо, но есть ощущение, что вариант из моего примера по-шустрее. Плюс, не все меры в отчете могу умножать на коэффициент - надо именно через справочники заходить. И вопрос сейчас стоит не столько в том, как считать, а более в том, как спрятать. Идея с username() интересная, только не уверен, что будет работать на 2013-м офисе.
Владимир, спасибо, но тут откуда не ждал приехало: на другой машине не позволяет авторизоваться. RunTime Error 1004: "Книга защищена и не может быть изменена". Пока со своего рабочего места верстал пример, такое не вылазило.
з.ы. да и нет на книгу никакого пароля. Пароль вводится пользователем на лист, потом грузится в модель, и там уже проверяет DAX, есть авторизованный юзер или нет. з.з.ы. и от защиты книги придется отказаться, таким образом, херится п6 и низводится на нет всякий смысл RLS, т.к. меру пользователь теперь сможет поправить.
Есть способ назначить пароль для редактирования объектов модели данных?
господь с ними, с менеджерами ищу решение, чтобы вовне инфа не уходила. а про студию я и позабыл ... но тут тоже, DAX знать надо. А коннект к модели либо использование надстройки в конкретной книге возможно запретить?
Демонстрирую попытку реализации RLS в модели данных PowerPivot. Логика задумки: 0) существует большой отчет по продажам, в который запросами загружается и трансформируется информация о продажах из разнородных источников. Читателей много: 30 человек с разным уровнем доступа (руководитель может видеть всю инфу, супервайзер - только по своей торговой команде) - надоело обновлять срезы по основному отчету, т.к. это не быстро; 1) существует запрос с юзерами и паролями в режиме "только подключение" - users; 2) пользователь при открытии заполняет таблицу Авторизация и прожимает кнопку, к которой привязан макрос, обновляющий соответствующий запрос; 3) при успешной авторизации (то есть в запросе users существует совпадение по ключам login и password ) пользователь видит доступную ему часть отчета, если совпадения нет - пустой отчет. (Директор имеет полный доступ, пароль 5678, Трус, Балбес и Бывалый - ограниченный, пароли соответственно abcd, efgh, 1234; пользователи ФамилияХ не имеют доступа вообще). 4) ограничение доступа прописывается в коде меры в DAX; 5) при закрытии книги отрабатывается макрос, очищающий таблицу "Авторизация" и обновляющий запрос в модели, чтобы скрыть отчет и сохранить книгу. 6) установленная защита книги не позволяет засунуть нос в текст запросов, в модель данных, чтобы подглядеть пароль либо удалить RLS-фильтр в мере, а скрытые от просмотра объекты не дают дернуть их значение функцией куба. (пароль для снятия защиты книги 159357)
Прошу у уважаемых форумчан критику решения. Возможно макрос, сохраняющий книгу при закрытии, можно записать более корректно/отказоустойчиво (фрагменты кода надергал отсюда и из приемов Николая Павлова)
Также остается ощущение, что от того, кто захочет что-то выудить из модели, я ничего-то и не защитил: прошу подсказку на тему того, что еще сюда можно добавить для защиты от несанкционированного чтения. (в VBA я серый ... очень серый)
написал: умудряется посередине месяца менять формат даты с "ММ-ДД-ГГГГ" на "ММ/ДД/ГГГГ" и потом назад. И все без разрешения и всякой логики
не замечал такого поведения: последние лет 5 они хранят дату в формате "2022-01-01T00:00:00" - и кривенько написанный пять лет назад запрос пока не падает. Коды валют иногда меняются.
Вообще, у вас может не быть прав, чтобы создавать объекты в кубе. Если задача в том, чтобы не добавить в куб новое измерение, а что-то рассчитать на основе объектов куба, то можно обойтись функциями кубов. Еще есть плагин для Excel, с его помощью можно развернуть сам текст MDX-инструкции.
каэшн может, а макрос, запускающий обновление выбранной пачки запросов PQ, я предложил привязать к кастомной кнопке, кто-то просто по ссылке не перешёл по ощущениям, если есть нужда обновляться раз в минуту, таблицы получатся большие. А если на них ещё будут ссылаться формулы, книга будет тормозить адово. Обновление + 50 выгрузок на лист + пересчет формул + успеть посмотреть, что получилось - уложится в минуту?)
Александр Кравчук написал: 50 файлов [...] каждый на отдельный лист [...] ежеминутно
Вангую, что книга от такого умрет... в смысле памяти может не хватить, т.к. очень много умных таблиц. А если и не умрет, то книга будет заниматься только обновлением. На тему автоматизации: https://youtu.be/gGQ-SlhZYN4 - если запросы надо обновлять выборочно, то не сложно. такой макрос можно привязать, например, к кнопке. Ежеминутно, скорее всего, тоже можно, только для этого ищите тут VBA-умельца.
Kirill Gureev написал: единое наименование, в итоге, попала в синюю с суммарной ценой
Не совсем понятно, зачем нужна вторая таблица: это товарный раздел ТТН, например? или что-то другое? И зачем тянуть расчет из второй в синюю, которая справочник?
Цитата
Kirill Gureev написал: Нужна простая БД в Excel и подсказка в логике реализации
А учет вам там нужен? Формы ввода? Отчеты? Или речь-таки не про БД? Просто в контексте БД и вашего примера: нужны таблицы с приходом и расходом, справочники: товары, рецептуры, поставщики, виды операций. Все, что связано с ценами, вынести в прейскуранты.
Владимир, вас в соседней теме просили не такой пример, а несколько файлов с обезличенной информацией, справочники и сам запрос, который вы хотите оптимизировать внутри примера.
Что мне помогло в похожем вопросе: 1. Все конструкции с Table.NestedJoin() меняете на Table.Join() ; 2. В запросах-справочниках, которые джойнятся в основном запросе, последний шаг заворачиваете в Table.Buffer(),
Код
[..]
LastStep = Table.Buffer( code )
in
LastStep
3. Часть источника, которая уже не обновляется, можно заранее трансформировать и сохранить в .csv, уже в финальной структуре. А вашим кодом обновлять только новую информацию, т.е. ваяете файл.csv, пишите запросы к нему и к новым файлам, потом финальный запрос Table.Combine( {old , new} )
1 - добавляет скорости, 2 - фиксит многократные обращения к источнику, 3 - вот ссылка на макрос от Андрея VG под это дело специально.
Николай Савенко написал: если у Вас таких показателей 513 шт.
то их надо классифицировать и прописывать сценарии для разных групп показателей: "только считать сумму по столбцу", "делить на сумму добычи" и т.п. Вот пример у Сэма МакКея. В финансовой отчетности получается красиво с таким подходом.
Ну и либо 513 мер, либо по 513 условий в двух мерах (профи, поправьте, если я неправ).
Трехуровневая зависимость связанных (зависимых) выпадающих списков, Добавление дополнительного условия при формировании связанных (зависимых) выпадающих списков
но, по-моему, "как в экселе" тоже перенесли. Пока не могу сам открыть проверить, но примерно: открыть режим связей - выбрать меру - формат - пользовательский