Difference between dates in Power Query-CodePudding

In power query editor, I added a custom column with the following formula:

=if[Type7]="REST" then [Til7]-[Fra7] else 0

That one works fine.

[Type#] is either "REST" or "REGULAR". [Fra#] and [Til#] is a time and date in the format dd.mm.yyyy hh:mm (the format is not a problem), I subtract the smaller one from the bigger one to get the amount of time in decimals ([Fra#] is always the smaller one).

However, when I try to chain several of them together, I get Error. I tried to do it like this:

=((if[Type7]="REST" then [Til7]-[Fra7] else 0)

(if[Type6]="REST" then [Til6]-[Fra6] else 0)

(if[Type5]="REST" then [Til5]-[Fra5] else 0)

(if[Type4]="REST" then [Til4]-[Fra4] else 0)

(if[Type3]="REST" then [Til3]-[Fra3] else 0)

(if[Type2]="REST" then [Til2]-[Fra2] else 0)

(if[Type1]="REST" then [Til1]-[Fra1] else 0)

(if[Type]="REST" then [Til]-[Fra] else 0))*24

I tried removing the *24 bit, but that is not the problem either.

Why is it not working? Thanks in advance for any help :)

Edit: Data screenshot

Comment to screenshot: I get Error as output in the rightmost column when I run the code.

let
  Source = Excel.Workbook(File.Contents("C:\Users\xxxxxx\Downloads\export.xlsx"), 
  null, true),
  #"ag-grid_Sheet" = Source{[Item="ag-grid",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"ag-grid_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"X_CREATED", type datetime}, {"X_CREATED_BY", type text}, {"X_DEFAULT_TZ", type text}, {"LocationData", type text}, {"Shift", type text}, {"ScheduleStart", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ScheduleStart"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Shift", Splitter.SplitTextByDelimiter("}", QuoteStyle.Csv), {"Shift.1", "Shift.2", "Shift.3", "Shift.4", "Shift.5", "Shift.6", "Shift.7", "Shift.8", "Shift.9"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",",{hours:,paidHours:,signature:,overtimeType:,","",Replacer.ReplaceText,{"Shift.1", "Shift.2", "Shift.3", "Shift.4", "Shift.5", "Shift.6", "Shift.7", "Shift.8", "Shift.9"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"Shift.1", "Shift.2", "Shift.3", "Shift.4", "Shift.5", "Shift.6", "Shift.7", "Shift.8", "Shift.9"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"",Replacer.ReplaceValue,{"Shift.1", "Shift.2", "Shift.3", "Shift.4", "Shift.5", "Shift.6", "Shift.7", "Shift.8", "Shift.9"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",",comment:","",Replacer.ReplaceText,{"Shift.1", "Shift.2", "Shift.3", "Shift.4", "Shift.5", "Shift.6", "Shift.7", "Shift.8", "Shift.9"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","[{hours:,paidHours:,signature:,overtimeType:,","",Replacer.ReplaceText,{"Shift.1", "Shift.2", "Shift.3", "Shift.4", "Shift.5", "Shift.6", "Shift.7", "Shift.8", "Shift.9"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value4", "Shift.1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Shift.1.1", "Shift.1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Shift.1.1", type text}, {"Shift.1.2", type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Shift.2", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Shift.2.1", "Shift.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Shift.2.1", type text}, {"Shift.2.2", type text}}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type2", "Shift.3", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Shift.3.1", "Shift.3.2", "Shift.3.3", "Shift.3.4", "Shift.3.5"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Shift.3.1", type text}, {"Shift.3.2", type text}, {"Shift.3.3", type text}, {"Shift.3.4", type text}, {"Shift.3.5", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"Shift.3.3", "Shift.3.4", "Shift.3.5"}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Removed Columns1", "Shift.4", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Shift.4.1", "Shift.4.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Shift.4.1", type text}, {"Shift.4.2", type text}}),
#"Split Column by Delimiter5" = Table.SplitColumn(#"Changed Type4", "Shift.5", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Shift.5.1", "Shift.5.2"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"Shift.5.1", type text}, {"Shift.5.2", type text}}),
#"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type5", "Shift.6", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Shift.6.1", "Shift.6.2"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"Shift.6.1", type text}, {"Shift.6.2", type text}}),
#"Split Column by Delimiter7" = Table.SplitColumn(#"Changed Type6", "Shift.7", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Shift.7.1", "Shift.7.2"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"Shift.7.1", type text}, {"Shift.7.2", type text}}),
#"Split Column by Delimiter8" = Table.SplitColumn(#"Changed Type7", "Shift.8", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Shift.8.1", "Shift.8.2"}),
#"Changed Type8" = Table.TransformColumnTypes(#"Split Column by Delimiter8",{{"Shift.8.1", type text}, {"Shift.8.2", type text}}),
#"Split Column by Delimiter9" = Table.SplitColumn(#"Changed Type8", "Shift.1.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shift.1.2.1", "Shift.1.2.2"}),
#"Changed Type9" = Table.TransformColumnTypes(#"Split Column by Delimiter9",{{"Shift.1.2.1", type text}, {"Shift.1.2.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type9",{"Shift.1.2.1"}),
#"Split Column by Delimiter10" = Table.SplitColumn(#"Removed Columns2", "Shift.2.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shift.2.2.1", "Shift.2.2.2"}),
#"Changed Type10" = Table.TransformColumnTypes(#"Split Column by Delimiter10",{{"Shift.2.2.1", type text}, {"Shift.2.2.2", type text}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type10",{"Shift.2.2.1"}),
#"Split Column by Delimiter11" = Table.SplitColumn(#"Removed Columns3", "Shift.3.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shift.3.2.1", "Shift.3.2.2"}),
#"Changed Type11" = Table.TransformColumnTypes(#"Split Column by Delimiter11",{{"Shift.3.2.1", type text}, {"Shift.3.2.2", type text}}),
#"Removed Columns4" = Table.RemoveColumns(#"Changed Type11",{"Shift.3.2.1"}),
#"Split Column by Delimiter12" = Table.SplitColumn(#"Removed Columns4", "Shift.4.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shift.4.2.1", "Shift.4.2.2"}),
#"Changed Type12" = Table.TransformColumnTypes(#"Split Column by Delimiter12",{{"Shift.4.2.1", type text}, {"Shift.4.2.2", type text}}),
#"Removed Columns5" = Table.RemoveColumns(#"Changed Type12",{"Shift.4.2.1"}),
#"Split Column by Delimiter13" = Table.SplitColumn(#"Removed Columns5", "Shift.5.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shift.5.2.1", "Shift.5.2.2"}),
#"Changed Type13" = Table.TransformColumnTypes(#"Split Column by Delimiter13",{{"Shift.5.2.1", type text}, {"Shift.5.2.2", type text}}),
#"Removed Columns6" = Table.RemoveColumns(#"Changed Type13",{"Shift.5.2.1"}),
#"Split Column by Delimiter14" = Table.SplitColumn(#"Removed Columns6", "Shift.6.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shift.6.2.1", "Shift.6.2.2"}),
#"Changed Type14" = Table.TransformColumnTypes(#"Split Column by Delimiter14",{{"Shift.6.2.1", type text}, {"Shift.6.2.2", type text}}),
#"Removed Columns7" = Table.RemoveColumns(#"Changed Type14",{"Shift.6.2.1"}),
#"Split Column by Delimiter15" = Table.SplitColumn(#"Removed Columns7", "Shift.7.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shift.7.2.1", "Shift.7.2.2"}),
#"Changed Type15" = Table.TransformColumnTypes(#"Split Column by Delimiter15",{{"Shift.7.2.1", type text}, {"Shift.7.2.2", type text}}),
#"Removed Columns8" = Table.RemoveColumns(#"Changed Type15",{"Shift.7.2.1"}),
#"Split Column by Delimiter16" = Table.SplitColumn(#"Removed Columns8", "Shift.8.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shift.8.2.1", "Shift.8.2.2"}),
#"Changed Type16" = Table.TransformColumnTypes(#"Split Column by Delimiter16",{{"Shift.8.2.1", type text}, {"Shift.8.2.2", type text}}),
#"Removed Columns9" = Table.RemoveColumns(#"Changed Type16",{"Shift.8.2.1"}),
#"Replaced Value5" = Table.ReplaceValue(#"Removed Columns9",null,"",Replacer.ReplaceValue,{"Shift.1.1", "Shift.1.2.2", "Shift.2.1", "Shift.2.2.2", "Shift.3.1", "Shift.3.2.2", "Shift.4.1", "Shift.4.2.2", "Shift.5.1", "Shift.5.2.2", "Shift.6.1", "Shift.6.2.2", "Shift.7.1", "Shift.7.2.2", "Shift.8.1", "Shift.8.2.2", "Shift.9"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value5",{"Shift.1.1", "Shift.1.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Shift.2.1", "Shift.2.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Shift.3.1", "Shift.3.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.2"),
#"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"Shift.4.1", "Shift.4.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.3"),
#"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"Shift.5.1", "Shift.5.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.4"),
#"Merged Columns5" = Table.CombineColumns(#"Merged Columns4",{"Shift.6.1", "Shift.6.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.5"),
#"Merged Columns6" = Table.CombineColumns(#"Merged Columns5",{"Shift.7.1", "Shift.7.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.6"),
#"Merged Columns7" = Table.CombineColumns(#"Merged Columns6",{"Shift.8.1", "Shift.8.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.7"),
#"Removed Columns10" = Table.RemoveColumns(#"Merged Columns7",{"LocationData"}),
#"Replaced Value6" = Table.ReplaceValue(#"Removed Columns10","/KVAKT","",Replacer.ReplaceText,{"X_CREATED_BY"}),
#"Split Column by Delimiter17" = Table.SplitColumn(#"Replaced Value6", "X_CREATED_BY", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"X_CREATED_BY.1", "X_CREATED_BY.2"}),
#"Changed Type17" = Table.TransformColumnTypes(#"Split Column by Delimiter17",{{"X_CREATED_BY.1", type text}, {"X_CREATED_BY.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type17",{{"X_CREATED_BY.1", "Stilling"}, {"X_CREATED_BY.2", "Fartøy"}, {"X_CREATED", "Dato_ført"}}),
#"Removed Columns11" = Table.RemoveColumns(#"Renamed Columns",{"X_DEFAULT_TZ"}),
#"Changed Type18" = Table.TransformColumnTypes(#"Removed Columns11",{{"Merged", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type18", "Merged", Splitter.SplitTextByPositions({0, 25}, false), {"Merged.1.1", "Merged.2.1"}),
#"Changed Type19" = Table.TransformColumnTypes(#"Split Column by Position",{{"Merged.1.1", type text}, {"Merged.2.1", type text}}),
#"Replaced Value7" = Table.ReplaceValue(#"Changed Type19","T"," ",Replacer.ReplaceText,{"Merged.1.1"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","L"," ",Replacer.ReplaceText,{"Merged.1.1"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","from: ","",Replacer.ReplaceText,{"Merged.1.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Replaced Value9",{{"Merged.1.1", "Fra"}}),
#"Replaced Value10" = Table.ReplaceValue(#"Renamed Columns1",",to:L","",Replacer.ReplaceText,{"Merged.2.1"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","T"," ",Replacer.ReplaceText,{"Merged.2.1"}),
#"Split Column by Delimiter18" = Table.SplitColumn(#"Replaced Value11", "Merged.2.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.2.1.1", "Merged.2.1.2"}),
#"Changed Type20" = Table.TransformColumnTypes(#"Split Column by Delimiter18",{{"Merged.2.1.1", type datetime}, {"Merged.2.1.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type20",{{"Merged.2.1.1", "Til"}}),
#"Changed Type21" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Fra", type datetime}}),
#"Replaced Value12" = Table.ReplaceValue(#"Changed Type21","type:","",Replacer.ReplaceText,{"Merged.2.1.2"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","RES","REST",Replacer.ReplaceText,{"Merged.2.1.2"}),
#"Renamed Columns3" = Table.RenameColumns(#"Replaced Value13",{{"Merged.2.1.2", "Type"}}),
#"Replaced Value14" = Table.ReplaceValue(#"Renamed Columns3","from:L","",Replacer.ReplaceText,{"Merged.1"}),
#"Split Column by Position1" = Table.SplitColumn(#"Replaced Value14", "Merged.1", Splitter.SplitTextByPositions({0, 19}, false), {"Merged.1.1", "Merged.1.2"}),
#"Changed Type22" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Merged.1.1", type datetime}, {"Merged.1.2", type text}}),
#"Renamed Columns4" = Table.RenameColumns(#"Changed Type22",{{"Merged.1.1", "Fra1"}, {"Merged.1.2", "Til1"}}),
#"Replaced Value15" = Table.ReplaceValue(#"Renamed Columns4",",to:L","",Replacer.ReplaceText,{"Til1"}),
#"Split Column by Delimiter19" = Table.SplitColumn(#"Replaced Value15", "Til1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Til1.1", "Til1.2"}),
#"Changed Type23" = Table.TransformColumnTypes(#"Split Column by Delimiter19",{{"Til1.1", type text}, {"Til1.2", type text}}),
#"Replaced Value16" = Table.ReplaceValue(#"Changed Type23",",type","",Replacer.ReplaceText,{"Til1.1"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","T"," ",Replacer.ReplaceText,{"Til1.1"}),
#"Changed Type24" = Table.TransformColumnTypes(#"Replaced Value17",{{"Til1.1", type datetime}}),
#"Renamed Columns5" = Table.RenameColumns(#"Changed Type24",{{"Til1.1", "Til1"}, {"Til1.2", "Type1"}}),
#"Replaced Value18" = Table.ReplaceValue(#"Renamed Columns5","from:L","",Replacer.ReplaceText,{"Merged.2"}),
#"Split Column by Delimiter20" = Table.SplitColumn(#"Replaced Value18", "Merged.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Merged.2.1", "Merged.2.2"}),
#"Changed Type25" = Table.TransformColumnTypes(#"Split Column by Delimiter20",{{"Merged.2.1", type text}, {"Merged.2.2", type text}}),
#"Replaced Value19" = Table.ReplaceValue(#"Changed Type25",",type","",Replacer.ReplaceText,{"Merged.2.1"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","T"," ",Replacer.ReplaceText,{"Merged.2.1"}),
#"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","L","",Replacer.ReplaceText,{"Merged.2.1"}),
#"Split Column by Delimiter21" = Table.SplitColumn(#"Replaced Value21", "Merged.2.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.2.1.1", "Merged.2.1.2"}),
#"Changed Type26" = Table.TransformColumnTypes(#"Split Column by Delimiter21",{{"Merged.2.1.1", type datetime}, {"Merged.2.1.2", type text}}),
#"Renamed Columns6" = Table.RenameColumns(#"Changed Type26",{{"Merged.2.1.1", "Fra2"}}),
#"Replaced Value22" = Table.ReplaceValue(#"Renamed Columns6","to:","",Replacer.ReplaceText,{"Merged.2.1.2"}),
#"Changed Type27" = Table.TransformColumnTypes(#"Replaced Value22",{{"Merged.2.1.2", type datetime}}),
#"Renamed Columns7" = Table.RenameColumns(#"Changed Type27",{{"Merged.2.1.2", "Til2"}, {"Merged.2.2", "Type2"}}),
#"Replaced Value23" = Table.ReplaceValue(#"Renamed Columns7","from:L","",Replacer.ReplaceText,{"Merged.3"}),
#"Split Column by Delimiter22" = Table.SplitColumn(#"Replaced Value23", "Merged.3", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Merged.3.1", "Merged.3.2"}),
#"Changed Type28" = Table.TransformColumnTypes(#"Split Column by Delimiter22",{{"Merged.3.1", type text}, {"Merged.3.2", type text}}),
#"Replaced Value24" = Table.ReplaceValue(#"Changed Type28",",type","",Replacer.ReplaceText,{"Merged.3.1"}),
#"Replaced Value25" = Table.ReplaceValue(#"Replaced Value24","T"," ",Replacer.ReplaceText,{"Merged.3.1"}),
#"Replaced Value26" = Table.ReplaceValue(#"Replaced Value25","L","",Replacer.ReplaceText,{"Merged.3.1"}),
#"Split Column by Delimiter23" = Table.SplitColumn(#"Replaced Value26", "Merged.3.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.3.1.1", "Merged.3.1.2"}),
#"Changed Type29" = Table.TransformColumnTypes(#"Split Column by Delimiter23",{{"Merged.3.1.1", type datetime}, {"Merged.3.1.2", type text}}),
#"Replaced Value27" = Table.ReplaceValue(#"Changed Type29","to:","",Replacer.ReplaceText,{"Merged.3.1.2"}),
#"Changed Type30" = Table.TransformColumnTypes(#"Replaced Value27",{{"Merged.3.1.2", type datetime}}),
#"Renamed Columns8" = Table.RenameColumns(#"Changed Type30",{{"Merged.3.1.1", "Fra3"}, {"Merged.3.1.2", "Til3"}, {"Merged.3.2", "Type3"}}),
#"Replaced Value28" = Table.ReplaceValue(#"Renamed Columns8","from:L","",Replacer.ReplaceText,{"Merged.4"}),
#"Split Column by Delimiter24" = Table.SplitColumn(#"Replaced Value28", "Merged.4", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Merged.4.1", "Merged.4.2"}),
#"Changed Type31" = Table.TransformColumnTypes(#"Split Column by Delimiter24",{{"Merged.4.1", type text}, {"Merged.4.2", type text}}),
#"Replaced Value29" = Table.ReplaceValue(#"Changed Type31",",type","",Replacer.ReplaceText,{"Merged.4.1"}),
#"Replaced Value30" = Table.ReplaceValue(#"Replaced Value29","L","",Replacer.ReplaceText,{"Merged.4.1"}),
#"Replaced Value31" = Table.ReplaceValue(#"Replaced Value30","T"," ",Replacer.ReplaceText,{"Merged.4.1"}),
#"Split Column by Delimiter25" = Table.SplitColumn(#"Replaced Value31", "Merged.4.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.4.1.1", "Merged.4.1.2"}),
#"Changed Type32" = Table.TransformColumnTypes(#"Split Column by Delimiter25",{{"Merged.4.1.1", type text}, {"Merged.4.1.2", type text}}),
#"Replaced Value32" = Table.ReplaceValue(#"Changed Type32","to:","",Replacer.ReplaceText,{"Merged.4.1.2"}),
#"Changed Type33" = Table.TransformColumnTypes(#"Replaced Value32",{{"Merged.4.1.1", type datetime}, {"Merged.4.1.2", type datetime}}),
#"Renamed Columns9" = Table.RenameColumns(#"Changed Type33",{{"Merged.4.1.1", "Fra4"}, {"Merged.4.1.2", "Til4"}, {"Merged.4.2", "Type4"}}),
#"Replaced Value33" = Table.ReplaceValue(#"Renamed Columns9","from:L","",Replacer.ReplaceText,{"Merged.5"}),
#"Split Column by Delimiter26" = Table.SplitColumn(#"Replaced Value33", "Merged.5", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Merged.5.1", "Merged.5.2"}),
#"Changed Type34" = Table.TransformColumnTypes(#"Split Column by Delimiter26",{{"Merged.5.1", type text}, {"Merged.5.2", type text}}),
#"Replaced Value34" = Table.ReplaceValue(#"Changed Type34",",type","",Replacer.ReplaceText,{"Merged.5.1"}),
#"Replaced Value35" = Table.ReplaceValue(#"Replaced Value34","T"," ",Replacer.ReplaceText,{"Merged.5.1"}),
#"Replaced Value36" = Table.ReplaceValue(#"Replaced Value35","L","",Replacer.ReplaceText,{"Merged.5.1"}),
#"Split Column by Delimiter27" = Table.SplitColumn(#"Replaced Value36", "Merged.5.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.5.1.1", "Merged.5.1.2"}),
#"Changed Type35" = Table.TransformColumnTypes(#"Split Column by Delimiter27",{{"Merged.5.1.1", type datetime}, {"Merged.5.1.2", type text}}),
#"Replaced Value37" = Table.ReplaceValue(#"Changed Type35","to:","",Replacer.ReplaceText,{"Merged.5.1.2"}),
#"Changed Type36" = Table.TransformColumnTypes(#"Replaced Value37",{{"Merged.5.1.2", type datetime}}),
#"Renamed Columns10" = Table.RenameColumns(#"Changed Type36",{{"Merged.5.1.1", "Fra5"}, {"Merged.5.1.2", "Til5"}, {"Merged.5.2", "Type5"}}),
#"Replaced Value38" = Table.ReplaceValue(#"Renamed Columns10","from:L","",Replacer.ReplaceText,{"Merged.6"}),
#"Split Column by Delimiter28" = Table.SplitColumn(#"Replaced Value38", "Merged.6", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Merged.6.1", "Merged.6.2"}),
#"Changed Type37" = Table.TransformColumnTypes(#"Split Column by Delimiter28",{{"Merged.6.1", type text}, {"Merged.6.2", type text}}),
#"Replaced Value39" = Table.ReplaceValue(#"Changed Type37",",type","",Replacer.ReplaceText,{"Merged.6.1"}),
#"Replaced Value40" = Table.ReplaceValue(#"Replaced Value39","T"," ",Replacer.ReplaceText,{"Merged.6.1"}),
#"Replaced Value41" = Table.ReplaceValue(#"Replaced Value40","L","",Replacer.ReplaceText,{"Merged.6.1"}),
#"Split Column by Delimiter29" = Table.SplitColumn(#"Replaced Value41", "Merged.6.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.6.1.1", "Merged.6.1.2"}),
#"Changed Type38" = Table.TransformColumnTypes(#"Split Column by Delimiter29",{{"Merged.6.1.1", type datetime}, {"Merged.6.1.2", type text}}),
#"Replaced Value42" = Table.ReplaceValue(#"Changed Type38","to:","",Replacer.ReplaceText,{"Merged.6.1.2"}),
#"Changed Type39" = Table.TransformColumnTypes(#"Replaced Value42",{{"Merged.6.1.2", type datetime}}),
#"Renamed Columns11" = Table.RenameColumns(#"Changed Type39",{{"Merged.6.1.1", "Fra6"}, {"Merged.6.1.2", "Til6"}, {"Merged.6.2", "Type6"}}),
#"Replaced Value43" = Table.ReplaceValue(#"Renamed Columns11","from:L","",Replacer.ReplaceText,{"Merged.7"}),
#"Split Column by Delimiter30" = Table.SplitColumn(#"Replaced Value43", "Merged.7", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Merged.7.1", "Merged.7.2"}),
#"Changed Type40" = Table.TransformColumnTypes(#"Split Column by Delimiter30",{{"Merged.7.1", type text}, {"Merged.7.2", type text}}),
#"Replaced Value44" = Table.ReplaceValue(#"Changed Type40","T"," ",Replacer.ReplaceText,{"Merged.7.1"}),
#"Replaced Value45" = Table.ReplaceValue(#"Replaced Value44","L","",Replacer.ReplaceText,{"Merged.7.1"}),
#"Replaced Value46" = Table.ReplaceValue(#"Replaced Value45",",type","",Replacer.ReplaceText,{"Merged.7.1"}),
#"Split Column by Delimiter31" = Table.SplitColumn(#"Replaced Value46", "Merged.7.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.7.1.1", "Merged.7.1.2"}),
#"Changed Type41" = Table.TransformColumnTypes(#"Split Column by Delimiter31",{{"Merged.7.1.1", type datetime}, {"Merged.7.1.2", type text}}),
#"Replaced Value47" = Table.ReplaceValue(#"Changed Type41","to:","",Replacer.ReplaceText,{"Merged.7.1.2"}),
#"Changed Type42" = Table.TransformColumnTypes(#"Replaced Value47",{{"Merged.7.1.2", type datetime}}),
#"Renamed Columns12" = Table.RenameColumns(#"Changed Type42",{{"Merged.7.1.1", "Fra7"}, {"Merged.7.1.2", "Til7"}, {"Merged.7.2", "Type7"}}),
#"Removed Columns12" = Table.RemoveColumns(#"Renamed Columns12",{"Shift.9"}),
#"Replaced Value48" = Table.ReplaceValue(#"Removed Columns12","REST ","REST",Replacer.ReplaceText,{"Type", "Type1", "Type2", "Type3", "Type4", "Type5", "Type6", "Type7"}),
#"Replaced Value49" = Table.ReplaceValue(#"Replaced Value48"," REST","REST",Replacer.ReplaceText,{"Type", "Type1", "Type2", "Type3", "Type4", "Type5", "Type6", "Type7"}),
#"Replaced Value50" = Table.ReplaceValue(#"Replaced Value49","REGULAR ","REGULAR",Replacer.ReplaceText,{"Type", "Type1", "Type2", "Type3", "Type4", "Type5", "Type6", "Type7"}),
#"Replaced Value51" = Table.ReplaceValue(#"Replaced Value50"," REGULAR","REGULAR",Replacer.ReplaceText,{"Type", "Type1", "Type2", "Type3", "Type4", "Type5", "Type6", "Type7"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value51", "Hours rest", each ((if[Type7]="REST" then [Til7]-[Fra7] else 0)
 (if[Type6]="REST" then [Til6]-[Fra6] else 0)
 (if[Type5]="REST" then [Til5]-[Fra5] else 0)
 (if[Type4]="REST" then [Til4]-[Fra4] else 0)
 (if[Type3]="REST" then [Til3]-[Fra3] else 0)
 (if[Type2]="REST" then [Til2]-[Fra2] else 0)
 (if[Type1]="REST" then [Til1]-[Fra1] else 0)
 (if[Type]="REST" then [Til]-[Fra] else 0))*24)
in
#"Added Custom"

CodePudding user response：

You can't directly subtract dates

Either try

Duration.Days([Fra]-[Fra1])

Number.From ([Fra])-Number.From([Fra1])

See potential duration functions https://docs.microsoft.com/en-us/powerquery-m/duration-functions

also maybe use some error catching

=( try if [Type6]="REST" then Duration.Days([Til6]-[Fra6]) else 0 otherwise 0)

=( try if [Type6]="REST" then Number.From([Til6]) -Number.From([Fra6]) else 0 otherwise 0)