cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
j_logan
Frequent Visitor

Fill missing dates in Power Query

Hello.

I'd like to request help as I'm getting stuck enriching missing dates in my dataset.

I have an ID where the dates are complete from 4/12 to 5/12.

ideal.png

However, I have some several IDs where dates are not complete and I wish to enrich.

enrich1.pngenrich2.png

 

My target objective is similar to this:

outcome.png

 

What I did was:

- Created a calendar table from 4/12 to 5/16.

- Tried some Merge Query joins but failing to achieve desired outcome.

 

Thank you

1 ACCEPTED SOLUTION

Hi! @j_logan 

As I coudn't copy your data as it is an image. I've tried something on sample data.


I created 4,5 rows with dates and added an index with 1.

 

AnkitKukreja_0-1642928814677.png

 

Similarly I created a date table for the same month ( you can create for May and June as reqd in your case).
Then I added a column from same index 1 (Please ensure your date starts from the start date that you've in your data and ends untill the last desired date or last available date in your data whichever is oldest).

 

AnkitKukreja_1-1642928938319.png

 

Then I did a simple left join using merge on both the index and got this result. Now I can use this date instead of old 1.

 

AnkitKukreja_2-1642928996406.png

 

 

Just an fyi, if your data will add more rows every month. I would advise you to sanitize your data in the datasource (it seems excel to me) So you can just drag those date and that can autofill. This will not add running time on Power BI if your data gets bigger every day or month.

 

I hope this helps, please let me know if you want it to be doen on your dataset, please share your pbix.

 

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

View solution in original post

2 REPLIES 2
vanessafvg
Super User
Super User

what is your merge join on? can you provide the m query code of what you are doing

 

what result are you getting from your soluton





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi! @j_logan 

As I coudn't copy your data as it is an image. I've tried something on sample data.


I created 4,5 rows with dates and added an index with 1.

 

AnkitKukreja_0-1642928814677.png

 

Similarly I created a date table for the same month ( you can create for May and June as reqd in your case).
Then I added a column from same index 1 (Please ensure your date starts from the start date that you've in your data and ends untill the last desired date or last available date in your data whichever is oldest).

 

AnkitKukreja_1-1642928938319.png

 

Then I did a simple left join using merge on both the index and got this result. Now I can use this date instead of old 1.

 

AnkitKukreja_2-1642928996406.png

 

 

Just an fyi, if your data will add more rows every month. I would advise you to sanitize your data in the datasource (it seems excel to me) So you can just drag those date and that can autofill. This will not add running time on Power BI if your data gets bigger every day or month.

 

I hope this helps, please let me know if you want it to be doen on your dataset, please share your pbix.

 

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors