Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |