Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Hi @Anonymous ,
You can do this with query editor:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAll 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi MFeilx
You mention using a measure and no need to use the calender, how would such a step be completed??
@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
Proud to be a Super User!
Check out my blog: Power BI em Português@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
Thanks mate, excellent as always. let me test.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |