Changing Column to Two Groups

[villasenorbritt]

I have a column that has numbers from 0-23 to represent the hours in the day. I am wanting to split these hours into a "AM shift" and a "PM shift" but can not figure out how. Can someone please post a detailed, step by step instruction? I feel like a lot of the answers I seen were vaugue and answered by people who have worked with Power BI before, and I have a little experience, but not enough apparently. Any help would be greatly apprecited as I am currently an intern working on a project and want to make sure my report is flawless. 

[mahenkj2 · Accepted solution]

Hi @villasenorbritt ,

If this hour in fact table, then don't make any change in this table. Just create a Dimension table with shift timing and make a realtionship with Hour of dim table with your fact table with Hour columns. Advantage of this is, you have more control on slicing and filtering of the data in your report.

To make a dim table, I would suggest that at first prepare it in excel as below:

Hour	Shift	ShiftTime
0	PM	5PM-5AM
1	PM	5PM-5AM
2	PM	5PM-5AM
3	PM	5PM-5AM
4	PM	5PM-5AM
5	AM	5AM-5PM
6	AM	5AM-5PM
7	AM	5AM-5PM
8	AM	5AM-5PM
9	AM	5AM-5PM
10	AM	5AM-5PM
11	AM	5AM-5PM
12	AM	5AM-5PM
13	AM	5AM-5PM
14	AM	5AM-5PM
15	AM	5AM-5PM
16	AM	5AM-5PM
17	AM	5AM-5PM
18	PM	5PM-5AM
19	PM	5PM-5AM
20	PM	5PM-5AM
21	PM	5PM-5AM
22	PM	5PM-5AM
23	PM	5PM-5AM
24	PM	5PM-5AM

Why, because this is a kind of fixed table, and should not change so often.

Then copy it. Ctrl+C

In power BI use enter data to paste this table:

Paste as below and create a new dim Shift table:

Now just relate hour of this table with fact table's hour column with 1 to many relationship.

Use dim table columns as slicers in your visuals.

Hope it helps.

[rohit_singh]

Hello @villasenorbritt ,

Please try this :

1) Load your data into power query editor. I have named the column as "Hour"

2) Click on "Add column" --> "Conditional Column"

3) In the next window, name the new column "AM Shift"

Enter the condition as if Hour < 12 then Hour else null.

Basically it will go through each row of the column "Hour" and compare the value to 12. If hour value is less than 12, it will copy that value into the AM shift column, else it will asssign a null value.

4) Repeat steps 2 and 3, but this time call the new column "PM shift"

Enter the condition as if Hour >= 12 then Hour else null.

5) This will now give you your final dataset with 3 columns

I have assumed AM shift to end at 12 and PM shift to begin at 12. You can change the conditions for AM and PM shift as per your requirement. 

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[villasenorbritt]

So how would I do the conditional columns if the AM shift runs from 5 AM to 5PM and the night shift runs from 5PM to 5 AM? I am not able to do the less than since it will add parts that belong to the other shift since PM works through the night and morning. Again, very thankful for your help. 

[rohit_singh]

Hi @villasenorbritt ,

You can try something like this 

For AM

For PM

Result

If you want an overlap in shift end time, replace [Hour] < with [Hour]<=

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[villasenorbritt]

I'm getting "Table" when I do this and it doesn't have all the numbers showing like yours does. Wha could I be doing wrong?

[villasenorbritt]

What would be the reason that the "add column" section is not able to be selected? The entire thing if light grey with no option to select anything. I appreciate your help. 

[villasenorbritt]

I was able to turn the data into a table and I can now click that option. I will let you know how the rest goes.