Adding custom column in Power Query

[LinlookZDJ]

Dear Power BI Experts,

Is there any ways I can achieve the following outcome by adding a custom column in Power Query?

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/drive/folders/1eX1JRYGjVUkebCruQ2gq-n4dBK0hSjbu?usp=sharing

Best regards

Lin

[AllisonKennedy · Accepted solution]

@LinlookZDJ 

Try this code, I have broken it into a few columns to try and help make it more clear what I've done: 

let
Source = RawData,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date Time", type datetime}, {"Complete Date Time", type datetime}, {"Machine", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added PrevCompleteDateTime" = Table.AddColumn(#"Added Index", "Previous Row Complete Date Time", each if [Index] = 0 then null else if #"Added Index"{[Index]-1}[Machine] = [Machine] then #"Added Index"{[Index]-1}[Complete Date Time] else null),
#"Added IsNotLastRow" = Table.AddColumn(#"Added PrevCompleteDateTime", "Is Not Last Row", each if [Index] = List.Max(#"Added Index"[Index]) then null else if #"Added Index"{[Index]+1}[Machine] = [Machine] then 1 else null),
#"Inserted Time Gap" = Table.AddColumn(#"Added IsNotLastRow", "Time Gap", each [Is Not Last Row] * ( [Start Date Time] - [Previous Row Complete Date Time] ), type duration)
in
#"Inserted Time Gap"

[AllisonKennedy · Accepted solution]

@LinlookZDJ  

You can use Imke's custom function to improve performance: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po... 

I have modified it slightly for you, note this won't give you null for the last row, but will be null for first row of each machine. 

See updated attachment.

[LinlookZDJ]

Hi Allison,

Thank you so much!

Best regards

Lin

[AllisonKennedy · Accepted solution]

@LinlookZDJ 

Try this code, I have broken it into a few columns to try and help make it more clear what I've done: 

let
Source = RawData,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date Time", type datetime}, {"Complete Date Time", type datetime}, {"Machine", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added PrevCompleteDateTime" = Table.AddColumn(#"Added Index", "Previous Row Complete Date Time", each if [Index] = 0 then null else if #"Added Index"{[Index]-1}[Machine] = [Machine] then #"Added Index"{[Index]-1}[Complete Date Time] else null),
#"Added IsNotLastRow" = Table.AddColumn(#"Added PrevCompleteDateTime", "Is Not Last Row", each if [Index] = List.Max(#"Added Index"[Index]) then null else if #"Added Index"{[Index]+1}[Machine] = [Machine] then 1 else null),
#"Inserted Time Gap" = Table.AddColumn(#"Added IsNotLastRow", "Time Gap", each [Is Not Last Row] * ( [Start Date Time] - [Previous Row Complete Date Time] ), type duration)
in
#"Inserted Time Gap"

[LinlookZDJ]

Hi Allison,

Thank you for the solution!

Just that after adding your custom columns, I can't load my original raw file into dashboard, I have been waiting for almost half an hr and the dashboard still shows loading in progress...

(This is very weird as my raw file is not very big in size and previously i can load up to a few hundreds of raw file within a few seconds)

Is there anything else i need to change to optimize the loading process?

(Attached 3 of my main raw files for your reference)

https://drive.google.com/drive/folders/1bobrSCuXLHAm1xeJWCndMAscVgvw_mzJ?usp=sharing

Best regards

Lin