I am trying to get the earliest date / minimum value from a field (in power query).
In my data table in an Excel table, each line/record has a stamp date for when the row occurs. I want to add a new column that shows the earliest start date.
How can I do that in Power Query?
row stamp date start date
1 3-31-20 3-31-20
2 4-1-20 3-31-20
3 4-2-20 3-31-20
4 4-3-20 3-31-20
5 3-31-20 3-31-20
6 4-1-20 3-31-20
7 3-31-20 3-31-20
Hi Ken,
Add a custom column that returns the 'List.Min' for the Stamp Date column. The formula looks like this:
=List.Min(#"Changed Type with Locale"[#"stamp date"])
Remember, Power Query is case sensitive so you may need to change the column name to suit. You'll also need to change the last step reference to suit your query.
Mynda
That's what I did, but it returned the date of the record.
Since it did not work with DateTime, I tried Date, and then record Index. Same result. File attached.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OpCenter", type text}, {"item", type text}, {"start", type datetime}, {"stop", type datetime}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Custom3" = Table.AddColumn(#"Added Index", "Min Index", each List.Min({[Index]})),
#"Added Custom" = Table.AddColumn(#"Added Custom3", "Min Start", each List.Min({[start]})),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Start Date", each DateTime.Date([start])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Start Date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Min Start Date", each List.Min({[Start Date]}))
in
#"Added Custom1"
What am I missing?
Thanks,
Ken
Hi Ken,
Our formulas are not the same. Your formula is:
=List.Min({[start]})
Mine is:
=List.Min(#"Changed Type"[#"start"])
Replace your "Added Custom" step formula with the one above.
This yields a different result (i.e. April 8, 2020 7:00:00 AM) because it's referring to a step, not just a record.
Mynda
