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.
Thank you very much Mohammed
Something I must not have done correctly, as I have this result.
1- Create my table of all the dates of the year
2- Group my data table by Model, but when Merge with date table, the missing dates are added correctly, but not for each model. That is, what I should do is for example, add for the days of 1/4 to 4/4, 3 times for each model. with null quantity.. and when you make FillDown take the value of the corresponding model from the last date, in this case, 31/3
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,
while this is an amazing logic, seems like there's a prerequisite that you have all plants available for each date. Unless I'm mistaken, this is how the grouping works.
Say for example we have plant A and plant B available on May 1st 2023, it groups them. Then, on May 2nd 2023, we have plant B only. This way, May 2nd 2023 will not be identified as a missing date for plant A, therefore will not list out.Also, what if we need to reference multiple columns, not only plants?
Appreciate any help on the way around it, thanks much!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.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!..
Hey, v-easonf-msft , Really cool solution ! Though in my case I have not only missed dates but also a category and per each ategory those missed days differ. Any suggestions on how to change the code from here ? Cause I think then I need to group not only by dates but also by category ?
The current situation:Desired result ( the red ones are inserted dauys and they differ per caregory ID):
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 tableEmployee ID
Month-Year,Date,
Time in and Time Out
Please help!
Post link - Add rows with missing dates in Power Query - Microsoft Fabric Communityv-easonf-msft This is a brilliant solution. It meets my requirement of filling in missing dates, but for those missing dates I need the value to be 0. Do you know how I can accomplish this?
