Forum Discussion

GiaD30's avatar
GiaD30
Helper II
2 years ago
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, but I miss some hours.

so, for those missing hours, I need to add in Power query the missing rows and the values to be taken from last time we had a timestamp.

for example: if I have a row for 6/1/2024, 1:00AM and the next row for that ID is 6/2/2024, 3:00 PM, I need to add the missing rows for each hour between this interval and the values to be taken from 6/1/2024, 1:00 AM.

 

thank you

 

Need Help
  • danextian's avatar
    danextian
    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"

     

     

     

  • danextian's avatar
    danextian
    2 years ago

    Glad it did. Could you please accept my pose as solution?

17 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi GiaD30 ,

    What happens to the values of added rows? Also, please post a workable sample data (not an image)

    • GiaD30's avatar
      GiaD30
      Helper II
      2 years ago

      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 🙂

    • danextian's avatar
      danextian
      Super User
      2 years ago

      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"