Adding Custom Column

[LinlookZDJ]

Dear Power BI Experts,

I have been stuck in this custom column quite some time and I couldn't figure out the solution.

This is my raw file data consist of 3 columns, namely "Start Date Time", "Complete Date Time" and "Machine" as shown in below:

I would like to add a custom column to calculate the time gap between each row for different type of machines. (However, i would like to leave first row and last row of individual machine as blank)

My desired outcome is as follow:

Appreciate for your kind assistance and welcome any types of solutions or recommendations. 

Thank you for your time in advance!

Attached my sample raw file for reference:

https://drive.google.com/file/d/1BzlSpP_YC3fjfSPThbnVcqAbn9GxILVe/view?usp=sharing

Best regards

Lin

[ryan_mayu · Accepted solution]

@LinlookZDJ 

pls try to create a column

Column = 
VAR _last=maxx(FILTER('Raw File','Raw File'[Complete Date Time]<EARLIER('Raw File'[Complete Date Time])&&'Raw File'[Machine]=EARLIER('Raw File'[Machine])),'Raw File'[Complete Date Time])
return if(ISBLANK(_last)||'Raw File'[Complete Date Time]=CALCULATE(max('Raw File'[Complete Date Time]),ALLEXCEPT('Raw File','Raw File'[Machine])),BLANK(),'Raw File'[Start Date Time]-_last)

pls see the attachment below

[LinlookZDJ]

Hi Ryan,

Just to check if there is anyways i can add the custom column inside transform data? As my raw data size is very big, so adding the new column in data view page is very lagging and it will hang easily.

Best regards

Lin

[ryan_mayu]

@LinlookZDJ 

you are welcome. I am not sure if we can do that in PQ. Maybe someone else can provide better solution for you.

[LinlookZDJ]

Hi Ryan,

Thanks for the solution! It works!

Best regards

Lin