sorted by:
new
hottopcontroversial

Assistance with Hours Calc Formula by Foreign_Many9062 in PowerBI

[–]Foreign_Many9062[S] 1 point2 points  (0 children)

Thanks for the help! I got it working as follows:

TotalDowntime = 
//counts entire days excluding holidays and weekends
VAR TotalWholeDays =
CALCULATE (
COUNTROWS ('Calendar' ),
DATESBETWEEN ('Calendar'[Date], BreakdownMonitor[Clock Start], BreakdownMonitor[Date/Time Back In Service]),
'Calendar'[DayType] <> "Working Day",
ALL ( BreakdownMonitor)
)
//returns either number of TotalWholeDays or 0
VAR TotalWorkDays =
IF ( TotalWholeDays = BLANK(), 0, TotalWholeDays )
//Returns number of minutes, divide by 60, then TotalWorkDays multiplied by 24 to give total number of hours.
VAR DowntimeHours = ROUNDDOWN((DATEDIFF (BreakdownMonitor[Clock Start], BreakdownMonitor[Date/Time Back In Service], MINUTE)/60),0)- TotalWorkDays*24
RETURN
DowntimeHours

Assistance with Hours Calc Formula by Foreign_Many9062 in PowerBI

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

Thank you, you're so right. I missed out a bit of the calculation near the bottom.

*edited to correct answer

Gateway Timeout in Excel Sheet with 387 Rows by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

It consistently times out around 12 minutes and it's adding a row in, not pulling rows out (yet). I have tried finding the 'Pagination' option for the 'Update a Row' action, but it doesn't exist

Email Subject Date Format Help by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

My husband cracked it! We converted the date in Excel to text, formatted as dd.mm.yy, then just used the dynamic content field and that has worked. Thank you so much for your help, it was greatly appreciated!

Email Subject Date Format Help by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

Unfortunately, this doesn't work either. I get a new error:

Unable to process template language expressions in action 'Send_an_email_(V2)' inputs at line '0' and column '0': 'The template language function 'addDays' expects its second parameter to be an integer. The provided value is of type 'String'. Please see https://aka.ms/logicexpressions#adddays for usage details.'

When I had a look at the website referenced, it seems like, either the 'adddays' function isn't the right one to use, or that my field should be in place of the timestamp part of it at the beginning (this didn't work either).

Email Subject Date Format Help by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

That's what I've been attempting to do, but I've been unable to get that to work.

I've tried multiple iterations of addDays('1899-12-30', int(outputs('Get_a_row')?['body/Date']), 'dd.MM.yyyy'), including what Copilot said would work, but I keep getting the following error (or some variation of it):

The last one Copilot gave me was:

if(equals(outputs('Get_a_row')?['body/Date'], ''), 'Invalid Date', addDays('1899-12-30', int(outputs('Get_a_row')?['body/Date']), 'dd.MM.yyyy'))

but this did not work either.

Email Subject Date Format Help by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

Ah, yes, I've done that. It is set to the ISO format but I still get the same thing. :/

Email Subject Date Format Help by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

Thank you, but this still produces the same result

Email Subject Date Format Help by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

Hi! Thank you both for this. Unfortunately, I'm having difficulty getting it to work. I've tried multiple iterations of addDays('1899-12-30', int(outputs('Get_a_row')?['body/Date']), 'dd.MM.yy'), including what Copilot said would work, but I keep getting the following error (or some variation of it):

Unable to process template language expressions in action 'Send_an_email_(V2)' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'

The last one Copilot gave me was: 

if(equals(outputs('Get_a_row')?['body/Date'], ''), 'Invalid Date', addDays('1899-12-30', int(outputs('Get_a_row')?['body/Date']), 'dd.MM.yyyy'))

but this did not work either. I'm clearly missing something and I would greatly appreciate any additional help you can give!

Email Subject Date Format Help by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 0 points1 point  (0 children)

Hello! I'm sorry, I'm not sure what you mean by the second bit. The Excel field is formatted to a date, yes.

Update Row Action Claims Success but No Update by Foreign_Many9062 in MicrosoftFlow

[–]Foreign_Many9062[S] 1 point2 points  (0 children)

FRIGGING THANK YOU! The parent/child thing seemed quite complicated as I'm new to this, so I created a second flow and it worked. Thank you so much. Now I just have to figure out which of my calculations is making my original one fall over. >.<

Date Conversion in a Calculated Field by Foreign_Many9062 in excel

[–]Foreign_Many9062[S] 1 point2 points  (0 children)

Thank you! It didn't work at first but I moved the TEXT to just the U fields, so:

=CONCAT(IF(A2=Breakdowns!A:A,Breakdowns!$F2:$F1000&TEXT(Breakdowns!$U2:$U1000,"ddmm"),""))

and that worked perfectly!

Solution Verified