Forum Discussion
Group by date range - Powe query
Hello all,
I have two columns: TimeStamp and LoadDate.
I would like to create a third column, LoadID based on this criteria:
Dates between 23-06-2022 and 04-07-2022 in TimeStamp should have LoadID = 05-07-2022,
dates between 05-07-2022 and 27-07-2022 in TimeStamp should have LoadID = 28-07-2022, and so on.
Do you have any suggestion?
Hi, Alex_nor ;
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdJLDoAgEATRu7A2wenh51kM97+GiRoIxY5qXbyI9x1KlEedUjhC6Mc7JA6ZQ+FQOTQOFwY/l6FGQwvt6IQeyHH89oL3KrqhL7o2KKVGqtFqxFrmQKbRaYQapaJU2zelVJSKUlEqSkXpvPx5/p+QvP0FvpJbZAvt6ITO6IKu6IYe4nHs/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, LoadDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type date}, {"LoadDate", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Timestamp", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each [LoadDate]), #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}), #"Added Conditional Column" = Table.AddColumn(#"Filled Up", "Load Id", each if [Timestamp] = [Custom] then null else [Custom]), #"Filled Up1" = Table.FillUp(#"Added Conditional Column",{"Load Id"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Up1",{"Custom"}) in #"Removed Columns"The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, Alex_nor ;
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdJLDoAgEATRu7A2wenh51kM97+GiRoIxY5qXbyI9x1KlEedUjhC6Mc7JA6ZQ+FQOTQOFwY/l6FGQwvt6IQeyHH89oL3KrqhL7o2KKVGqtFqxFrmQKbRaYQapaJU2zelVJSKUlEqSkXpvPx5/p+QvP0FvpJbZAvt6ITO6IKu6IYe4nHs/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, LoadDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type date}, {"LoadDate", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Timestamp", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each [LoadDate]), #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}), #"Added Conditional Column" = Table.AddColumn(#"Filled Up", "Load Id", each if [Timestamp] = [Custom] then null else [Custom]), #"Filled Up1" = Table.FillUp(#"Added Conditional Column",{"Load Id"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Up1",{"Custom"}) in #"Removed Columns"The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Alex_nor
Try this M code : Download sample file
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"TimeStamp", type date}, {"LoadDate", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "LoadID", each if [TimeStamp] <= #date(2022,7,4) then #date(2022,7,5) else if [TimeStamp] <= #date(2022,7,27) then #date(2022,7,28) else null) in #"Added Custom"Which gives this
In my data the date columns are actually dates, in your data they look to be text.
Regards
Phil
