Используя PowerQuery, я хочу вернуть минимальную дату, которая больше или равна указанной дате.
Например, приведенная ниже таблица отфильтрована, поэтому каждая строка в NewDiagnosis содержит значение даты.
Первая строка вернет 07/03/2019, равное NewDiagnosis. Второй вернет 17/04/2019, третий 05/08/2019, а четвертый вернет null.
| NewDiagnosis | NewDisposition | PriorityDisposition | RMARequested | AwaitingCustoms |
|--------------|----------------|---------------------|--------------|-----------------|
| 07/03/2019 | 07/03/2019 | | 06/03/2019 | 08/03/2019 |
| 15/04/2019 | | 17/04/2019 | | 18/04/2019 |
| 11/04/2019 | | | 05/08/2019 | |
| 14/05/2019 | | | | |
| 13/06/2019 | | | 17/06/2019 | |
| 17/06/2019 | | | 18/06/2019 | |
| 18/06/2019 | | | 17/07/2019 | |
| 12/06/2019 | | | | 14/06/2019 |
| 20/06/2019 | 25/06/2019 | | | |
| 03/06/2019 | | | | |
| 10/06/2019 | | | | |
| 24/06/2019 | 02/07/2019 | | 02/07/2019 | |
Я могу вернуть общее минимальное значение, используя (с полным списком столбцов, на которые я смотрю), но это не учитывает значение в NewDiagnosis.
#"Inserted Earliest" = Table.AddColumn(#"Filtered Rows", "Earliest",
each List.Min({[#"New#(lf)Disposition"], [#"Priority#(lf)Disposition"],
[#"RMA#(lf)Requested"], [#"Awaiting#(lf)Customs"],
[#"Awaiting#(lf)Verification"], [#"New Cards#(lf)Awaiting Process"],
[Initial Email], [#"Day 4#(lf)Reminder#(lf)incl. SCC"],
[#"Escalation#(lf)SQE"], [#"Escalation#(lf)Clinic Manager"],
[#"Escalation#(lf)HOQ"], [#"Section B#(lf)Email#(lf)Received"],
[#"Day 7#(lf)Reminder Email"], [#"Day 14#(lf)Reminder#(lf)incl. SCC"],
[#"Day 21#(lf)Escalation#(lf)SQE"], [#"Day 28#(lf)Escalation Clinic Manager"],
[#"Day 42#(lf)Escalation#(lf)HOQ"], [#"Section C#(lf)Email#(lf)Received"],
[#"Technical#(lf)Review"], [#"1st#(lf)Supplier Turnback"],
[#"2nd#(lf)Supplier Turnback"], [#"Internal#(lf)Review"],
[#"1st#(lf)Internal Turnback"], [#"2nd#(lf)Internal Turnback"],
[#"Awaiting#(lf)Customer Closure"], [Closed],
[Back to Stock], [Sent to SCRAP], [Internal]}), type date)
Я посмотрел, смогу ли я это понять, но пока ничего не нашел. Справочник по функциям M, M Primer, предыдущая дата PowerQuery Countif
Любая помощь будет принята с благодарностью.
Следуя предложению @AlekseiZhigulin, мой запрос:
let
Source = #"Source_Data",
#"Removed Columns" = Table.RemoveColumns(Source,
{"SUPPLIER", "Vendor_Supplier", "Customer",
"SCC", "SCM", "DATE", "Phase", "Liability",
"Agreed#(lf)Start Date", "Date of Acceptable Response",
"Owner", "Created#(lf)Date", "Created#(lf)By",
"Modified#(lf)Date", "Modified#(lf)By", "VENDOR CODE",
"Section B Required#(lf)Receipt", "Section B#(lf)Email#(lf)Days Late",
"Part Returned to Supplier", "Section C Required#(lf)Receipt",
"Section C#(lf)Email#(lf)Days Late", "Comments"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"Cancelled?"] = null) and ([#"New#(lf)Diagnosis"] <> null)),
dateCols = {"New#(lf)Disposition", "Priority#(lf)Disposition",
"RMA#(lf)Requested", "Awaiting#(lf)Customs",
"Awaiting#(lf)Verification", "New Cards#(lf)Awaiting Process",
"Initial Email", "Day 4#(lf)Reminder#(lf)incl. SCC",
"Escalation#(lf)SQE", "Escalation#(lf)Clinic Manager",
"Escalation#(lf)HOQ", "Section B#(lf)Email#(lf)Received",
"Day 7#(lf)Reminder Email", "Day 14#(lf)Reminder#(lf)incl. SCC",
"Day 21#(lf)Escalation#(lf)SQE", "Day 28#(lf)Escalation Clinic Manager",
"Day 42#(lf)Escalation#(lf)HOQ", "Section C#(lf)Email#(lf)Received",
"Technical#(lf)Review", "1st#(lf)Supplier Turnback",
"2nd#(lf)Supplier Turnback", "Internal#(lf)Review",
"1st#(lf)Internal Turnback", "2nd#(lf)Internal Turnback",
"Awaiting#(lf)Customer Closure", "Closed",
"Back to Stock", "Sent to SCRAP", "Internal"},
minDate = Table.AddColumn(dateCols, "minDate",
(z)=> List.Min(List.Select(Record.ToList(Record.SelectFields(z, dateCols)),
each _ >= z[NewDiagnosis])), type date)
in
minDate
но это возвращает Мы не можем преобразовать значение типа List в тип Table.
