Forum Discussion
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.
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.
7 Replies
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! 😊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.
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 AMFor 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! 😊
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.
