Страницы: 1
RSS
Power query Открыть самый первый файл отчетного месяца
 
Друзья, снова я к вам.
Итак, файлы лежат в папке и имеют названия вида: файл 2019_02_20.xlsx
В power query передается в качестве параметра текущая отчетная дата.
Помогите открыть файл из папки, который бы имел минимальную дату в названии файла месяца, указанного в параметре.
Пытался вот так отловить дату, но что-то не работает :-(
Код
#"Новая дата"= List.Max(Table.FromRecords(
List.Generate(
()=>[id=1,dEx=Date.StartOfMonth(Date.From(Value.FromText(#"Параметры"{0}[Значение])))],
each Table.RowCount(Table.SelectRows(Источник, each Text.Contains([Name], (Date.ToText([dEx], "yyyy_MM_dd")))))=0,
each [id=[id]+1,dEx=Date.AddDays([dEx],1)])
)[dEx])
 
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.02.2019 15:07:02
 
Надо выбрать все файлы, имеющие минимальную дату в рамках каждого месяца?

Зачем так, сложно, вот тупой вариант в редакторе
Код
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"
 
В тексте
Цитата
volfman написал:
который бы имел минимальную дату в названии файла месяца, указанного в параметре.
в коде
Цитата
volfman написал:
#"Новая дата"= List.Max(Table.FromRecords...
Вас ничего не смущает?
Цитата
Aleksei_Zhigulin написал:
filter = Table.SelectRows(types, each [Date] = List.Min(types[Date]))
Так лучше не надо делать. На больших массивах ленивые вычисления могут повесить запрос. Лучше так:
Код
filter = Table.SelectRows(types, each let latest = List.Min(types[Date]) in [Date] = latest)

В таком случае он точно не будет для каждой строки таблицы пересчитывать каждый раз минимум по столбцу из предыдущего шага, а посчитает один раз, сохранит в переменной и уже с ней будет сравнивать каждую строку.
Aleksei_Zhigulin, Alexey_Spb, по-моему вы оба не обратили внимание на
Цитата
volfman написал:
В power query передается в качестве параметра текущая отчетная дата...
Value.FromText(#"Параметры"{0}[Значение])...
Предлагаю такой вариант:
Код
let
    Источник = Folder.Files("E:\1"),
    ДобДата = Table.AddColumn(Источник, "Пользовательская", each Date.From( Text.Replace( Text.BetweenDelimiters([Name], " ", "."), "_", "." ) ), type date),
    ФильтрПоМесяцуПараметра = Table.SelectRows(ДобДата, each Date.Month([Пользовательская]) = Date.Month(Параметры) ),
    ВыборМинДаты = Table.SelectRows(ФильтрПоМесяцуПараметра, let earliest = List.Min(ФильтрПоМесяцуПараметра[Пользовательская]) in each [Пользовательская] = earliest){0}[Content],
    ЛеземВФайл = Excel.Workbook(ВыборМинДаты)
in
    ЛеземВФайл
Изменено: PooHkrd - 20.02.2019 16:19:34
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
В таком случае он точно не будет для каждой строки таблицы пересчитывать каждый раз минимум по столбцу из предыдущего шага, а посчитает один раз, сохранит в переменной и уже с ней будет сравнивать каждую строку.
Точно? Ведь это определение функции, и при каждом ее вызове по идее это значение будет высчитываться заново. Но это тоже мои предположения.
Если вынести вычисление минимума за тело функции, то, уверен, будет быстрее.
 
Цитата
PooHkrd написал:
Код
each let latest = List.Min(types[Date]) in [Date] = latest)
В таком случае он точно не будет для каждой строки таблицы пересчитывать каждый раз минимум по столбцу из предыдущего шага, а посчитает один раз, сохранит в переменной и уже с ней будет сравнивать каждую строку.
Вы уверены?.. по идее, мы передаем функцию-comparer, которая будет вызвана N раз для каждой строки.
Что эта функция
Код
each [Date] = List.Min(types[Date])

что эта
Код
each let latest = List.Min(types[Date]) in [Date] = latest

будут вызваны одинаковое количество раз, соответственно List.Min(types[Date]) так же будет посчитан одинаковое количество раз.
Тогда уж надо добавлять до Table.SelectRows такой шаг:
Код
latest = List.Min(types[Date])

и уже тогда
Код
each [Date] = latest
Изменено: Максим Зеленский - 20.02.2019 16:46:37
F1 творит чудеса
 
все примеры без источника
 
Alexey_Spb, точно. Проверял неоднократно. К тому же именно таким образом генерит код сам редактор при выборе по фильтру поздней/ранней даты. Подозреваю что это не спроста.
Если переменная один раз посчиталась и на выходе простой тип (число/текст/дата) то эта переменная автоматом пишется в память, и при следующем обращении к этой переменной не пересчитывает её а берет готовое значение. В данном случае функция вызывается один раз до each, после чего уже по рассчитанному значению прогоняется итератор. если же List.Min загнать внутрь each, то он каждый раз и посчитается.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал: эта переменная
Предлагаю заменить на "именованное выражение" чтобы не путать новичков ))

Смотрите, данное вражение имеет диапазон видимости только внутри функции и вычисляется при запуске функции, не имея связи с другими ее запусками. Хотя, может какая-то внутренняя оптимизация и есть, черт знает. Но тогда она должна работать и во втором случае. Какая разница интерпретатору, вычислить выражение или вычислить и присвоить его полю записи (let это же на самом деле запись засахаренная).Можно, кстати, проверить наши догадки на какой-нибудь трудоемкой операции по фильтрацию списка в несколько миллионов строк.
Изменено: Alexey_Spb - 20.02.2019 20:13:31
 
Максим Зеленский, each забыл, тут согласен.
Вот горшок пустой, он предмет простой...
 
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 сек.

 
Цитата
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.02.2019 20:13:43
 
Alexey_Spb, я догадываюсь, что 2 и 3 варианты логически идентичны, но всё равно решил их сравнить. Вдруг есть какая-то оптимизация дефолтного кода, мало ли.
Изменено: Aleksei_Zhigulin - 20.02.2019 18:04:34
 
Цитата
PooHkrd написал:
Если переменная один раз посчиталась и на выходе простой тип (число/текст/дата) то эта переменная автоматом пишется в память, и при следующем обращении к этой переменной не пересчитывает её а берет готовое значение. В данном случае функция вызывается один раз до each,
А здесь другой принцип.
Смотрите, вы даете в виде функции в Table.SelectRows выражение, которое можно переписать как отдельный шаг и затем использовать в Table.SelectRows:
Код
MyFunction = let earliest = List.Min(Typed[Date]) in (_)=>_[Date] = earliest,
Filtered = Table.SelectRows(Typed, MyFunction)

результат его вычисления - функция (function value), принимающая аргумент _:

Вопрос: на каком этапе вычисляется List.Min(Typed[Date])? Предположим, что Table.SelectColumns сначала вычисляет выражение MyFunction и получает function value, которое затем invoked (вычисляется с передачей аргументов) для каждой строки. Так вот из-за ленивости let-выражения, будет ли List.Min(Typed[Date]) рассчитан до того, как функция вызывается для расчёта результата?
А если вот так переписать:
Код
MyFunction = each [Date] = List.Min(Typed[Date])
будет ли результат тот же?
а если написать просто
Код
Table.SelectRows(Typed, each [Date] = List.Min(Typed[Date]))

будет ли разница?
F1 творит чудеса
 
Aleksei_Zhigulin, у меня вот такие результаты за 10 обновлений на массиве 1 млн дат:

BuiltIn - это встроенный отбиратор (где let earliest...)
PreCalc - расчёт минимума в переменную заранее
Func - вынос функции-отбиратора в отдельный шаг
Simple - обычное сравнение типа [Date] = List.Min(Typed[Date])

Предрасчёт более-менее быстрее, хотя разница первых трех приемов незаметна. Простой отбор, конечно, самый нудный.
Эксперимент не очень чистый, так как порядок расчёта может влиять, не был отключен Caclulation, ну и т.п.
F1 творит чудеса
 
Цитата
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])
 
Очень познавательное обсуждение. Спасибо всем.
В лоб-то я, конечно, решил эту проблему. Просто все хочу понять работу List.Generate и в чем моя ошибка в первом посте. Потому что эта функция через раз у меня работает. :-(
Вот с помощью нее искал первый рабочий день от даты параметра, отрабатывает корректно.
Код
try List.Max(
Table.FromRecords(
List.Generate(
()=>[id=1,dEx=[Дата параметра]],
each List.Contains(Праздники[С1],[dEx])=true,
each [id=[id]+1,dEx=Date.AddDays([dEx],1)])
)[dEx]) otherwise [Дата параметра],
 
volfman, вашем коде нет определения кто такой Праздники[С1]. Вы бы файл пример приложили со всеми запросами и параметрами, которые используете. Было бы проще понять в чем беда.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
volfman , вашем коде нет определения кто такой Праздники[С1]
Это список дат государственных выходных и праздников.
Этот код как раз работает. Не работает код из первого поста.
 
Цитата
Aleksei_Zhigulin написал:
1. Согласно посту  #14  все варианты можно вывести один из другого,
вот в том и дело, что не получается пока. У меня нет точного ответа ещё
Изменено: Максим Зеленский - 21.02.2019 09:12:37
F1 творит чудеса
 
В общем, PooHkrd был прав, что в этом случае
Код
let earliest = List.Min(types[Date]) in each [Date] = earliest
List.Min вызывается только один раз и очень близок к правде о том, почему это происходит. Если интересно, ответ от разработчика Power Query (немного неожиданный, кстати, во второй части). Посмотрим, подтвердит ли он мою догадку о природе этого явления, но я думаю, что да
F1 творит чудеса
Страницы: 1
Наверх