Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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):

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

I need something like this:

 

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.

Need Help

11 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 :)

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

       

       

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

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