Help win unpivot super slow query

labello27 · Feb 25, 2024

Hi,

I am very new to power query and I am trying to get the records that are all in column and unpivot them. I was able to do it but the query takes a long time to run. Could you take a look at the attached file and help me out?

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Sweep"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([COL1], "Record") then 1 else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Errors", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each List.Sum(List.FirstN(#"Added Index"[Custom],[Index]))),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom.1"}, {{"All", each _, type table [COL1=any, Custom=nullable number, Index=number, Custom.1=number]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"ColIden",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"COL1", "ColIden"}, {"Custom.COL1", "Custom.ColIden"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"All"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.ColIden", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.ColIden", type text}}, "en-US")[Custom.ColIden]), "Custom.ColIden", "Custom.COL1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"4", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"1", "Record Number"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Record Number", "2", "3", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",":","",Replacer.ReplaceText,{"Record Number"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"2", type date}, {"3", type time}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type", "Date", each [2]&[3]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom3",{"Record Number", "Date", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "2", "3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"5", "Station"}, {"6", "Registration #"}, {"7", "Ship #"}, {"9", "Measure Applied to Flight"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Record Number", "Date", "Station", "Registration #", "Ship #", "8", "Measure Applied to Flight", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "2", "3", "10"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns1",{{"11", "Outbound Flight"}, {"12", "Origin"}, {"13", "Destination"}, {"16", "Completed Date"}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Renamed Columns2", "14", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"14.1", "14.2", "14.3"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Character Transition",{{"14.1", "Search Location"}}),
    #"Added Custom4" = Table.AddColumn(#"Renamed Columns3", "Time", each [14.2]&[14.3]),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom4",{"Record Number", "Date", "Station", "Registration #", "Ship #", "8", "Measure Applied to Flight", "Outbound Flight", "Origin", "Destination", "Search Location", "14.2", "14.3", "15", "Completed Date", "Time", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "2", "3", "10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns2",{{"Completed Date", type date}, {"Time", type time}}),
    #"Added Custom5" = Table.AddColumn(#"Changed Type1", "Interior Completion Date", each [Completed Date]&[Time]),
    #"Reordered Columns3" = Table.ReorderColumns(#"Added Custom5",{"Record Number", "Date", "Station", "Registration #", "Ship #", "8", "Measure Applied to Flight", "Outbound Flight", "Origin", "Destination", "Search Location", "Interior Completion Date", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "14.2", "14.3", "15", "Completed Date", "Time", "17", "30", "31", "2", "3", "10", "18"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Reordered Columns3",{{"19", "Search Agents"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns4",{{"22", type date}}),
    #"Added Custom6" = Table.AddColumn(#"Changed Type2", "Ext Time", each if [20] <> null then Text.End([20], 5) else null),
    #"Reordered Columns4" = Table.ReorderColumns(#"Added Custom6",{"Record Number", "Date", "Station", "Registration #", "Ship #", "8", "Measure Applied to Flight", "Outbound Flight", "Origin", "Destination", "Search Location", "Interior Completion Date", "Search Agents", "20", "21", "22", "Ext Time", "23", "24", "25", "26", "27", "28", "29", "14.2", "14.3", "15", "Completed Date", "Time", "17", "30", "31", "2", "3", "10", "18"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Reordered Columns4",{{"20", "Exterior Search"}}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Renamed Columns5", "Exterior Search", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Exterior Search.1", "Exterior Search.2", "Exterior Search.3"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Split Column by Character Transition1",{{"Search Agents", "Interior Search Agents"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns6",{{"Ext Time", type time}}),
    #"Added Custom7" = Table.AddColumn(#"Changed Type3", "Exterior Completion Date", each if [22] <> null and [Ext Time] <> null then [22] & [Ext Time] else null),
    #"Reordered Columns5" = Table.ReorderColumns(#"Added Custom7",{"Record Number", "Date", "Station", "Registration #", "Ship #", "8", "Measure Applied to Flight", "Outbound Flight", "Origin", "Destination", "Search Location", "Interior Completion Date", "Interior Search Agents", "Exterior Search.1", "Exterior Search.2", "Exterior Search.3", "21", "22", "Ext Time", "Exterior Completion Date", "23", "24", "25", "26", "27", "28", "29", "14.2", "14.3", "15", "Completed Date", "Time", "17", "30", "31", "2", "3", "10", "18"}),
    #"Renamed Columns7" = Table.RenameColumns(#"Reordered Columns5",{{"Exterior Search.1", "Exterior Search"}}),
    #"Reordered Columns6" = Table.ReorderColumns(#"Renamed Columns7",{"Record Number", "Date", "Station", "Registration #", "Ship #", "8", "Measure Applied to Flight", "Outbound Flight", "Origin", "Destination", "Search Location", "Interior Completion Date", "Interior Search Agents", "Exterior Search", "Exterior Completion Date", "23", "24", "25", "26", "27", "28", "29", "14.2", "14.3", "15", "Completed Date", "Time", "17", "30", "31", "2", "3", "10", "18", "Exterior Search.2", "Exterior Search.3", "21", "22", "Ext Time"}),
    #"Renamed Columns8" = Table.RenameColumns(#"Reordered Columns6",{{"25", "Ext Search Agents"}}),
    #"Reordered Columns7" = Table.ReorderColumns(#"Renamed Columns8",{"Record Number", "Date", "Station", "Registration #", "Ship #", "8", "Measure Applied to Flight", "Outbound Flight", "Origin", "Destination", "Search Location", "Interior Completion Date", "Interior Search Agents", "Exterior Search", "Exterior Completion Date", "Ext Search Agents", "26", "27", "28", "29", "14.2", "14.3", "15", "Completed Date", "Time", "17", "30", "31", "2", "3", "10", "18", "Exterior Search.2", "Exterior Search.3", "21", "22", "Ext Time", "23", "24"}),
    #"Renamed Columns9" = Table.RenameColumns(#"Reordered Columns7",{{"26", "Aircraft Baggage / Cargo Bins"}, {"28", "Bag Date"}}),
    #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns9", "Aircraft Baggage / Cargo Bins", Splitter.SplitTextByPositions({0, 5}, true), {"Aircraft Baggage / Cargo Bins.1", "Aircraft Baggage / Cargo Bins.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Search Location", type text}, {"Exterior Search", type text}, {"Aircraft Baggage / Cargo Bins.1", type text}, {"Aircraft Baggage / Cargo Bins.2", type time}, {"14.2", type text}, {"14.3", Int64.Type}, {"Exterior Search.2", type text}, {"Exterior Search.3", Int64.Type}}),
    #"Renamed Columns10" = Table.RenameColumns(#"Changed Type4",{{"Aircraft Baggage / Cargo Bins.1", "Aircraft Baggage / Cargo Bins"}, {"Aircraft Baggage / Cargo Bins.2", "Aircraft Baggage Time"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns10",{"27"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Removed Columns2",{{"Bag Date", type date}}),
    #"Added Custom8" = Table.AddColumn(#"Changed Type5", "Baggage Bins Completion Date", each if [Bag Date] <> null and [Aircraft Baggage Time] <> null then [Bag Date] & [Aircraft Baggage Time] else null),
    #"Reordered Columns8" = Table.ReorderColumns(#"Added Custom8",{"Record Number", "Date", "Station", "Registration #", "Ship #", "8", "Measure Applied to Flight", "Outbound Flight", "Origin", "Destination", "Search Location", "Interior Completion Date", "Interior Search Agents", "Exterior Search", "Exterior Completion Date", "Ext Search Agents", "Aircraft Baggage / Cargo Bins", "Baggage Bins Completion Date", "15", "Completed Date", "Time", "17", "30", "31", "2", "3", "10", "18", "Exterior Search.2", "Exterior Search.3", "21", "22", "Ext Time", "23", "24", "Aircraft Baggage Time", "Bag Date", "29", "14.2", "14.3"}),
    #"Renamed Columns11" = Table.RenameColumns(#"Reordered Columns8",{{"31", "Cargo Search Agents"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns11",{"15", "Completed Date", "Time", "17", "30", "2", "3", "10", "18", "Exterior Search.2", "Exterior Search.3", "21", "22", "Ext Time", "23", "24", "Aircraft Baggage Time", "Bag Date", "29", "14.2", "14.3"}),
    #"Split Column by Position1" = Table.SplitColumn(#"Removed Columns3", "Station", Splitter.SplitTextByPositions({0, 3}, true), {"Station.1", "Station.2"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Station.1", type text}, {"Station.2", type text}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Changed Type6",{"Station.1"}),
    #"Renamed Columns12" = Table.RenameColumns(#"Removed Columns4",{{"Station.2", "Station"}}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns12",{{"Date", type datetime}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type7", {{"Registration #", each Text.AfterDelimiter(_, "#:"), type text}}),
    #"Extracted Text After Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Ship #", each Text.AfterDelimiter(_, "#:"), type text}}),
    #"Changed Type8" = Table.TransformColumnTypes(#"Extracted Text After Delimiter1",{{"Ship #", Int64.Type}}),
    #"Removed Columns5" = Table.RemoveColumns(#"Changed Type8",{"8"}),
    #"Extracted Text After Delimiter2" = Table.TransformColumns(#"Removed Columns5", {{"Measure Applied to Flight", each Text.AfterDelimiter(_, ":"), type text}, {"Outbound Flight", each Text.AfterDelimiter(_, ":"), type text}, {"Origin", each Text.AfterDelimiter(_, ":"), type text}, {"Destination", each Text.AfterDelimiter(_, ":"), type text}}),
    #"Changed Type9" = Table.TransformColumnTypes(#"Extracted Text After Delimiter2",{{"Interior Completion Date", type datetime}, {"Exterior Completion Date", type datetime}, {"Baggage Bins Completion Date", type datetime}, {"Ship #", type text}, {"Outbound Flight", Int64.Type}})
in
    #"Changed Type9"

Thanks,

labello27 · Feb 29, 2024

labello27 said:
here is a sample data:

Book1
B
2 Record 1:
3 29-Feb-24
4 02:03
5 Print Audit
6 Station:JFK
7 A/C FAA Reg #:N174DN
8 Ship #:000174
9 Date:2024-02-29
10 Measure applied to flight: FULL
11 International Mainline Stations Only (Not DC Flights)
12 Outbound Flight Number:0742
13 Flight Origin:JFK
14 Flight Destination:LAX
15 Aircraft Interior02:03
16 Completed Date:
17 2/29/2024
18 Notes:
19 Search Agents:
20 BIANCA,YOUNG DE DE LEON; GAIL,GUY JONES; GEURY,ESPINAL VARGAS; YAHAIRA,FERNANDEZ; YANDEL,VEGA; SETH,PLAZA; TAYLOR,TROYE; Jamani,Barnell; JENNA,HAMILTON;
21 Aircraft Exterior06:10
22 Completed Date:
23 2/29/2024
24 Notes:
25 Search Agents:
26 kino,galbraith;
27 Aircraft Baggage / Cargo Bins06:09
28 Completed Date:
29 2/29/2024
30 Notes:
31 Search Agents:
32 kino,galbraith;
33 Record 2:
34 28-Feb-24
35 21:40
36 Print Audit
37 Station:JFK
38 A/C FAA Reg #:N177DN
39 Ship #:000177
40 Date:2024-02-29
41 Measure applied to flight: FULL
42 International Mainline Stations Only (Not DC Flights)
43 Outbound Flight Number:0926
44 Flight Origin:JFK
45 Flight Destination:LAX
46 Aircraft Interior21:43
47 Completed Date:
48 2/28/2024
49 Notes:
50 Search Agents:
51 WATSON,VAUGHN; DANESHA,SIMMS; STEFFAN,DASRAT; YAIROL,Matos; antoine,davis; ZONA,LUGO; FRANCINSCO,Rivera; JENNIEL,Joseph; Francia,Tejedia; ENCARNACION LISSETT,VILLAR; Jean,Festa; Jeffrey,Bastidas; KASHEKA,MESSAM; TYSHAWN,PAYNE; JENNI FLORE,THOMAS; Devon,Parbat;TIANNA,MAYERS;Evans,Salomon;Bastidas,Jeffery ;
52 Record 3:
53 29-Feb-24
54 13:56
55 Print Audit
56 Station:JFK
57 A/C FAA Reg #:N189DN
58 Ship #:000189
59 Date:2024-02-29
60 Measure applied to flight: FULL
61 International Mainline Stations Only (Not DC Flights)
62 Outbound Flight Number:2170
63 Flight Origin:JFK
64 Flight Destination:ATL
65 Aircraft Interior13:56
66 Completed Date:
67 2/29/2024
68 Notes:
69 Search Agents:
70 ALVES,ABRIANA; Vilma,LOPEZ PAZMINO; Antoinette,Harding; Kelvin,Mcpherson; Estela,Nicolas; DARLINE,CONTENT; Stacey,Brutus; TERESA,BETEMIT DE JIMENEZ; JOMARIE ,PAMPILO; ANGEL,SANCHEZ;Basdaye,Laltoo;Edson,Mora;Eugenie,Auguste;FARANIAINA,ANDRIANASOLO;JORGE,SARMIENTO-ARCE;Judy,Blair;LINDA,ROWE;MASHERA,MITCHELL;Mohammed,Amin;OLIVER,TORRESGUZMAN;PARBATTIE,ROOPCHAN;Ramona,Thakurdyal;ROSA,Rodriguez;
71 Record 4:
72 28-Feb-24
73 20:47
74 Print Audit
75 Station:JFK
76 A/C FAA Reg #:N190DN
77 Ship #:000190
78 Date:2024-02-29
79 Measure applied to flight: FULL
80 International Mainline Stations Only (Not DC Flights)
81 Outbound Flight Number:0567
82 Flight Origin:JFK
83 Flight Destination:LAX
84 Aircraft Interior20:47
85 Completed Date:
86 2/28/2024
87 Notes:
88 Search Agents:
89 AQUILLA,SYKES; GLENDON,COSBERT; Chelsea,Marshall; RAYSHAN,RAMSEY; Tim,CHARIAH; Thomas,Smith Sam; SONJI,RANDALL; Michael,Tannis; KEIDON,RICHENS; JOHANNA,SUE;
90 Aircraft Exterior07:48
91 Completed Date:
92 2/29/2024
93 Notes:
94 Search Agents:
95 timothy,rodney;
96 Aircraft Baggage / Cargo Bins07:48
97 Completed Date:
98 2/29/2024
99 Notes:
100 Search Agents:
101 timothy,rodney;
102 Record 5:
103 29-Feb-24
104 07:32
105 Print Audit
106 Station:JFK
107 A/C FAA Reg #:N194DN
108 Ship #:000194
109 Date:2024-02-29
110 Measure applied to flight: FULL
111 International Mainline Stations Only (Not DC Flights)
112 Outbound Flight Number:0773
113 Flight Origin:JFK
114 Flight Destination:LAX
115 Aircraft Interior07:32
116 Completed Date:
117 2/29/2024
118 Notes:
119 Search Agents:
120 SHELLYANN,GUTHRIE-MONTAQUE; JUANITA,ABREU; Julie,Cole; Deidre,Anderson; Nicole R,Sullivan; HILBERTO,USHER HOLMES; NATALIE,TABLADA; DORCIA,Williams; NELSON,RAYMOND; SHARLINE,WILLIAMS; KESIA,THOMAS;
121 Aircraft Exterior09:10
122 Completed Date:
123 2/29/2024
124 Notes:
125 Search Agents:
126 Kwame,Fredericks;
127 Aircraft Baggage / Cargo Bins09:05
128 Completed Date:
129 2/29/2024
130 Notes:
131 Search Agents:
132 Kwame,Fredericks;
133 Record 6:
134 28-Feb-24
135 21:22
136 Print Audit
137 Station:JFK
138 A/C FAA Reg #:N693DL
139 Ship #:000693
140 Date:2024-02-29
141 Measure applied to flight: FULL
142 International Mainline Stations Only (Not DC Flights)
143 Outbound Flight Number:1887
144 Flight Origin:JFK
145 Flight Destination:SXM
146 Aircraft Interior21:22
147 Completed Date:
148 2/28/2024
149 Notes:
150 Search Agents:
151 ARBELINA,HUNT; Avendra,Jienarine; Tianna,Thomas; LAWRENCE,SUMMER JR; Brian,Fernandez; PHILIP,DUNCAN; HASAN ,RAQUIBUL; OLIVER ,OSSE KARL ;
152 Aircraft Exterior08:26
153 Completed Date:
154 2/29/2024
155 Notes:
156 Search Agents:
157 mario,johnson;
158 Aircraft Baggage / Cargo Bins08:26
159 Completed Date:
160 2/29/2024
161 Notes:
162 Search Agents:
163 mario,johnson;
164 Record 7:
165 28-Feb-24
166 20:10
167 Print Audit
168 Station:JFK
169 A/C FAA Reg #:N102DN
170 Ship #:001002
171 Date:2024-02-29
172 Measure applied to flight: FULL
173 International Mainline Stations Only (Not DC Flights)
174 Outbound Flight Number:2383
175 Flight Origin:JFK
176 Flight Destination:RSW
177 Aircraft Interior20:10
178 Completed Date:
179 2/28/2024
180 Notes:
181 Search Agents:
182 SAVITA,RAMBARAN; DALLISCA,massiah; Selencia,Tannis; ALSOPP,COLIN; DAMION,CLARKE; CLIVE,ROBINSON; SHONETTE,SCHWARTZ; SHEIK,RASHED;
183 Aircraft Exterior11:59
184 Completed Date:
185 2/29/2024
186 Notes:
187 Search Agents:
188 raymond,burgos;
189 Aircraft Baggage / Cargo Bins11:59
190 Completed Date:
191 2/29/2024
192 Notes:
193 Search Agents:
194 raymond,burgos;
195 Record 8:
196 29-Feb-24
197 04:43
198 Print Audit
199 Station:JFK
200 A/C FAA Reg #:N111DC
201 Ship #:001011
202 Date:2024-02-29
203 Measure applied to flight: FULL
204 International Mainline Stations Only (Not DC Flights)
205 Outbound Flight Number:2556
206 Flight Origin:JFK
207 Flight Destination:MIA
208 Aircraft Interior04:43
209 Completed Date:
210 2/29/2024
211 Notes:
212 Search Agents:
213 Michelle V,Duncan; LUC BOY,CONSTANT; Kshanni,Knight; ELAINE,FRAY-BERRY; ANNMARIE,DACOSTA; PETER,MARIMBI; BONNILYN,JEFFERY; GAVIN,Williams; KATALI,WUMPINI;
214 Aircraft Exterior06:10
215 Completed Date:
216 2/29/2024
217 Notes:
218 Search Agents:
219 Nigel,Spencer;
220 Aircraft Baggage / Cargo Bins06:10
221 Completed Date:
222 2/29/2024
223 Notes:
224 Search Agents:
225 Nigel,Spencer;
226 Record 9:
227 29-Feb-24
228 07:58
229 Print Audit
230 Station:JFK
231 A/C FAA Reg #:N169DZ
232 Ship #:001601
233 Date:2024-02-29
234 Measure applied to flight: FULL
235 International Mainline Stations Only (Not DC Flights)
236 Outbound Flight Number:0253
237 Flight Origin:JFK
238 Flight Destination:BOG
239 Aircraft Interior07:57
240 Completed Date:
241 2/29/2024
242 Notes:
243 Search Agents:
244 Deloris H,Freeman; Yvonne,Griffith; ESTEFANI,Rosario; James,LEFEVRE; Sheryl,Lavia; Norma J,Joseph; SHARMILA,RAMPERSAUD; Craig,Lecky; Mark,Prescott; Prisca,Sargeant; DAWN,DAVIS;
245 Record 10:
246 29-Feb-24
247 07:45
248 Print Audit
249 Station:JFK
250 A/C FAA Reg #:N827MH
251 Ship #:001803
252 Date:2024-02-29
253 Measure applied to flight: FULL
254 International Mainline Stations Only (Not DC Flights)
255 Outbound Flight Number:0106
256 Flight Origin:JFK
257 Flight Destination:FRA
258 Aircraft Interior07:45
259 Completed Date:
260 2/29/2024
261 Notes:
262 Search Agents:
263 MALICA,CHICHESTER; Felix,Duran; ARACELY,PADILLA-RIVERA; Antoinette,Deane; Mohammed,Hossain; Robin A,RamosSr; Lilawtee,Ramsawak; DIANA PATRICIA,GARCIA RIVEROS; WAYNE,LEVIEN; RITA,THOMAS-CARBON; Bethoven,Cordero Lora ;
264 Record 11:
265 28-Feb-24
266 13:57
267 Print Audit
268 Station:JFK
269 A/C FAA Reg #:N834MH
270 Ship #:001810
271 Date:2024-02-29
272 Measure applied to flight: FULL
273 International Mainline Stations Only (Not DC Flights)
274 Outbound Flight Number:1810
275 Flight Origin:JFK
276 Flight Destination:QSM
277 Aircraft Interior13:57
278 Completed Date:
279 2/28/2024
280 Notes:
281 Search Agents:
282 Tarwattie,Jienarine; Anthony,Hunte; Martha,LOPEZ-PAZMINO; Marc,Alexander; Trevolyn,Goodluck; PAULETTE,PASSLEY; Lasakay,Peterkin; MARIE,DEJEAN DESMANGLES; KHADIJAH,Gordon; GAYLE,OMEIR; Vanessa,Saul; CLAUDINE,SPAULDING;
283 Record 12:
284 29-Feb-24
285 04:43
286 Print Audit
287 Station:JFK
288 A/C FAA Reg #:N835MH
289 Ship #:001811
290 Date:2024-02-29
291 Measure applied to flight: FULL
292 International Mainline Stations Only (Not DC Flights)
293 Outbound Flight Number:0227
294 Flight Origin:JFK
295 Flight Destination:GRU
296 Aircraft Interior00:40
297 Completed Date:
298 2/29/2024
299 Notes:
300 Search Agents:
301 NELSEY,DOMINGUEZ;CHRISPIN,CALLISTE;KIRAN,BARUA;MICKEL,SMITH;PAMELA,CHOY;ROSEMARIE,TURNER;SASHEKA,JAMES;SAYPULU,TURAY;YAKEEMA,COOTE;
Sheet1

This is the desired final ouput:



Record Number	Date	Station	Registration #	Ship #	Measure Applied to Flight	Outbound Flight	Origin	Destination	Search Location	Interior Completion Date	Interior Search Agents	Exterior Search	Exterior Completion Date	Ext Search Agents	Aircraft Baggage / Cargo Bins	Baggage Bins Completion Date	Cargo Search Agents
Record 1	2/26/24 19:11	JFK	N174DN	174	FULL	742	JFK	LAX	Aircraft Interior	2/26/2024 19:11	SHANTAL,FINDLATER; Judy,Goseine; PAULETTE,PASSLEY; Pauline,Brown; Lasakay,Peterkin; MARIE,RIVAGE; jacqueline,tapia; KHADIJAH,Gordon; DARRAINE,WIGGINS; BRIANNA,ALLEN; ERICK,AROSEMENA;
Record 2	2/27/24 04:50	JFK	N175DN	175	FULL	2170	JFK	ATL	Aircraft Interior	2/27/2024 4:50	Renee,Grant; MARGARET,HINDS; CHRIST,MCMICHAEL; Denise,Jeffers; EMMANUEL,AKILAH; ROSA,CORPORAN; JAYDEN,MCMEO; LINDBERGH,DIEUDONNE; KATALI,WUMPINI; TEHILLIAH,DAVIS;

labello27 · Feb 29, 2024

labello27 said:
This is the desired final ouput:

Record Number Date Station Registration # Ship # Measure Applied to Flight Outbound Flight Origin Destination Search Location Interior Completion Date Interior Search Agents Exterior Search Exterior Completion Date Ext Search Agents Aircraft Baggage / Cargo Bins Baggage Bins Completion Date Cargo Search Agents
Record 1 2/26/24 19:11 JFK N174DN 174 FULL 742 JFK LAX Aircraft Interior 2/26/2024 19:11 SHANTAL,FINDLATER; Judy,Goseine; PAULETTE,PASSLEY; Pauline,Brown; Lasakay,Peterkin; MARIE,RIVAGE; jacqueline,tapia; KHADIJAH,Gordon; DARRAINE,WIGGINS; BRIANNA,ALLEN; ERICK,AROSEMENA;
Record 2 2/27/24 04:50 JFK N175DN 175 FULL 2170 JFK ATL Aircraft Interior 2/27/2024 4:50 Renee,Grant; MARGARET,HINDS; CHRIST,MCMICHAEL; Denise,Jeffers; EMMANUEL,AKILAH; ROSA,CORPORAN; JAYDEN,MCMEO; LINDBERGH,DIEUDONNE; KATALI,WUMPINI; TEHILLIAH,DAVIS;

This the data in a table format:



COL1
Record 1:
26-Feb-24
19:11
Print Audit
Station:JFK
A/C FAA Reg #:N174DN
Ship #:000174
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:0742
Flight Origin:JFK
Flight Destination:LAX
Aircraft Interior19:11
Completed Date:
2/26/2024
Notes:
Search Agents:
SHANTAL,FINDLATER; Judy,Goseine; PAULETTE,PASSLEY; Pauline,Brown; Lasakay,Peterkin; MARIE,RIVAGE; jacqueline,tapia; KHADIJAH,Gordon; DARRAINE,WIGGINS; BRIANNA,ALLEN; ERICK,AROSEMENA;
Record 2:
27-Feb-24
04:50
Print Audit
Station:JFK
A/C FAA Reg #:N175DN
Ship #:000175
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:2170
Flight Origin:JFK
Flight Destination:ATL
Aircraft Interior04:50
Completed Date:
2/27/2024
Notes:
Search Agents:
Renee,Grant; MARGARET,HINDS; CHRIST,MCMICHAEL; Denise,Jeffers; EMMANUEL,AKILAH; ROSA,CORPORAN; JAYDEN,MCMEO; LINDBERGH,DIEUDONNE; KATALI,WUMPINI; TEHILLIAH,DAVIS;
Record 3:
27-Feb-24
04:32
Print Audit
Station:JFK
A/C FAA Reg #:N676DL
Ship #:000676
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:1955
Flight Origin:JFK
Flight Destination:MBJ
Aircraft Interior04:32
Completed Date:
2/27/2024
Notes:
Search Agents:
CARLA,Rivera; MAURICE,DADIE; Beverley,Nedd; Claudeth,Williams; MELISSA,BISHOP; GEURY,ESPINAL VARGAS; TERI,Smith; FELIX,HOLNESS; Mimose,Lamothe; ANIYA,GOFFE;
Record 4:
27-Feb-24
00:29
Print Audit
Station:JFK
A/C FAA Reg #:N687DL
Ship #:000687
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:1887
Flight Origin:JFK
Flight Destination:SXM
Aircraft Interior00:29
Completed Date:
2/27/2024
Notes:
Search Agents:
CARLA,Rivera; MAURICE,DADIE; Beverley,Nedd; Claudeth,Williams; MELISSA,BISHOP; GEURY,ESPINAL VARGAS; TERI,Smith; FELIX,HOLNESS; Mimose,Lamothe; ANIYA,GOFFE;
Record 5:
27-Feb-24
05:05
Print Audit
Station:JFK
A/C FAA Reg #:N103DY
Ship #:001003
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:2587
Flight Origin:JFK
Flight Destination:MCO
Aircraft Interior05:05
Completed Date:
2/27/2024
Notes:
Search Agents:
Renee,Grant; MARGARET,HINDS; CHRIST,MCMICHAEL; Denise,Jeffers; EMMANUEL,AKILAH; ROSA,CORPORAN; JAYDEN,MCMEO; LINDBERGH,DIEUDONNE; KATALI,WUMPINI; TEHILLIAH,DAVIS;
Record 6:
27-Feb-24
02:20
Print Audit
Station:JFK
A/C FAA Reg #:N121DZ
Ship #:001021
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:1421
Flight Origin:MCO
Flight Destination:FLL
Aircraft Interior02:16
Completed Date:
2/27/2024
Notes:
Search Agents:
Michelle ,Duncan ;Boyce ,Dnell ;Nichlas ,Lyzale ;Constant ,LucBoy ;Barnwell ,Nesha ;Elaine ,Frayberry ;Sofleigh,Keyshawn ;Padilla ,Erika ;Kshanni ,Knight ;Sammerson,Yvette ;Rose ,Davion ;
Record 7:
2/27/2024
01:15
Print Audit
Station:JFK
A/C FAA Reg #:N174DZ
Ship #:001704
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:0567
Flight Origin:JFK
Flight Destination:LAX
Aircraft Interior01:15
Completed Date:
2/27/2024
Notes:
Search Agents:
RAMONA,HARBAJAN; Jason,Forde; NATAVIA,JONES; PHABIAN,Robert; JOSHUA,BLACKWELL; GENEIL,ROWE; ELENA ,CYRILLE; Alyssa,Charles Sidberry; RENEE,WILLIAMS; TRAVIS,LATTY;
Record 8:
2/26/2024
00:57
Print Audit
Station:JFK
A/C FAA Reg #:N833MH
Ship #:001809
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:0106
Flight Origin:JFK
Flight Destination:HNL
Aircraft Interior14:54
Completed Date:
2/26/2024
Notes:
Search Agents:
OMAR,KOONCE;ADRIAN,RDUHANEY;AKINBOLA,SOWOLE;ANA,CONTRERAS;DANIELA,CASTRO;EMANUEL,DELACRUZ;ESTHER,PERSAUD;KESIA,THOMAS;RITA,THOMASCARBON;TANVIR,CHOWDHURY;YOLANDA,NARANJOORMAZA;
Record 9:
2/27/2024
00:18
Print Audit
Station:JFK
A/C FAA Reg #:N340DN
Ship #:003040
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:2581
Flight Origin:MIA
Flight Destination:MIA
Aircraft Interior00:14
Completed Date:
2/27/2024
Notes:
Search Agents:
Michelle ,Duncan ;Boyce,D'Nell;Nichlas ,Lyzale ;Constant ,LucBoy ;Barnwell ,Nesha ;Elaine ,Frayberry ;Softleigh,Keyshawn ;Padilla ,Erika ;Kshanni ,Knight ;Sammerson,Yvette ;Rose,Devion;
Record 10:
26-Feb-24
22:54
Print Audit
Station:JFK
A/C FAA Reg #:N363DN
Ship #:003063
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:2567
Flight Origin:JFK
Flight Destination:MIA
Aircraft Interior22:54
Completed Date:
2/26/2024
Notes:
Search Agents:
RAMONA,HARBAJAN; Jason,Forde; NATAVIA,JONES; PHABIAN,Robert; JOSHUA,BLACKWELL; GENEIL,ROWE; ELENA ,CYRILLE; Alyssa,Charles Sidberry; RENEE,WILLIAMS; TRAVIS,LATTY;
Record 11:
27-Feb-24
04:47
Print Audit
Station:JFK
A/C FAA Reg #:N341NB
Ship #:003141
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:2328
Flight Origin:JFK
Flight Destination:MSY
Aircraft Interior04:47
Completed Date:
2/27/2024
Notes:
Search Agents:
Pamela A,Baldeo; LUCINDA,NAUPARI; RASHID,RASHID; MARVIN,PALMER; KIMBERLY,SKINNER; JOHNNY,MONROE; DESTINY,TURNER; Jamani,Barnell; RALPH ,JEAN; STAR,WILSON;
Record 12:
27-Feb-24
00:31
Print Audit
Station:JFK
A/C FAA Reg #:N354NB
Ship #:003154
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:2227
Flight Origin:JFK
Flight Destination:ATL
Aircraft Interior00:30
Completed Date:
2/27/2024
Notes:
Search Agents:
Renee,Grant; MARGARET,HINDS; CHRIST,MCMICHAEL; Denise,Jeffers; EMMANUEL,AKILAH; ROSA,CORPORAN; JAYDEN,MCMEO; LINDBERGH,DIEUDONNE; KATALI,WUMPINI; RAVINA,GHISIAWAN; TEHILLIAH,DAVIS;
Record 13:
27-Feb-24
04:29
Print Audit
Station:JFK
A/C FAA Reg #:N407DX
Ship #:003407
Date:2024-02-27
Measure applied to flight: FULL
International Mainline Stations Only (Not DC Flights)
Outbound Flight Number:0917
Flight Origin:JFK
Flight Destination:SLC
Aircraft Interior04:29
Completed Date:
2/27/2024
Notes:
Search Agents:
RAMONA,HARBAJAN; Jason,Forde; NATAVIA,JONES; PHABIAN,Robert; JOSHUA,BLACKWELL; GENEIL,ROWE; ELENA ,CYRILLE; Alyssa,Charles Sidberry; RENEE,WILLIAMS; TRAVIS,LATTY;

JEC · Feb 29, 2024

Here a solution, at the end you need some more steps to convert the date columns to real dates...

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cType = Table.TransformColumnTypes(Source,{{"COL1", type text}}),
    excludes = {1,1,1,0,1,1,1,0,1,0,1,1,1,1,0,1,0,0,1,1,0,1,0,0,1,1,0,1,0,0,1},
    cols = {"Record Number","Date","Time","Station","Registration","Ship","Measure Applied to Flight","Outbound Flight","Origin","Destination","Search Location","Interior Completion Date","Interior Search Agents","Exterior Search","Exterior Completion Date","Ext Search Agents","Aircraft Baggage / Cargo Bins","Baggage Bins Completion Date","Cargo Search Agents"},
    nCol = Table.AddColumn(cType, "Custom", each if Text.Contains([COL1],"Record") then [COL1] else null),
    fill = Table.FillDown(nCol,{"Custom"}),
    grp = Table.Group(fill, {"Custom"}, {{"Count", each List.Zip({excludes, _[COL1],_[Custom]})}}),
    expand = Table.ExpandListColumn(grp, "Count"),
    getTable = Table.FromRows( expand[Count]),
    fltrHeaders = Table.SelectRows(getTable, each ([Column1] = 1)),
    grp2 = Table.Group(fltrHeaders, {"Column3"}, {{"Count", each List.Zip({cols,_[Column2],_[Column3]}) }}),
    delNulls = Table.SelectRows(grp2, each ([Column3] <> null)),
    expand2 = Table.ExpandListColumn(delNulls, "Count"),
    getTable2 = Table.FromRows(expand2[Count]),
    repNulls = Table.ReplaceValue(getTable2,null,"",Replacer.ReplaceValue,{"Column2"}),
    splitVals = Table.AddColumn(repNulls, "Custom", each if Text.StartsWith([Column2],"Record") then [Column2] else try Text.Trim(Text.Split([Column2],":"){1}) otherwise [Column2]),
    delCol = Table.RemoveColumns(splitVals,{"Column2"}),
    grp3 = Table.Group(delCol, {"Column3"}, {{"Count", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(_,{"Column1","Custom"}))) }}),
    delNulls2 = Table.SelectRows(grp3, each ([Column3] <> null)),
    result = Table.Combine(delNulls2[Count])
in
    result

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

Record Number

Date

Time

Station

Registration

Ship

Measure Applied to Flight

Outbound Flight

Origin

Destination

Search Location

Interior Completion Date

Interior Search Agents

Exterior Search

Exterior Completion Date

Ext Search Agents

Aircraft Baggage / Cargo Bins

Baggage Bins Completion Date

Cargo Search Agents

2

Record 1:

45351

0.0854166666666667

JFK

N174DN

000174

FULL

0742

JFK

LAX

03

45351

BIANCA,YOUNG DE DE LEON; GAIL,GUY JONES; GEURY,ESPINAL VARGAS; YAHAIRA,FERNANDEZ; YANDEL,VEGA; SETH,PLAZA; TAYLOR,TROYE; Jamani,Barnell; JENNA,HAMILTON;

10

45351

kino,galbraith;

09

45351

kino,galbraith;

3

Record 2:

45350

0.902777777777778

JFK

N177DN

000177

FULL

0926

JFK

LAX

43

45350

WATSON,VAUGHN; DANESHA,SIMMS; STEFFAN,DASRAT; YAIROL,Matos; antoine,davis; ZONA,LUGO; FRANCINSCO,Rivera; JENNIEL,Joseph; Francia,Tejedia; ENCARNACION LISSETT,VILLAR; Jean,Festa; Jeffrey,Bastidas; KASHEKA,MESSAM; TYSHAWN,PAYNE; JENNI FLORE,THOMAS; Devon,Parbat;TIANNA,MAYERS;Evans,Salomon;Bastidas,Jeffery ;

4

Record 3:

45351

0.580555555555556

JFK

N189DN

000189

FULL

2170

JFK

ATL

56

45351

ALVES,ABRIANA; Vilma,LOPEZ PAZMINO; Antoinette,Harding; Kelvin,Mcpherson; Estela,Nicolas; DARLINE,CONTENT; Stacey,Brutus; TERESA,BETEMIT DE JIMENEZ; JOMARIE ,PAMPILO; ANGEL,SANCHEZ;Basdaye,Laltoo;Edson,Mora;Eugenie,Auguste;FARANIAINA,ANDRIANASOLO;JORGE,SARMIENTO-ARCE;Judy,Blair;LINDA,ROWE;MASHERA,MITCHELL;Mohammed,Amin;OLIVER,TORRESGUZMAN;PARBATTIE,ROOPCHAN;Ramona,Thakurdyal;ROSA,Rodriguez;

5

Record 4:

45350

0.865972222222222

JFK

N190DN

000190

FULL

0567

JFK

LAX

47

45350

AQUILLA,SYKES; GLENDON,COSBERT; Chelsea,Marshall; RAYSHAN,RAMSEY; Tim,CHARIAH; Thomas,Smith Sam; SONJI,RANDALL; Michael,Tannis; KEIDON,RICHENS; JOHANNA,SUE;

48

45351

timothy,rodney;

48

45351

timothy,rodney;

6

Record 5:

45351

0.313888888888889

JFK

N194DN

000194

FULL

0773

JFK

LAX

32

45351

SHELLYANN,GUTHRIE-MONTAQUE; JUANITA,ABREU; Julie,Cole; Deidre,Anderson; Nicole R,Sullivan; HILBERTO,USHER HOLMES; NATALIE,TABLADA; DORCIA,Williams; NELSON,RAYMOND; SHARLINE,WILLIAMS; KESIA,THOMAS;

10

45351

Kwame,Fredericks;

05

45351

Kwame,Fredericks;

7

Record 6:

45350

0.890277777777778

JFK

N693DL

000693

FULL

1887

JFK

SXM

22

45350

ARBELINA,HUNT; Avendra,Jienarine; Tianna,Thomas; LAWRENCE,SUMMER JR; Brian,Fernandez; PHILIP,DUNCAN; HASAN ,RAQUIBUL; OLIVER ,OSSE KARL ;

26

45351

mario,johnson;

26

45351

mario,johnson;

8

Record 7:

45350

0.840277777777778

JFK

N102DN

001002

FULL

2383

JFK

RSW

10

45350

SAVITA,RAMBARAN; DALLISCA,massiah; Selencia,Tannis; ALSOPP,COLIN; DAMION,CLARKE; CLIVE,ROBINSON; SHONETTE,SCHWARTZ; SHEIK,RASHED;

59

45351

raymond,burgos;

59

45351

raymond,burgos;

9

Record 8:

45351

0.196527777777778

JFK

N111DC

001011

FULL

2556

JFK

MIA

43

45351

Michelle V,Duncan; LUC BOY,CONSTANT; Kshanni,Knight; ELAINE,FRAY-BERRY; ANNMARIE,DACOSTA; PETER,MARIMBI; BONNILYN,JEFFERY; GAVIN,Williams; KATALI,WUMPINI;

10

45351

Nigel,Spencer;

10

45351

Nigel,Spencer;

10

Record 9:

45351

0.331944444444444

JFK

N169DZ

001601

FULL

0253

JFK

BOG

57

45351

Deloris H,Freeman; Yvonne,Griffith; ESTEFANI,Rosario; James,LEFEVRE; Sheryl,Lavia; Norma J,Joseph; SHARMILA,RAMPERSAUD; Craig,Lecky; Mark,Prescott; Prisca,Sargeant; DAWN,DAVIS;

11

Record 10:

45351

0.322916666666667

JFK

N827MH

001803

FULL

0106

JFK

FRA

45

45351

MALICA,CHICHESTER; Felix,Duran; ARACELY,PADILLA-RIVERA; Antoinette,Deane; Mohammed,Hossain; Robin A,RamosSr; Lilawtee,Ramsawak; DIANA PATRICIA,GARCIA RIVEROS; WAYNE,LEVIEN; RITA,THOMAS-CARBON; Bethoven,Cordero Lora ;

12

Record 11:

45350

0.58125

JFK

N834MH

001810

FULL

1810

JFK

QSM

57

45350

Tarwattie,Jienarine; Anthony,Hunte; Martha,LOPEZ-PAZMINO; Marc,Alexander; Trevolyn,Goodluck; PAULETTE,PASSLEY; Lasakay,Peterkin; MARIE,DEJEAN DESMANGLES; KHADIJAH,Gordon; GAYLE,OMEIR; Vanessa,Saul; CLAUDINE,SPAULDING;

13

Record 12:

45351

0.196527777777778

JFK

N835MH

001811

FULL

0227

JFK

GRU

40

45351

NELSEY,DOMINGUEZ;CHRISPIN,CALLISTE;KIRAN,BARUA;MICKEL,SMITH;PAMELA,CHOY;ROSEMARIE,TURNER;SASHEKA,JAMES;SAYPULU,TURAY;YAKEEMA,COOTE;

Table2 (2)

Ken Puls · Feb 29, 2024

Nicely done JEC.

JEC · Mar 1, 2024

Thanks Ken!

labello27 · Mar 1, 2024

JEC said:
Here a solution, at the end you need some more steps to convert the date columns to real dates...

Power Query:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], cType = Table.TransformColumnTypes(Source,{{"COL1", type text}}), excludes = {1,1,1,0,1,1,1,0,1,0,1,1,1,1,0,1,0,0,1,1,0,1,0,0,1,1,0,1,0,0,1}, cols = {"Record Number","Date","Time","Station","Registration","Ship","Measure Applied to Flight","Outbound Flight","Origin","Destination","Search Location","Interior Completion Date","Interior Search Agents","Exterior Search","Exterior Completion Date","Ext Search Agents","Aircraft Baggage / Cargo Bins","Baggage Bins Completion Date","Cargo Search Agents"}, nCol = Table.AddColumn(cType, "Custom", each if Text.Contains([COL1],"Record") then [COL1] else null), fill = Table.FillDown(nCol,{"Custom"}), grp = Table.Group(fill, {"Custom"}, {{"Count", each List.Zip({excludes, _[COL1],_[Custom]})}}), expand = Table.ExpandListColumn(grp, "Count"), getTable = Table.FromRows( expand[Count]), fltrHeaders = Table.SelectRows(getTable, each ([Column1] = 1)), grp2 = Table.Group(fltrHeaders, {"Column3"}, {{"Count", each List.Zip({cols,_[Column2],_[Column3]}) }}), delNulls = Table.SelectRows(grp2, each ([Column3] <> null)), expand2 = Table.ExpandListColumn(delNulls, "Count"), getTable2 = Table.FromRows(expand2[Count]), repNulls = Table.ReplaceValue(getTable2,null,"",Replacer.ReplaceValue,{"Column2"}), splitVals = Table.AddColumn(repNulls, "Custom", each if Text.StartsWith([Column2],"Record") then [Column2] else try Text.Trim(Text.Split([Column2],":"){1}) otherwise [Column2]), delCol = Table.RemoveColumns(splitVals,{"Column2"}), grp3 = Table.Group(delCol, {"Column3"}, {{"Count", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(_,{"Column1","Custom"}))) }}), delNulls2 = Table.SelectRows(grp3, each ([Column3] <> null)), result = Table.Combine(delNulls2[Count]) in result

Book1
A B C D E F G H I J K L M N O P Q R S
1 Record Number Date Time Station Registration Ship Measure Applied to Flight Outbound Flight Origin Destination Search Location Interior Completion Date Interior Search Agents Exterior Search Exterior Completion Date Ext Search Agents Aircraft Baggage / Cargo Bins Baggage Bins Completion Date Cargo Search Agents
2 Record 1: 45351 0.0854166666666667 JFK N174DN 000174 FULL 0742 JFK LAX 03 45351 BIANCA,YOUNG DE DE LEON; GAIL,GUY JONES; GEURY,ESPINAL VARGAS; YAHAIRA,FERNANDEZ; YANDEL,VEGA; SETH,PLAZA; TAYLOR,TROYE; Jamani,Barnell; JENNA,HAMILTON; 10 45351 kino,galbraith; 09 45351 kino,galbraith;
3 Record 2: 45350 0.902777777777778 JFK N177DN 000177 FULL 0926 JFK LAX 43 45350 WATSON,VAUGHN; DANESHA,SIMMS; STEFFAN,DASRAT; YAIROL,Matos; antoine,davis; ZONA,LUGO; FRANCINSCO,Rivera; JENNIEL,Joseph; Francia,Tejedia; ENCARNACION LISSETT,VILLAR; Jean,Festa; Jeffrey,Bastidas; KASHEKA,MESSAM; TYSHAWN,PAYNE; JENNI FLORE,THOMAS; Devon,Parbat;TIANNA,MAYERS;Evans,Salomon;Bastidas,Jeffery ;
4 Record 3: 45351 0.580555555555556 JFK N189DN 000189 FULL 2170 JFK ATL 56 45351 ALVES,ABRIANA; Vilma,LOPEZ PAZMINO; Antoinette,Harding; Kelvin,Mcpherson; Estela,Nicolas; DARLINE,CONTENT; Stacey,Brutus; TERESA,BETEMIT DE JIMENEZ; JOMARIE ,PAMPILO; ANGEL,SANCHEZ;Basdaye,Laltoo;Edson,Mora;Eugenie,Auguste;FARANIAINA,ANDRIANASOLO;JORGE,SARMIENTO-ARCE;Judy,Blair;LINDA,ROWE;MASHERA,MITCHELL;Mohammed,Amin;OLIVER,TORRESGUZMAN;PARBATTIE,ROOPCHAN;Ramona,Thakurdyal;ROSA,Rodriguez;
5 Record 4: 45350 0.865972222222222 JFK N190DN 000190 FULL 0567 JFK LAX 47 45350 AQUILLA,SYKES; GLENDON,COSBERT; Chelsea,Marshall; RAYSHAN,RAMSEY; Tim,CHARIAH; Thomas,Smith Sam; SONJI,RANDALL; Michael,Tannis; KEIDON,RICHENS; JOHANNA,SUE; 48 45351 timothy,rodney; 48 45351 timothy,rodney;
6 Record 5: 45351 0.313888888888889 JFK N194DN 000194 FULL 0773 JFK LAX 32 45351 SHELLYANN,GUTHRIE-MONTAQUE; JUANITA,ABREU; Julie,Cole; Deidre,Anderson; Nicole R,Sullivan; HILBERTO,USHER HOLMES; NATALIE,TABLADA; DORCIA,Williams; NELSON,RAYMOND; SHARLINE,WILLIAMS; KESIA,THOMAS; 10 45351 Kwame,Fredericks; 05 45351 Kwame,Fredericks;
7 Record 6: 45350 0.890277777777778 JFK N693DL 000693 FULL 1887 JFK SXM 22 45350 ARBELINA,HUNT; Avendra,Jienarine; Tianna,Thomas; LAWRENCE,SUMMER JR; Brian,Fernandez; PHILIP,DUNCAN; HASAN ,RAQUIBUL; OLIVER ,OSSE KARL ; 26 45351 mario,johnson; 26 45351 mario,johnson;
8 Record 7: 45350 0.840277777777778 JFK N102DN 001002 FULL 2383 JFK RSW 10 45350 SAVITA,RAMBARAN; DALLISCA,massiah; Selencia,Tannis; ALSOPP,COLIN; DAMION,CLARKE; CLIVE,ROBINSON; SHONETTE,SCHWARTZ; SHEIK,RASHED; 59 45351 raymond,burgos; 59 45351 raymond,burgos;
9 Record 8: 45351 0.196527777777778 JFK N111DC 001011 FULL 2556 JFK MIA 43 45351 Michelle V,Duncan; LUC BOY,CONSTANT; Kshanni,Knight; ELAINE,FRAY-BERRY; ANNMARIE,DACOSTA; PETER,MARIMBI; BONNILYN,JEFFERY; GAVIN,Williams; KATALI,WUMPINI; 10 45351 Nigel,Spencer; 10 45351 Nigel,Spencer;
10 Record 9: 45351 0.331944444444444 JFK N169DZ 001601 FULL 0253 JFK BOG 57 45351 Deloris H,Freeman; Yvonne,Griffith; ESTEFANI,Rosario; James,LEFEVRE; Sheryl,Lavia; Norma J,Joseph; SHARMILA,RAMPERSAUD; Craig,Lecky; Mark,Prescott; Prisca,Sargeant; DAWN,DAVIS;
11 Record 10: 45351 0.322916666666667 JFK N827MH 001803 FULL 0106 JFK FRA 45 45351 MALICA,CHICHESTER; Felix,Duran; ARACELY,PADILLA-RIVERA; Antoinette,Deane; Mohammed,Hossain; Robin A,RamosSr; Lilawtee,Ramsawak; DIANA PATRICIA,GARCIA RIVEROS; WAYNE,LEVIEN; RITA,THOMAS-CARBON; Bethoven,Cordero Lora ;
12 Record 11: 45350 0.58125 JFK N834MH 001810 FULL 1810 JFK QSM 57 45350 Tarwattie,Jienarine; Anthony,Hunte; Martha,LOPEZ-PAZMINO; Marc,Alexander; Trevolyn,Goodluck; PAULETTE,PASSLEY; Lasakay,Peterkin; MARIE,DEJEAN DESMANGLES; KHADIJAH,Gordon; GAYLE,OMEIR; Vanessa,Saul; CLAUDINE,SPAULDING;
13 Record 12: 45351 0.196527777777778 JFK N835MH 001811 FULL 0227 JFK GRU 40 45351 NELSEY,DOMINGUEZ;CHRISPIN,CALLISTE;KIRAN,BARUA;MICKEL,SMITH;PAMELA,CHOY;ROSEMARIE,TURNER;SASHEKA,JAMES;SAYPULU,TURAY;YAKEEMA,COOTE;
Table2 (2)

Thank you so much!

Help win unpivot super slow query

labello27

New Member

labello27

New Member

Excel Facts

labello27

New Member

JEC

Well-known Member

Ken Puls

Active Member

JEC

Well-known Member

labello27

New Member

Similar threads

Forum statistics

Share this page

Help win unpivot super slow query

labello27

New Member

labello27

New Member

Excel Facts

labello27

New Member

JEC

Well-known Member

Ken Puls

Active Member

JEC

Well-known Member

labello27

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock