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.
Hello, I have a doubt about point 2, because at one point the code worked correctly for me, but a month later when updating the data, I get the following error:
Expression.Error: The "Column1" column in the table was not found.
And I don't understand what the mistake could be since I didn't change anything.