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.
Hi v-easonf-msft , I am facing a similar issue.
I need to add the missing dates to the attendance list for each employee.
I have below information in my table
Employee ID
Month-Year,
Date,
Time in and Time Out
Please help!
Post link - Add rows with missing dates in Power Query - Microsoft Fabric Community