Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
villasenorbritt
Resolver I
Resolver 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
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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
Solution Sage
Solution Sage

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.