let
tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FD = Table.FillDown(tbl1,{"Description"}),
TSR = Table.SelectRows(FD, each ([#"Accessories Return/ Fault"] <> null)),
TRC = Table.RenameColumns(TSR,{{"Accessories Return/ Fault", "AccessoriesReturnFault"}}),
TG = Table.Group(TRC, {"Case ID", "Description"}, {{"Count", each _, type table [Case ID=text, Description=nullable text, AccessoriesReturnFault=nullable text]}}),
TAC = Table.AddColumn(TG, "Fault(s)", each List.Distinct([Count][AccessoriesReturnFault])),
TTC = Table.TransformColumns(TAC, {"Fault(s)", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
RC = Table.RemoveColumns(TTC,{"Count"}),
tbl1D = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TSC = Table.SelectColumns(tbl1D,{"Description"}),
Join = Table.NestedJoin(TSC, {"Description"}, RC, {"Description"}, "Table", JoinKind.FullOuter),
RCJ = Table.RemoveColumns(Join,{"Description"}),
ExpJ = Table.ExpandTableColumn(RCJ, "Table", {"Case ID", "Description", "Fault(s)"}, {"Case ID", "Description", "Fault(s)"}),
FDJ = Table.FillDown(ExpJ,{"Case ID"})
in
FDJ
Bookmarks