Forum Discussion
Solved
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...
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?
Prior to doing anything else, I would convert the data type of that value column to text and then replace null with something like "this is null". After filling down, rows with "this is null" should stiill be preserved. I would just revert that back to null by using the same replace method and change the data type back to number.
danextian Thank you again. I tried, unfortunately, after all the steps, I still see null. Right at the beginning, I changed from number to text, I have replaced null with something else. Then I have all the steps to add missing rows and then I have my missing rows, but I only see null, even for those rows where I replaced null with somethng else
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?