Forum Discussion
Generate a query with dates in column
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.
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
2 Replies
- mahoneypat
Microsoft Employee
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
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.
