How to Create 24 rows for hour of day?

[abittinger]

Hi All,

I am fairly new to Power BI and was wondering if it were possible to do the following.

I am running a report that outputs data if activity is captured during a specific hour on any given day.  If there is no data captured during a particular hour then, there is no data written to the report.  In the photo below, there was no activity during the 2:00:00 AM time slot, nor is there any data for 6:00:00 - 7:00:00 PM.  The source where we pull the data is not able to be configured to write anything to the report if no Data is captured.  I believe I will need to create a new table that has each hour listed (IE. 12:00, 1:00, 2:00, etc.) and link it to the 'Hour_of_chat' column in this table.  I am just unsure how to go about creating a brand new column that lists each hour, and linking it to the "Hour_of_Chat" in such a way that I can get a full list of hours, even if there is no data in a specific row.  I have tried playing around with a brand new 'Calendar' Table, but I just can't get it to work. Sorry if this explanation is terrible.  Thank you all in advance for the potential help.

[Anonymous · Accepted solution]

You can use the following to create a new table:

Table = 
VAR __StartTime= TIME(0,0,0)
var __EndTime= TIME(23,0,0)
var __Duration= TIME(1,0,0)
return

GENERATESERIES(__StartTime,__EndTime,__Duration)

[v-juanli-msft · Accepted solution]

Hi @abittinger 

First please note, when using one to one relationship, it only supports both direction.

My test is as below:

Create the time table as below, change the "Value" to the date format as below

Change the relationship as below

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

You can use the following to create a new table:

Table = 
VAR __StartTime= TIME(0,0,0)
var __EndTime= TIME(23,0,0)
var __Duration= TIME(1,0,0)
return

GENERATESERIES(__StartTime,__EndTime,__Duration)

[abittinger]

Nick_M,

Thank you so much for the response. I was able to create a new column for times and that did work.  However, Now I am having issues linking the data.  If I put it into the existing visualization without any relationships, it is not how I want it to appear.

If I use the "Manage Relationships" option to link these fields, it also does not yield the results I would like it to.  The only option that works is a "One to Many" relationship, which yields the same exact results as before I made the new hours table.  It does link them, but the hours I want to show up, still are not showing up.

Selecting any of the other types of relationships throw an error "The Cardinality you selected isn't valid for this relationship." I am assuming this is something setup back end from our SQL dataset that isn't allowing me to do this.  Do you happen to have any other recommendations?

[Anonymous]

without seeing some of the data, it's hard to tell. What if you  manually switch to 1:many. The 1 coming from our new table. If that still doesnt work, is the time column in your fact table set as date/time data type?  It may look like one, but it should say date/time when you click the column in Data View--> then modelling tab.