Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Missing data in time series Power Bi Desktop

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.

 

10 REPLIES 10
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Mariusz
Community Champion
Community Champion

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"

Hi @Mariusz ,

 

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

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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.

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi MFeilx

 

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

Mariusz
Community Champion
Community Champion

@MFelix the R approach wasn't bad either.

@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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Mariusz
Community Champion
Community Champion

@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. Smiley Happy
Mariusz

Anonymous
Not applicable

Thanks mate, excellent as always. let me test.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.