Missing data in time series Power Bi Desktop

[Anonymous]

Hi experts

Is it possible to do the following action s discribed in the following link in Rhttps://bocoup.com/blog/padding-time-series-with-r

but within Power BI using Power Query or an alternative method like M Script.

[MFelix]

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

[Mariusz]

Hi @MFelix, @Anonymous 

@MFelix you've beat me to it I was just working on the asware, anyways my take on it in one query below.

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]),
    dataTable = Table.TransformColumnTypes(Source,{{"Time", type date}, {"Observations", Int64.Type}}),
    minDate = List.Min(dataTable[Time]),
    maxDate = List.Max(dataTable[Time]),
    listYears = {Date.Year(minDate)..Date.Year(maxDate)},
    #"Converted to Table" = Table.FromList(listYears, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Year"}}),
    #"Added Months" = Table.AddColumn(#"Renamed Columns", "Months", each {1..12}),
    #"Expanded Months" = Table.ExpandListColumn(#"Added Months", "Months"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Months",{{"Months", Int64.Type}, {"Year", Int64.Type}}),
    #"Added Time" = Table.AddColumn(#"Changed Type", "Time", each #date([Year], [Months], 1), type date),
    #"Filtered Rows" = Table.SelectRows(#"Added Time", each ([Time] >= minDate and [Time] <= maxDate)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Time"}, dataTable, {"Time"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Observations"}, {"Observations"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Filtered Rows",null,0,Replacer.ReplaceValue,{"Observations"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Time", "Observations"})
in
    #"Removed Other Columns"

[MFelix]

Hi @Mariusz ,

Great mind think alike, same approach but with different steps on the middle 😄

Regards,

MFelix

[Anonymous]

All Experts

Quick silly question so this calender over ride your standard Dim_Date Table or work in conjunction with it. For learn so i understand how things work.

[MFelix]

Hi @Anonymous ,

I don't use the DimDate table on this option, I create a calendar within the query that I use to make the fill of empty dates.

If you are using a dimension table, then in my opinion you don't even need to do this steps described, using a measure you can fill out the data in any visual.

Regards,

MFelix

[Anonymous]

Hi MFeilx

You mention using a measure and no need to use the calender, how would such a step be completed??

[Mariusz]

@MFelix the R approach wasn't bad either.

[MFelix]

@Mariusz ,

Agree with you however I have no clue how to handle R code, further more I believe that to waht I have read about R in PBI you need to have some additional settings so that users can do it (not sure if I'm expressing myself correctly).

With M Language or DAX(this would also be possible making a table in DAX or creating a calendar table and a measure without the need of adding new data to the set) you can do it with default PBI.

Regards,

MFelix

[Mariusz]

@MFelix 

You are absolutely right M and DAX are native to Power BI therefore a lot easier to handle.

Thanks and hope to speak to you soon. 
Mariusz

[Anonymous]

Thanks mate, excellent as always. let me test.