The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone,
I'm new to Power BI and could use your help.
- I have an Excel sheet which contains tasks (repeating) and time stamps for end and start of task
- I want to calculate, categorize and visualize the needed times for my tasks + the waiting time inbetween two tasks
- Excel sheet looks like this
Task | Time Stamp |
Task n Start | dd.mm.yyyy hh:mm:ss |
Task n End | dd.mm.yyyy hh:mm:ss |
Task n+1 Start | dd.mm.yyyy hh:mm:ss |
Task n+1 End | dd.mm.yyyy hh:mm:ss |
... | ... |
My goal is a table that looks like that:
Task 1 time needed = Task n End - Task n Start
Task 1-2 waiting time = Task n+1 Start - Task n End
Task 1 time needed | Task 1 - 2 waiting time | Task 2 time needed | Task 2-3 waiting time | ... |
hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
... | ... | ... | ... |
Step 1:
- imported this sheet in PBI
- problem: cant calculate in one column
- solution: created calculated columns containing the time stamps of each task by using a simple if formula (if [Task] = Task n; [Time Stamp]; blank() )
Step 2:
- converted each calculated column into list, then back into table
- deleted empty rows
- tried to merge them back together, values are in different rows -> no calculation
Step 3:
- back to before merging my tables together
- added index columns to each table
- am now able to calculate time needed for each task using calculated columns with DAX related([Index]) Formula
- table looks like this
Index | Time Stamp for Task n Start | needed time for Task n |
1 | dd.mm.yyyy hh:mm:ss | hh:mm:ss |
2 | dd.mm.yyyy hh:mm:ss | hh:mm:ss |
Step 4:
- created new table with DAX selectcolumns([needed time for task n])
- tried to create more calculated columns in this table for the other tasks -> ERROR
!HELP!
I missed out on waiting time for the next task bc its basically the same..
Questions:
1) is there a better solution to my mission? This one seems kind of complicated and isnt working in the end
2) in the end i want a table like shown above which im able to visualize only from the data from my excel sheet
thank you in advance!
Philipp
Solved! Go to Solution.
Hi @Anonymous ,
The "greenish functions" are variables, you can get more details from this blog. You can check the applied steps of source query in Power Query Editor.
Please give some sample data in your model, later we will provide you the solution suitable for your scenario.
Best Regards
Rena
Hello everyone,
I'm new to Power BI and could use your help.
- I have an Excel sheet which contains tasks (repeating) and time stamps for end and start of task
- I want to calculate, categorize and visualize the needed times for my tasks + the waiting time inbetween two tasks
- Excel sheet looks like this
Task | Time Stamp |
Task n Start | dd.mm.yyyy hh:mm:ss |
Task n End | dd.mm.yyyy hh:mm:ss |
Task n+1 Start | dd.mm.yyyy hh:mm:ss |
Task n+1 End | dd.mm.yyyy hh:mm:ss |
... | ... |
time needed for task n = task n Start - Task n End
waiting time for task n+1 = task n+1 Start - Task n End
etc.
Step 1:
- imported this sheet in PBI
- problem: cant calculate in one column
- solution: created calculated columns containing the time stamps of each task by using a simple if formula (if [Task] = Task n; [Time Stamp]; blank() )
- now my PBI table looks like this
Task | Time Stamp | Task n Start | Task n End | Task n+1 Start | Task n+1 End | ... | Task n Start |
Task n Start | dd.mm.yyyy hh:mm:ss | dd.mm.yyyy hh:mm:ss | |||||
Task n End | dd.mm.yyyy hh:mm:ss | dd.mm.yyyy hh:mm:ss | |||||
Task n+1 Start | dd.mm.yyyy hh:mm:ss | dd.mm.yyyy hh:mm:ss | |||||
Task n+1 End | dd.mm.yyyy hh:mm:ss | dd.mm.yyyy hh:mm:ss | |||||
... | ... | ... |
|
|
| ... |
|
Task n Start | dd.mm.yyyy hh:mm:ss |
|
|
|
|
| dd.mm.yyyy hh:mm:ss |
Task n End | dd.mm.yyyy hh:mm:ss |
|
|
|
|
|
|
Step 2:
- converted each calculated column into list, then back into table
- deleted empty rows
- tried to merge them back together
- problem: table looks like this and i still cant calculate bc values arent in one row
Task n Start | Task n End | ... |
dd.mm.yyyy hh:mm:ss | ||
dd.mm.yyyy hh:mm:ss | ||
... | ||
dd.mm.yyyy hh:mm:ss | ||
| dd.mm.yyyy hh:mm:ss |
|
Step 3:
- back to before merging my tables together
- added index columns to each table
- am now able to calculate time needed for each task using calculated columns with DAX related([Index]) Formula
- table looks like this
Index | Time Stamp for Task n Start | needed time for Task n |
1 | dd.mm.yyyy hh:mm:ss | hh:mm:ss |
2 | dd.mm.yyyy hh:mm:ss | hh:mm:ss |
Step 4:
- created new table with DAX selectcolumns([needed time for task n])
- tried to create more calculated columns in this table for the other tasks -> ERROR
!HELP!
I missed out on waiting time for the next task bc its basically the same..
Questions:
1) is there a better solution to my mission? This one seems kind of complicated and isnt working in the end
2) in the end i want a table im able to visualize only from the data from my excel sheet and looks like this:
Task 1 time needed | Task 1 - 2 waiting time | Task 2 time needed | Task 2-3 waiting time | ... |
hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | ... |
... | ... | ... | ... |
thank you in advance!
hi @Anonymous
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Lin
Hi @v-lili6-msft ,
accidentally i made 2 Posts, there's already a discussion going on, see here:
https://community.powerbi.com/t5/Desktop/calculate-time-values-in-one-column/m-p/1392732
i added sample data and my expected result.
Thank you!
Kind Regards
Philipp
Hi @Anonymous ,
I created a sample pbix file base on your requirement, please check if that is what you want. If no, please provide some sample data and your desired result. And if it involve any other calculation, please provide the related calculation logic. Thank you.
Best Regards
Rena
Hi @Anonymous Rena,
Thanks for your reply and your help.
The final table 2) you created looks exactly like i wanted it.
Problem is your source table, you created 2 columns with End and Start of each task, my source table only contains one column "Time Stamp" with End and Start in one column.
Sorry it's in German "Nomenklatur" are my tasks, i created a model with just 5 tasks, repeating 2 times.
My first step was to separate Start and End of my tasks, i created tables for each task.
I can now calculate between them but cant find a way to create the final table containing all tasks with all calulacted times.
I can now calculate between them but cant find a way to create the final table containing all tasks with all calulacted times.
"Intervall" is my time needed for a task for simplifying reasons i missed out on waiting times in the first model.
Thank you in advance!
Kind Regards
Philipp
Hi @Anonymous ,
The "greenish functions" are variables, you can get more details from this blog. You can check the applied steps of source query in Power Query Editor.
Please give some sample data in your model, later we will provide you the solution suitable for your scenario.
Best Regards
Rena
Hi @Anonymous Rena,
i cant follow and recreate your steps in Power Query Editor, can u show me what you did in the steps:
- Split column by delimiter
- changed type 1
- merged columns
- pivoted columns
you can find sample data in the screenshot of my post, right?
thank you!
Kind regards
Philipp
Hi @Anonymous ,
You can refer the following links to do these steps(Split column by delimiter,merged columns and pivoted columns😞
Shape and combine data in Power BI Desktop
Split and Merge Columns Power Query
Pivot and Unpivot with Power BI
Could you please provide the sample data in form of text as below table? Please explain which are the same task? How do you identify the start time and end time of each task? Thank you.
Task | Time Stamp |
XXX | XXXX |
XXX | XXX |
Best Regards
Rena
Hi @Anonymous ,
sorry beginner 😅 thanks for your patience..
i can follow your steps in power query editor and created a table like you with the columns: task, start, end but if the tasks repeat each other it doesnt work.
I just realized my sample data wasnt good, i changed to another sample.
Task | Time Stamp |
Start Goto Pos 2 | 01.01.2000 00:00:01 |
Ende Goto Pos 2 | 01.01.2000 00:00:07 |
Start Goto Pos 3 | 01.01.2000 00:00:12 |
Ende Goto Pos 3 | 01.01.2000 00:00:18 |
Start Goto Pos 4 | 01.01.2000 00:00:22 |
Ende Goto Pos 4 | 01.01.2000 00:00:28 |
Start Goto Pos 5 | 01.01.2000 00:00:31 |
Ende Goto Pos 5 | 01.01.2000 00:00:33 |
Start Goto Pos 6 | 01.01.2000 00:00:37 |
Ende Goto Pos 6 | 01.01.2000 00:00:45 |
Now its easier to understand the tasks with each start and end time.
Goto Pos n is one task.
Further for [waiting time Goto Pos n] = Start Goto Pos n - End Goto Pos n -1
Kind Regards
Philipp
Hi @Anonymous ,
I updated my sample pbix file base on your sample data, please find it in this link.
Best Regards
Rena
Hi @Anonymous ,
thank you very much for your kind help.
I can't open your file yet, seems like my company doesn't run the latest PBI Desktop Version.
Best Regards
Philipp