vetrintsev (Автор тем)

Выключение параллельной загрузки PowerQuery, Excel 2016 - нагружает процессор на 100% при обновлении большой модели.

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

28.01.2021 10:00:48

Всем добрый день!

Надо запускать обновление файла excel в фоне (хочу запускать с помощью VBA), который содержит модель Power Pivot с запросами в Power Query.
Но при запуске

Код
WorkBook.RefreshAll

даже с

Код
Application.MultiThreadCalculation.Enabled = False

все равно запускает несколько mashup контейнеров (по количеству ядер).

Есть ли решение, как запустить обновление всех запросов в одном потоке?
В Power BI есть галочка - параллельная загрузка, а как это сделать в Excel?

Перейти

SELECTCOLUMNS поверх NATURALLEFTOUTERJOIN с разным контекстом, Нужна помощь в DAX

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

28.06.2019 01:24:53

Всем привет!

В общем, 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

Скрытый текст

Код

Line   Records   Physical Query Plan
1      AddColumns: IterPhyOp LogOp=SelectColumns IterCols(3, 4, 5, 6)(''[migration_id], ''[B], ''[A], ''[])
2          LeftOuterJoin: IterPhyOp LogOp=LeftOuterJoin IterCols(3, 4, 5, 6)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], ''[B])
3              AddColumns: IterPhyOp LogOp=SelectColumns IterCols(3, 4, 5)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61])
4   607               Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(19, 95, 140)('Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], 'Наряды'[Слой разовой услуги], 'orders_migrations'[migration_id]) #Records=607 #KeyCols=146 #ValueCols=0
5   607                   ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=607
6                          VertipaqResult: IterPhyOp #FieldCols=3 #ValueCols=0
7                  Add: LookupPhyOp LogOp=Add LookupCols(140)('orders_migrations'[migration_id]) Double
8                      ColValue<'orders_migrations'[migration_id]>: LookupPhyOp LogOp=ColValue<'orders_migrations'[migration_id]>'orders_migrations'[migration_id] LookupCols(140)('orders_migrations'[migration_id]) Double
9                      Constant: LookupPhyOp LogOp=Constant Integer 0
10   9           Spool_MultiValuedHashLookup: IterPhyOp LogOp=CalculateTable LookupCols(3)(''[migration_id]) IterCols(4)(''[B]) #Records=9 #KeyCols=2 #ValueCols=0
11   9               AggregationSpool<GroupBy>: SpoolPhyOp #Records=9
12                      AddColumns: IterPhyOp LogOp=SelectColumns IterCols(3, 4)(''[migration_id], ''[B])
13   9                       Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(95, 140)('Наряды'[Слой разовой услуги], 'orders_migrations'[migration_id]) #Records=9 #KeyCols=146 #ValueCols=0
14   9                           ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=9
15                                  VertipaqResult: IterPhyOp #FieldCols=2 #ValueCols=0
16                          Add: LookupPhyOp LogOp=Add LookupCols(140)('orders_migrations'[migration_id]) Double
17                              ColValue<'orders_migrations'[migration_id]>: LookupPhyOp LogOp=ColValue<'orders_migrations'[migration_id]>'orders_migrations'[migration_id] LookupCols(140)('orders_migrations'[migration_id]) Double
18                              Constant: LookupPhyOp LogOp=Constant Integer 0
19          ColValue<''[migration_id]>: LookupPhyOp LogOp=ColValue<''[migration_id]>''[migration_id] LookupCols(3)(''[migration_id]) Double

А вот без SELECTCOLUMNS:

Код

Line   Records   Physical Query Plan
1      LeftOuterJoin: IterPhyOp LogOp=LeftOuterJoin IterCols(3, 4, 5, 6)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], ''[B])
2          AddColumns: IterPhyOp LogOp=SelectColumns IterCols(3, 4, 5)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61])
3   607           Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(19, 95, 140)('Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], 'Наряды'[Слой разовой услуги], 'orders_migrations'[migration_id]) #Records=607 #KeyCols=146 #ValueCols=0
4   607               ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=607
5                      VertipaqResult: IterPhyOp #FieldCols=3 #ValueCols=0
6              Add: LookupPhyOp LogOp=Add LookupCols(140)('orders_migrations'[migration_id]) Double
7                  ColValue<'orders_migrations'[migration_id]>: LookupPhyOp LogOp=ColValue<'orders_migrations'[migration_id]>'orders_migrations'[migration_id] LookupCols(140)('orders_migrations'[migration_id]) Double
8                  Constant: LookupPhyOp LogOp=Constant Integer 0
9   9       Spool_MultiValuedHashLookup: IterPhyOp LogOp=CalculateTable LookupCols(3)(''[migration_id]) IterCols(4)(''[B]) #Records=9 #KeyCols=2 #ValueCols=0
10   9           AggregationSpool<GroupBy>: SpoolPhyOp #Records=9
11                  AddColumns: IterPhyOp LogOp=SelectColumns IterCols(3, 4)(''[migration_id], ''[B])
12   9                   Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(95, 140)('Наряды'[Слой разовой услуги], 'orders_migrations'[migration_id]) #Records=9 #KeyCols=146 #ValueCols=0
13   9                       ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=9
14                              VertipaqResult: IterPhyOp #FieldCols=2 #ValueCols=0
15                      Add: LookupPhyOp LogOp=Add LookupCols(140)('orders_migrations'[migration_id]) Double
16                          ColValue<'orders_migrations'[migration_id]>: LookupPhyOp LogOp=ColValue<'orders_migrations'[migration_id]>'orders_migrations'[migration_id] LookupCols(140)('orders_migrations'[migration_id]) Double
17                          Constant: LookupPhyOp LogOp=Constant Integer 0

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

Скрытый текст

Код

Line   Logical Query Plan
1   CalculateTable: RelLogOp DependOnCols()() 3-6 RequiredCols(3, 4, 5, 6)(''[migration_id], ''[B], ''[A], ''[])
2       CalculateTable: RelLogOp DependOnCols()() 3-6 RequiredCols(3, 4, 5, 6)(''[migration_id], ''[B], ''[A], ''[])
3           SelectColumns: RelLogOp DependOnCols()() 3-6 RequiredCols(3, 4, 5, 6)(''[migration_id], ''[B], ''[A], ''[])
4               LeftOuterJoin: RelLogOp DependOnCols()() 3-6 RequiredCols(3, 4, 5, 6)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], ''[B])
5                   CalculateTable: RelLogOp DependOnCols()() 3-5 RequiredCols(3, 4, 5)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61])
6                       SelectColumns: RelLogOp DependOnCols()() 3-5 RequiredCols(3, 4, 5)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61])
7                           Scan_Vertipaq: RelLogOp DependOnCols()() 3-140 RequiredCols(19, 95, 140)('Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], 'Наряды'[Слой разовой услуги], 'orders_migrations'[migration_id])
8                           Add: ScaLogOp DependOnCols(140)('orders_migrations'[migration_id]) Double DominantValue=NONE
9                               'orders_migrations'[migration_id]: ScaLogOp DependOnCols(140)('orders_migrations'[migration_id]) Double DominantValue=NONE
10                               Constant: ScaLogOp DependOnCols()() Integer DominantValue=0
11                           'Наряды'[Слой разовой услуги]: ScaLogOp DependOnCols(95)('Наряды'[Слой разовой услуги]) String DominantValue=NONE
12                   CalculateTable: RelLogOp DependOnCols()() 3-5 RequiredCols(3, 4)(''[migration_id], ''[B])
13                       SelectColumns: RelLogOp DependOnCols()() 3-5 RequiredCols(3, 4)(''[migration_id], ''[B])
14                           Scan_Vertipaq: RelLogOp DependOnCols()() 3-140 RequiredCols(95, 140)('Наряды'[Слой разовой услуги], 'orders_migrations'[migration_id])
15                           Add: ScaLogOp DependOnCols(140)('orders_migrations'[migration_id]) Double DominantValue=NONE
16                               'orders_migrations'[migration_id]: ScaLogOp DependOnCols(140)('orders_migrations'[migration_id]) Double DominantValue=NONE
17                               Constant: ScaLogOp DependOnCols()() Integer DominantValue=0
18                           'Наряды'[Слой разовой услуги]: ScaLogOp DependOnCols(95)('Наряды'[Слой разовой услуги]) String DominantValue=NONE
19               ''[migration_id]: ScaLogOp DependOnCols(3)(''[migration_id]) Double DominantValue=NONE
20               ''[B]: ScaLogOp DependOnCols(6)(''[B]) String DominantValue=NONE
21               ''[A]: ScaLogOp DependOnCols(4)(''[A]) String DominantValue=NONE
22       Filter_Vertipaq: RelLogOp DependOnCols()() 0-0 RequiredCols(0)('Отчетный период'[Год])
23           Scan_Vertipaq: RelLogOp DependOnCols()() 0-0 RequiredCols(0)('Отчетный период'[Год])
24           'Отчетный период'[Год] = 2019: ScaLogOp DependOnCols(0)('Отчетный период'[Год]) Boolean DominantValue=FALSE
25       Filter_Vertipaq: RelLogOp DependOnCols()() 1-1 RequiredCols(1)('Отчетный период'[Номер квартала])
26           Scan_Vertipaq: RelLogOp DependOnCols()() 1-1 RequiredCols(1)('Отчетный период'[Номер квартала])
27           'Отчетный период'[Номер квартала] = 1: ScaLogOp DependOnCols(1)('Отчетный период'[Номер квартала]) Boolean DominantValue=FALSE
28       Filter_Vertipaq: RelLogOp DependOnCols()() 2-2 RequiredCols(2)('Продукт'[Подгруппа продуктов])
29           Scan_Vertipaq: RelLogOp DependOnCols()() 2-2 RequiredCols(2)('Продукт'[Подгруппа продуктов])
30           'Продукт'[Подгруппа продуктов] = Продукт1: ScaLogOp DependOnCols(2)('Продукт'[Подгруппа продуктов]) Boolean DominantValue=FALSE

А вот без SELECTCOLUMNS:

Код

Line   Logical Query Plan
1   CalculateTable: RelLogOp DependOnCols()() 3-6 RequiredCols(3, 4, 5, 6)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], ''[B])
2       CalculateTable: RelLogOp DependOnCols()() 3-6 RequiredCols(3, 4, 5, 6)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], ''[B])
3           LeftOuterJoin: RelLogOp DependOnCols()() 3-6 RequiredCols(3, 4, 5, 6)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], ''[B])
4               CalculateTable: RelLogOp DependOnCols()() 3-5 RequiredCols(3, 4, 5)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61])
5                   SelectColumns: RelLogOp DependOnCols()() 3-5 RequiredCols(3, 4, 5)(''[migration_id], ''[A], 'Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61])
6                       Scan_Vertipaq: RelLogOp DependOnCols()() 3-140 RequiredCols(19, 95, 140)('Услуги'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], 'Наряды'[Слой разовой услуги], 'orders_migrations'[migration_id])
7                       Add: ScaLogOp DependOnCols(140)('orders_migrations'[migration_id]) Double DominantValue=NONE
8                           'orders_migrations'[migration_id]: ScaLogOp DependOnCols(140)('orders_migrations'[migration_id]) Double DominantValue=NONE
9                           Constant: ScaLogOp DependOnCols()() Integer DominantValue=0
10                       'Наряды'[Слой разовой услуги]: ScaLogOp DependOnCols(95)('Наряды'[Слой разовой услуги]) String DominantValue=NONE
11               CalculateTable: RelLogOp DependOnCols()() 3-5 RequiredCols(3, 4)(''[migration_id], ''[B])
12                   SelectColumns: RelLogOp DependOnCols()() 3-5 RequiredCols(3, 4)(''[migration_id], ''[B])
13                       Scan_Vertipaq: RelLogOp DependOnCols()() 3-140 RequiredCols(95, 140)('Наряды'[Слой разовой услуги], 'orders_migrations'[migration_id])
14                       Add: ScaLogOp DependOnCols(140)('orders_migrations'[migration_id]) Double DominantValue=NONE
15                           'orders_migrations'[migration_id]: ScaLogOp DependOnCols(140)('orders_migrations'[migration_id]) Double DominantValue=NONE
16                           Constant: ScaLogOp DependOnCols()() Integer DominantValue=0
17                       'Наряды'[Слой разовой услуги]: ScaLogOp DependOnCols(95)('Наряды'[Слой разовой услуги]) String DominantValue=NONE
18       Filter_Vertipaq: RelLogOp DependOnCols()() 0-0 RequiredCols(0)('Отчетный период'[Год])
19           Scan_Vertipaq: RelLogOp DependOnCols()() 0-0 RequiredCols(0)('Отчетный период'[Год])
20           'Отчетный период'[Год] = 2019: ScaLogOp DependOnCols(0)('Отчетный период'[Год]) Boolean DominantValue=FALSE
21       Filter_Vertipaq: RelLogOp DependOnCols()() 1-1 RequiredCols(1)('Отчетный период'[Номер квартала])
22           Scan_Vertipaq: RelLogOp DependOnCols()() 1-1 RequiredCols(1)('Отчетный период'[Номер квартала])
23           'Отчетный период'[Номер квартала] = 1: ScaLogOp DependOnCols(1)('Отчетный период'[Номер квартала]) Boolean DominantValue=FALSE
24       Filter_Vertipaq: RelLogOp DependOnCols()() 2-2 RequiredCols(2)('Продукт'[Подгруппа продуктов])
25           Scan_Vertipaq: RelLogOp DependOnCols()() 2-2 RequiredCols(2)('Продукт'[Подгруппа продуктов])
26           'Продукт'[Подгруппа продуктов] = Продукт1: ScaLogOp DependOnCols(2)('Продукт'[Подгруппа продуктов]) Boolean DominantValue=FALS

Выглядит вывод вот так:

Скрытый текст

А без SELECTCOLUMNS выглядит вот так:

Скрытый текст

У кого-нибудь есть идеи как это вылечить?)

Изменено: vetrintsev - 28.06.2019 02:11:16

Перейти

Контроль уникальности по двум независимым столбцам в Power Query

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

23.06.2019 14:55:55

Всем привет!

Сначала сам вопрос:
как из выборки:

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?

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

10.04.2019 22:00:52

Всем привет!

Есть модель в 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, ну не получается сделать это в лоб

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

17.12.2018 09:25:17

Всем добрый день!

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

В общем внутри 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

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

17.05.2018 11:02:33

Добрый день!

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

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

Вопрос - можно ли, каким-то образом нацелить PQ на PowerPivot или саму сводную таблицу в той же книге, чтобы потом ее использовать для формирования других запросов?

Перейти

Проход по цепочке с Power Query, Есть список повторных обращений, надо объединить все повторы в эпизоды

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

16.05.2018 10:31:40

Добрый день!

Вот есть, например, такая таблица:

Номер обращения \|	Номер предыдущего обращения
2	1
3	2
16	15
17	16
18	17

Таблица отсортирована, повторы из одного эпизода всегда идут следом друг за другом.

Мне надо ее превратить в такую:

Номер обращения \|	Номер предыдущего обращения \|	Номер первого обращения в эпизоде
2	1	1
3	2	1
16	15	15
17	16	15
18	17	15

И все это надо сделать в Power Query.
Такие деревья можно быстро разобрать в Power Pivot, но хотелось бы сделать это именно в PQ.
Конечно можно сделать это в VBA или на чистом Excel, который может смотреть на предыдущую строку, но это тоже не подходит.

Может есть какие-нибудь простые алгоритмы на этот счет? Или может я упустил какую формулу из справочника?

Изменено: vetrintsev - 16.05.2018 10:33:11

Перейти

Текст из столбца в ячейку через запятую по условию в PowerQuery, Ну не могу нигде найти детали

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

11.12.2017 09:49:37

Всем добрый день!

да, есть много тем, как это сделать с помощью самописной ВПР.
Но мне надо в PowerQuery.

Нашел вот такой код:

Код
Table.ToList( Table.Transpose( Table.FromList( Table.Column([Products], "ProductName") ) ), Combiner.CombineTextByDelimiter(", ") )

Но мне же надо склеить текст по условию, в редакторе дернул формулу фильтра:

Код
each Table.SelectRows(#"Измененный тип", each ([#"ProductName"] = "apple"))

но он всегда (что и логично из формулы) выдает таблицу по продукту "apple"...
Как отфильтровать таблицу по значению из ячейки "ProductName" текущей строки?

Помогите, пожалуйста...

Перейти

DAX: Посчитать среднее по столбцу в связанной таблице с фильтром по текущей таблице

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

20.07.2017 15:14:42

Добрый день!

Не могу сам справиться.
Поиском пользовался, не нашел, как сформулировать тоже пока не очень понимаю.

В таблице Показатели создал меру:

Код
Средний чек:=CALCULATE(AVERAGE('Услуги'[Чек]);'Показатели'[Отключен]=1)

Хочу посчитать средний чек отключенных клиентов.
Информация о чеке хранится в таблице "Услуги", которая связана с таблицей "Показатели" связью: Показатели[Услуга]=Услуги[Код]. При этом для одной записи таблицы "Услуги" есть множество связанных записей в таблице "Показатели".

Но если эту меру посчитать, то она возвращает среднее по всем строкам таблицы "Услуги" вне зависимости от контекста, даже фильтр 'Показатели'[Отключен]=1 здесь не работает. Как будто связь вообще не задействована.

Подскажите пожалуйста,
как правильно посчитать среднее по значениям в связанной таблице "Услуги" для всех связанных записей с таблицей "Показатели"?

Изменено: vetrintsev - 20.07.2017 15:15:48

Перейти

Семантическая ошибка с функцией "Calculate"

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

19.07.2017 16:20:42

Добрый день!

Чувствую себя не умным)
При расчете меры получил такую ошибку:

Цитата
Семантическая ошибка: Функция "Calculate" использовалась в логическом выражении (True/False), которое используется как выражение фильтра таблицы. Такое использование недопустимо.

Вот формула этой меры:

Код
База на конец периода:=CALCULATE(DISTINCTCOUNT([Услуга]);'Показатели'[Период]='Показатели'[Дата конца периода])

При этом 'Показатели'[Дата конца периода] - это мера, рассчитанная по формуле:

Код
Дата конца периода:=MAX([Период])

Цель формулы - посчитать количество уникальных записей в выборке, для которых Период равен максимальному значению Периода в этой выборке.

Помогите, пожалуйста.

Перейти

PowerPivot. Подсчет меры через промежуточную таблицу, Как правильно построить связь?

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

22.06.2017 22:50:11

Добрый день!

Открыл для себя совершенно недавно мир Power надстроек

Такой мини Olap кубик оказывается)
Столкнулся с проблемой.

Приложил схему связей между таблицами, в том числе и в аксессе (там видно связь между конкретными полями).
Это пример, все персонажи выдуманные и совпадения случайны. Главное что повторяет проблемную структуру.

Так вот, если в PP создать сводную таблицу и заполнить структуру следующим образом:
Значения: Средний чек (мера в таблице Продажи)
Строки: Услуги[Наименование услуги]
Столбцы: Продукты[Наименование продукта]

То вылезет ошибка - что между указанными полями нет связи.

Проблема в том, что для одной продажи, может быть подвязано несколько услуг в таблице Дополнительные услуги.

Как мне правильно посчитать средний чек продажи?
Помогите пожалуйста...

Перейти

VBA: Как сохранить свои метаданные, Как сохранить большой массив строк в книге средствами VBA

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

10.03.2016 21:51:52

Добрый день!

В процессе выполнения макроса компилируется большой массив данных за длительное время.
Эта компиляция необходима один раз для книги.
На выходе получаем массив с Объектами пользовательского класса.

В дальнейшем по запросу пользователя другая процедура опирается на этот скомпилированный набор данных.

Встал вопрос:
Возможно ли в самой книге Excel физически сохранить свои метаданные, которые потом, при открытии файла будут просто возвращаться в память?

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

Перейти

VarPtr(Obj)=VarPtr(Obj) или Obj Is Obj, Что быстрее

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

03.03.2016 21:41:48

Друзья, я провел одно наблюдение.

Произвел в цикле For по 100 000 000 итераций простейших операций по сравнению объектов:

Код

b = Obj Is Obj 'Время выполнения = 6,6016 сек

b = ObjPtr(Obj) = ObjPtr(Obj) 'Время выполнения = 4,2422

b = VarPtr(Obj) = VarPtr(Obj) 'Время выполнения = 1,5625

И вижу, что сравнение указателей с VarPtr происходит в 4,23 раза быстрее, чем использование для этих же целей оператора Is.

Я правильно понимаю, что в одном Application не может быть двух одинаковых указателей VarPtr ?

Если это так, то зачем использовать Is ?

Я понимаю, что записывать VarPtr и хранить чревато последствиями, так как объект может быть уничтожен мусорщиком и на его адрес может встать другой объект. Но если точно известно что объекты на момент проверки не уничтожены, то этим можно спокойно пользоваться ? Верно ?

Знатоки, пожалуйста, вразумите меня

Перейти

Как понять, какие листы выбраны, Пользователь выбрал несколько листов, как понять какие именно

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

08.12.2015 13:13:02

Добрый день!

Делаю себе маленькую форму для замены части формул на защищенных листах.
Так как обычная CTRL+H отказывается.

Все сделал, все работает.
Хочу выбрать несколько листов, и применить макрос к аналогичным диапазонам на выбранных листах.

НО! Не могу найти в интернете, как получить список выбранных листов...
Как выбрать несколько листов - понятно, а как понять, какие выделены - не найду.

Кто имеет такой опыт, подскажите, пожалуйста.

Перейти

Максимальная длина SQL для ODBC, Какова максимальная длина SQL запроса в ODBC подключениях к EXCEL

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

22.10.2015 15:50:22

Однофорумчане, добрый день

У меня сотрудники ведут несколько файлов (у каждого сотрудника свой файл), я сделал себе сводный файл, который через ODBC подключение SQL запросом транспонирует и объединяет данные со всех файлов.
Всего подключенных файла 4.

Так вот нехитрыми манипуляциями сделал запрос длиной 18 746 символов без пробелов/абзацев и т.д. (а с последними вообще 20 551 символов), т.е. почти по 5000 строк на одного сотрудника.
Все это сохранил в *.odc файл и пользуюсь. Скорость работы на уровне.
Строка подключения:

Код
DBQ=\\Отчеты\Бюджет\БОМ_Семенова.xlsx; Driver={Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)}; DriverId=1046; MaxBufferSize=2048; PageTimeout=5; ReadOnly=1;

И тут стало интересно - а какова максимальная длина SQL запроса?
Ведь с каждым сотрудником запрос будет увеличиваться на 5000 символов...
Как эта система переварит 100000 символов или более?

Изменено: vetrintsev - 22.10.2015 17:01:18

Перейти

Сводная таблица из нескольких книг с разгруппировкой, Необходимо сформулировать оптимальный сложный SQL запрос

vetrintsev

Пользователь

Сообщений: 77 Регистрация: 08.10.2015

08.10.2015 13:40:43

Добрый день!

Перерыл интернет, решения не нашел.

Прикрепил наглядную модель.

У меня есть две таблицы, которые заполняют два сотрудника - Ваня и Вася - на практике это расходы с разбивкой по городам. Сотрудникам так удобнее заполнять данные.

Хочу построить сводную таблицу по всем сотрудникам, но при этом в источнике данных столбцы с названиями городов превращаются в два столбца:
1) Название города;
2) Сумма по городу;

Сейчас я смог сделать два решения

Решение 1: через 3 подключения

1-е и 2-е подключения собирают данные из файлов Вани и Васи в файл "Сводная" в соответствующие листы, при этом SQL запрос строится из нескольких подзапросов, которые переворачивают данные под нужный формат.

3-е подключение соединяет данные из двух листов этой же книги "Сводная" и строит по ним сводную таблицу.
Плюс данного решения - понятные SQL запросы.

Решение 2: через 1 подключение (сложное)

Берем SQL 1-го и 2-го подключения из Решения 1 и соединяем через UNION ALL
SQL получается не читаемым и громоздким

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

В данной модели это терпимо, так как столбцов мало.

Вопрос:

В реальности столбцов по городам 17 и сотрудников больше.

Использовать 2-е решение нереально, SQL будет не обосновано огромным.

Прошу Вас помочь составить одного SQL запроса таким образом, чтобы он выполнил пошаговые действия:
1) Сначала соединил все таблицы пользователей, результату подзапроса присвоим псевдоним tb1;
2) Потом по псевдониму tb1 совершил "разгруппировку" данных по всем городам, результату дадим псевдоним tb2;
3) После чего INNER JOIN по tb1.ID=tb2.ID;

Проблема вся в псевдонимах...
Я не могу сообразить как сделать SQL запрос оптимальным, и как добиться использования одного псевдонима для нескольких подзапросов, может все это надо обернуть в еще один SELECT?.

Элегантное решение этого вопроса будет полезно многим!)
Ведь для составления сводной таблицы по данным от разных пользователей, потребуется прописать всего одно подключение, это очень удобно и сильно облегчит процессы контроля и отчетностей.

Заранее спасибо за Ваши комментарии!)))

Изменено: vetrintsev - 08.10.2015 13:41:37

Перейти