Forum Discussion
URGENT! Add rows with missing dates in Power Query
Hi, Syndicate_Admin
Please try follow steps:
1. group all rows by column ’DeclarationDate‘
2. Inset a step after step 'Grouped Rows' as below to get the list of missing date
= Table.RenameColumns(Table.FromList(List.Difference(List.Dates(List.Min(#"Grouped Rows"[DeclarationDate]),Duration.TotalDays(List.Max(#"Grouped Rows"[DeclarationDate])-List.Min(#"Grouped Rows"[DeclarationDate])), #duration(1,0,0,0) ), #"Grouped Rows"[DeclarationDate]), Splitter.SplitByNothing(),null, null, ExtraValues.Error), {{"Column1", "DeclarationDate"}})3.Concatenate rows from the tables generated in the previous two steps
= Table.Combine({#"Grouped Rows", ListMissingDates})4.sort the new table
5.fill down the column value
6. expand the column you need
result：
Please check my sample for more details.
Similar thread:
How to fill in missing data values in timeseries by linear interpolation
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, Syndicate_Admin
Please try follow steps:
1. group all rows by column ’DeclarationDate‘
2. Inset a step after step 'Grouped Rows' as below to get the list of missing date
= Table.RenameColumns(Table.FromList(List.Difference(List.Dates(List.Min(#"Grouped Rows"[DeclarationDate]),Duration.TotalDays(List.Max(#"Grouped Rows"[DeclarationDate])-List.Min(#"Grouped Rows"[DeclarationDate])), #duration(1,0,0,0) ), #"Grouped Rows"[DeclarationDate]), Splitter.SplitByNothing(),null, null, ExtraValues.Error), {{"Column1", "DeclarationDate"}})
3.Concatenate rows from the tables generated in the previous two steps
= Table.Combine({#"Grouped Rows", ListMissingDates})
4.sort the new table
5.fill down the column value
6. expand the column you need
result：
Please check my sample for more details.
Similar thread:
How to fill in missing data values in timeseries by linear interpolation
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It works almost perfectly!!! The main drawback was when I crossed with the date table, that the column of the date table did not have the same name DeclarationDate of the column I wanted to combine, so I added it in another 3rd column.
Thanks a lot!! You're a genius!..