Forum Discussion
Slow Load with Row Based Transformation
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 |
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"})
2 Replies
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"})