Can this excell formula be done in PBI or Power query?

[mayans]

I have this data set. Where the 01:30 is being used as a parameter of time.

=IF(F2=D3,IF((E3-G2)<$I$1,E2,E3),E3)

=IF(F3=D4,IF((E4-G3)<$I$1,G4,G3),G3)

Any ideas?

								01:30
RECORDDATE	LoginTime	LogoutTime	LoginTime	login_time	LogoutTime	logout_time	login	logout
2021-01-05 00:00	2021-01-05 14:58	2021-01-05 22:40	05/01/2021	14:58	05/01/2021	22:40	14:58	23:00
2021-01-05 00:00	2021-01-05 22:50	2021-01-05 23:00	05/01/2021	22:50	05/01/2021	23:00	14:58	23:00
2021-01-06 00:00	2021-01-06 22:48	2021-01-07 06:32	06/01/2021	22:48	07/01/2021	06:32	22:48	07:06
2021-01-07 00:00	2021-01-07 06:59	2021-01-07 07:06	07/01/2021	06:59	07/01/2021	07:06	22:48	07:06
2021-01-07 00:00	2021-01-07 16:55	2021-01-07 23:13	07/01/2021	16:55	07/01/2021	23:13	16:55	23:13
2021-01-09 00:00	2021-01-09 06:55	2021-01-09 14:03	09/01/2021	06:55	09/01/2021	14:03	06:55	14:03
2021-01-11 00:00	2021-01-11 15:00	2021-01-11 22:28	11/01/2021	15:00	11/01/2021	22:28	15:00	22:58
2021-01-11 00:00	2021-01-11 22:45	2021-01-11 22:58	11/01/2021	22:45	11/01/2021	22:58	15:00	22:58
2021-01-12 00:00	2021-01-12 23:09	2021-01-13 00:09	12/01/2021	23:09	13/01/2021	00:09	23:09	07:06

[v-easonf-msft · Accepted solution]

Hi, @mayans 

In Power BI Table visual, only one record of the same data is displayed, so it is recommended to add index column (unique column) in PQ, and then add the index  to the column of the table in report view.

Best Regards,
Community Support Team _ Eason

[negi007]

@mayans You can use the if formula in powerbi measure. Syntext would be little different from the excel. As in excel you specify the range but in powerbi you have to mention the column that you are using in your formula. In your case, you can first create a measure which can have pre-determined value like below

Parameter_Time = TIMEVALUE("01:30")

then you can change the format for the measure as below and use this measure in your second measure where you are using if condition. 

let me know if you are able to achive the desired output. else you can always write back with your pbix file to help you better. cheers!!!

[mayans]

@negi007 how do i achieve A1-A2 in pbi? meaning the value of row 1 - the value of row 2 of the same column?

[v-easonf-msft · Accepted solution]

Hi,  @mayans 

I make a sample file for reference.

Hope it could help you.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft · Accepted solution]

Hi, @mayans 

In Power BI Table visual, only one record of the same data is displayed, so it is recommended to add index column (unique column) in PQ, and then add the index  to the column of the table in report view.

Best Regards,
Community Support Team _ Eason

[mayans]

The solution was to add an index and use it to access other rows in this format. {[Index]}[columnName]

[selimovd]

Basically if you have the same table you can add the formula as a calculated column.

Did you try that?

[mayans]

just copy the formula from excel?