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.

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

Thank you very much Mohammed

Something I must not have done correctly, as I have this result.

JosePareja_0-1654782575645.png

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

mohammedadnant's avatar
mohammedadnant
Impactful Individual
4 years ago

Hi Syndicate_Admin.

 

In my reply, I want you to merge Calendar & Fact, but you did Merge Fact & Calendar

In my answer Calendar is the base table.

 

Try to merge the fact table using the Calendar table and pick a column from the fact table by keeping the calendar as a base, because it has to give continuous dates.

 

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https://www.youtube.com/c/taik18