Conditional combine

[kulkarni21vinee]

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"

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share datai in a format that can be pasted in an MS Excel file.

[kulkarni21vinee]

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)

[Ashish_Mathur]

I cannot understand anything from the data that you have shared.