Forum Discussion
Add missing rows with missing date and time in Power query
Filling down should come after replacing null with something else so the replacement is included in the filldown as well.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBNCoAgEAXgq4hrwxnHH2jXAYL24qLaWq26f0Vhgel2+HiPN97zcZobAEQuuJUoFSgtGKoWgHX9eUTgQZRYUuseY9m9aVRJo6T0j6KvGi5lfpR7lE7K3mpb1gZrKyssKVNGb5QrI6r25QspR/nA61nhAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"DateTime Value" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime Value", type datetime}, {"Value", Int64.Type}}, "en-US"), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"this is null",Replacer.ReplaceValue,{"Value"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"MinDateTime", each List.Min([DateTime Value]), type nullable datetime}, {"MaxDateTime", each List.Max([DateTime Value]), type nullable datetime}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DateTime Value", each List.DateTimes([MinDateTime], Number.Round( (Number.From([MaxDateTime])-Number.From([MinDateTime])) * 24, 0) + 1, #duration(0,1,0,0))), #"Expanded DateTime Value" = Table.ExpandListColumn(#"Added Custom", "DateTime Value"), #"Removed Columns" = Table.RemoveColumns(#"Expanded DateTime Value",{"MinDateTime", "MaxDateTime"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"DateTime Value", type datetime}}), #"Combined Original and Expanded Dates" = Table.Combine({#"Replaced Value", #"Changed Type1"}), #"Changed Type2" = Table.TransformColumnTypes(#"Combined Original and Expanded Dates",{{"Value", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"ID", Order.Ascending}, {"DateTime Value", Order.Ascending}, {"Value", Order.Descending}}), #"Applied Table.Buffer" = Table.Buffer(#"Sorted Rows"), #"Removed Duplicates" = Table.Distinct(#"Applied Table.Buffer", {"ID","DateTime Value"}), #"Filled Down" = Table.FillDown(#"Removed Duplicates",{"Value"}), #"Replaced Value1" = Table.ReplaceValue(#"Filled Down","this is null",null,Replacer.ReplaceValue,{"Value"}), #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value1",{{"Value", Int64.Type}}) in #"Changed Type3"Glad it did. Could you please accept my pose as solution?
Hi GiaD30 ,
What happens to the values of added rows? Also, please post a workable sample data (not an image)
danextian Thank you for your reply, the values will be used to calculate some other metrics, for me it;s important now to know how to add the missing rows.
I see I cannot attach an excel. I am new in this community. Should I create a table here, I see I have this option.
Thank you for your help 🙂
Hi GiaD30
Try this in the query editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdA9CoAwDAXgq0jniknTH3DzAIK7OKizev9Ni5IKbbOGj/d4mWe1bnsLgKi08h12BozVDZoeoBnG54igFl1jrIygUhYJiljZgqK/mqJyBRU+ZVn5V13H2aK0UWCsXB2lqFBHJPblCylH+cD4rOUG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"DateTime Value" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime Value", type datetime}, {"Value", Int64.Type}}, "en-US"), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"MinDateTime", each List.Min([DateTime Value]), type nullable datetime}, {"MaxDateTime", each List.Max([DateTime Value]), type nullable datetime}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DateTime Value", each List.DateTimes([MinDateTime], Number.Round( (Number.From([MaxDateTime])-Number.From([MinDateTime])) * 24, 0) + 1, #duration(0,1,0,0))), #"Expanded DateTime Value" = Table.ExpandListColumn(#"Added Custom", "DateTime Value"), #"Removed Columns" = Table.RemoveColumns(#"Expanded DateTime Value",{"MinDateTime", "MaxDateTime"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"DateTime Value", type datetime}}), #"Combined Original and Expanded Dates" = Table.Combine({#"Changed Type", #"Changed Type1"}), #"Sorted Rows" = Table.Sort(#"Combined Original and Expanded Dates",{{"ID", Order.Ascending}, {"DateTime Value", Order.Ascending}, {"Value", Order.Descending}}), #"Applied Table.Buffer" = Table.Buffer(#"Sorted Rows"), #"Removed Duplicates" = Table.Distinct(#"Applied Table.Buffer", {"ID","DateTime Value"}) in #"Removed Duplicates"danextian The code from you add a duplicate of each line I have. For example, if I have 3 linea for the same ID, with 6/1/2024, 1:00 AM, 6/2/2024 2:00 AM , 6/2/2024, 7:00 AM , the code will just duplicate those 3 lines, I need to return all missing rows between these intervals: 6/1/2024 2:00AM, 6/1/2024 3 AM, 6/1/2024, 4:00 AM etc
I checked on my code and they're not supposed to return duplicates caused I removed then in the process. You can see below that after 3 AM, 4 AM comes next and so on and so forth.