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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

calculate time values in one column

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 

 

TaskTime Stamp 
Task n Startdd.mm.yyyy hh:mm:ss
Task n Enddd.mm.yyyy hh:mm:ss
Task n+1 Startdd.mm.yyyy hh:mm:ss
Task n+1 Enddd.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 neededTask 1 - 2 waiting timeTask 2 time neededTask 2-3 waiting time...
hh:mm:sshh:mm:sshh:mm:sshh:mm:ss...
hh:mm:sshh:mm:sshh:mm:sshh:mm:ss...
hh:mm:sshh:mm:sshh:mm:sshh: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 

 

IndexTime Stamp for Task n Startneeded time for Task n 
1dd.mm.yyyy hh:mm:sshh:mm:ss
2dd.mm.yyyy hh:mm:sshh: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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

Transform date.JPGapplied steps.JPG

Please give some sample data in your model, later we will provide you the solution suitable for your scenario.

Best Regards

Rena

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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 

 

TaskTime Stamp 
Task n Startdd.mm.yyyy hh:mm:ss
Task n Enddd.mm.yyyy hh:mm:ss
Task n+1 Startdd.mm.yyyy hh:mm:ss
Task n+1 Enddd.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 

 

TaskTime StampTask n StartTask n EndTask n+1 StartTask n+1 End...Task n Start
Task n Startdd.mm.yyyy hh:mm:ssdd.mm.yyyy hh:mm:ss     
Task n Enddd.mm.yyyy hh:mm:ss dd.mm.yyyy hh:mm:ss    
Task n+1 Startdd.mm.yyyy hh:mm:ss  dd.mm.yyyy hh:mm:ss   
Task n+1 Enddd.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 StartTask 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 

 

IndexTime Stamp for Task n Startneeded time for Task n 
1dd.mm.yyyy hh:mm:sshh:mm:ss
2dd.mm.yyyy hh:mm:sshh: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 neededTask 1 - 2 waiting timeTask 2 time neededTask 2-3 waiting time...
hh:mm:sshh:mm:sshh:mm:sshh:mm:ss...
hh:mm:sshh:mm:sshh:mm:sshh:mm:ss...
hh:mm:sshh:mm:sshh:mm:sshh: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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

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.

calculate time values in one column.JPG

Best Regards

Rena

Anonymous
Not applicable

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.

 

PBI Forum 2.jpg

 

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.

 

PBI Forum 3.jpg

 

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

Anonymous
Not applicable

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.

Transform date.JPGapplied steps.JPG

Please give some sample data in your model, later we will provide you the solution suitable for your scenario.

Best Regards

Rena

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

TaskTime Stamp
XXXXXXX
XXXXXX

Best Regards

Rena

Anonymous
Not applicable

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.

 

TaskTime Stamp
Start Goto Pos 201.01.2000 00:00:01
Ende Goto Pos 201.01.2000 00:00:07
Start Goto Pos 301.01.2000 00:00:12
Ende Goto Pos 301.01.2000 00:00:18
Start Goto Pos 401.01.2000 00:00:22
Ende Goto Pos 401.01.2000 00:00:28
Start Goto Pos 501.01.2000 00:00:31
Ende Goto Pos 501.01.2000 00:00:33
Start Goto Pos 601.01.2000 00:00:37
Ende Goto Pos 601.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

Anonymous
Not applicable

Hi @Anonymous ,

I updated my sample pbix file base on your sample data, please find it in this link.

Best Regards

Rena

Anonymous
Not applicable

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors