Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 След.
День недели из даты. Power Query
 
вкладка преобразование - дата - день - день недели
Сохранение ссылок в Power Query
 
Цитата
PooHkrd написал:
Если формулу внести в столбец при помощи автозаполнения
Простите а можно вот эту часть поподробнее?

У меня обычно выходит так: в результирующую таблицу PQ добавляю столбцы с формулами  - автозаполнение ссылки, на смарт таблицу все хорошо.
при обновлении запроса добавляются новые строки и эксель увеличивает всю таблицу подставляя новые строки. при этом новые вставленные строки будут пустыми без настроенных формул.
как этого перебороть?
Проставить в ячейку значение, соответствующее максимальному из другого диапазона
 
в том же столбце не выйдет --> циклические ссылки а в целом вот:
{=СУММ(($C$6:$C$102)*(--($A$6:$A$102=СЖПРОБЕЛЫ(B7)))*--($D$6:$D$102=МАКС($D$6:$D$102*--($A$6:$A$102=СЖПРОБЕЛЫ(B7)))))}

СЖПРОБЕЛЫ - потому что ваши данные грязные - если будите лучше готовить исходные будет легче писать формулы.
Power Query - определение функции обновляется при каждом изменении запроса, как настроить функцию в PQ, так чтобы она обновлялась при каждом изменении исходного запроса
 
PooHkrd,да верно при сборе из папки сгенерятся и файл пример и параметр и функция, и они окажутся связанными. так вот и вопрос эта связь, ее видно в описании запроса, но как то можно ей управлять? где то должно быть прописано ))

попробуем на англ ресурсах поискать.

У кого англоязычная версия PQ, можете посмотреть как эта надпись по английски звучит?
Счет в диапазоне смешанных значений
 
alex1210,к сожалению не понял из вашей формулы, чего вы хотели добиться.
давайте так, залейте еще один пример, без лишних листов. с исходными данными и с набором значений что должно из всего этого получиться.
и попробуем решить ваши пример.
Почему Power Query так медленно работает?
 
JosephineUa,на вскидку код совсем легкий и тормозить не должен. могу сделать несколько предположений:
1 в шаге с группировкой в колонке 13 есть нет только цифры но и ошибки или текст  -  PQ это не любит
2 в вашем файле этот запрос не единственный и при изменении одного начинают обновляться все остальные
3 источник данные я не совсем понял - это текущая книга эксель, а дальше что? все листы книги? какая то пользовательская функция? может где то здесь томоза

попробуйте последовательно устранить каждый из пунктов, и сравните быстродействие
Power Query - определение функции обновляется при каждом изменении запроса, как настроить функцию в PQ, так чтобы она обновлялась при каждом изменении исходного запроса
 
Добрый день, В PQ, при загрузке файлов из папки, программа самостоятельно создает цепочку связанных запросов, параметр, пример обработки и функцию, которую будет применять к каждому загружаемому файлу. У этой функции по умолчанию есть очень интересный параметр  - "определение этой функции обновляется при каждом изменении запроса". Иными словами эта функция связанна с примером обработки и, при каждом изменении примера обработки, аналогично изменится и наша функция.


Устанавливается этот параметр автоматически и последствии его можно отключить. Но вот, как наоборот его включить? Допустим связь между примером и функцией сбилась? Или я сам захотел связать этот пример с новой функцией? И, самый интересный вопрос, - а можно указывать пример обработки за пределами рабочей книги, например в отдельном файле?
Количество дней недели между двумя датами
 
Здесь хочется немного добавить, во первых вы наверное заметили что формула массива работает, но вычисляет довольно медленно - это происходит из за использования в массиве целого столбца СТРОКА(A:A) - формула выглядит лаконично, но работает не быстро. Если ограничить эксель в используемых датах то можно сильно повысить скорость вычисления. Например используем минимальные и максимальные значения дат как границы вычисляемого диапазона вот так: СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L))) этот прием позволяет задать произвольный дипазон только из используемых дат.

и напоследок вариант с удалением праздничных дат (список дат конечно прийдется заполнить вручную)
=СУММ(--(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L))));$O$7:$O$9;0))*1<>1;ЕСЛИ((--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)))<=L7)*--(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)))>=J7));ДЕНЬНЕД(СТРОКА(ДВССЫЛ("A"&МИН(J:J)&":A"&МАКС(L:L)));11);0);0)=ЕСЛИ(--(C7:I7<>"");{1;2;3;4;5;6;7};"НЕТ")))
Изменено: Blood81 - 26 Апр 2019 20:58:17
Количество дней недели между двумя датами
 
Можно вот так, без дополнительного листа
Код
=СУММ(--(ЕСЛИ((--(СТРОКА(A:A)<=L7)*--(СТРОКА(A:A)>=J7));ДЕНЬНЕД(СТРОКА(A:A));0)=ЕСЛИ(--(C7:I7<>"");{1;2;3;4;5;6;7};"НЕТ")))

формула массива
Изменено: Blood81 - 26 Апр 2019 18:44:35
Power Query открывает файлы источники без команды, сбои в работе запросов приводят к нарушению вычислений ETL и начинают самостоятельно открываться файлы источники
 
Итого, для нового файла изменил формат всех источников на xlsx, избыточного форматирования и обрывков данных там нет все чисто (спасибо  inquire).Проблем не замечено.
Однако файл со старыми источниками в формате xls, так же работает без сбоев в настоящее время. утверждать что причина была в источниках не могу.

есть мнение что сработала цепь проблем:  1 взаимные ссылки запросов друг на друга вернее на промежуточные шаги с использованием "meta" --> сбой алгоритма вычисления запросов --> ошибка выполнения запроса --> начинаются ошибки в фоновом выполнении и мы видим открывающиеся файлы которые раньше не видели --> так как на открытие файла экселю нужны ресурсы имеем комбинированый эффект тормозов вычисления и тормозов открытия файлов.
проблему цикличности алгоритма решил разрезанием запроса на несколько командой "извлечь предыдущий", новых проблем пока не заметил.

PooHkrd, спасибо за консультацию. пойду попробую проделать все тоже но в .csv
Power Query открывает файлы источники без команды, сбои в работе запросов приводят к нарушению вычислений ETL и начинают самостоятельно открываться файлы источники
 
PooHkrd,у меня xls выгрузки из 1С - начал использовать старый формат взамен xlsx из за того что при передаче данных 1С в формат эксель 2007 и старше происходит дополнительная обработка этих данных, эксель добавляет своих форматов и сильно усложняет жизнь. Соответственно старый формат xlы такого не делает. отсюда вопрос откуда там форматирование? и какая разница для query на форматы?

основную версию понял попробую изменить форматы исходников. отпишусь по результатам.
Power Query открывает файлы источники без команды, сбои в работе запросов приводят к нарушению вычислений ETL и начинают самостоятельно открываться файлы источники
 
PooHkrd, да у меня открываются  .xls и .xlsx на .csv пока такого не замечал
Power Query открывает файлы источники без команды, сбои в работе запросов приводят к нарушению вычислений ETL и начинают самостоятельно открываться файлы источники
 
Добрый день,
Проявилась неожиданная проблема при работе с запросами в Power Query.
Суть проблемы - при вычислении запроса начинает тормозить компьютер.Нне может, ни просчитать обновление запроса, ни выгрузить результат. Далее, после появления сообщения об ошибке (предлагают послать нахмуренный смайлик), система начинает самостоятельно открывать файлы источники. При попытке их закрывать вручную, открытие повторяется. Открывает не все источники, а только относящиеся к последнему редактированному запросу - даже если в нем всего одна таблица в одну строку.
Проблема плавающая причину вычислить пока не смог.
Кто нибудь сталкивался с подобным? есть способы защиты от этой ошибки?
PQ: Подстановка значений с разными условиями
 
Aleksei_Zhigulin,какой интересный способ объявить пользовательскую функцию, без "Let ... in"
так любую функцию можно в переменную закодировать? а как быть с параметрами функции где их указывать у переменой потом?
Умная таблица с разным количеством подкатегорий
 
вот такое вариант решения отфильтрует вам необходимые блоки (данные / повторить фильтр) - но, имхо, если у вас таблицы на 1000 наименований лучше искать  решения в какой либо базе данных
Изменено: Blood81 - 11 Апр 2019 18:21:56
[ Закрыто] Универсальная формула ИНДЕКС
 
Сергей,да за вами, флудерами, не угонишься вроде перед отправкой даже обновил страницу не увидел ничего нового...

тему пердлагаю переименовать в "суровая дедовщина" и отправить в курилку  :D  
[ Закрыто] Универсальная формула ИНДЕКС
 
у вас в исходных данных одни названия а в сводной другие в таком случае компьютер не понимает что чему соответствует можно сделать вот так:
Не суммируются данные столбца
 
У вас в цифровые значения попадают пробелы (точнее непечатные символы) разделители разрядов,

"215 024,58" выберите любую ячейку разделителем и увидите там еще один символ.вариант решения1:заменить эти символы на всем листе - сначала находите в ячейке этот символ - копируете его ctrl+c.  затем ctrl+h -вызываем меню замены, в строку найти вставляем скопированный символ в строку заменить ничего и нажимаем "заменить все". готово, теперь эксель понимает эти цифры и может их просуммировать.
Изменено: Blood81 - 21 Июн 2018 15:14:33
Редактирование данных в последнем столбце умной таблицей PowerQuery (столбец создан вручую)
 
Преложенный Михаилом вариант - столбец комментариев создается из текущей таблицы при обновлении запроса - работает только когда ваша база постоянно расширяется, так как при удалении индекса Description пропадет и описание к нему. Если такое ограничение допустимо то используйте - а если нет то придется генерировать еще и таблицу архива комментариев.
Изменено: Blood81 - 21 Июн 2018 11:00:44
Время обновления запросов Power Query, способ измерения быстродействия
 
PooHkrd, спасибо, пошел пробовать )))
Поиск даты в формате МЕСЯЦ()&ГОД() в диапазоне дат, Оптимизация поиска
 
на 29/02/2017 - выдает эпическую ошибку ))))
вот вариант формулы тоже самое что и у PooHkrd, только по другому ))
=ЕСЛИ(СЧЁТЕСЛИМН(   $B$2:$B$35;  ">="&ДАТА(ГОД($A$2);МЕСЯЦ($A$2);1);     $B$2:$B$35;     "<"&ДАТА(ГОД($A$2);МЕСЯЦ($A$2)+1;1))    >0;    "Да";   "Нет")
Время обновления запросов Power Query, способ измерения быстродействия
 
Добрый день, уважаемые форумчане.

возник вопрос: существует ли какой либо способ измерить скорость обновления запроса в power query по аналогии с RangeTimer  - макросом

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

поделитесь опытом кто то что знает по данной теме )))))
 
облако тегов в поиске, прям вот только увидел
 
Добрый день, модераторам и форумчанам.
заметил при поиске интересное облако тегов над формой поиска
[img]c:\YandexDisk\YandexDisk\%D0%A1%D0%BA%D1%80%D0%B8%D0%BD%D1%88%D0%BE%D1%82%D1%8B\2018-02-21_08-49-30.png[/img]
вопрос оно там всегда было а я не пользовался? или это обновление удобное?

второй вопрос а принцип работы этих полей? отфильтровывает результат поиска по заданному тегу? а если по двум отфильтровать хочу можно как то это сделать?
DAX Сумма Нарастающим Итогом, для суммирования в PP по отфильтрованным данным
 
Цитата
Андрей VG написал:
1. применять нужно его разновидность ALLSELECTED
да с ALLSELECTED все заработало. Спасибо огромное ))))))

Цитата
Андрей VG написал:
2. к тому, что нужно, а именно к календарю - вы же его фильтруете срезом, а уже он определяет, что видно в таблице фактов?
я не до конца перестроился с обычного Экселя на вычисление в Столбцах и Моделях данных - потому и не понимаю до конца синтаксис формул,
тоесть интуитивно я понимаю что формула должна "читаться" но я ее читаю так как привык в эксель вот столбец вот сумма по признакам и т.д.
Ps а нули они только при таком срезе неосмысленые и там для наглядности ошибки, а если выбрать другой период то и нули пропадут.
Спасибо еще раз.
DAX Сумма Нарастающим Итогом, для суммирования в PP по отфильтрованным данным
 
очень справедливое замечание Андрей, полностью принимаю, в работе пишу адекватные названия, чес слово - тут торопился пример оформить для планеты потому и ломанул - а PP очень плохо кушает смену имен на лету...

Цитата
Андрей VG написал: вы используете ALL для таблицы фактов? Вот и слетают все фильтры...
в варианте убрать All из Filter  моя мера превращается в обычную сумму без нарастающего итога
DAX Сумма Нарастающим Итогом, для суммирования в PP по отфильтрованным данным
 
без файла боюсь смысл формулы будет не понятным....
но в целом я так и сделал

Код
сумм наростающий итог:=CALCULATE(SUM([Сумма]);
                            FILTER(ALL('Таблица1');
                                  [Дата]<=MAX('Таблица2'[Календарь])
                                  )
                                )

в такой версии сумма "нарастает" от первой даты во всей базе, а я пытаюсь сделать нарастающий итог по отфильтрованной части
Изменено: Blood81 - 29 Авг 2017 16:03:23
Как "разобрать" сгруппированные строки по столбцам, чем то похоже с задачей которую решал Николай в макросе "Редизайнер"
 
попробуйте вот такой подход
https://yadi.sk/i/BgI6RB4h3MRTSe
если не поможет постарайтесь сделать понятный файл с исходными данными и нужным вам результатом
сразу замечу за третий уровень группировки вы не говорили так что я тоже не заморачивался
PS всю сложность поставленной Вами задачи можно решить перенастроив отчет в 1С - это будет гораздо быстрее и удобней.
DAX Сумма Нарастающим Итогом, для суммирования в PP по отфильтрованным данным
 
Добрый день Уважаемые Форумчане.

возник вот такой вопрос:
как настроить в PP меру для расчета суммы нарастающим итогом?
погуглил существующие приемы:
http://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115
https://social.technet.microsoft.com/Forums/en-US/de544149-a358-4421-9405-e09457374a4b/cumulative-to...
http://www.finalytics.pro/inform/top20-dax/

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

самое близкое что я нашел это функция TOTALMTD() - соответсвенно нарастающий итог с начала месяца.
или ее аналог:
Код
VAR RowDate = Table1[Date]
RETURN
CALCULATE (
        SUM ( Table1[Recurring] );
        FILTER (
            Table1;
            Table1[Date] <= RowDate
                && Mounth ( Table1[Date] ) = Mounth ( RowDate )

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

файл не пролезает на форум  -  выложил на диск:
https://yadi.sk/d/twrFg5ee3MRBVN
Power BI - выпадающие подсказки на "египетском" не понятном языке, у кого то выскакивали похожие? есть способы поставить русский язык на подсказки?
 
начал вводить формулу и вот такая картинка

забавно конечно но неожиданно где можно это перенастроить?
Power Query Быстрая замена по справочнику, поиск легкого решения
 
Цитата
PooHkrd написал:
поиск в справочнике происходит столько раз, сколько имеется строк в вашем массиве
ну вот и ответ в различиях пересчета вложенный цикл против доп столбца с расчетами
Цитата
PooHkrd написал:
И раз пошла такая пьянка, то за какое время выполняется мой запрос?
при выполнении кода с Table.Join этот же запрос на этом же массиве пересчитывается за 54 / 45 / 50 сек   -  примерно те 50 сек что и Table.NestedJoin  с разворачиванием столбца

Цитата
Андрей VG написал:
Если же вы не поленитесь использовать Table.Join функцию
:D  уже не поленился Спасибо Андрей
Код
    dict = Table.Buffer(Excel.CurrentWorkbook(){[Name="Имена_Складов"]}[Content]),
    JoinDict = Table.Join(#"Повышенные заголовки",{"Склад"},dict,{"В плане"},JoinKind.LeftOuter),
    //    #"Развернутый элемент NewColumn2" = Table.ExpandTableColumn(JoinDict, "NewColumn", {"Должно быть"}, {"Должно быть"}),
    #"Добавлен пользовательский объект3" = Table.AddColumn(JoinDict, "Пользовательская", each if [Должно быть]=null then [Склад] else [Должно быть]),
    #"Переименованные столбцы2" = Table.RenameColumns(#"Добавлен пользовательский объект3",{{"Склад", "Удалить"}, {"Пользовательская", "Склад"}}),
Изменено: Blood81 - 23 Авг 2017 13:57:13
Страницы: 1 2 3 4 След.
Наверх