Generate a query with dates in column

[asterinnovacion]

We are signing in the company, I need to take a report, the program generates a column with dates and time

I'd like to make a query that sorts me by date row and time columns.

Thank you.

[mahoneypat · Accepted solution]

Here is one way to do it in the query editor with some example data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  All is done with ribbon buttons, except for the #"Grouped Rows" has custom M code to get the list of times.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQMLMyMrcyMFCK1UEStLQyNcMQNDS0MjUBi8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"DateTime", type text}}, "en-US"), "DateTime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"DateTime.1", "DateTime.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DateTime.1", type date}, {"DateTime.2", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"DateTime.1"}, {{"Times", each List.Sort(_[DateTime.2])}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Times", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted Values", "Times", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Times.1", "Times.2", "Times.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Times.1", type time}, {"Times.2", type time}, {"Times.3", type time}})
in
    #"Changed Type2"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[asterinnovacion]

Thank you very much, you have solved the problem, more than a pat I would give you a hug, excellent resolution and the promptness of your response.