Slow Load with Row Based Transformation

[Anonymous]

Hi, I've made a transformation to a Table that is the result of a series of XLSX files (6) stored locally, the combined table is reasonably small at approx. 55k rows / 22 columns. Load times are quick at only a few seconds (both PQ preview and Table load), however, after adding my row-based transformation, using an index column, the load times are so long (hours) that I've never finished it to determine exactly how long. 

I've included the entire query below and how I expect the result to display, however, I've determined the load issue arises at #"Added Custom". I'm assuming that I've made an error in how I've gone about adding the columns.

Any help would be greatly appreciated.

Source = Table.Combine({#"S1 Asbuilt", #"S2 Asbuilt", #"S3-4A Asbuilt", #"S4B Asbuilt", #"S5A Asbuilt", #"S6 Asbuilt"}),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "WT1", each if [Pipe WT] = null then (try #"Added Index" [Pipe WT] {[Index] - 1} otherwise null) else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "WT2", each if [Pipe WT] = null then (try #"Added Custom" [Pipe WT] {[Index] + 1} otherwise null) else null),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"WT1", type text}, {"WT2", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "CBT", each if [WT1] = [WT2] then null else "CB " & [WT1] & " / " & [WT2]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"WT1", "WT2"})

 Expected Results

	#"Added Custom"	#Added Cutom1"	#"Added Custom 2"
Pipe WT	WT1	WT2	CBT
25.4	null	null	null
25.4	null	null	null
null	25.4	19.0	CB 25.4 / 19.0
19.0	null	null	null
19.0	null	null	null
null	19.0	14.7	CB 19.0 / 14.7
14.7	null	null	null

[wdx223_Daniel · Accepted solution]

Source = Table.Combine({#"S1 Asbuilt", #"S2 Asbuilt", #"S3-4A Asbuilt", #"S4B Asbuilt", #"S5A Asbuilt", #"S6 Asbuilt"}),
Custom1=Table.FromColumns(Table.ToColumns(Source)&{{null}&List.Transform(List.Zip({List.RemoveLastN(Source[Pipe WT],2),List.Skip(Source[Pipe WT],2)}),each if Text.From(_{0})=Text.From(_{1}) then null else Text.Format("CB#{0}/#{1}",_))&{null}},Table.ColumnNames(Source)&{"CBT"})

[Anonymous]

This worked great, thank you so much for the help, now I just need to figure out why this worked, haha.