Forum Discussion
GiaD30
2 years agoHelper II
Add missing rows with missing date and time in Power query
Hello everyone, I need your help please. I have a table with several IDs and for each ID, I have several dates and times and also values. normally I should have for each IDs hourly values, bu...
- 2 years ago
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" - 2 years ago
Glad it did. Could you please accept my pose as solution?
danextian
2 years agoSuper User
Hi GiaD30 ,
What happens to the values of added rows? Also, please post a workable sample data (not an image)
GiaD30
2 years agoHelper II
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 🙂