Missing Dates with Part Number and Value

[MitchConnor]

Hi All,

First post here, ill try to explain my issue as precis as possible.

My data set looks like this:

Table Name: "Table 1"

PartNo:  Date:              Value:

12345   2018-01-01    1

12345   2018-01-02    5

12345   2018-01-05    3

54321   2018-01-01    10

54321   2018-01-10    20

What i want:

PartNo:  Date:              Value:

12345   2018-01-01    1

12345   2018-01-02    5

12345   2018-01-03    0

12345   2018-01-04    0

12345   2018-01-05    3

54321   2018-01-01    10

54321   2018-01-02    0

54321   2018-01-03    0

...

54321   2018-01-08    0

54321   2018-01-09    0

54321   2018-01-10    20

I have about 30 000 parts and missing dates when the Value is 0. I would like to add all dates for all parts but with Value 0.

If anyone has any idea to solve this it would help me alot!

Many thanks in advance!

/MitchConnor

[Mariusz]

Hi @MitchConnor 

Adding Calendar table would be a good start, it would sort the problem of missing dates.

Hope this helps.
Mariusz

[MitchConnor]

Hi  @Mariusz ,

Thank you for your reply!

Yes i got this. In the visualisation this helps.

The issue is that i also need to calculate the development from day to day in a new column, in the table.

I got this already, but this is wrong since i am missing dates where the value should be 0 and also i get aggregation issues in the visualisation.

I have tried to Join the 2 tables: Dates and my dataset. But i need all dates for each part with the value 0 if they dont exist in the dataset. And that i cannot solve 🙂

[Mariusz]

Hi @MitchConnor ,

You can merge the tables in query Editor (Marge Queries), just make sure Date table is First and Join Kind is left outer, after just use Replace Values "blanks" with 0.

Hope this helps
Mariusz

[MitchConnor]

Hi again @Mariusz,

I have tried this. The issue is that this will only add dates that are missing in the full dataset. I.E. If there is one Part Number missing date 2018-11-01 but there is another that has that date then it will not add an empty row for the first one with values to fill in.

The problem is that each Part Number are different from the others. Therefore i will need all dates for all Part Numbers. And the ones that has missing dates need the date and the part number and value of 0.

At least the Merge did not work for me :S