Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Syndicate_Admin
Administrator
Administrator

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:

JosePareja_0-1654620397763.png

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!

1 ACCEPTED SOLUTION

Hi, @Syndicate_Admin 

Please try follow steps:

1. group all rows by column ’DeclarationDate‘

veasonfmsft_0-1655101997978.png

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"}})

veasonfmsft_1-1655102148235.png

3.Concatenate rows from the tables generated in the previous two steps

= Table.Combine({#"Grouped Rows", ListMissingDates})

veasonfmsft_2-1655102478209.png

4.sort the new table

veasonfmsft_4-1655102517448.png

5.fill down the column value

veasonfmsft_5-1655102628337.png

6. expand the column you need

veasonfmsft_6-1655102684800.png

result:

veasonfmsft_7-1655102955593.png

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.

View solution in original post

11 REPLIES 11
Syndicate_Admin
Administrator
Administrator

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?

Anonymous
Not applicable

Is there an limitation to how much data this can prosess? 
I have a dataset with around 10 000 lines daily over 5 year that have been put into a slowly changing dimention table, but need to be able to to visualize the value of inventory every day. 
Is there a good way of doing this? 

mohammedadnant
Impactful Individual
Impactful Individual

Hi @Syndicate_Admin 

 

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 

mohammedadnant_0-1654622373669.png

 

Hope this will help.

 

Thanks & Regards,

Mohammed Adnan

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

Thanks & Regards,

Mohammed Adnan

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

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

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

Hi, @Syndicate_Admin 

Please try follow steps:

1. group all rows by column ’DeclarationDate‘

veasonfmsft_0-1655101997978.png

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"}})

veasonfmsft_1-1655102148235.png

3.Concatenate rows from the tables generated in the previous two steps

= Table.Combine({#"Grouped Rows", ListMissingDates})

veasonfmsft_2-1655102478209.png

4.sort the new table

veasonfmsft_4-1655102517448.png

5.fill down the column value

veasonfmsft_5-1655102628337.png

6. expand the column you need

veasonfmsft_6-1655102684800.png

result:

veasonfmsft_7-1655102955593.png

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

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.

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: 

Aleksandra_MLT_0-1686164171467.png

Desired result ( the red ones are inserted dauys and they differ per caregory ID): 

Aleksandra_MLT_1-1686164235664.png

 

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!

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!..

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.