Forum Discussion
How to add rows with missing dates
Hello everyone.
Need help with a table. There is a table with exchange rates.
Question 1: How to add rows between missing dates
For example, I need to add dates between 28/02/2025 and 03/03/2025, data for 28/02/2025 should be for missing dates. see the picture
Question 2: How to shift data one forward?
Thanks in advance
Hi Anonymous ,
After loading the table in the Power Query editor and adding a new query, use the following M code to reference the source table to create a new table that meets the requirements.
let StartDate = List.Min(Table[Date from original report]), EndDate = List.Max(Table[Date from original report]), DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)), DateTable = Table.TransformColumnTypes( Table.FromList( DateList, Splitter.SplitByNothing(), {"Date from original report"}, null, ExtraValues.Error ), {{"Date from original report", type date}} ), #"Merged Queries" = Table.NestedJoin( DateTable, {"Date from original report"}, Table, {"Date from original report"}, "Table", JoinKind.LeftOuter ), #"Expanded Table" = Table.Sort( Table.ExpandTableColumn(#"Merged Queries", "Table", {"ExRate"}, {"ExRate"}), {{"Date from original report", Order.Ascending}} ), #"Filled Down" = Table.FillDown(#"Expanded Table", {"ExRate"}), #"Added Custom" = Table.AddColumn( #"Filled Down", "Date from final report", each [Date from original report] + #duration(1, 0, 0, 0) ), #"Changed Type" = Table.TransformColumnTypes( #"Added Custom", {{"Date from final report", type date}} ), #"Reordered Columns" = Table.ReorderColumns( #"Changed Type", {"Date from original report", "Date from final report", "ExRate"} ) in #"Reordered Columns"The approximate steps are as follows:First generate a date table containing the desired date range.
Then merge the date table with the source data table using the left join.
Select the 'ExRate' column and use the fill down.
Add a custom column that shows the date moved forward one day.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Anonymous ,
After loading the table in the Power Query editor and adding a new query, use the following M code to reference the source table to create a new table that meets the requirements.
let StartDate = List.Min(Table[Date from original report]), EndDate = List.Max(Table[Date from original report]), DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)), DateTable = Table.TransformColumnTypes( Table.FromList( DateList, Splitter.SplitByNothing(), {"Date from original report"}, null, ExtraValues.Error ), {{"Date from original report", type date}} ), #"Merged Queries" = Table.NestedJoin( DateTable, {"Date from original report"}, Table, {"Date from original report"}, "Table", JoinKind.LeftOuter ), #"Expanded Table" = Table.Sort( Table.ExpandTableColumn(#"Merged Queries", "Table", {"ExRate"}, {"ExRate"}), {{"Date from original report", Order.Ascending}} ), #"Filled Down" = Table.FillDown(#"Expanded Table", {"ExRate"}), #"Added Custom" = Table.AddColumn( #"Filled Down", "Date from final report", each [Date from original report] + #duration(1, 0, 0, 0) ), #"Changed Type" = Table.TransformColumnTypes( #"Added Custom", {{"Date from final report", type date}} ), #"Reordered Columns" = Table.ReorderColumns( #"Changed Type", {"Date from original report", "Date from final report", "ExRate"} ) in #"Reordered Columns"The approximate steps are as follows:First generate a date table containing the desired date range.
Then merge the date table with the source data table using the left join.
Select the 'ExRate' column and use the fill down.
Add a custom column that shows the date moved forward one day.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello Anonymous
You can follow below M code as welllet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcjJCcAwDATAXvZthUXO+qhFqP82EtAjxDCviYAvo5vThQZe0hSyBdjt9f3gqr//r9nrdX71OHpvZD4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date from original report" = _t, ExRate = _t]), #"Added Index" = Table.AddIndexColumn(Source, "FirstIndex", 1 , 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "SecondIndex", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"FirstIndex"}, #"Added Index1", {"SecondIndex"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Date from original report"}, {"EndDate"}), #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"SecondIndex", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"EndDate"}), #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"EndDate", type date},{"Date from original report",type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDate", each List.Dates( [Date from original report], Number.From([EndDate] ) - Number.From( [Date from original report] ) , #duration(1,0,0,0) )), #"Expanded ListOfDate" = Table.ExpandListColumn(#"Added Custom", "ListOfDate"), #"Added Conditional Column" = Table.AddColumn(#"Expanded ListOfDate", "OriginalDate", each if [ListOfDate] = null then [Date from original report] else [ListOfDate]), #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"ExRate", "OriginalDate"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"OriginalDate", "ExRate"}) in #"Reordered Columns"Below screenshot
Hope it will help
Regards
sanalytics
