Forum Discussion
Changing Column to Two Groups
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.
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.