Help win unpivot super slow query

L

labello27

New Member
Joined
Feb 2, 2009
Messages
26
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,
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
You say you’re trying to unpivot columns, but I don’t see any unpivot steps and it’s not clear which columns you are trying to unpivot. Can you post a sample workbook?
 
Upvote 0
Hi Matt,

Thanks for responding. I attached screen shot of the table with just one column with all the data. The records are separated by the word "Record" space, number and then colon.
 

Attachments

  • Capture.JPG
    Capture.JPG
    163.1 KB · Views: 16
Upvote 0
We can't help with only a picture here... but to give you a push in the right direction:
You can add more text.contains to filter your desired columns.

PS: I would rather look at how your data is coming out of the source system...this is a mess

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    nCol = Table.AddColumn(Source, "Custom", each if Text.Contains([COL1],"Record") then [COL1] else null),
    fill = Table.FillDown(nCol,{"Custom"}),
    grp = Table.Group(fill, {"Custom"},
        {
            {"tbls", each List.Select(_[COL1], (x)=>
                    Text.Contains(x,"date") or
                    Text.Contains(x, "station") or
                    Text.Contains(x,"blabla")
                   )
            }
        }
    )
in
    grp
 
Upvote 0
I don't normally do this, but here we go. Ken covers this in our Power Query Academy online training course available at Skillwave Training

Here is a link to the video from that course. Private video on Vimeo

I will probably disable the link sometime, so please take a look ASAP

The password is MrExcel case sensitive.
Rich (BB code):
 
Upvote 0
Thanks Matt, that was helpful! My original data column does not have a nice pattern in the same number of rows. Every new record does start with the word “Record”, though and every record could have different number of rows.
 
Upvote 0
"My original data column does not have a nice pattern in the same number of rows."

I only counted off the rows in the image, but from the rows you shared, it seems to restart every 19 rows? Or does that vary further down? If I could get my hands on a sample of the data (rather than an image) that contains the issue, I could help you work though it...
 
Upvote 0
Ken Puls said:
"My original data column does not have a nice pattern in the same number of rows."

I only counted off the rows in the image, but from the rows you shared, it seems to restart every 19 rows? Or does that vary further down? If I could get my hands on a sample of the data (rather than an image) that contains the issue, I could help you work though it...
Click to expand...

Hi, I was able to get the raw data below


Book1
B
2Record 1:
329-Feb-24
402:03
5Print Audit
6Station:JFK
7A/C FAA Reg #:N174DN
8Ship #:000174
9Date:2024-02-29
10Measure applied to flight: FULL
11International Mainline Stations Only (Not DC Flights)
12Outbound Flight Number:0742
13Flight Origin:JFK
14Flight Destination:LAX
15Aircraft Interior02:03
16Completed Date:
172/29/2024
18Notes:
19Search Agents:
20BIANCA,YOUNG DE DE LEON; GAIL,GUY JONES; GEURY,ESPINAL VARGAS; YAHAIRA,FERNANDEZ; YANDEL,VEGA; SETH,PLAZA; TAYLOR,TROYE; Jamani,Barnell; JENNA,HAMILTON;
21Aircraft Exterior06:10
22Completed Date:
232/29/2024
24Notes:
25Search Agents:
26kino,galbraith;
27Aircraft Baggage / Cargo Bins06:09
28Completed Date:
292/29/2024
30Notes:
31Search Agents:
32kino,galbraith;
33Record 2:
3428-Feb-24
3521:40
36Print Audit
37Station:JFK
38A/C FAA Reg #:N177DN
39Ship #:000177
40Date:2024-02-29
41Measure applied to flight: FULL
42International Mainline Stations Only (Not DC Flights)
43Outbound Flight Number:0926
44Flight Origin:JFK
45Flight Destination:LAX
46Aircraft Interior21:43
47Completed Date:
482/28/2024
49Notes:
50Search Agents:
51WATSON,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 ;
52Record 3:
5329-Feb-24
5413:56
55Print Audit
56Station:JFK
57A/C FAA Reg #:N189DN
58Ship #:000189
59Date:2024-02-29
60Measure applied to flight: FULL
61International Mainline Stations Only (Not DC Flights)
62Outbound Flight Number:2170
63Flight Origin:JFK
64Flight Destination:ATL
65Aircraft Interior13:56
66Completed Date:
672/29/2024
68Notes:
69Search Agents:
70ALVES,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;
71Record 4:
7228-Feb-24
7320:47
74Print Audit
75Station:JFK
76A/C FAA Reg #:N190DN
77Ship #:000190
78Date:2024-02-29
79Measure applied to flight: FULL
80International Mainline Stations Only (Not DC Flights)
81Outbound Flight Number:0567
82Flight Origin:JFK
83Flight Destination:LAX
84Aircraft Interior20:47
85Completed Date:
862/28/2024
87Notes:
88Search Agents:
89AQUILLA,SYKES; GLENDON,COSBERT; Chelsea,Marshall; RAYSHAN,RAMSEY; Tim,CHARIAH; Thomas,Smith Sam; SONJI,RANDALL; Michael,Tannis; KEIDON,RICHENS; JOHANNA,SUE;
90Aircraft Exterior07:48
91Completed Date:
922/29/2024
93Notes:
94Search Agents:
95timothy,rodney;
96Aircraft Baggage / Cargo Bins07:48
97Completed Date:
982/29/2024
99Notes:
100Search Agents:
101timothy,rodney;
102Record 5:
10329-Feb-24
10407:32
105Print Audit
106Station:JFK
107A/C FAA Reg #:N194DN
108Ship #:000194
109Date:2024-02-29
110Measure applied to flight: FULL
111International Mainline Stations Only (Not DC Flights)
112Outbound Flight Number:0773
113Flight Origin:JFK
114Flight Destination:LAX
115Aircraft Interior07:32
116Completed Date:
1172/29/2024
118Notes:
119Search Agents:
120SHELLYANN,GUTHRIE-MONTAQUE; JUANITA,ABREU; Julie,Cole; Deidre,Anderson; Nicole R,Sullivan; HILBERTO,USHER HOLMES; NATALIE,TABLADA; DORCIA,Williams; NELSON,RAYMOND; SHARLINE,WILLIAMS; KESIA,THOMAS;
121Aircraft Exterior09:10
122Completed Date:
1232/29/2024
124Notes:
125Search Agents:
126Kwame,Fredericks;
127Aircraft Baggage / Cargo Bins09:05
128Completed Date:
1292/29/2024
130Notes:
131Search Agents:
132Kwame,Fredericks;
133Record 6:
13428-Feb-24
13521:22
136Print Audit
137Station:JFK
138A/C FAA Reg #:N693DL
139Ship #:000693
140Date:2024-02-29
141Measure applied to flight: FULL
142International Mainline Stations Only (Not DC Flights)
143Outbound Flight Number:1887
144Flight Origin:JFK
145Flight Destination:SXM
146Aircraft Interior21:22
147Completed Date:
1482/28/2024
149Notes:
150Search Agents:
151ARBELINA,HUNT; Avendra,Jienarine; Tianna,Thomas; LAWRENCE,SUMMER JR; Brian,Fernandez; PHILIP,DUNCAN; HASAN ,RAQUIBUL; OLIVER ,OSSE KARL ;
152Aircraft Exterior08:26
153Completed Date:
1542/29/2024
155Notes:
156Search Agents:
157mario,johnson;
158Aircraft Baggage / Cargo Bins08:26
159Completed Date:
1602/29/2024
161Notes:
162Search Agents:
163mario,johnson;
164Record 7:
16528-Feb-24
16620:10
167Print Audit
168Station:JFK
169A/C FAA Reg #:N102DN
170Ship #:001002
171Date:2024-02-29
172Measure applied to flight: FULL
173International Mainline Stations Only (Not DC Flights)
174Outbound Flight Number:2383
175Flight Origin:JFK
176Flight Destination:RSW
177Aircraft Interior20:10
178Completed Date:
1792/28/2024
180Notes:
181Search Agents:
182SAVITA,RAMBARAN; DALLISCA,massiah; Selencia,Tannis; ALSOPP,COLIN; DAMION,CLARKE; CLIVE,ROBINSON; SHONETTE,SCHWARTZ; SHEIK,RASHED;
183Aircraft Exterior11:59
184Completed Date:
1852/29/2024
186Notes:
187Search Agents:
188raymond,burgos;
189Aircraft Baggage / Cargo Bins11:59
190Completed Date:
1912/29/2024
192Notes:
193Search Agents:
194raymond,burgos;
195Record 8:
19629-Feb-24
19704:43
198Print Audit
199Station:JFK
200A/C FAA Reg #:N111DC
201Ship #:001011
202Date:2024-02-29
203Measure applied to flight: FULL
204International Mainline Stations Only (Not DC Flights)
205Outbound Flight Number:2556
206Flight Origin:JFK
207Flight Destination:MIA
208Aircraft Interior04:43
209Completed Date:
2102/29/2024
211Notes:
212Search Agents:
213Michelle V,Duncan; LUC BOY,CONSTANT; Kshanni,Knight; ELAINE,FRAY-BERRY; ANNMARIE,DACOSTA; PETER,MARIMBI; BONNILYN,JEFFERY; GAVIN,Williams; KATALI,WUMPINI;
214Aircraft Exterior06:10
215Completed Date:
2162/29/2024
217Notes:
218Search Agents:
219Nigel,Spencer;
220Aircraft Baggage / Cargo Bins06:10
221Completed Date:
2222/29/2024
223Notes:
224Search Agents:
225Nigel,Spencer;
226Record 9:
22729-Feb-24
22807:58
229Print Audit
230Station:JFK
231A/C FAA Reg #:N169DZ
232Ship #:001601
233Date:2024-02-29
234Measure applied to flight: FULL
235International Mainline Stations Only (Not DC Flights)
236Outbound Flight Number:0253
237Flight Origin:JFK
238Flight Destination:BOG
239Aircraft Interior07:57
240Completed Date:
2412/29/2024
242Notes:
243Search Agents:
244Deloris H,Freeman; Yvonne,Griffith; ESTEFANI,Rosario; James,LEFEVRE; Sheryl,Lavia; Norma J,Joseph; SHARMILA,RAMPERSAUD; Craig,Lecky; Mark,Prescott; Prisca,Sargeant; DAWN,DAVIS;
245Record 10:
24629-Feb-24
24707:45
248Print Audit
249Station:JFK
250A/C FAA Reg #:N827MH
251Ship #:001803
252Date:2024-02-29
253Measure applied to flight: FULL
254International Mainline Stations Only (Not DC Flights)
255Outbound Flight Number:0106
256Flight Origin:JFK
257Flight Destination:FRA
258Aircraft Interior07:45
259Completed Date:
2602/29/2024
261Notes:
262Search Agents:
263MALICA,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 ;
264Record 11:
26528-Feb-24
26613:57
267Print Audit
268Station:JFK
269A/C FAA Reg #:N834MH
270Ship #:001810
271Date:2024-02-29
272Measure applied to flight: FULL
273International Mainline Stations Only (Not DC Flights)
274Outbound Flight Number:1810
275Flight Origin:JFK
276Flight Destination:QSM
277Aircraft Interior13:57
278Completed Date:
2792/28/2024
280Notes:
281Search Agents:
282Tarwattie,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;
283Record 12:
28429-Feb-24
28504:43
286Print Audit
287Station:JFK
288A/C FAA Reg #:N835MH
289Ship #:001811
290Date:2024-02-29
291Measure applied to flight: FULL
292International Mainline Stations Only (Not DC Flights)
293Outbound Flight Number:0227
294Flight Origin:JFK
295Flight Destination:GRU
296Aircraft Interior00:40
297Completed Date:
2982/29/2024
299Notes:
300Search Agents:
301NELSEY,DOMINGUEZ;CHRISPIN,CALLISTE;KIRAN,BARUA;MICKEL,SMITH;PAMELA,CHOY;ROSEMARIE,TURNER;SASHEKA,JAMES;SAYPULU,TURAY;YAKEEMA,COOTE;
Sheet1
 
Upvote 0
JEC said:
We can't help with only a picture here... but to give you a push in the right direction:
You can add more text.contains to filter your desired columns.

PS: I would rather look at how your data is coming out of the source system...this is a mess

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    nCol = Table.AddColumn(Source, "Custom", each if Text.Contains([COL1],"Record") then [COL1] else null),
    fill = Table.FillDown(nCol,{"Custom"}),
    grp = Table.Group(fill, {"Custom"},
        {
            {"tbls", each List.Select(_[COL1], (x)=>
                    Text.Contains(x,"date") or
                    Text.Contains(x, "station") or
                    Text.Contains(x,"blabla")
                   )
            }
        }
    )
in
    grp
Click to expand...

here is a sample data:

Book1
B
2Record 1:
329-Feb-24
402:03
5Print Audit
6Station:JFK
7A/C FAA Reg #:N174DN
8Ship #:000174
9Date:2024-02-29
10Measure applied to flight: FULL
11International Mainline Stations Only (Not DC Flights)
12Outbound Flight Number:0742
13Flight Origin:JFK
14Flight Destination:LAX
15Aircraft Interior02:03
16Completed Date:
172/29/2024
18Notes:
19Search Agents:
20BIANCA,YOUNG DE DE LEON; GAIL,GUY JONES; GEURY,ESPINAL VARGAS; YAHAIRA,FERNANDEZ; YANDEL,VEGA; SETH,PLAZA; TAYLOR,TROYE; Jamani,Barnell; JENNA,HAMILTON;
21Aircraft Exterior06:10
22Completed Date:
232/29/2024
24Notes:
25Search Agents:
26kino,galbraith;
27Aircraft Baggage / Cargo Bins06:09
28Completed Date:
292/29/2024
30Notes:
31Search Agents:
32kino,galbraith;
33Record 2:
3428-Feb-24
3521:40
36Print Audit
37Station:JFK
38A/C FAA Reg #:N177DN
39Ship #:000177
40Date:2024-02-29
41Measure applied to flight: FULL
42International Mainline Stations Only (Not DC Flights)
43Outbound Flight Number:0926
44Flight Origin:JFK
45Flight Destination:LAX
46Aircraft Interior21:43
47Completed Date:
482/28/2024
49Notes:
50Search Agents:
51WATSON,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 ;
52Record 3:
5329-Feb-24
5413:56
55Print Audit
56Station:JFK
57A/C FAA Reg #:N189DN
58Ship #:000189
59Date:2024-02-29
60Measure applied to flight: FULL
61International Mainline Stations Only (Not DC Flights)
62Outbound Flight Number:2170
63Flight Origin:JFK
64Flight Destination:ATL
65Aircraft Interior13:56
66Completed Date:
672/29/2024
68Notes:
69Search Agents:
70ALVES,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;
71Record 4:
7228-Feb-24
7320:47
74Print Audit
75Station:JFK
76A/C FAA Reg #:N190DN
77Ship #:000190
78Date:2024-02-29
79Measure applied to flight: FULL
80International Mainline Stations Only (Not DC Flights)
81Outbound Flight Number:0567
82Flight Origin:JFK
83Flight Destination:LAX
84Aircraft Interior20:47
85Completed Date:
862/28/2024
87Notes:
88Search Agents:
89AQUILLA,SYKES; GLENDON,COSBERT; Chelsea,Marshall; RAYSHAN,RAMSEY; Tim,CHARIAH; Thomas,Smith Sam; SONJI,RANDALL; Michael,Tannis; KEIDON,RICHENS; JOHANNA,SUE;
90Aircraft Exterior07:48
91Completed Date:
922/29/2024
93Notes:
94Search Agents:
95timothy,rodney;
96Aircraft Baggage / Cargo Bins07:48
97Completed Date:
982/29/2024
99Notes:
100Search Agents:
101timothy,rodney;
102Record 5:
10329-Feb-24
10407:32
105Print Audit
106Station:JFK
107A/C FAA Reg #:N194DN
108Ship #:000194
109Date:2024-02-29
110Measure applied to flight: FULL
111International Mainline Stations Only (Not DC Flights)
112Outbound Flight Number:0773
113Flight Origin:JFK
114Flight Destination:LAX
115Aircraft Interior07:32
116Completed Date:
1172/29/2024
118Notes:
119Search Agents:
120SHELLYANN,GUTHRIE-MONTAQUE; JUANITA,ABREU; Julie,Cole; Deidre,Anderson; Nicole R,Sullivan; HILBERTO,USHER HOLMES; NATALIE,TABLADA; DORCIA,Williams; NELSON,RAYMOND; SHARLINE,WILLIAMS; KESIA,THOMAS;
121Aircraft Exterior09:10
122Completed Date:
1232/29/2024
124Notes:
125Search Agents:
126Kwame,Fredericks;
127Aircraft Baggage / Cargo Bins09:05
128Completed Date:
1292/29/2024
130Notes:
131Search Agents:
132Kwame,Fredericks;
133Record 6:
13428-Feb-24
13521:22
136Print Audit
137Station:JFK
138A/C FAA Reg #:N693DL
139Ship #:000693
140Date:2024-02-29
141Measure applied to flight: FULL
142International Mainline Stations Only (Not DC Flights)
143Outbound Flight Number:1887
144Flight Origin:JFK
145Flight Destination:SXM
146Aircraft Interior21:22
147Completed Date:
1482/28/2024
149Notes:
150Search Agents:
151ARBELINA,HUNT; Avendra,Jienarine; Tianna,Thomas; LAWRENCE,SUMMER JR; Brian,Fernandez; PHILIP,DUNCAN; HASAN ,RAQUIBUL; OLIVER ,OSSE KARL ;
152Aircraft Exterior08:26
153Completed Date:
1542/29/2024
155Notes:
156Search Agents:
157mario,johnson;
158Aircraft Baggage / Cargo Bins08:26
159Completed Date:
1602/29/2024
161Notes:
162Search Agents:
163mario,johnson;
164Record 7:
16528-Feb-24
16620:10
167Print Audit
168Station:JFK
169A/C FAA Reg #:N102DN
170Ship #:001002
171Date:2024-02-29
172Measure applied to flight: FULL
173International Mainline Stations Only (Not DC Flights)
174Outbound Flight Number:2383
175Flight Origin:JFK
176Flight Destination:RSW
177Aircraft Interior20:10
178Completed Date:
1792/28/2024
180Notes:
181Search Agents:
182SAVITA,RAMBARAN; DALLISCA,massiah; Selencia,Tannis; ALSOPP,COLIN; DAMION,CLARKE; CLIVE,ROBINSON; SHONETTE,SCHWARTZ; SHEIK,RASHED;
183Aircraft Exterior11:59
184Completed Date:
1852/29/2024
186Notes:
187Search Agents:
188raymond,burgos;
189Aircraft Baggage / Cargo Bins11:59
190Completed Date:
1912/29/2024
192Notes:
193Search Agents:
194raymond,burgos;
195Record 8:
19629-Feb-24
19704:43
198Print Audit
199Station:JFK
200A/C FAA Reg #:N111DC
201Ship #:001011
202Date:2024-02-29
203Measure applied to flight: FULL
204International Mainline Stations Only (Not DC Flights)
205Outbound Flight Number:2556
206Flight Origin:JFK
207Flight Destination:MIA
208Aircraft Interior04:43
209Completed Date:
2102/29/2024
211Notes:
212Search Agents:
213Michelle V,Duncan; LUC BOY,CONSTANT; Kshanni,Knight; ELAINE,FRAY-BERRY; ANNMARIE,DACOSTA; PETER,MARIMBI; BONNILYN,JEFFERY; GAVIN,Williams; KATALI,WUMPINI;
214Aircraft Exterior06:10
215Completed Date:
2162/29/2024
217Notes:
218Search Agents:
219Nigel,Spencer;
220Aircraft Baggage / Cargo Bins06:10
221Completed Date:
2222/29/2024
223Notes:
224Search Agents:
225Nigel,Spencer;
226Record 9:
22729-Feb-24
22807:58
229Print Audit
230Station:JFK
231A/C FAA Reg #:N169DZ
232Ship #:001601
233Date:2024-02-29
234Measure applied to flight: FULL
235International Mainline Stations Only (Not DC Flights)
236Outbound Flight Number:0253
237Flight Origin:JFK
238Flight Destination:BOG
239Aircraft Interior07:57
240Completed Date:
2412/29/2024
242Notes:
243Search Agents:
244Deloris H,Freeman; Yvonne,Griffith; ESTEFANI,Rosario; James,LEFEVRE; Sheryl,Lavia; Norma J,Joseph; SHARMILA,RAMPERSAUD; Craig,Lecky; Mark,Prescott; Prisca,Sargeant; DAWN,DAVIS;
245Record 10:
24629-Feb-24
24707:45
248Print Audit
249Station:JFK
250A/C FAA Reg #:N827MH
251Ship #:001803
252Date:2024-02-29
253Measure applied to flight: FULL
254International Mainline Stations Only (Not DC Flights)
255Outbound Flight Number:0106
256Flight Origin:JFK
257Flight Destination:FRA
258Aircraft Interior07:45
259Completed Date:
2602/29/2024
261Notes:
262Search Agents:
263MALICA,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 ;
264Record 11:
26528-Feb-24
26613:57
267Print Audit
268Station:JFK
269A/C FAA Reg #:N834MH
270Ship #:001810
271Date:2024-02-29
272Measure applied to flight: FULL
273International Mainline Stations Only (Not DC Flights)
274Outbound Flight Number:1810
275Flight Origin:JFK
276Flight Destination:QSM
277Aircraft Interior13:57
278Completed Date:
2792/28/2024
280Notes:
281Search Agents:
282Tarwattie,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;
283Record 12:
28429-Feb-24
28504:43
286Print Audit
287Station:JFK
288A/C FAA Reg #:N835MH
289Ship #:001811
290Date:2024-02-29
291Measure applied to flight: FULL
292International Mainline Stations Only (Not DC Flights)
293Outbound Flight Number:0227
294Flight Origin:JFK
295Flight Destination:GRU
296Aircraft Interior00:40
297Completed Date:
2982/29/2024
299Notes:
300Search Agents:
301NELSEY,DOMINGUEZ;CHRISPIN,CALLISTE;KIRAN,BARUA;MICKEL,SMITH;PAMELA,CHOY;ROSEMARIE,TURNER;SASHEKA,JAMES;SAYPULU,TURAY;YAKEEMA,COOTE;
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
adding extra lines to query removed all steps
Replies
8
Views
374
RoryA
RoryA
R
  • Question Question
Power Query Help to Calculate Stock Splits
Replies
0
Views
157
rasinc
R
D
  • Question Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
2K
Matt Allington
M
muhammad susanto
  • Question Question
PQ : New column in source table not appearing in dataset
Replies
1
Views
536
Automatrix
A
ExcelAtEverything
  • Solved
Fill In Missing Dates in Power Query
Replies
2
Views
5K
ExcelAtEverything
ExcelAtEverything

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top