User Profile
kulkarni21vinee
Frequent Visitor
Joined 1 year ago
User Widgets
Contributions
Re: Conditional combine
MEL 3293550 EMK WAS OPENED AND USED | MEL 3294156 ATTENDANT CREW REST AIR IS TOO DRY | MEL 3295182 HUMIDITY CONTROL SYSTEM TEST FOUND FWD ZONAL DRYER HAS AN INTERNAL FAULT | DIP 9864630 PAINT PEELED FROM RIGHT WING ROOT AND SCRATCHED (3.00 I) | EA 0521-01000 OP10 GENX CUSHIONED LOOP CLAMP INSP *RIR* (22.00 L) | #2 RH DOOR SLIDE RPL *ACP RENDER SAFE CKLIST* (18.00 S) | B787-9 UMD SOFTWARE RESTORE (5.00 I) | OPEN-O 3297052 1L STUCK IN ACCESAB ILITY MODE (0|0|0|0) | OPEN-O 3303465 SCHED WORKLOAD IS ASSIGNED TO BE ACCOMPLISHED IN EWR ON 24DEC24. PERFORMING A MRD CHECK MAY BE REQUIRED TO INSURE NO OUTSTANDING WORKLOAD REMAINS, BEFORE CLEARING THIS LOGPAGE. IF WORKLOAD IS UNABLE TO BE COMPLETED, CONTACT PLANNING FOR RESCHEDULING (0|0|0|0)Conditional combine
I would like to combine rows based upon text in List records. E.g. All items should be combined by pipe delimiter, however when text is OPEN-O, these line items should be on new line as delimiter. Currently all are combined with pipe, expected result in screenshot below Note: WorkLoadDetails is List as shown below and alternatively can suggest conditional grouping as well for OPEN-O items instead of while combining. M query for reference: let Source = Json.Document( Web.Contents( "WebAPI endpoint URL" ) ), StationWorkLoadInfoList = Source[StationWorkLoadInfoList], StationWorkLoadInfoList1 = StationWorkLoadInfoList{0}, #"Converted to Table" = Table.FromList( StationWorkLoadInfoList, Splitter.SplitByNothing(), null, null, ExtraValues.Error ), #"Expanded Column1" = Table.ExpandRecordColumn( #"Converted to Table", "Column1", { "ArrivalDate", "ArrivalGate", "ArrivingFromCity", "DepartureCity", "DepartureFlightNumber", "DepartureGate", "DeptDate", "FlightNumber", "GroundTime", "IsRON", "LocationName", "ManHours", "ServiceCheck", "Station", "TailNumber", "WorkLoadDetails" }, { "ArrivalDate", "ArrivalGate", "ArrivingFromCity", "DepartureCity", "DepartureFlightNumber", "DepartureGate", "DeptDate", "FlightNumber", "GroundTime", "IsRON", "LocationName", "ManHours", "ServiceCheck", "Station", "TailNumber", "WorkLoadDetails" } ), #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([IsRON] = true)), #"Expanded WorkLoadDetails" = Table.ExpandListColumn(#"Filtered Rows", "WorkLoadDetails"), #"Expanded WorkLoadDetails1" = Table.ExpandRecordColumn( #"Expanded WorkLoadDetails", "WorkLoadDetails", { "DefPosCode", "IsDash8", "MaintenanceDescription", "MaintenanceItem", "MxComments", "TimeRemaining", "WorkItemType" }, { "DefPosCode", "IsDash8", "MaintenanceDescription", "MaintenanceItem", "MxComments", "TimeRemaining", "WorkItemType" } ), #"Trimmed Text" = Table.TransformColumns( #"Expanded WorkLoadDetails1", {{"DefPosCode", Text.Trim, type text}} ), #"Filtered Rows1" = Table.SelectRows( #"Trimmed Text", each not Text.StartsWith([MaintenanceDescription], "SC1") and not Text.StartsWith([MaintenanceDescription], "1SC") and not Text.StartsWith([MaintenanceDescription], "SC2") and not Text.StartsWith([MaintenanceDescription], "2SC") ), #"Added Conditional Column" = Table.AddColumn( #"Filtered Rows1", "SortOrder", each if [WorkItemType] = "LP" and [DefPosCode] = "OPEN-O" then 3 else if [IsDash8] = false and [DefPosCode] <> null and [DefPosCode] <> "OPEN-O" then 1 else 2 ), #"Sorted Rows" = Table.Sort(#"Added Conditional Column", {{"SortOrder", Order.Ascending}}), #"Added Conditional Column1" = Table.AddColumn( #"Sorted Rows", "CustomMaintenanceItem", each if [WorkItemType] = "LP" then [MaintenanceItem] else "" ), #"Added Conditional Column2" = Table.AddColumn( #"Added Conditional Column1", "CustomMaintenanceDescription", each if (Record.Field(_, "MxComments") <> null and Record.Field(_, "MxComments") <> "") then Record.Field(_, "MxComments") else Record.Field(_, "MaintenanceDescription") ), #"Added Conditional Column3" = Table.AddColumn( #"Added Conditional Column2", "CustomTimeRemaining", each if (Record.Field(_, "TimeRemaining") <> null and Record.Field(_, "TimeRemaining") <> "") then "" & "(" & Record.Field(_, "TimeRemaining") & ")" else null ), #"Added Conditional Column4" = Table.AddColumn( #"Added Conditional Column3", "CustomDefPosCode", each if [WorkItemType] = "LP" then [DefPosCode] else "" ), #"Merged Columns" = Table.CombineColumns( #"Added Conditional Column4", { "CustomDefPosCode", "CustomMaintenanceItem", "CustomMaintenanceDescription", "CustomTimeRemaining" }, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Merged" ), #"Grouped Rows" = Table.Group( #"Merged Columns", {"TailNumber"}, { { "AllData", each _, type table [ ArrivalDate = text, ArrivalGate = text, ArrivingFromCity = text, DepartureCity = text, DepartureFlightNumber = text, DepartureGate = text, DeptDate = text, FlightNumber = text, GroundTime = number, IsRON = logical, LocationName = text, ManHours = number, ServiceCheck = text, Station = text, TailNumber = text, DefPosCode = nullable text, IsDash8 = logical, MxComments = nullable text, Merged = text, WorkItemType = text, SortOrder = number ] } } ), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WorkLoadDetails", each [AllData][Merged]), #"Extracted Values" = Table.TransformColumns( #"Added Custom", {"WorkLoadDetails", each Text.Combine(List.Transform(_, Text.From), " | "), type text} ), #"Expanded AllData" = Table.ExpandTableColumn( #"Extracted Values", "AllData", { "ArrivalDate", "ArrivalGate", "ArrivingFromCity", "DepartureCity", "DepartureFlightNumber", "DepartureGate", "DeptDate", "FlightNumber", "GroundTime", "LocationName", "ManHours", "ServiceCheck" }, { "ArrivalDate", "ArrivalGate", "ArrivingFromCity", "DepartureCity", "DepartureFlightNumber", "DepartureGate", "DeptDate", "FlightNumber", "GroundTime", "LocationName", "ManHours", "ServiceCheck" } ) in #"Expanded AllData"Find, If its not a last record
I wanted to append delimiter | (pipe symbol) to differentiate each Maintenance Record, of a List, unless it’s not last record Also, specific Maintaince Records would be filtering out based upon if-else conditions. e.g.: with below M query, when I loop through each Maintenance Description it append an unnecessary | (pipe symbol) at the end. #"Extracted Values" = Table.TransformColumns( #"Filtered Rows", { "WorkLoadDetails", each Text.Combine( List.Transform( _, each ( if not Text.StartsWith(Record.Field(_, "MaintenanceDescription"), "Specific Code") then Record.Field(_, "MaintenanceDescription") else "" )& ( if not Text.StartsWith(Record.Field(_, "MaintenanceDescription"), "Specific Code") then "|" else "" ) ) ), type text } ) in #"Extracted Values" My need is to remove remove extra pipe delimiter, which gets added after last record for each row (sample example shown with 3 red arrows in second screenshot).
