Fill missing dates with zero

[Anonymous]

Hi, I've a table (Gen Colgener) with some data but some dates are missing. 

This table has a relationship with a Calendar table.... 

I wish to fullfill the data in Gen Colgener with zero, for that dates missing to elaborate continuous line graphs

[edhans]

In the Query Editor:

• Click on that column of dates
• On the Transform ribbon, click Replace
• type "null" to search for, and "0" to replace with (without the quotes

Alternatively, just filter out dates with null data so that data isn't in the model.

[Anonymous]

The problem isn't null data, the problem is that some dates aren't in the original data, in the photo you can notice how the data jump from April 12 of 2015, to May 6..... I wish a measure o column that fill the data with zero for the dates between apr 12 and may 6

[edhans]

Ok. You need to create a date table that has all dates from Jan 1, first year through at least the last date in your model, but I prefer Dec 31, last year. (assuming calendar - if fiscal, your dates will be a bit different).

In the Query Editor:

1. Merge the Sales data table with the Calendar table using the DATE fields. DO a "Right Outer" join. This will bring in all dates from the calendar table.
2. Expand the Calendar Date.
3. Now you have a bunch of nulls. This is good.
4. Right-click on the Sales data and Replace Values. Replace null with 0.
5. Delete the original Date column in the Sales table.
6. Rename the expanded date column from the calendar table from probalby Date.1 to Date.
7. Close the query editor and tell it to apply changes.

Now put your Date from the Sales table in to the line chart. You should see the 0's graphed.

[Anonymous]

Yeah, I did it but doesn't know how to fill the field "Planta", if you notice those are text are there are some of them. 😕

[edhans]

Use the Fill Down feature for those columns, or leave them null. Depends on what you want. Do the FIll Down step (or Fill up, or both if necessary) after my step #6 above.

You can do that for each column. Or do a replace with "No Sales" or whatever you want.