cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
villasenorbritt
Helper I
Helper I

Changing Column to Two Groups

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. 

1 ACCEPTED SOLUTION
mahenkj2
Solution Supplier
Solution Supplier

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:

HourShiftShiftTime
0PM5PM-5AM
1PM5PM-5AM
2PM5PM-5AM
3PM5PM-5AM
4PM5PM-5AM
5AM5AM-5PM
6AM5AM-5PM
7AM5AM-5PM
8AM5AM-5PM
9AM5AM-5PM
10AM5AM-5PM
11AM5AM-5PM
12AM5AM-5PM
13AM5AM-5PM
14AM5AM-5PM
15AM5AM-5PM
16AM5AM-5PM
17AM5AM-5PM
18PM5PM-5AM
19PM5PM-5AM
20PM5PM-5AM
21PM5PM-5AM
22PM5PM-5AM
23PM5PM-5AM
24PM5PM-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:

mahenkj2_0-1653158574344.png

 

Paste as below and create a new dim Shift table:

 

mahenkj2_1-1653158628859.png

 

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.

View solution in original post

7 REPLIES 7
mahenkj2
Solution Supplier
Solution Supplier

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:

HourShiftShiftTime
0PM5PM-5AM
1PM5PM-5AM
2PM5PM-5AM
3PM5PM-5AM
4PM5PM-5AM
5AM5AM-5PM
6AM5AM-5PM
7AM5AM-5PM
8AM5AM-5PM
9AM5AM-5PM
10AM5AM-5PM
11AM5AM-5PM
12AM5AM-5PM
13AM5AM-5PM
14AM5AM-5PM
15AM5AM-5PM
16AM5AM-5PM
17AM5AM-5PM
18PM5PM-5AM
19PM5PM-5AM
20PM5PM-5AM
21PM5PM-5AM
22PM5PM-5AM
23PM5PM-5AM
24PM5PM-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:

mahenkj2_0-1653158574344.png

 

Paste as below and create a new dim Shift table:

 

mahenkj2_1-1653158628859.png

 

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
Super User
Super User

Hello @villasenorbritt ,

 

Please try this :

 

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

 

rohit_singh_0-1652981805788.png

 

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

 

rohit_singh_1-1652981868626.png

 

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.

 

rohit_singh_2-1652981926969.png

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.

rohit_singh_3-1652982086140.png

 

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

rohit_singh_4-1652982131139.png

 

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! 😊

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. 

Hi @villasenorbritt ,

You can try something like this 

For AM

rohit_singh_0-1652993409057.png

 

For PM

rohit_singh_1-1652993427020.png

Result

rohit_singh_2-1652993454386.png

 

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! 😊

 

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_0-1653071843565.png

 

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. 

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors