Missing data in time series Power Bi Desktop

Hi  Anonymous ,

 

You can do this with query editor:

• Insert a blank step after the last step of your query
• Create a custom calendar list based on the max and min values of the dates in the previous step
  • This calendar will have the beginning of the minimum date and the duration of the difference between max and minimum dates
• If like the blog post you want to have only beginning of the month dates add a Start of month column
• Do a merge between this calendar step and the previous step before the calendar.
• Expand the columns value
• Replace null by 0

I have a query with full calendar and one only with beginning of month:

 

Start of Month

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNQHIgNDJR0lQyOlWB2YmBFUzMAAJggSgQgaGyELmkEEzZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t, Observations = _t]),
    Format = Table.TransformColumnTypes(Source,{{"Time", type date}, {"Observations", Int64.Type}}),
    Calendar = Table.FromList( List.Dates(List.Min(Format[Time]),Number.From (List.Max(Format[Time])-List.Min(Format[Time]))+1, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    StartOfMonth = Table.AddColumn(Calendar, "Start of Month", each Date.StartOfMonth([Column1]), type date),
    RemoveColumns = Table.RemoveColumns(StartOfMonth,{"Column1"}),
    RemoveDuplicates = Table.Distinct(RemoveColumns),
    Merge = Table.NestedJoin(RemoveDuplicates, {"Start of Month"}, Format, {"Time"}, "Inserted Start of Month", JoinKind.LeftOuter),
    ExpandValues = Table.ExpandTableColumn(Merge, "Inserted Start of Month", {"Observations"}, {"Observations"}),
    Replacenulls = Table.ReplaceValue(ExpandValues,null,0,Replacer.ReplaceValue,{"Observations"})
in
    Replacenulls

All Dates between max and minimum

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNQHIgNDJR0lQyOlWB2YmBFUzMAAJggSgQgaGyELmkEEzZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t, Observations = _t]),
    Format = Table.TransformColumnTypes(Source,{{"Time", type date}, {"Observations", Int64.Type}}),
    Calendar = Table.FromList( List.Dates(List.Min(Format[Time]),Number.From (List.Max(Format[Time])-List.Min(Format[Time]))+1, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    StartOfMonth = Table.AddColumn(Calendar, "Start of Month", each Date.StartOfMonth([Column1]), type date),
    Merge = Table.NestedJoin(StartOfMonth, {"Column1"}, Format, {"Time"}, "Inserted Start of Month", JoinKind.LeftOuter),
    ExpandValues = Table.ExpandTableColumn(Merge, "Inserted Start of Month", {"Observations"}, {"Observations"}),
    Replacenulls = Table.ReplaceValue(ExpandValues,null,0,Replacer.ReplaceValue,{"Observations"})
in
    Replacenulls

Steps in bold are the ones that need to be manually edit in order to pickup previous query steps.

 

Check M code below and PBIX file attach.

 

Regards,

MFelix