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

Страницы: 1 2 3 4 5 След.
Группировка строк с суммированием в PQ
 
Цитата
adamm написал:
unpivot b pivot, схожи с отменённым свёртыванием и сведённым столбцом
Это ровно они и есть
Цитата
adamm написал:
И как я понял вы добавили дубликат столбца, который соответствует "сегодня"
совершенно верно
Power query Открыть самый первый файл отчетного месяца
 
Цитата
Alexey_Spb написал:
Ради интереса проверьте на оригинальном коде:
Код
each let latest = List.Min(types[Date]) in [Date] = latest
По идее тоже должно быть 7.5 секунд.
Вот здесь как раз прогон по всему массиву идёт в каждой строке, т.е. на миллионе строк запрос ожидаемо подвисает.

Цитата
Максим Зеленский написал:
у меня вот такие результаты за 10 обновлений на массиве 1 млн дат:
Любопытно, вызывает куда больше доверия, чем мои расчёты "на коленке". Т.е. условно можно считать, что вариант с  [Date] = List.Min(Typed[Date]) на миллионе строк выполняется дольше остальных примерно в 1,5 раза.


Я бы предложил к обсуждению два вопроса:
1. Согласно посту #14 все варианты можно вывести один из другого, тогда почему разница с [Date] = List.Min(Typed[Date]) всё же существует?
2. Верно ли будет заключить, что мы можем малой кровью выполнять агрегацию по всему столбцу в построчных вычислениях?
Код
= Table.AddColumn(types, "temp", each ([Date] - List.Min(types[Date]))*[SomeValue])
Power query Открыть самый первый файл отчетного месяца
 
Alexey_Spb, я догадываюсь, что 2 и 3 варианты логически идентичны, но всё равно решил их сравнить. Вдруг есть какая-то оптимизация дефолтного кода, мало ли.
Изменено: Aleksei_Zhigulin - 20 Фев 2019 18:04:34
Power query Открыть самый первый файл отчетного месяца
 
PooHkrd, не думаю, что для данного примера разница критична, но спасибо за информацию, похоже, Вы правы. Руками заменил дефолтный код на
Код
each [Date] = List.Min(types[Date])

т.к. на первый взгляд показалось, что он идентичен.

На массиве 1 млн. строк

Код
each [Date] = List.Min(types[Date])

отработал за 7,5 сек.

Код
let earliest = List.Min(types[Date]) in each [Date] = earliest

за 5 сек.

с выносом earliest в отдельный шаг и затем

Код
each [Столбец3] = earliest

также за 5 сек.

Power query Открыть самый первый файл отчетного месяца
 
volfman, что-то сложный путь выбрали ИМХО. Вот решение в лоб:
Код
let
    Source = Folder.Files("C:\Temp\folder"),
    date = Table.AddColumn(Source, "Date", each Text.BeforeDelimiter([Name], "."), type text),
    replace = Table.ReplaceValue(date,"_","-",Replacer.ReplaceText,{"Date"}),
    types = Table.TransformColumnTypes(replace,{{"Date", type date}}),
    filter = Table.SelectRows(types, each [Date] = List.Min(types[Date])),
    content = Excel.Workbook(filter[Content]{0})
in
    content
Изменено: Aleksei_Zhigulin - 20 Фев 2019 15:07:02
Группировка строк с суммированием в PQ
 
adamm, так?
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Source, {"Город", "Фамилия", "Имя"}, "Дата", "Сумма"),
    pivot = Table.Pivot(unpivot, List.Distinct(unpivot[Дата]), "Дата", "Сумма", List.Sum),
    duplicate = Table.DuplicateColumn(pivot, Date.ToText(Date.From(DateTime.LocalNow()),"dd.MM.yy"), "За сегодня")
in
    duplicate
Сохранение итого все запроса в PQ в другой файл, Итоги выводятся в текущую книгу но нужно выводить в другой файл excel
 
naa700, нет, но Вы можете написать запрос сразу из "другого" файла.
PQ в условном или в настраиваемом столбце пронумеровать строки в случае истины
 
Цитата
Андрей VG написал:
можно сделать на порядок быстрее
Спасибо, буду знать. List.Generate использовал всего во второй раз, на моей памяти. С записями, по Вашему примеру, уже делал, теперь вот попробовал напрямую к таблице обращаться. Догадывался, что с записями на больших объёмах будет быстрее, но не ожидал, что настолько.
PQ в условном или в настраиваемом столбце пронумеровать строки в случае истины
 
Ещё вариант, для разнообразия.  :)  Правда, не в одну строку:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    list = List.Generate(()=>
[i = 0, a = Source{i}[Столбец2], b = Number.From(a<30), c = if b=0 then null else b],
each [i] < Table.RowCount(Source),
each [i = [i]+1, a = Source{i}[Столбец2], b = [b]+Number.From(a<30), c = if a < 30 then b else null], each [c]),
    final = Table.FromColumns(Table.ToColumns(Source)&{list}, Table.ColumnNames(Source)&{"<30"})
in
    final
Удаление дубликатов из 5 столбцов, После выгрузки контактов в Excel необходимо избавиться от дублей номеров, которые добавили мессенджеры
 
Цитата
PooHkrd написал:
Ну что ж может и я когда-нибудь дойду до такого скилла.
Ну, по-моему, Вам-то на скилл грех жаловаться. ;)  Хотя пределов совершенству нет, и до таких мастеров как Андрей VG нам по-прежнему далеко. Его решение более элегантное. Небольшой разбор, вдруг кому пригодится:
1. В моем коде используется конструкция Table.ToColumns(Table.Transpose(Source)), конечно, лаконичнее будет просто Table.ToRows(Source).
2. Необходимость шага transform в моем коде обусловлена тем, что в финальную таблицу должен был подаваться список столбцов одинаковой длины. Однако, функция Table.FromColumns(uniques, null) (или в данном примере можно просто Table.FromColumns(uniques)) работает со списками разной длины, недостающие значения заполняются как null.
3. Функция Table.Transpose имеет второй параметр, поэтому можно сразу передать в неё заголовки столбцов из исходной таблицы - Table.Transpose(Table.FromColumns(uniques, null), heads).
Вычисления над значениями через запятую, Необходимо выделить значения через запятую и произвести над ними вычисления
 
SergBSA, вариант в Power Query:
Код
let
    Данные = Excel.CurrentWorkbook(){[Name="Данные"]}[Content],
    Масса = Excel.CurrentWorkbook(){[Name="Масса"]}[Content],
    func = Splitter.SplitTextByDelimiter(","),
    first = Table.AddColumn(Данные, "first", each Text.BeforeDelimiter([Значения], ",")),
    count = Table.AddColumn(first, "count", each List.Count(func([Значения]))),
    sum = Table.AddColumn(count, "sum", each List.Sum(List.Transform(func([Значения]), each Масса{[Код = _]}[Масса])))
in
    sum
Изменено: Aleksei_Zhigulin - 14 Фев 2019 19:31:58
Удаление дубликатов из 5 столбцов, После выгрузки контактов в Excel необходимо избавиться от дублей номеров, которые добавили мессенджеры
 
Цитата
artyrH написал:
или все таки с клавиатуры...
В этом случае - да, с клавиатуры.

Цитата
PooHkrd написал:
Очень похоже, что у него за плечами большой опыт программирования на другом языке.
Вашими бы устами...  :D  Это не про меня, к сожалению) PQ осваивал по кнопкам, да и сейчас мне зачастую проще сформировать код кнопками, а потом уже корректировать вручную. Тем более, IntelliSense в Excel пока не подвезли.
Удаление дубликатов из 5 столбцов, После выгрузки контактов в Excel необходимо избавиться от дублей номеров, которые добавили мессенджеры
 
PooHkrd, поправлю чуток:
Код
= Table.Distinct(#"Другие столбцы с отмененным свертыванием", {"Индекс", "Значение"})
Получение нескольких таблиц с конкретными данными из одной
 
Ludmilaz77, вариант в Power Query.
Удаление дубликатов из 5 столбцов, После выгрузки контактов в Excel необходимо избавиться от дублей номеров, которые добавили мессенджеры
 
andreyglad-48, вариант в Power Query:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    toColumns = List.Transform(Table.ToColumns(Table.Transpose(Source)), each List.RemoveNulls(List.Distinct(_))),
    headers = Table.ColumnNames(Source),
    transform = List.Transform(toColumns, each _ & List.Repeat({null}, List.Count(headers) - List.Count(_))),
    final = #table(headers,transform)
in
    final
Из двух разных столбцов сделать один, Из двух разных столбцов сделать один
 
Цитата
ALexxela написал:
номера счетов должны оставаться в том порядке, в котором они и были
В предложенном мной варианте сначала берутся все значения из первой таблицы, потом из второй. Дубликаты удаляются, в остальном порядок остаётся прежним. Что не так?
Из двух разных столбцов сделать один, Из двух разных столбцов сделать один
 
ALexxela, если я верно понял задачу, и нужно получить объединённый список значений из двух столбцов (без дубликатов), то вот вариант в Power Query:
Код
let
    Source = Excel.CurrentWorkbook(),
    func = (tbl) => Source{[Name=tbl]}[Content][Table1],
    combine = List.Distinct(func("Tabelle1")&func("Tabelle3"))
in
    combine
Изменено: Aleksei_Zhigulin - 11 Фев 2019 13:43:28
Слияние групп столбцов, Слияние нескольких "групп" столбцов с однородными столбцами, макросом или Power Query
 
Ragnar64, примерно так:
Код
let
    Source = Excel.CurrentWorkbook(),
    filter = Table.SelectRows(Source, each List.Contains({"Рег1","Рег2"},[Name])),
    expand = Table.ExpandTableColumn(filter, "Content", Table.ColumnNames(filter{0}[Content])),
    unpivot = Table.UnpivotOtherColumns(expand, {"Код товара", "Name"}, "Поле", "Значение"),
    num = Table.AddColumn(unpivot, "num", each Text.End([Поле], 1)),
    transform = Table.TransformColumns(num, {"Поле", each Text.Trim(Text.Select(_,{"a".."я"," ", "+"}))}),
    pivot = Table.Pivot(transform, List.Distinct(transform[Поле]), "Поле", "Значение"),
    rename = Table.RenameColumns(pivot,{{"Name", "Регион"}}),
    remove = Table.RemoveColumns(rename,{"num", "Ссылка", "Комментарий"}),
    reorder = Table.ReorderColumns(remove,{"Регион", "Код товара", "Конкурент", "Цена", "Отсутствует", "Аналог", "Комментарий + Ссылка"})
in
    reorder

В исходных данных столбец "Акция" у Вас не заполнен, поэтому его нет в финальной таблице. Т.е. когда данные появятся, его просто нужно будет добавить на последнем шаге в список столбцов.
Слияние групп столбцов, Слияние нескольких "групп" столбцов с однородными столбцами, макросом или Power Query
 
Ragnar64, сведение столбцов сработало бы (и имело бы смысл), только если для каждой связки Код товара + Регион было бы только одно уникальное значение Конкурента, цены и т.д. Очевидно, это не Ваш случай. Т.е. нужно понять, как именно Вы хотите агрегировать данные. Например, неясно, из какой логики на листе Шаблон отсутствует Цена = 1899 по Коду товара 1001117174 и Региону 1.
Расчет списания товара по средней себестоимости в Power Query
 
alex112, как-то так:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица13"]}[Content],
    cols = Table.SelectColumns(Source,{"Дата", "ПриходШТ", "ПриходСумм", "РасходШТ"}),
    types = Table.TransformColumnTypes(cols,{{"Дата", type date}, {"ПриходШТ", Int64.Type}, {"ПриходСумм", Int64.Type}, {"РасходШТ", Int64.Type}}),
    replace = Table.ReplaceValue(types,null,0,Replacer.ReplaceValue,{"Дата", "ПриходШТ", "ПриходСумм", "РасходШТ"}),
    records = List.Buffer(Table.ToRecords(replace)),
    generate = List.Generate(()=>
[i=0, row = records{i},
ПриходСумм = row[ПриходСумм], ПриходШТ = row[ПриходШТ], РасходШТ = row[РасходШТ],
С2шт = ПриходШТ - РасходШТ, С2Сумм = ПриходСумм - РасходСумм,
РасходСумм = ПриходСумм/ПриходШТ*РасходШТ],
each [i]<List.Count(records),
each [i = [i]+1, row = records{i},
ПриходСумм = row[ПриходСумм], ПриходШТ = row[ПриходШТ], РасходШТ = row[РасходШТ],
С2шт = [С2шт] + ПриходШТ - РасходШТ, С2Сумм = [С2Сумм] + ПриходСумм - РасходСумм,
РасходСумм = ([С2Сумм] + ПриходСумм)/([С2шт] + ПриходШТ)*РасходШТ],
each [[РасходСумм], [С2шт], [С2Сумм]]),
    combine = List.Transform(List.Zip({records,generate}), Record.Combine),
    toTable = Table.FromList(combine, Splitter.SplitByNothing()),
    expand = Table.ExpandRecordColumn(toTable, "Column1", {"Дата", "ПриходШТ", "ПриходСумм", "РасходШТ", "РасходСумм", "С2шт", "С2Сумм"}),
    types1 = Table.TransformColumnTypes(expand,{{"Дата", type date}, {"ПриходШТ", Int64.Type}, {"ПриходСумм", Int64.Type}, {"РасходШТ", Int64.Type}, {"РасходСумм", type number}, {"С2шт", Int64.Type}, {"С2Сумм", type number}})
in
    types1
Изменено: Aleksei_Zhigulin - 6 Фев 2019 19:57:51
Расчет списания товара по средней себестоимости в Power Query
 
alex112, понятно, задачка интересная. Уверен, что в PQ такое можно повторить, на досуге попробую.
Расчет списания товара по средней себестоимости в Power Query
 
alex112, честно говоря, не понял, с чем у Вас возникли сложности, и причём тут цикл. Просто добавляете в PQ новые столбцы с элементарной арифметикой.

P.S. Могу только предположить, что из-за null у Вас получался некорректный результат.
Изменено: Aleksei_Zhigulin - 5 Фев 2019 13:00:10
PQ таблица с отрезком дат рабочих дней
 
artyrH, добавил в Ваш запрос несколько шагов.
Power Query, не все столбцы попадают в обработку при развертывании
 
Dyroff, добавьте вспомогательный столбец и измените шаг с развёртыванием:
Код
    add = Table.AddColumn(#"Удаленные столбцы", "headers", each Table.ColumnNames([Data])),
    #"Развернутый элемент Data" = Table.ExpandTableColumn(add, "Data", List.Distinct(List.Combine(add[headers]))),

Возможно, порядок столбцов изменится.

PQ удалить столбцы с прошедшей датой
 
artyrH, но лучше через Table.SelectColumns:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    final = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), each Date.From(_) >= Date.From(DateTime.FixedLocalNow())))
in
    final
Изменено: Aleksei_Zhigulin - 31 Янв 2019 18:40:46
PQ удалить столбцы с прошедшей датой
 
artyrH,
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Источник, "Индекс", 0, 1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Индекс"}, "Дата", "Сумма"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Date.From([Дата]) >= Date.From(DateTime.FixedLocalNow())),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Дата]), "Дата", "Сумма", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Индекс"})
in
    #"Removed Columns"
Можно ли выцепить из ячейки числа и отдельно их суммировать?
 
drezna,

Полагаю, это можно сделать и макросами, и формулами, и в Power Query, но неужели Вам удобнее вбивать данные в таком формате?
Power Query порядок столбцов
 
vavilon2707,

Если Вы имеете в виду, что внутри PQ столбцы в другом порядке - при объединении запросов первым укажите запрос с правильным порядком столбцов. Если Вы про выгрузку на лист, то, вероятно, в свойствах запроса у Вас стоит эта галка:
Изменено: Aleksei_Zhigulin - 31 Янв 2019 12:09:28
Преобразование базы тестов с вопросами и правильными ответами в шпаргалку по алфавиту.
 
terros,

Выделить в каждом вопросе первое слово полужирным шрифтом можно только макросом, насколько я понимаю. Иначе можно было бы сделать в Power Query.
Подсчёт уникальных фамилий с несколькими условиями
 
dioniska169, gолагаю, макросом это сделать можно, помочь с написанием не могу.
Цитата
dioniska169 написал:
данные параметры будут встроены в отчёт с уже установленной структурой, поэтому лучше формулой.
Поясните, пожалуйста, в чём конкретно Вы видите проблему с использованием варианта в PQ? Т.к. Вы упомянули параметры, судя по всему Вы имеете в виду исходные данные. Если они такие же, как в Вашем файле примере, не вижу препятствий. Максимум, нужно будет использовать в запросе другие имена таблиц. Если Вы про установленную структуру финального отчёта, никто не мешает Вам подтянуть в него информацию из предварительно рассчитанной в PQ таблицы.
Страницы: 1 2 3 4 5 След.
Наверх