Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

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...
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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.

v-easonf-msft's avatar
v-easonf-msft
Community Support
4 years ago

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.

drakkad's avatar
drakkad
New Member
3 years ago

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!