Forum Discussion
URGENT! Add rows with missing dates in Power Query
Good morning, I need to add the missing dates to the list of dates declared in various files.
In power query I have this info:
I need to add the rows of the missing dates, and that the value is the same as the last date declared...
Example: on 31/3 I have 3 models with their values, the next declared value is from day 5/4 with their respective values, but I have to add the dates from 1/4 to 4/4 with the same values from 31/3.
Thank you all very much!
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.
13 Replies
Basically, you need to table,
1. your fact table
2. Calendar Table: pls refer to this video: https://youtu.be/CIfwwD5986Y
steps:
1. Merge calendar & fact table --> expand and take only the columns you need from the fact table
(for merge & append refer this video: https://youtu.be/5UGxSFzw5W4)
2. you will get the blank for those rows for which you don't have data.
3. under transform --> fill --> fill down
Hope this will help.
Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18
Thanks & Regards,
Mohammed Adnan
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.
I have a similar problem, point two of the solution code, creates an empty table, so it doesn't find column1 and an error occurs, is there any way to fix this error?
I have almost exactly the same question, except my data also includes different equipment, and each equipment is missing dates. I've followed these steps after "grouping" by the equipment, for one equipment table only, but now I want to expand the solution for multiple equipment. Any tips for how to do that??
Thanks!
