Combine or Merge Columns if the Column Header contains "Type"

[BI_Analyticz]

Hi I have a dataset like below. I can manually merge the columns to make it as one. Is it possible to write a condition to merge the columns.

Example: If the column header name contains Type then Merge all those columns to create a new column.

Col 1	Col 2	Col 3	Type-123	Type-456	Col 8	Col 10

[BI_Analyticz]

@CNENFRNL Kindly help

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRS0lGCIBOfYBDp7O0CpCx9gIS/v79SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col 1" = _t, #"Type-000" = _t, #"Type-001" = _t, #"Type-123" = _t, #"Type-456" = _t, #"Col 8" = _t, #"Col 10" = _t]),
    #"Combined Columns" = Table.CombineColumns(Source, List.Select(Table.ColumnNames(Source), each Text.Contains(_, "Type")), each Text.Combine(List.RemoveItems(_, {null, ""}), "|"), "Combined")
in
    #"Combined Columns"

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRS0lHycQwDkiaGLiDSJxhEOnuDOJY+QMLf318pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col 1" = _t, #"Col 2" = _t, #"Col 3" = _t, #"Type-123" = _t, #"Type-456" = _t, #"Col 8" = _t, #"Col 10" = _t]),
    #"Combined Columns" = Table.CombineColumns(Source, List.Select(Table.ColumnNames(Source), each Text.Contains(_, "Type")), Combiner.CombineTextByDelimiter("|", QuoteStyle.Csv), "Combined")
in
    #"Combined Columns"

[BI_Analyticz]

This is working but I am getting ||||4LS|4CKD since I have null for the previous columns (with Type headers). Can we add a condition to avoid null cells.