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

Страницы: 1 2 3 4 5 6 След.
Какие возможности Excel использовать, Большая таблица, порядка 50 контрагентов, с несколькими продажами нужно собрать данные по каждому контрагенту отдельно.
 
Только вы возьмите обновленную версию файла, в первой я что-то невнимательно прочитал и сделал выбор тех ,у кого 100 тыс. и более. Потом исправил.
Какие возможности Excel использовать, Большая таблица, порядка 50 контрагентов, с несколькими продажами нужно собрать данные по каждому контрагенту отдельно.
 
Это делается за минуту (и даже быстрее) в PowerQuery.

P.S.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Groupped = Table.Group(Source, {"Name"}, {{"Total", each List.Sum([Spent]), type number}}),
    FRows = Table.SelectRows(Groupped, each [Total] < 100000),
    Sorted = Table.Sort(FRows,{{"Total", Order.Ascending}})
in
    Sorted
Изменено: Alexey_Spb - 21 Фев 2019 17:14:49
Ошибка в PQ: слишком длинное выражение, ошибка при обновлении данных из источников и затем операции слияния таблиц
 
Не скрин ошибка наполовину загаженный рилагать надо, а образец xlsx-файла с запросом и данными, на которых возникает ошибка.
Power query Открыть самый первый файл отчетного месяца
 
Цитата
Aleksei_Zhigulin написал: let earliest = List.Min(types[Date]) in each [Date] = earliest
Ну так это и есть вынос earliest вне тела функции )) Понятно что разницы не будет. Ради интереса проверьте на оригинальном коде:
Код
each let latest = List.Min(types[Date]) in [Date] = latest
По идее тоже должно быть 7.5 секунд.
Изменено: Alexey_Spb - 20 Фев 2019 20:13:43
PQ как обратиться к шагу другого запроса
 
Цитата
PooHkrd написал:
научите человека плохому
Почему плохому? Активно использую эту функцию, разницы в скорости не заметил, а возможности дает фантастические.

Правда у меня довольно специфические типовые приложения - сложные обработки небольших обьемов данных, а не наоборот )

Еще варианты:
Код
Record.Field(#shared, "Источник")

#shared[Источник]
Изменено: Alexey_Spb - 20 Фев 2019 17:36:23
Power query Открыть самый первый файл отчетного месяца
 
Цитата
PooHkrd написал: эта переменная
Предлагаю заменить на "именованное выражение" чтобы не путать новичков ))

Смотрите, данное вражение имеет диапазон видимости только внутри функции и вычисляется при запуске функции, не имея связи с другими ее запусками. Хотя, может какая-то внутренняя оптимизация и есть, черт знает. Но тогда она должна работать и во втором случае. Какая разница интерпретатору, вычислить выражение или вычислить и присвоить его полю записи (let это же на самом деле запись засахаренная).Можно, кстати, проверить наши догадки на какой-нибудь трудоемкой операции по фильтрацию списка в несколько миллионов строк.
Изменено: Alexey_Spb - 20 Фев 2019 20:13:31
Power query Открыть самый первый файл отчетного месяца
 
Цитата
PooHkrd написал:
В таком случае он точно не будет для каждой строки таблицы пересчитывать каждый раз минимум по столбцу из предыдущего шага, а посчитает один раз, сохранит в переменной и уже с ней будет сравнивать каждую строку.
Точно? Ведь это определение функции, и при каждом ее вызове по идее это значение будет высчитываться заново. Но это тоже мои предположения.
Если вынести вычисление минимума за тело функции, то, уверен, будет быстрее.
Power query Открыть самый первый файл отчетного месяца
 
Надо выбрать все файлы, имеющие минимальную дату в рамках каждого месяца?

Зачем так, сложно, вот тупой вариант в редакторе
Код
let
    Source = Folder.Files("C:\Temp\1"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Name", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", Int64.Type}, {"Name.2", Int64.Type}, {"Name.3", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Name.3", each Text.BeforeDelimiter(_, "."), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Name.1", "Name.2"}, {{"Day", each List.Min([Name.3]), type text}})
in
    #"Grouped Rows"
Группировка строк с суммированием в PQ
 
А вам что нужно в результате? Зачем вы создаете столбец "за сегодня", при этом оставляя старые?
PQ как обратиться к шагу другого запроса
 
А почему бы просто не сделать результат в виде записи?
Удаление строк в PQ если данные есть из всех столбцов только в столбце NAME
 
Блин, как не зайдешь в тему, уже все порешали )) К сожалению, пока могу заходить только вечером (еще плюс отрицательная разница во времени с Москвой  :( )

Ладно, напишу хоть что-нибудь  :)
Цитата
PooHkrd написал:
#"Удаленные столбцы" = Table.RemoveColumns(Expanded,{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
Имхо, я бы лучше не удалял ненужные, а выбирал только нужные столбцы так как это чуть-чуть увеличивает устойчивость запроса к левым столбцам и их переименованию.
Изменено: Alexey_Spb - 13 Фев 2019 17:06:24
Расчет списания товара по средней себестоимости в Power Query
 
Цитата
alex112 написал:
Вот и хочу выяснить можно ли повторить это в PQ?

Можно, но не самым оптимальным образом. Ранее я приводил пример рекурсивной функции, находящей стартовую дату этапаз, которая зависит от завершения множества предыдущих этапов.
Изменено: Alexey_Spb - 5 Фев 2019 15:02:37
PowerPivot, PowerQuery, PowerBI
 
Цитата
alpopo написал: В чем смысл частого употребления as any?
Здесь это было написано чтобы показать разницу между вариантами.
А если говорить в целом, то PooHkrd довольно подробно все написал.
Power Qwery - подгружать один и тот же файл с разными названиями
 
Цитата
alpopo написал:
Запрос на картинке.

Здесь принято выкладывать подготовленные примеры в формате Excel, а не картинки.
PQ в условный столбец добавить еще условие
 
Цитата
PooHkrd написал:
все гуру в унисон твердят что обращение к ячейкам по их адресам это медленно
Да, я сейчас проверил, это просто epic fail. На миллионе строк - пара строк в секунду. Жесть  8-0  (core i5-8250, 8 gb ram)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Query1"]}[Content],
    Ai = Table.Buffer(Table.AddIndexColumn(Source, "Index", 0, 1)),
    Ac = Table.AddColumn(Ai, "Custom", each Ai{[Index=[Index]-1]}[Query1])
in
    Ac

// UPDATED:

Это не недостаток алгоритма, это просто медленный доступ к элементам таблицы.

Если взять вместо обращения к таблицы обращение к списку, сделанному из колонки по номеру элемента (инексу), то операция на миллионе строк выполнется принципиально быстрее (в районе 10 секунд):
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Query1"]}[Content],
    Ai = Table.AddIndexColumn(Source, "Index", 0, 1),
    L = List.Buffer(Ai[Query1]),
    Ac = Table.AddColumn(Ai, "Custom", each L{[Index]-1}) 
in
    Ac

Но наверное, действительно самый быстрый способ добавить элемент перед списком, удалить последний и использовать его как новы столбец, о котором указал PooHkrd.
Изменено: Alexey_Spb - 4 Фев 2019 01:04:59
Power Qwery - подгружать один и тот же файл с разными названиями
 
А запрос в файле где?
Наиболее быстрое сравнение двух огромных списков
 
БМВ,.в редакторе действительно проще. Визуалка же удобна чтобы выполнить какую нибудь муторную операцию типа доступа к таблице из файла или ручного приведения типа столбцов. Имхо, оптимально их комбировать.

P. S. Кто нибудь знает как отключить генерацию гееславных имён типа #"Я щёлкнул три раза мышкой"? Задалбывает из предварительно сделанного в визуалке запроса вычищать эту дрянь.  
Изменено: Alexey_Spb - 3 Фев 2019 16:34:28
PQ в условный столбец добавить еще условие
 
Цитата
artyrH написал:
легче и, по моему, быстрее
Если вам так удобно, то лучше делать так .

Но насчёт быстрее - надо проверять так как в вашем случае нужно добавить два индексных столбца (скорее всего, эта операция проходит быстрее чем добавление стандартного столбца). Затем вам нужно сделать операцию join, развернуть столбцы, убрать лишние.

Всё будет зависеть от того, как устроена функция объединения внутри pq - внутри неё все равно надо для каждого значения ключа делать выборку. Может, конечно, есть оптимизация для повторяющиеся ключей, но это явно не наш случай.

Все это одной операции добавления столбца, но с функцией вычисляемой на каждой строке таблицы. Тем не менее доберусь до экселя и проверю.  
Изменено: Alexey_Spb - 3 Фев 2019 15:06:27
Power Qwery - подгружать один и тот же файл с разными названиями
 
Цитата
dennn написал:
но там фигурирует ИМЯ файла
. Кажется, я понял. Вы взяли пример из статьи на этом сайте, где делается функция из запроса, которая по полному пути файла берет из него таблицу.

Если так, то в качестве аргумента берите имя файла, а полный путь склеивайте в функции:
Код
(filename) =>
   ..... "c:\work\" & filename ... 
Изменено: Alexey_Spb - 3 Фев 2019 11:00:05
PQ в условный столбец добавить еще условие
 
Цитата
artyrH написал: пробовал прикрутить и ничего не получилось.
Да, я ошибся - надо было писать так:

Код
(if [Index] > 0 then Таблица[Столбец_Ячейки]{[Index] - 1} else null)

Где таблица - имя таблицы с предыдущего шага, вы можете посмотреть его в расширенном редакторе.

Цитата
Anton555 написал: конечно можно, добавляйте индекс от 0 + еще один индекс от 1 и соединяйте
А зачем так сложно и долго?  
Изменено: Alexey_Spb - 4 Фев 2019 00:34:50
Power Query: Быстрое обновление
 
Пишите пожалуйста код из расширенного редактора вместе с файлом при помощи форматирования "код". В экселе на телефоне pq нет)  
PQ в условный столбец добавить еще условие
 
Цитата
artyrH написал: если  ячейка выше содержит значение
Если вам нужно сделать зависимость значения от другой строки в таблице, то все несколько усложняется.
Добавьте в таблицу индексную числовую колонку, которая начинается с нуля с увеличением на единицу . Назовите её Index. Тогда обращение в формуле добавления нового столбца к ячейке в предыдущей строке будет таким:
Код
(if [Index] > 0 then [Столбец_Ячейки]{[Index] - 1} else null) 

Прям копируете и используйте со скобками.  
Проверка на допустимые символы
 
Цитата
IADmitriy написал: PQ не подходит.
Почему PQ не подходит для той задачи, в которой он оптимален?
Проверка на допустимые символы
 
У меня практически мгновенно конвертирует 10 тысяч строк по 180 символов. Простой код PQ по фильтрации вcего лишнего:
Код
List.Transform
 (
  List.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Column1]),
  each Text.Select(_, {"M", "T", "W", "R", "F", "S", "U"})
 )
Вариант с проверкой (true/false) на левые символы и выводом признака в новую колонку:
Код
Table.AddColumn
 (
  Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
  "Bad name",
  each Text.Select([Column1], {"M", "T", "W", "R", "F", "S", "U"}) <> [Column1]
 )
Работает так же почти мгновенно.
Изменено: Alexey_Spb - 1 Фев 2019 17:04:41
Выбрать в отдельный список имена одного пола
 
Элементарное действие на PQ
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FR = Table.SelectRows(Source, each ([Пол] = "м"))
in
    FR
PQ вывести таблицу со строками-заголовками, Вывести таблицу с заголовками из первой не повторяющейся ячейки в Power Query
 
Код от Андрей VG более правильный и оптимальный, я в своем варианте рассчитывал операцию формирования полноценной записи для заголовков выполнить "нахаляву" за счет опции MissingField.UseNull, а когда увидел что ее не оказалось, не стал продолжать идею. Хотя более правильным являлось бы добитие записей с заголовками всеми колонками, что и сделано у Андрей VG:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Gr = Table.Group(Source, {"Поставщик", "№", "Приход"}, {"temp", (sub) =>
    let
        first = sub{0},
        head = first[Поставщик] & " №" & Text.From(first[#"№"]) & " от " & Text.From(Date.From(first[Приход])) & " (" & Text.Upper(first[Покрытие]) & ")"
    in
        #table({"Код"}, {{head}}) & sub
    })[[temp]],
    Eo = Table.ExpandTableColumn(Gr, "temp", Table.ColumnNames(Source))
in
    Eo
Изменено: Alexey_Spb - 1 Фев 2019 09:51:53
PQ вывести таблицу со строками-заголовками, Вывести таблицу с заголовками из первой не повторяющейся ячейки в Power Query
 
Цитата
PooHkrd написал:
Откуда дровишки?
Из ошибки pq - "вы передали в функцию три параметра, когда у ней максимум два" .  
Создание Столбца по условию(если содержится слово РАСЧЕТ) Power Query, подсказать функцию
 
try проверяет выражение внутри этой конструкции и при ошибке возвращает то что идет после otherwise.

То есть если в ячейке не строка, то возникате ошибка, которая обрабатывается try и заменяется на null.

Alexey_Spb, я опять после Вас убираю! Зачем здесь пустая строка? Зачем в предыдущем ообщении цитата (удалена)? Зачем в предыдущем сообщении  по две пустых подряд (почищено)?
PQ вывести таблицу со строками-заголовками, Вывести таблицу с заголовками из первой не повторяющейся ячейки в Power Query
 
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Gr = Table.Group(Source, {"Поставщик", "№", "Приход"}, {{"Agg", each _, type table}}),
    Lt = Table.AddColumn(Gr, "Out", each {[Код = Text.From([Поставщик]) & " №" & Text.From([#"№"]) & " от " & Text.From(Date.From([Приход]))]} & Table.ToRecords([Agg])),
    Exp = Table.ExpandListColumn(Lt, "Out"),
    Ro = Table.SelectColumns(Exp,{"Out"}),
    Cols = {"Код", "OПоставщик", "№", "Приход", "Штрих-код", "Вид", "Проба", "Артикул", "Тип", "Размер", "Вес", "Покрытие", "Бренд", "Цена-закуп", "Цена", "Цена со СКИДКОЙ", "№Заказа"},
    Eo = Table.ExpandRecordColumn(Ro, "Out", Cols, Cols)
in
    Eo
P.S. Мелкомягкие испортили функцию Table.FromRecords по сравнению с описанием на сайте - теперь у нее два аргумента вместо трех и нельзя использовать MissingField.UseNull :-( Из-за этого код вырос в два раза, блин!!

А раньше можно было объединить списке в столбце Out и сделать операцию Table.FromRecords с опцией MissingField.UsNull - таблица готова.  
Изменено: Alexey_Spb - 31 Янв 2019 17:55:36
Создание Столбца по условию(если содержится слово РАСЧЕТ) Power Query, подсказать функцию
 
Александр L , у вас там попадаются цифры, null - отсюда и ошибка. Столбец содержит смешанные данные. Перед операцией, которую вам написал PooHkrd,преобразуйте столбец в текст.

Еще второй вариант, но правильнее работать с нормальными данными:
Код
= try if Text.Contains( [Column2], "РАСЧЕТ" ) then [Column2] else null otherwise null
Изменено: Alexey_Spb - 31 Янв 2019 18:01:32
Страницы: 1 2 3 4 5 6 След.
Наверх