cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AnthonyDF Regular Visitor
Regular Visitor

Missing dates with one criteria.

Hello community,

I didn't found a similar topic in this forum to help me so I'm creating this one.

 

I'm new to powerquery and I'm struggling with fusion and jointure types to find and fill missing dates.

 

Here is the structure of my query (I've simplified the thing so you can see it's a table and not a query for the moment):

Ihave.PNG

For each name I need the full range of dates betwen the 14 and the 24/01/2016. 

I need something like this:

 

IWANT.PNG

I tried different combinaisons of fusion and joiture type with a table containing all dates but I'm missing something...

 

Someone has an idea?

 

Thank you ver much for your help

 

Rgds.

1 ACCEPTED SOLUTION

Accepted Solutions
ChandeepChhabra Established Member
Established Member

Re: Missing dates with one criteria.

Created a solution with some dummy data similar to yours. 

Does this work for you?

 

Capture.PNG

All dates (from 21st to 26th for all names)

 

You can download the excel file (power query solution) here

Hope it helps

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Missing dates with one criteria.

@AnthonyDF can you explaing the reasonw why you want to fill missing dates. Usually you have calendar/date table in your model which has all the dates and get used for any kind of time intelligence calculations, and can be used for missing dates. 

 

It will be good to know what the end goal is and there might be other solution to achieve the goal.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





AnthonyDF Regular Visitor
Regular Visitor

Re: Missing dates with one criteria.

Hi @parry2k,

First of all thank you.

 

Here is the thing, I use the query as an input for a pivot table and forecast calculations. Missing dates are creating discontinuities in my graph when I sum up them all.

 

I created a macro and it was working well but since I use powerquery to update my datas I need something more convenient .

 

I know that in pivotable table you can replace missing datas with a value but the thing is that I need to have no discontinuities in the timeline for my forecasts.

 

I hope it's clear...

 

 

Super User
Super User

Re: Missing dates with one criteria.

@AnthonyDF as mentioned date table in your model is exactly helps you to achieve this without adding missing dates in your table.

 

Here are the steps:

 

- add Calendar table in your model (there are many posts on how to create/add Calendar table)

- join your transaction table with Calendar table

- use date from Calendar on x-axis

- choose option to show "show with no data"

 

if you run into any issue on above step, feel free to reach out.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





AnthonyDF Regular Visitor
Regular Visitor

Re: Missing dates with one criteria.

Yep I tried this already but it seems to work when you have only one name for example. As I have many names powerBi has to understand that for each name no date is mssing (kind of loop I guess?)

Super User
Super User

Re: Missing dates with one criteria.

@AnthonyDF not sure what you mean by one name. Can you share more details and how you are doing it?






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





AnthonyDF Regular Visitor
Regular Visitor

Re: Missing dates with one criteria.

By name I mean the column "Name" in my example. If you have only name "A" then it works to merge this table with the date table. As I have name "A" and "B" and so on then the query has to cmpare all dates from names "A" & "B" to all dates in the dates table.

ChandeepChhabra Established Member
Established Member

Re: Missing dates with one criteria.

Created a solution with some dummy data similar to yours. 

Does this work for you?

 

Capture.PNG

All dates (from 21st to 26th for all names)

 

You can download the excel file (power query solution) here

Hope it helps

View solution in original post

AnthonyDF Regular Visitor
Regular Visitor

Re: Missing dates with one criteria.

Oh yeah that's it! I'm digging into you query to understand the thing but the final result is here.

 

Thank you very much Smiley Happy

ChandeepChhabra Established Member
Established Member

Re: Missing dates with one criteria.

@AnthonyDF You are welcome Smiley Happy

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 494 members 4,275 guests
Please welcome our newest community members: