Adding days of the year to a table

[TechR21]

Hi.

Below column is part of a table that consists of data of performed quality checks/runs. This table gets automatically updated from an odatafeed. Whenever there was a quality check/run, there is an entry of that run in the table.

As you can see some dates are not in the list because there was no check/run that day, like 14/01, 12/01

However I would like to report on a weekly and monthly base, how many times a check/run has happened, and which days there were no runs. If i use this table now in a visual I obviously see only the days that there was a run.

How can I add rows to this table when there was no run that day? What are the options for this issue?

[Anonymous]

You need to make a date table (Data - Create Table - Create it by using CALENDARAUTO())

link this date table to your date in your table in the model view.

Then select your visual and select "Show items with no data" from your column and use the date from the date table. 

Hope this helps, any queations let me know.

[TechR21]

hm this doesnt seem to work. i linked both tables on DayNrOfYear, and "Show items with no data" on the date column but still only those show with run date

[Anonymous]

Try creating the relationship between two Date fields, can you send a screenshot of the data model so i can see? Also any screenshots of your date table would be great. 🙂 

[TechR21]

the problem is that the date in my main table is a date + time, because it is used to calculate the runtime in the end. I cant create other relationship than the one i created, which is also a many-to-one

[Anonymous]

Create another Column that is a copy of the Date/time in power query, then format one at just Date. You can use that for your relationship, should work 🙂 If not sorry I couldn't help further!

[Ahmedx]

in your original data, the date must be separate and the time separate. separate them

for this you need to create a column in the calendar table. Like this!

and then count and see where it was not, like this

download file
https://dropmefiles.com/JXsA6

[TechR21]

the problem is that not each day is in the table. Some days are "missing" because there was no run that day.

For these days I would like to have some sort of entry in the table so that I can use this date to see when there was no run

[Ahmedx]

you looked at the file, but I showed where zero is just when it wasn’t no run

[TechR21]

yes I now see it. unfortunately this doesnt work as I need the original times in my visuals and also other columns. So than I would need to make some sort of relationship between the table and date table