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

Страницы: 1 2 3 След.
Выключение параллельной загрузки PowerQuery, Excel 2016 - нагружает процессор на 100% при обновлении большой модели.
 
Цитата
PooHkrd написал:
не виляет :( Это как раз то что пробовал:
Код
Application.MultiThreadCalculation.Enabled = False
Выключение параллельной загрузки PowerQuery, Excel 2016 - нагружает процессор на 100% при обновлении большой модели.
 
Цитата
PooHkrd написал:
никак
Неужели даже нельзя что то в конфигах или в регистре поправить для этого?)))
А если начать убивать лишние мешапы на уровне процессов, ексель сильно расстроится?
Выключение параллельной загрузки PowerQuery, Excel 2016 - нагружает процессор на 100% при обновлении большой модели.
 
Всем добрый день!

Надо запускать обновление файла excel в фоне (хочу запускать с помощью VBA), который содержит модель Power Pivot с запросами в Power Query.
Но при запуске
Код
WorkBook.RefreshAll
даже с
Код
Application.MultiThreadCalculation.Enabled = False
все равно запускает несколько mashup контейнеров (по количеству ядер).

Есть ли решение, как запустить обновление всех запросов в одном потоке?
В Power BI есть галочка - параллельная загрузка, а как это сделать в Excel?
Power Query. Выбор строк с самыми актуальными данными, в таблице присутствуют повторы, оставить только самые актуальные
 
Цитата
PooHkrd написал: Или я чего-то пропустил?
ТС просит
Цитата
ivanka написал:  оставить записи только из самых последних файлов,
А файлы именуются
Цитата
ivanka написал: файлы с именем по типу ГГГГ.ММ.ДД
Power Query. Выбор строк с самыми актуальными данными, в таблице присутствуют повторы, оставить только самые актуальные
 
Цитата
artyrH написал:
Код
Table.Distinct(#"Reversed Rows", {"ID", "Название", "Сумма в валюте сделки"})
Только наверное это надо заменить на просто:
Код
Table.Distinct(#"Reversed Rows", {"ID"})

И стоит добавить влоб сортировку по дате (вместо реверса), кто его знает в каком порядке произойдет импорт файлов...

Изменено: vetrintsev - 16.07.2019 16:15:39
Добавить условие в формулу на Dax, Изменить(добавить) условие
 
Воспользуйтесь функцией LOOKUPVALUE.
будет примерно так (для нового столбца в таблице 'btx_Transaction'):
Код
=LOOKUPVALUE(
    'Курсы'[Курс], 
    'Курсы'[Дата], 'btx_Transaction'[Дата], 
    'Курсы'[Валюта_Key], "e1985a04-0b00-11e7-80c0-000c29a6bb80"
)

Связь по дате придется разорвать.

Изменено: vetrintsev - 16.07.2019 10:45:05
Создание сводной таблицы с помощью Power Query и DAX формул
 
Цитата
sanych09 написал:
Пытался сделать что-то похожее, но не получается...

Если честно не понял, что не получается.
Обычный свод выдал точно такую же таблицу, возможно проблема именно в форматах, тогда во вложении вот такой же вариант, поправил:
- к источнику добавил два столбца, в которых отбросил информацию о номере дня в месяце и установил формат "mmm YYYY" (без дальнейшей группировки по столбцу)
- Конструктор - Макет отчета - Табличная форма
- Конструктор - Промежуточные итоги - Показывать все промежуточные итоги в нижней части...
- по элементу столбца status правой кнопкой - параметры поля - разметка и печать - в виде структуры - отобразить подписи .. в том же столбце

Но я так понимаю, проблема в том, что есть платежи до даты выставления счета? Или косяк тестовой выборки?

UPD. Но да, как запихать GR вверх и избавиться от Paid не ясно... PQ можно использовать для этих целей, но кто будет контролировать количество столбцов? DAX - такая структура тоже будет тяжело реализована - придется создать таблицу с номерами месяцев, где первой строкой будет GR... И вписывать логику проверки отфильтрованного значения... Здесь также согласен с предложением выше - заливать в форму.
Изменено: vetrintsev - 15.07.2019 15:25:33
PowerBI/DAX: Настрока фильтра для каждого слоя отчета в условиях иерархии.
 
все, я понял :)

Я сначала прочитал:
Цитата
поместите её вместо поля Значение...
И думал, зачем ее в строку вгонять... И только сейчас прочитал:
Цитата
...в области полей графика, а фильтр никакой не нужен, и колонка тем более
Спасибо!
PowerBI/DAX: Настрока фильтра для каждого слоя отчета в условиях иерархии.
 
Цитата
Максим Зеленский написал: ISINSCOPE ( 'ДанныеBI'[ПунктПродаж] )
не соображу, как отрабатывает это условие для контекста строки?
PowerBI/DAX: Настрока фильтра для каждого слоя отчета в условиях иерархии.
 
Цитата
bobyjoy написал:
Как использовать этот показатель?
К сожалению, не имею практики использования интерфейса Power BI, здесь лучше подскажут другие участники форума.
По идее, мера сама по себе возвращает данные только в том случае - если порог соблюден. В привычном Excel в отчете остануться только строки удовлетворящие порогу.
А значит для всех остальных срезов вернет BLANK() - и измерение должно скрыться с отчета/графика.

Цитата
bobyjoy написал:
создать колонку
Вот здесь не понял, зачем колонка, если это чистая мера?
PowerBI/DAX: Настрока фильтра для каждого слоя отчета в условиях иерархии.
 
Может быть поможет функция ISFILTERED(<column_name>)?
Получится что то подобное:
Код
Отклонение (ТОП самых негативных):=VAR porog = IF(ISFILTERED('ДанныеBI'[ПунктПродаж]), -2000, -15000)
VAR delta = CALCULATE(SUM('ДанныеBI'[Значение]);'Легенд'[Признак отчета]="Отклонение";ALL('Легенд');ALL('ДанныеBI'[ПунктПродаж]))
RETURN 
IF(delta<=porog, delta, BLANK())

Код не тестил (реализации PBI у меня нет, только сервер), но думаю логика должна быть такая же...

Еще есть функция ISCROSSFILTERED

А вообще, наверное, интересно должно быть не по абсолютным отсечкам формировать рейтинг, а по количеству - например ТОП 5 самых негативных отклонений...
SELECTCOLUMNS поверх NATURALLEFTOUTERJOIN с разным контекстом, Нужна помощь в DAX
 
Цитата
Максим Зеленский написал:
Не, не всегда.
Вот сервер ночью переночевал и утром выдает корректные данные сразу после NATURALLEFTOUTERJOIN. Так потребность в SELECTCOLUMNS отпала сама собой.

Цитата
Максим Зеленский написал:
Дальше я бы предложил внешний SELECTCOLUMNS всё же вытащить наружу
перед публикацией пробовал такой маневр вчера - он не помог.

Цитата
Максим Зеленский написал:
ну вот так у меня работает
по поводу вывода в VAR да, не подумал про такой вариант...

В общем все как-то само заработало, что немного пугает :)
Но это мелочи, все таки результат космический)))
SELECTCOLUMNS поверх NATURALLEFTOUTERJOIN с разным контекстом, Нужна помощь в DAX
 
Цитата
Ігор Гончаренко написал:
извините, а в чем суть задачи?

Темой ранее мы сделали таблицу, которая из одного реестра событий связывает их в цепочку из двух последовательных событий (бизнес логика - миграция абонентов с одной услуги на другую)

Поле чего эта таблица связывается с первичной таблицей событий неактивными связями (активируя которые я успешно получаю срезы).

Теперь есть мера, которая считает количество миграций (с услуги, на услугу) и надо настроить выражение строк детализации.
Соответственно в деталях надо вывести какая услуга была до миграции, и какая стала после (там на самом деле 10 столбцов слева и 5 справа).

Это обычный джоин, но он не работает...
SELECTCOLUMNS поверх NATURALLEFTOUTERJOIN с разным контекстом, Нужна помощь в DAX
 
Всем привет!

В общем, NATURALLEFTOUTERJOIN объединяет две таблицы, которые строятся из одной и той же, но по разному фильтруются (активациями связей).
Читсый NATURALLEFTOUTERJOIN возвращает корректные данные, но почему то сначала идут столбцы правой таблицы, а потом левой... Это неудобно эстетически (данный запрос представляет строки детализации).

И чтобы упорядочить столбцы как мне надо, я использую дополнительную обертку SELECTCOLUMNS.
Но она меняет результат запроса кардинально, контектс со второй таблицы слетает, и в ее столбцах я вижу ровно теже данные, что и в столбцах левой таблицы!!!
Ниже запрос, пример выложить не могу, так как запрос на сервере SSAS Tabular 2017.
Код
EVALUATE CALCULATETABLE(
    SELECTCOLUMNS(
        NATURALLEFTOUTERJOIN(
            CALCULATETABLE(
                SELECTCOLUMNS('Услуги',
                    "migration_id",RELATED('orders_migrations'[migration_id])+0,
                    "A",RELATED('Наряды'[Слой разовой услуги])
                ),
                USERELATIONSHIP('Наряды'[bi_order_id], orders_migrations[bi_order_id_make]),
                USERELATIONSHIP('Наряды'[bi_order_id],'services'[bi_order_make_num_list])
            ),
            CALCULATETABLE(
                SELECTCOLUMNS('Услуги',
                    "migration_id",RELATED('orders_migrations'[migration_id])+0,
                    "B",RELATED('Наряды'[Слой разовой услуги])
                ),
                USERELATIONSHIP('Наряды'[bi_order_id], orders_migrations[bi_order_id_drop]),
                USERELATIONSHIP('Наряды'[bi_order_id],'services'[bi_order_drop_num_list])
            )
        ),
        "migration_id",[migration_id],
        "A",[A],
        "B",[B],
    ),
    USERELATIONSHIP('Отчетный период'[date],'orders_migrations'[date_make_report])
)

Физический план запроса из DAX Studio
Скрытый текст

Логический план запроса из DAX Studio
Скрытый текст

Выглядит вывод вот так:
Скрытый текст

А без SELECTCOLUMNS выглядит вот так:
Скрытый текст
У кого-нибудь есть идеи как это вылечить?)
Изменено: vetrintsev - 28.06.2019 02:11:16
Получение данных о наличии статуса самозанятого через сайт ФНС
 
А какой смысл использовать PQ, если там таймаут:
Цитата
Установлен запрет на количество запросов с одного ip адреса - не чаще чем 2 раза за 1 минуту
Если в источнике будет 3 инн, то 3 ответ - будет с ошибкой. Даже если сервис не успеет отработать блокировку быстро, врядли получится массово это реализовать.

А для точечного анализа, лучше VBA. Так и ищите VBA REST API
Контроль уникальности по двум независимым столбцам в Power Query
 
Андрей VG, Спасибо, Вам, за помощь!
Контроль уникальности по двум независимым столбцам в Power Query
 
Цитата
Андрей VG написал:
Очевидный вариант на List.Accumulate.
На локальном компе отработал на 3К строках, а на сервере ошибка:
Цитата
Expression.Error: При вычислении возникло переполнение стека. Продолжение невозможно.
Про WITH RECURSIVE было бы идеально, если бы не postgresql 8.2 :(

С List.Generate получилось, на сервере успешно отработало...
В коде ниже left и right поменяны на drop и make, и еще в процессе пришлось добавить значения null в left_id
Скрытый текст
Контроль уникальности по двум независимым столбцам в Power Query
 
Андрей VG, огромное спасибо за направление!

Про Generator логику понял (правда не понял зачем тут рекурсивная функция...), но у меня тогда вопрос, если каждый раз будет осуществляться индексированное обращение к источнику по номеру строки, не увеличит ли это нагрузку на источник в N раз, где N - количество записей (против последовательного прохода по строкам)? Или PQ увидит что обращения идут последовательно и будет также последовательно фетчить данные? Или PQ загонит таблицу в память? Или ему нужно об это сказать через Table.Buffer? Там и 100 метров не будет, таблица хоть и длинная, но узкая и на числах...
Изменено: vetrintsev - 23.06.2019 20:55:16
Контроль уникальности по двум независимым столбцам в Power Query
 
И что он вернет? Distinct может вернуть либо уникальные значения по одному столбу, либо уникальные сочетания по нескольким.

Здесь скриптом я бы реализовал такой алгоритм:
Код
инициализируем переменные max_left_id=-1,  max_right_id=-1
Цикл по всем записям, в цикле:
    если left_id>max_left_id и right_id>max_right_id, то:
        max_left_id=left_id
        max_right_id=right_id
        и выбрать текущую запись
    иначе пропускаем текущую запись
но скрипт запустить не где, кроме SQL и M.
Distinct выдаёт другой результат.  
Контроль уникальности по двум независимым столбцам в Power Query
 
Всем привет!

Сначала сам вопрос:
как из выборки:
left_id right_id
1 1
2 1
1 3
2 3
3 3
Оставить только строки:
Left_id right_id
1 1
2 3
А вот зачем это:


Есть таблица с событиями.
мне надо связать два типа события.

В одном отчетном периоде клиент может отключить старую услугу и подключить новую, вот надо в строку вывести код событий отключения и подключения, чтобы потом можно было сравнить сами услуги (старую и новую)

первый уровень усложнения: у клиента может быть несколько услуг, причём отключить он может две, а подключить одну. Благо требования достаточно мягкие, чтобы можно было просто сцепить события в порядке их возникновения: это я решил SQL запросом пронумеровал ссобытия попарно и сджоинил по его номеру.

но такой механизм не отвечает реалиям бизнесзадачи.

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

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

Данную задачу можно было бы решить, если можно бы было создать таблицу с двумя столбцами с констрейнтамии на уникальность в каждомъ. Тогда, полученный запрос просто бы пытался вставить в эту таблицу данные, и игнорировал бы ошибки, на выходе таблица содержала бы только корректные связи событий.
НО. Create table нельзя.

Соответственно, надо каким либо образом симулировать проверку на уникальность по каждому из двух столбцов.

Сам запрос вызывается из MS SSAS 2017 Tabular к хранилищу на postgresql 8.2, поэтому из инструментов только M и SQL...

 
Прямой запрос к модели PowerPivot и вывод в SmartTable, Как то можно составить ручной DAX или MDX запрос внутри Excel к PowerPivot?
 
С помощью VBA
Код
Sub test()
    
    Dim s As Worksheet
    Dim t As TableObject
    Dim dax_query As String
    
    Set s = ThisWorkbook.Sheets("Sheet6")
    
    Set t = s.ListObjects("Table_ExternalData_1").TableObject
    
    dax_query = t.WorkbookConnection.OLEDBConnection.CommandText
    
    MsgBox dax_query
    
End Sub
Нашел строку подключения:
Код
Provider=MSOLAP.5;
Persist Security Info=True;
Initial Catalog=Microsoft_SQLServer_AnalysisServices;
Data Source=$Embedded$;
MDX Compatibility=1;
Safety Options=2;
MDX Missing Member Mode=Error;
Subqueries=0;
Optimize Response=7;
Update Isolation Level=2
И даже виден DAX запрос.

Но подключение повторить не удалось...
Однако удалось внести изменение уже в самом VBA и обновить таблицу:
Код
t.WorkbookConnection.OLEDBConnection.CommandText = "some DAX query"
t.Refresh
Но попытки сделать кнопку обновления активной - оказались безуспешны.
+ подключение, как-будто одно на всех.

Цитата
PooHkrd написал:
По копайтесь в постах Максима Зеленского в поиске форума. Не так давно он давал ссылки как такое можно провернуть. Андрей VG кажется тоже на эту тему ссылки давал.
Не смог найти подходящие ответы.

Можете помочь советом? Или скажите, что это невозможно и MS намерено закрыл такую шикарную возможность :(
Прямой запрос к модели PowerPivot и вывод в SmartTable, Как то можно составить ручной DAX или MDX запрос внутри Excel к PowerPivot?
 
Правильно я понимаю, что это единственный вариант?  
Прямой запрос к модели PowerPivot и вывод в SmartTable, Как то можно составить ручной DAX или MDX запрос внутри Excel к PowerPivot?
 
Всем привет!

Есть модель в PowerPivot, есть сводная таблица подключенная к ней.
Но так как в Excel нет вохможности настроить выдачу при детализации данных для сложных рассчетов,
то постоянно лезу в саму модель и оттуда копирую данные, но при этом постоянно вручную настриваю фильтры.
А хотелось видеть это примерно так же, как реализовано в MS SSAS Tabular 2017, когда при детализации он создает привязанную смарт-таблицу с понятным запросом, например:
Код
DRILLTHROUGH MAXROWS 1000 
SELECT [Measures].[АСР.Churn] ON COLUMNS 
FROM (SELECT {[Отчетный период].[Отчетный период (по кварталам)].[Год].&[2018].&[4 кв-л.]} ON COLUMNS 
FROM (SELECT  FROM (SELECT ({[Отчетный период].[Отчетный период (по кварталам)].[Год].&[2019].&[1 кв-л.], 
    [Отчетный период].[Отчетный период (по кварталам)].[Год].&[2018].&[4 кв-л.]}) ON COLUMNS  
FROM ...

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

А если я делаю детализацию от модели в PowerPivot, то там нет такого запроса.

Есть ли возможность все таки создать произвольный запрос к экземпляру PowerPivot?

Как отключить автоматический перерасчет Excel при открытии его из Access, ну не получается сделать это в лоб
 
Цитата
JayBhagavan написал:
так работает:
Спасибо!
Вот так пришлось реализовать открытие книги, чтобы до открытия вырубить расчет и после закрытия вернуть обратно.
Код
Private excel_app As Object
Private excel_calculation As Integer
Private dummy_book As Object
Public source_file As String

Private Function Open_book()
    
    Set excel_app = CreateObject("Excel.application")
    
    Set dummy_book = excel_app.workbooks.Add
    excel_calculation = excel_app.Application.Calculation
    excel_app.Application.Calculation = -4135
    
    Set Open_book = excel_app.workbooks.Open(source_file, False, True)
    
End Function

Private Sub Close_excel()
    
    Dim book As Variant
    
    For Each book In excel_app.workbooks
        If Not book Is dummy_book Then book.Close False
    Next book
    
    excel_app.Application.Calculation = excel_calculation
    Set excel_app = Nothing
    dummy_book.Close False
    
End Sub

Sub Test()
    source_file = "some file.xlsx"
    With Open_book()
        'do something
    End With
    Close_excel
End Sub
Как отключить автоматический перерасчет Excel при открытии его из Access, ну не получается сделать это в лоб
 
Всем добрый день!

Из специфики вопроса найти ответ в интеренет мне не удалось.

В общем внутри Excel я могу отключить автоматический пересчет формул с помощью
Код
Application.Calculation = -4135
Но если я создаю экзепляр приложения в Access:
Код
Dim excel_app as Object
Set excel_app = CreateObject("Excel.application")
excel_calculation = excel_app.Calculation 'Запоминаем текущее значение
excel_app.Calculation = -4135
То я получаю ошибку - Error 2042, причем уже в выражении:
Код
excel_app.Calculation
Т.е. я даже прочитать его не могу...

Соответственно я хочу выключить автоматический пересчет формул до открытия книги, открыть книгу, и вернуть настройки обратно.
Но не могу :(

Кто-нибудь с таким сталкивался?

UPD:
Excel, Access 2016. Win 10
Изменено: vetrintsev - 17.12.2018 09:25:50
Как в одой и той же книге забрать из отчета сводной таблицы или Power Pivot данные и запихнуть их в PQ
 
Цитата
PooHkrd написал:
НО, если хочется по-извращаться, то запросом из имеющейся сводной можно сделать. Для этого нужно создать таблицу и в неё при помощи формулы ЯЧЕЙКА прописать путь к файлу (это если файл с запросом может находиться где угодно, если путь стационарный, то и путь к нему просто пропишите ручками)
Спасибо, идея понятная)
Как в одой и той же книге забрать из отчета сводной таблицы или Power Pivot данные и запихнуть их в PQ
 
Добрый день!

Не могу в интернете найти решение.

У меня настроены запросы в PQ которые формируют таблицы напрямую в Power Pivot.
Теперь я вытащил сводную таблицу из Power Pivot и отобрал некоторую выборку.
Следующим шагом мне надо использовать отфильтрованные ID шники для формирования детальной выгрузки.
Конечно можно руками натыкать все столбцы в сводной таблице и так получить детальную простыню.
Но на входе одного из запросов может прилететь таблица с переменным количеством столбцов, соответственно свод не покажет новые столбцы, если я их снова руками не перетыкаю.

Вопрос - можно ли, каким-то образом нацелить PQ на PowerPivot или саму сводную таблицу в той же книге, чтобы потом ее использовать для формирования других запросов?
Проход по цепочке с Power Query, Есть список повторных обращений, надо объединить все повторы в эпизоды
 
Цитата
Максим Зеленский написал:
Я как-то обленился и практически на кнопочках собрал (немного в другом виде, наверное?):
Table.FillDown - век живи, век учись! То что надо! Спасибо!
и никаких иттераций +3 строчки в запросе.
Проход по цепочке с Power Query, Есть список повторных обращений, надо объединить все повторы в эпизоды
 
Цитата
Андрей VG написал:
28 часов
опечатался - 48 часов, исправил.
Цитата
Андрей VG написал:
Всё, я пас.
Да я уже понял, что сам всех запутал своим "простым" примером) хотел как лучше.
Но Ваши идеи я понял, если получится - выложу, может еще кому пригодится разворачивать цепочки...
Проход по цепочке с Power Query, Есть список повторных обращений, надо объединить все повторы в эпизоды
 
Цитата
StepanWolkoff написал:
Как вот это понимать? В исходных данных есть признак клиента? Или как вы там определяете, что это обращение должно быть в эпизоде, а это нет, если у вас идет клиент1:1,2,4; клиент2:3,5,6 - по вашей логике это будет два эпизода всего, или по два эпизода у каждого, а итого будет четыре?
у меня все обращения по одному клиенту сгруппированы, отсортированы по дате, поэтому не принципиально - последовательность можно восстановить проиндексировав таблицу, и не важно в таком случае какой был номер у обращения. Даже связи между повторками есть в последовательных индексах (см. пример, второй запрос).

если у одного клиента по одной и той же тематике за последние 48 часов было зафиксировано обращение, то текущее обращение считается повторным. Но еще раз говорю, последовательная нумерация легко делается, причем даже без группировки - сортируем по клиенту, потом по дате, потом индексируем, и все соединено последовательно.

блин пока сообщение напишешь, светлые умы еще новый текст накидают) Спасибо Вам всем за отзывчивость!
Вернусь на форум через часа 2.
Изменено: vetrintsev - 16.05.2018 14:24:14
Страницы: 1 2 3 След.
Наверх