CODEWITHSUNDEEP
powerquerypowerpivot
Ben Cruz
Ben Cruz

Reputation: 55

Extract min max date of a row with multiple dates

How can I split the min and the max date which is located in a row with multiplate dates

This is in Power Query

8/31/2018, 8/29/2018, 4/9/2018, 8/29/2018, 8/29/2018

Result in different Columns 

min            max
29/8/2019      4/9/2019

Can anybody help me to figure it out?

Code: = Table.TransformColumns(#"Added Custom4", {"FechaFinalTarea", each Text.Combine(List.Transform(_, Text.From), ", "), type text})

enter image description here

Full code: let Source = Etapa_1_Caricam, #"Grouped Rows" = Table.Group(Source, {"Num Form"}, {{"mynewtable", each _, type table [Num Form=text, DupForm=text, Creado En=date, FechaCreac=date, SLA=number, Initiator Name=text, Business Unit=text, BU Description=text, Tipo Form=text, Descripción del proyecto=text, Texto tarea=text, Field12=number, Task Assigned=text, Status=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Task_Etapa1", each Table.Column([mynewtable],"Texto tarea")), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Task_Etapa1", each Text.Combine(List.Transform(, Text.From), ", "), type text}), #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Iniciador", each Table.Column([mynewtable],"Initiator Name")), #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Iniciador", each Text.Combine(List.Transform(, Text.From), ", "), type text}), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Values1", {{"Iniciador", each Text.BeforeDelimiter(, ", "), type text}}), #"Added Custom2" = Table.AddColumn(#"Extracted Text Before Delimiter", "Custom", each Table.Column([mynewtable],"Descripción del proyecto")), #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"Custom", each Text.Combine(List.Transform(, Text.From), ", "), type text}), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Extracted Values2", "Text Before Delimiter", each Text.BeforeDelimiter([Custom], ","), type text), #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Custom"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Before Delimiter", "Descripcion del Proyecto"}}), #"Added Custom3" = Table.AddColumn(#"Renamed Columns", "FechaInicio", each Table.Column([mynewtable],"Creado En")), #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"FechaInicio", each Text.Combine(List.Transform(, Text.From), ", "), type text}), #"Inserted Text Before Delimiter1" = Table.AddColumn(#"Extracted Values3", "Text Before Delimiter", each Text.BeforeDelimiter([FechaInicio], ", "), type text), #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter1", "Text After Delimiter", each Text.AfterDelimiter([FechaInicio], ", "), type text), #"Removed Columns1" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Text After Delimiter", "Text Before Delimiter"}), #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Removed Columns1", {{"FechaInicio", each Text.BeforeDelimiter(, ", "), type text}}), #"Added Custom4" = Table.AddColumn(#"Extracted Text Before Delimiter1", "FechaFinalTarea", each Table.Column([mynewtable],"FechaCreac")), #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"FechaFinalTarea", each Text.Combine(List.Transform(_, Text.From), ", "), type text}) in #"Extracted Values4"

Upvotes: 0

Views: 385

Answers (1)

chillin
chillin

Reputation: 4486

There is no date 29/8/2019 in your input data (or any date relating to the year 2019). You've also presented one of your "results" as day/month/year. This may just be a couple of typos, but makes both your "results" and question are unclear and harder to follow.

One of the approaches below should work, if I've understood correctly.

If your initial table is something like the below (where all the dates are in month/day/year format and are part of a single comma-delimited string):

Input table

then I get this output (note the columns min and max):

Output table

using this code:

let
    initialTable = Table.FromRows({{"8/31/2018, 8/29/2018, 4/9/2018, 8/29/2018, 8/29/2018"}}),
    split = Table.AddColumn(initialTable, "datesToCheck", each List.Transform(Text.Split([Column1], ","), each Date.FromText(_, "en-US")), type list),
    minAndMax = Table.AddColumn(split, "toExpand", each [min = List.Min([datesToCheck]), max = List.Max([datesToCheck])], type record),
    expanded = Table.ExpandRecordColumn(minAndMax, "toExpand", {"min", "max"})
in
    expanded

However, if your initial table is something like the below (where the dates are in their own columns to begin with):

Input table

then I get this output (note the columns min and max):

Output table

using the code below:

let
    initialTable = Table.FromRows({List.Transform({"8/31/2018", "8/29/2018", "4/9/2018", "8/29/2018", "8/29/2018"}, each Date.FromText(_, "en-US"))}),
    split = Table.AddColumn(initialTable, "datesToCheck", each Record.FieldValues(_), type list),
    minAndMax = Table.AddColumn(split, "toExpand", each [min = List.Min([datesToCheck]), max = List.Max([datesToCheck])], type record),
    expanded = Table.ExpandRecordColumn(minAndMax, "toExpand", {"min", "max"})
in
    expanded

Based on your code, it seems your table contains a table column (called mynewtable), within which there is a column named FechaCreac, which itself contains dates. I can't test this, but I recommend that you replace these lines in your code:

  #"Added Custom4" = Table.AddColumn(#"Extracted Text Before Delimiter1", "FechaFinalTarea", each Table.Column([mynewtable],"FechaCreac")),
  #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"FechaFinalTarea", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
  #"Extracted Values4"

with:

  #"Added Custom4" = Table.AddColumn(#"Extracted Text Before Delimiter1", "FechaFinalTarea", each let dates = Table.Column([mynewtable],"FechaCreac") in [min = List.Min(dates), max = List.Max(dates)]),
  expanded = Table.ExpandRecordColumn(#"Added Custom4", "FechaFinalTarea", {"min", "max"})
in
  expanded

and that should then show you min and max columns.

Upvotes: 1

Related Questions