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, bu...
  • 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"

     

     

     

  • GiaD30's avatar
    GiaD30
    2 years ago

    danextian  Thank you very much, it worked:)

  • danextian's avatar
    danextian
    2 years ago

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